Set secondary category axis tick labels' position in Java

Aspose.Cells Product Family
1

Hi, I am trying to set the tick label position for secondary axis, but it is not getting set. Below is the code that I am trying to set TickLabelPositionType.NEXT_TO_AXIS to a secondary axis, but when I open the xls file it not getting set.

Sample code.

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
worksheets.removeAt(0);
Worksheet dataSheet = worksheets.add("Data");
Worksheet chartSheet = worksheets.get(worksheets
.add(SheetType.CHART));
chartSheet.setName("Chart");
worksheets.setActiveSheetIndex(chartSheet.getIndex());

Chart chart = chartSheet.getCharts().get(
chartSheet.getCharts().add(ChartType.COLUMN_STACKED, 0, 0,
50, 50));

Cells cells = dataSheet.getCells();

cells.get("A1").setValue("Product Name");
cells.get("B1").setValue("Quarter 1");
cells.get("C1").setValue("Quarter 2");
cells.get("D1").setValue("Quarter 3");
cells.get("E1").setValue("Quarter 4");
cells.get("A2").setValue("Product 1");
cells.get("B2").setValue(33);
cells.get("C2").setValue(21);
cells.get("D2").setValue(35);
cells.get("E2").setValue(22);
cells.get("A3").setValue("Product 2");
cells.get("B3").setValue(17);
cells.get("C3").setValue(54);
cells.get("D3").setValue(17);
cells.get("E3").setValue(60);
cells.get("A4").setValue("Product 3");
cells.get("B4").setValue(50);
cells.get("C4").setValue(25);
cells.get("D4").setValue(48);
cells.get("E4").setValue(18);
cells.get("A5").setValue("Avg Line");
cells.get("B5").setValue(0);
cells.get("C5").setValue(0);
cells.get("D5").setValue(0);
cells.get("E5").setValue(0);

chart.getNSeries().setCategoryData("=Data!B1:E1");

int index = chart.getNSeries().add("=Data!B2:E2", false);
chart.getNSeries().get(index).setName("=Data!A2");

index = chart.getNSeries().add("=Data!B3:E3", false);
chart.getNSeries().get(index).setName("=Data!A3");

index = chart.getNSeries().add("=Data!B4:E4", false);
chart.getNSeries().get(index).setName("=Data!A4");

index = chart.getNSeries().add("=Data!B5:E5", false);
chart.getNSeries().get(index).setXValues("=Data!A1:A4");
chart.getNSeries().get(index).setName("=Data!A5");
chart.getNSeries().get(index).setType(ChartType.LINE);
chart.getNSeries().get(index).setPlotOnSecondAxis(true);

chart.getCategoryAxis().setVisible(false);
chart.getSecondCategoryAxis().setVisible(true);
chart.getSecondCategoryAxis().setTickLabelPosition(
TickLabelPositionType.NEXT_TO_AXIS);
chart.getSecondValueAxis().setPlotOrderReversed(true);

workbook.save("stacekd bar second axis.xls");

But this problem happens, only when setPlotOrderReversed() is set to 'true' for the second value axis. when this line is commented, the tick label position is getting set properly.,

I wanted to show the secondary category axis, in the chart.

Any help would be appreciated?

Regards

Sundhar

2

Hi,

Please provide us your actual output and expected output xls files and the screenshot highlighting the problem with red circles.

You can created expected output using Ms-Excel manually and you can create a screenshot using Ms-Paint.

We will look into it and help you asap.

3

Hi,

If you execute this program,

you will find from the generated xls file, the secondary category axis -> tick label position is set to none, which should not be the case, 'coz this program has statement to set the secondary category axis label position to NEXT_TO_AXIS.

If I comment the line chart.getSecondValueAxis().setPlotOrderReversed(true) -> then the above value is getting set correctly..,

I am out of station, unable to generated the xls and screenshot files.

Can you pls try from the above steps to reproduce this problem..

Regards

Sundhar

4

Hi,

I have tried your code using the latest version:
Aspose.Cells
for Java v7.1.0.4 and it looks fine to me.

Please see the output xls file generated by this code and the screenshot.

Java


String path = “F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook();

WorksheetCollection worksheets = workbook.getWorksheets();


worksheets.removeAt(0);

Worksheet dataSheet = worksheets.add(“Data”);

Worksheet chartSheet = worksheets.get(worksheets

.add(SheetType.CHART));

chartSheet.setName(“Chart”);

worksheets.setActiveSheetIndex(chartSheet.getIndex());


