Issue with secondary plot axis positioning

Aspose.Cells Product Family
1

Hi
PFA the below code.
The secondary axis comes to the right of the graph.
I would like it to come on top of the chart.
PFA attached output xls also.

public static void testAsposeSecondaryAxis() throws Exception
{
//Instantiating a Workbook object
Workbook workbook = new Workbook();
//Adding a new worksheet to the Excel object
int sheetIndex = workbook.getWorksheets().addSheet().getIndex();
//Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.getWorksheets().getSheet(sheetIndex);
//Adding a sample value to “A1” cell
worksheet.getCells().getCell(“A1”).setValue(“A”);
worksheet.getCells().getCell(“A2”).setValue(“B”);
worksheet.getCells().getCell(“A3”).setValue(“C”);

worksheet.getCells().getCell(“B1”).setValue(50);
//Adding a sample value to “A2” cell
worksheet.getCells().getCell(“B2”).setValue(100);
//Adding a sample value to “A3” cell
worksheet.getCells().getCell(“B3”).setValue(150);
//Adding a sample value to “B1” cell
worksheet.getCells().getCell(“C1”).setValue(4);
//Adding a sample value to “B2” cell
worksheet.getCells().getCell(“C2”).setValue(20);
//Adding a sample value to “B3” cell
worksheet.getCells().getCell(“C3”).setValue(50);
//Adding a chart to the worksheet
Chart chart = worksheet.getCharts().addChart(ChartType.BAR_CLUSTERED, 5, 0, 25, 5);
chart.getPlotArea().getArea().setForegroundColor(new Color(0xE0,0xE6,0xF8));
//Accessing the instance of the newly added chart
//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B3”
chart.getNSeries().add(“B1:B3”, true);
chart.getNSeries().add(“C1:C3”, true);
NSeries w_nSeries = chart.getNSeries();
ASeries w_aSeries = w_nSeries.get(0);
w_aSeries.setType(ChartType.BAR_CLUSTERED);
//Set the max value of value axis
chart.getValueAxis().setMaxValue(200);
//Set the min value of value axis
chart.getValueAxis().setMinValue(0);
//Set the major unit
chart.getValueAxis().setMajorUnit(25);
//Set he number of categories or series between tick-mark labels.
chart.getCategoryAxis().setTickLabelSpacing(2);
//setup the secondary axis
w_aSeries = w_nSeries.get(1);
w_aSeries.setType(ChartType.LINE);
w_aSeries.setPlotOnSecondAxis(true);
chart.getSecondValueAxis().setVisible(true);

w_nSeries.setCategoryData(“A1:A3”);
//Saving the Excel file
workbook.save(“C:\temp\book1.xls”);
}

2

Hi,

Thanks for your posting and using Aspose.Cells for Java.

I have attached the output file generated by your code with the older version. Kindly download it and fix it manually using Ms-Excel and renamed as expected and attach it again after setting the secondary axis correctly as you desired.

Attachment: book1.xls (attached)
Attachment: book1_expected.xls (needed)

We will look into your expected output xls file and then help you with sample code.

Java


String DirPath = “F:\Shak-Data-RW\Downloads\”;


//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Adding a new worksheet to the Excel object

int sheetIndex = workbook.getWorksheets().addSheet().getIndex();

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.getWorksheets().getSheet(sheetIndex);

//Adding a sample value to “A1” cell

worksheet.getCells().getCell(“A1”).setValue(“A”);

worksheet.getCells().getCell(“A2”).setValue(“B”);

worksheet.getCells().getCell(“A3”).setValue(“C”);


worksheet.getCells().getCell(“B1”).setValue(50);

//Adding a sample value to “A2” cell

worksheet.getCells().getCell(“B2”).setValue(100);

//Adding a sample value to “A3” cell

worksheet.getCells().getCell(“B3”).setValue(150);

//Adding a sample value to “B1” cell

worksheet.getCells().getCell(“C1”).setValue(4);

//Adding a sample value to “B2” cell

worksheet.getCells().getCell(“C2”).setValue(20);

//Adding a sample value to “B3” cell

worksheet.getCells().getCell(“C3”).setValue(50);

//Adding a chart to the worksheet

Chart chart = worksheet.getCharts().addChart(ChartType.BAR_CLUSTERED, 5, 0, 25, 5);

chart.getPlotArea().getArea().setForegroundColor(new Color(0xE0,0xE6,0xF8));

//Accessing the instance of the newly added chart

//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B3”

chart.getNSeries().add(“B1:B3”, true);

chart.getNSeries().add(“C1:C3”, true);

NSeries w_nSeries = chart.getNSeries();

ASeries w_aSeries = w_nSeries.get(0);

w_aSeries.setType(ChartType.BAR_CLUSTERED);

//Set the max value of value axis

chart.getValueAxis().setMaxValue(200);

//Set the min value of value axis

chart.getValueAxis().setMinValue(0);

//Set the major unit

chart.getValueAxis().setMajorUnit(25);

//Set he number of categories or series between tick-mark labels.

chart.getCategoryAxis().setTickLabelSpacing(2);

//setup the secondary axis

w_aSeries = w_nSeries.get(1);

w_aSeries.setType(ChartType.LINE);

w_aSeries.setPlotOnSecondAxis(true);

chart.getSecondValueAxis().setVisible(true);


w_nSeries.setCategoryData(“A1:A3”);

//Saving the Excel file

workbook.save(DirPath + “book1.xls”);

Screenshot:
3

Hi,
Please find attached expected excel

4

Hi,

Thanks for your posting and using Aspose.Cells for Java.

It seems like there is some bug or probably, this feature is not support. We need to investigate this issue further.

We have logged this issue in our database. We will look into it and resolve it or provide you a sample code.

This issue has been logged as CELLSJAVA-40403.

5

Hi,

Thanks for your posting and using Aspose.Cells for Java.

We may set the reserve of related axis to change position of the axis’s label. e.g. Change value axis by chart.getCategoryAxis().setReversed(true/false) (In latest version, the method is setPlotOrderReversed())

Please download and try this fix: Aspose.Cells for Java v7.3.4.4 and let us know your feedback.