Chart Axis Options

Aspose.Cells Product Family
1

Hi,


I was asked to do two settings on our charts. In Excel they are found here:

1. category axis/Format Axis/Axis Options/Interval between labels: "Automatic"

2. value axis/Format Axis/Number/Decimal places (the default comes from the chart table, however such precision is not needed in the chart)

Can I do these in Aspose?

thanks,
Laszlo Borsos
MSCI Barra

2

Hi Laszlo,

Thank you for considering Aspose.

kuvera:

1. category axis/Format Axis/Axis Options/Interval between labels: "Automatic"

By default, Interval between Label is automatic. You can use Chart.CategoryAxis.TickLabelSpacing to set the interval between labels.
kuvera:

2. value axis/Format Axis/Number/Decimal places (the default comes from the chart table, however such precision is not needed in the chart)

You can set the Number Format as per your requirement by using Chart.CategoryAxis.TickLabels.Number OR Chart.CategoryAxis.TickLabels.NumberFormat.
Thank You & Best Regards,
3

Hi,


Thank for you answer.
1. With the default in Excel I get “Specify interval unit: 1”. setTickLabelSpacing() accepts an int argument and it’s hard to judge programmatically what is the right value for our arbitrary data points. Please check that Automatic is not applied by default. I will attach some code. Is there a value I could pass that method which applies the “Automatic” setting?

2. In Java API I cannot see getTickLabels(), only methods related to tick label position and spacing.

regards,
Laszlo

4

Hi,


Thank for your answer.

1. With the default in Excel I get “Specify interval unit: 1”. setTickLabelSpacing() accepts an int argument and it’s hard to judge programmatically what is the right value for our arbitrary data points. Please check that Automatic is not applied by default. I will attach some code. Is there a value I could pass that method which applies the “Automatic” setting?

2. In Java API I cannot see getTickLabels(), only methods related to tick label position and spacing.

regards,
Laszlo

5

Hi,

Thank You for considering Aspose.

kuvera:

  1. With the default in Excel I get “Specify interval unit: 1”. setTickLabelSpacing() accepts an int argument and it’s hard to judge programmatically what is the right value for our arbitrary data points. Please check that Automatic is not applied by default. I will attach some code. Is there a value I could pass that method which applies the “Automatic” setting?

Well, the “Automatic” option is not available in MS Excel 2003. If you create a file in Excel 2003, the default interval unit will be set to one (if you don’t specify any interval). Where as if you don’t specify any interval in Excel 2007 (Xlsx), the interval will be set as automatic and it will be equal to 1 as per the default of Excel 2003 generated file. Currently, you cannot set the value “Automatic”, if you need to set the default value (1 for Excel 2003 Xls and Automatic for Excel 2007 Xlsx) then you don’t need to specify the setTickLabelSpacing() in your code.

kuvera:

  1. In Java API I cannot see getTickLabels(), only methods related to tick label position and spacing.

In Java you may try chart.getCategoryAxis().setNumber(int) OR chart.getCategoryAxis().setNumberFormat(string) as per your requirement.

Thank You & Best Regards,

6

Thanks for your tips.

7

Hi,

I'm also in need to setting a chart to have Automatic value on interval between labels. I just did what was stated here. I didn't specify any ticklabelspacing, but the output in Excel2007 set the interval unit to 1. Is there another option here? I'm using aspose v5.1.4.

thanks!

8

Hi,


Are you using .NET version of the product. We recommend you to try our latest version/fix: Please download and try: Aspose.Cells for .NET v7.0.4.6

If you still find any issue with latest version/fix, give us your complete sample code (runnable) and template file to show the issue, we will check it soon.

Thank you.

9

I've tried using the new version 7.0.4.6, and I still have the same problem. It doesn't use the automatic button in Interval between labels. It computes a different number which i didn't know how it got it. here's my sample code.

Aspose.Cells.Workbook wb = new Workbook();

Aspose.Cells.Worksheet ws = wb.Worksheets[0];

Range range = ws.Cells.CreateRange(ws.Cells[1, 1].Name, ws.Cells[1000, 1].Name);

for (int row = 0; row < range.RowCount; row++)

range[row, 0].PutValue("Sample Data "+ row.ToString());

Range range2 = ws.Cells.CreateRange(ws.Cells[1, 2].Name, ws.Cells[1000, 2].Name);

for (int row = 0; row < range2.RowCount; row++)

range2[row, 0].PutValue(300);

Range range3 = ws.Cells.CreateRange(ws.Cells[1, 3].Name, ws.Cells[1000, 3].Name);

for (int row = 0; row < range3.RowCount; row++)

range3[row, 0].PutValue(Convert.ToDateTime("01/15/2012"));

int chartIndex = ws.Charts.Add(Aspose.Cells.Charts.ChartType.BarStacked, 1, 5, 1000*2, 50);

Aspose.Cells.Charts.Chart chart = ws.Charts[chartIndex];

chart.NSeries.Add(ws.Cells[1, 3].Name + ":" + ws.Cells[1000, 3].Name, true);

chart.NSeries.Add(ws.Cells[1, 2].Name + ":" + ws.Cells[1000, 2].Name, true);

chart.NSeries.CategoryData = ws.Cells[1, 1].Name + ":" + ws.Cells[1000, 1].Name;

//Hide the start date bar area

Series bar1 = chart.NSeries[0];

bar1.Area.Formatting = FormattingType.None;

bar1.Line.IsVisible = false;

Series bar2 = chart.NSeries[1];

bar2.Area.ForegroundColor = Color.Gray;

//Reverse the category data axis

chart.CategoryAxis.IsPlotOrderReversed = true;

chart.CategoryAxis.CategoryType = CategoryType.AutomaticScale;

chart.CategoryAxis.AxisBetweenCategories = true;

chart.CategoryAxis.DisplayUnitLabel.AutoScaleFont = true;

chart.CategoryAxis.TickLabels.AutoScaleFont = true;

chart.ValueAxis.CategoryType = CategoryType.TimeScale;

chart.ValueAxis.BaseUnitScale = TimeUnit.Days;

chart.ValueAxis.MajorUnit = 31;

chart.ValueAxis.MajorUnitScale = TimeUnit.Days;

chart.ValueAxis.TickLabels.NumberFormat = "[$-409]mmm/yyyy;@";

//Sets the Chart's starting date point and ending date point

chart.ValueAxis.MinValue = 40909;

chart.ValueAxis.MaxValue = 41275;

chart.PlotArea.Area.ForegroundColor = Color.White;

chart.ShowLegend = false;

wb.Save("C:\\p.xls", Aspose.Cells.FileFormatType.Excel97To2003);

Thanks!

10

Hi,


Could you create your expected “p.xls” file manually with your desired chart’s category/values axis’ formatting options in MS Excel and post it here, we will check it soon.

Thank you.