Plot column chart on secondary axis using Aspose.Cells for .NET in C#

Hello,


I am using Aspose-Cells version 7.5.2

I am having troubles creating double value axis chart.
Currently code creates chart where Bar and Line chart are combined. I want bar chart to use secondary value axis and line chart use primary value axis.

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

//Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();

//Accessing the instance of the newly added chart
int chartIndex = worksheet.getCharts().add(ChartType.COLUMN,5,0,15,5);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.getSecondValueAxis().setVisible(true);

//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”
SeriesCollection nSeries = chart.getNSeries();
int i1 = nSeries.add("{50, 100, 150, 300}",true);
Series columnsSeries = nSeries.get(i1);
columnsSeries.setPlotOnSecondAxis(true);

//Setting the chart type of 2nd NSeries to display as line chart
int i2 = nSeries.add("{60, 32, 50, 40}",true);
Series lineSeries = nSeries.get(i2);
lineSeries.getMarker().setMarkerStyle(ChartMarkerType.SQUARE);
lineSeries.getMarker().setMarkerSize(6);
lineSeries.setType(ChartType.LINE);

//Saving the Excel file
workbook.save(“C:\Users\VZ0715\Desktop\dest.pdf”, SaveFormat.PDF);

but when I am trying to do so I get exception:

Exception in thread “main” com.aspose.cells.CellsException: If there is only a series on the primary axis, you could not plot the series to the second axis

if I set line chart use secondary value axis and bar chart use primary value axis then I don’t get any exception.

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

//Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();

//Accessing the instance of the newly added chart
int chartIndex = worksheet.getCharts().add(ChartType.COLUMN,5,0,15,5);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.getSecondValueAxis().setVisible(true);

//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”
SeriesCollection nSeries = chart.getNSeries();
int i1 = nSeries.add("{50, 100, 150, 300}",true);
Series columnsSeries = nSeries.get(i1);

//Setting the chart type of 2nd NSeries to display as line chart
int i2 = nSeries.add("{60, 32, 50, 40}",true);
Series lineSeries = nSeries.get(i2);
lineSeries.getMarker().setMarkerStyle(ChartMarkerType.SQUARE);
lineSeries.getMarker().setMarkerSize(6);
lineSeries.setType(ChartType.LINE);
lineSeries.setPlotOnSecondAxis(true);

//Saving the Excel file
workbook.save(“C:\Users\VZ0715\Desktop\dest.pdf”, SaveFormat.PDF);

So how can I make bar chart to use secondary value axis and line chart use primary value axis without getting exception.

I’m trying to solve this issue 2 days already and it would be great some of your help.
Thanks

Kind Regards
Alikas

Hi,


You are doing a little mistake in your code segment. Please first add your desired series for both Bar and Line chart types and then specify which series to be plotted on secondary axis. Please see the updated sample code below for your reference (see the lines of code in bold), I have tested the following code and it works fine:
e.g
Sample code:

//Instantiating a Workbook object
Workbook workbook = new Workbook();

//Obtaining the reference of the newly added worksheet
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

//Adding a chart to the worksheet
ChartCollection charts = worksheet.getCharts();

//Accessing the instance of the newly added chart
int chartIndex = worksheet.getCharts().add(ChartType.COLUMN,5,0,15,5);
Chart chart = worksheet.getCharts().get(chartIndex);
chart.getSecondValueAxis().setVisible(true);

//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”
SeriesCollection nSeries = chart.getNSeries();
int i1 = nSeries.add("{50, 100, 150, 300}",true);

//Setting the chart type of 2nd NSeries to display as line chart
int i2 = nSeries.add("{60, 32, 50, 40}",true);
Series lineSeries = nSeries.get(i2);
lineSeries.getMarker().setMarkerStyle(ChartMarkerType.SQUARE);
lineSeries.getMarker().setMarkerSize(6);
lineSeries.setType(ChartType.LINE);

//Get the Column chart series (first series)
Series columnsSeries = nSeries.get(0);
columnsSeries.setPlotOnSecondAxis(true);


//Saving the Excel file
workbook.save(“C:\Users\VZ0715\Desktop\dest.pdf”, SaveFormat.PDF);


Hope, this helps a bit.

Thank you.

Hello,


Thank you for your quick replay. Solution is working. But I am afraid there is another problem now. Columns now are up side down. Please look at attached image (result.jpg)

I tried to replicate same chart in excel with same parameters and excel produces chart right. I hope you can help me with this too. Thanks a lot.

Kind Regards
Alikas

Hi,


Please add a line to the end of your code segment:
e.g
Sample code:

Series columnsSeries = nSeries.get(0);
columnsSeries.setPlotOnSecondAxis(true);
chart.getSecondValueAxis().setCrossAt(0);


Thank you.

Thanks. It works fine. But no I have one more issue here.

I have illustrated in attached image (results2.jpg).
When I save file as PDF I get line hiding behind columns. But if file is saved as Excel I can see that line is displaying fine. Is this some kind of bug? Thank you.

Kind Regards
Alikas

Hi,


Please try our latest fix/version: Aspose.Cells for Java (Latest Version)

I have tested with it using your code segment (after incorporating my suggestions), it works fine and the output PDF file is fine tuned.


Thank you.

Thanks, it’s all working fine now with new version.


Kind Regards

Alikas

Hi,


Good to know that it figures out your issue now. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.