Chart chart = chartSheet.getCharts().get(

chartSheet.getCharts().add(ChartType.COLUMN_STACKED, 0, 0,

50, 50));


Cells cells = dataSheet.getCells();


cells.get(“A1”).setValue(“Product Name”);

cells.get(“B1”).setValue(“Quarter 1”);

cells.get(“C1”).setValue(“Quarter 2”);

cells.get(“D1”).setValue(“Quarter 3”);

cells.get(“E1”).setValue(“Quarter 4”);

cells.get(“A2”).setValue(“Product 1”);

cells.get(“B2”).setValue(33);

cells.get(“C2”).setValue(21);

cells.get(“D2”).setValue(35);

cells.get(“E2”).setValue(22);

cells.get(“A3”).setValue(“Product 2”);

cells.get(“B3”).setValue(17);

cells.get(“C3”).setValue(54);

cells.get(“D3”).setValue(17);

cells.get(“E3”).setValue(60);

cells.get(“A4”).setValue(“Product 3”);

cells.get(“B4”).setValue(50);

cells.get(“C4”).setValue(25);

cells.get(“D4”).setValue(48);

cells.get(“E4”).setValue(18);

cells.get(“A5”).setValue(“Avg Line”);

cells.get(“B5”).setValue(0);

cells.get(“C5”).setValue(0);

cells.get(“D5”).setValue(0);

cells.get(“E5”).setValue(0);


chart.getNSeries().setCategoryData(“=Data!B1:E1”);


int index = chart.getNSeries().add(“=Data!B2:E2”, false);

chart.getNSeries().get(index).setName(“=Data!A2”);


index = chart.getNSeries().add(“=Data!B3:E3”, false);

chart.getNSeries().get(index).setName(“=Data!A3”);


index = chart.getNSeries().add(“=Data!B4:E4”, false);

chart.getNSeries().get(index).setName(“=Data!A4”);


index = chart.getNSeries().add(“=Data!B5:E5”, false);

chart.getNSeries().get(index).setXValues(“=Data!A1:A4”);

chart.getNSeries().get(index).setName(“=Data!A5”);

chart.getNSeries().get(index).setType(ChartType.LINE);

chart.getNSeries().get(index).setPlotOnSecondAxis(true);


chart.getCategoryAxis().setVisible(false);

chart.getSecondCategoryAxis().setVisible(true);

chart.getSecondCategoryAxis().setTickLabelPosition(

TickLabelPositionType.NEXT_TO_AXIS);

chart.getSecondValueAxis().setPlotOrderReversed(true);


workbook.save(path + “.out.xls”);

workbook.save(path + “.out.xlsx”);


Screenshot:

5

Hi,

I tried with Aspose.cells.7.1.0 release, which I was thinking as the latest offical relese, Is that not so? Please try with this release and see if you find this issue?

I dont see any official release made for Aspose.cells.7.1.0.4. Moreover, in the downloads page, I dont find this release link.

Regards

Sundhar

6

Hi,

This is a MINOR release. Please download it from this link: Aspose.Cells
for Java v7.1.0.4

Please first download the MAJOR release:

Aspose.Cells for Java 7.1.0 then update the aspose.cells jar with the MINOR release.

The minor release contains all the previous fixes, so it can be used for production environment.

7

Hi,

I treid with the new release, but this has also the same issue but in other parameters.

Now, the TickLabelPosition.NEXT_TO_AXIS is getting set, but

chart.getSecondValueAxis().setPlotOrderReversed(true); -> this is not getting set. If you open the attacehd xls file you can notice the second value axis "values in reverse order" is not checked, but it has to set with above statement.

Could you pls clarify more on this?

Regards

Sundhar

8

Hi,

I need your expected output file.

Please open the source.xlsx.out.xls into Ms-Excel and then modify it and post the file again.

9

Hi, I have attached the updated xls along with this..

I checked the "Values in reverse order of the second value axis"

Regards

Sundhar

10

Hi,

Thanks for your file.

We will look into it and get back to you asap.

11

Hi,

Please try the following code.

We add the following segment in the code you provided us.

Java


chart.getSecondValueAxis().setVisible(true);
chart.getSecondValueAxis().setPlotOrderReversed(true);

//We find it is necessary to set the ticklabel position of the first category axis to none.
chart.getCategoryAxis().setTickLabelPosition(TickLabelPositionType.NONE);



12

Hi, sorry for the delay.

Thanks for the suggesstions. It works fine now.

Regards

Sundhar