Hello,
i am using Aspose Cells Java 19.4. I generated a bar-chart with four series, and three of them are marked as ChartType.LINE_WITH_DATA_MARKERS.
I tried again with a stacked-bar-chart and Series-type ChartType.LINE_STACKED_WITH_DATA_MARKERS.
The document is different but wrong too.
In Excel every Line-Series is shown inverted and in LibreOffice the Values are not correct set in the chart.
Showing the whole code isn’t possible because it is within a more complex java-application.
documents.zip (18.6 KB)
@rkunschke,
I have reviewed the output files but these are not sufficient to reproduce the issue. You may provide us a simple console application(extracted from your Java application) which can be compiled without any error. Also, provide us the expected output files created by MS Excel for our reference. We will test the scenario by creating files using your sample code and comparing them with the expected output files. We will provide our feedback once the sample code and expected output files are received.
I made a simple Example:
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();
cells.get(“A2”).setValue(“a”);
cells.get(“A3”).setValue(“b”);
cells.get(“A4”).setValue(“c”);
cells.get(“A5”).setValue(“d”);
cells.get(“B1”).setValue(“Series 1”);
cells.get(“C1”).setValue(“Series 2”);
cells.get(“B2”).setValue(72.6);
cells.get(“B3”).setValue(64.8);
cells.get(“B4”).setValue(35.8);
cells.get(“B5”).setValue(38.4);
cells.get(“C2”).setValue(86.3);
cells.get(“C3”).setValue(38.4);
cells.get(“C4”).setValue(89.3);
cells.get(“C5”).setValue(43.7);
worksheet.getCharts().add(ChartType.BAR, 4, 4, 30, 20); //wrong values
//worksheet.getCharts().add(ChartType.BAR_STACKED, 4, 4, 30, 20); //wrong values
// worksheet.getCharts().add(ChartType.COLUMN, 4, 4, 30, 20); //correct
//worksheet.getCharts().add(ChartType.COLUMN_STACKED, 4, 4, 30, 20); //correct
Chart chart = worksheet.getCharts().get(0);
chart.setChartDataRange(“B$2:C$5”, true);
chart.getNSeries().get(1).setType(ChartType.LINE_STACKED_WITH_DATA_MARKERS);
//chart.getNSeries().get(1).setType(ChartType.LINE_WITH_DATA_MARKERS); //I tryed with these two options
try{
workbook.save(path+“test.xlsx”, SaveFormat.XLSX);
}catch (Exception e){
System.out.println(e.toString());
}
I think the Problem is the bar-chart itself. Everything is shown up correct if I use a column chart or a stacked column chart. It does not really matter if i use a series with ChartType.LINE_STACKED_WITH_DATA_MARKERS or ChartType.LINE_WITH_DATA_MARKERS.
I tried it in LibreOffice calc too and it looked like it is not possible to create a bar-chart with a line series.
But it looks like Aspose Cells try to do it anyway when i use a bar cahrt with a line series.
@rkunschke,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-42906 - Issues while generating bar chart with points
@rkunschke,
We have investigated a bit more. The two series use different axes. The bar type series Y Value corresponds to the Primary Horizontal axis and the line series Y value corresponds to the Secondary Vertical axis.
Following sample code can be used to show the Secondary Axis
//The axis of line type series is on second
chart.getSecondCategoryAxis().setVisible(true);
chart.getSecondCategoryAxis().getTitle().setText ("Line's X Axis");
chart.getSecondValueAxis().setVisible (true);
chart.getSecondValueAxis().getTitle().setText ("Line's Y Axis");
Hope, this helps a bit.
I noticed the Secondary-Axis Objects. Using them does not solve my problem.
@rkunschke,
I have tried the suggested changes in your code as follows:
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();
cells.get("A2").setValue("a");
cells.get("A3").setValue("b");
cells.get("A4").setValue("c");
cells.get("A5").setValue("d");
cells.get("B1").setValue("Series 1");
cells.get("C1").setValue("Series 2");
cells.get("B2").setValue(72.6);
cells.get("B3").setValue(64.8);
cells.get("B4").setValue(35.8);
cells.get("B5").setValue(38.4);
cells.get("C2").setValue(86.3);
cells.get("C3").setValue(38.4);
cells.get("C4").setValue(89.3);
cells.get("C5").setValue(43.7);
worksheet.getCharts().add(ChartType.BAR, 4, 4, 30, 20);
Chart chart = worksheet.getCharts().get(0);
chart.setChartDataRange("B$2:C$5", true);
chart.getNSeries().get(1).setType(ChartType.LINE_STACKED_WITH_DATA_MARKERS);
//The axis of line type series is on second
chart.getSecondCategoryAxis().setVisible(true);
chart.getSecondCategoryAxis().getTitle().setText ("Line's X Axis");
chart.getSecondValueAxis().setVisible (true);
chart.getSecondValueAxis().getTitle().setText ("Line's Y Axis");
try
{
workbook.save("BAR-LINE_STACKED_WITH_DATA_MARKERS.xlsx", SaveFormat.XLSX);
}
catch (Exception e)
{
System.out.println(e.toString());
}
It creates output as attached here:
BAR-LINE_STACKED_WITH_DATA_MARKERS.zip (7.7 KB)
Capture.PNG (29.2 KB)
Apparently it creates the required output. If it is not as per your requirements, please explain with images. Also provide us an expected output file created by Excel for our reference. We will use it for comparison with the output generated by Aspose.Cells and provide our feedback accordingly.
Thank you. That helps me to understand how charts with a point series should work
The Output looks correct in that way that series 1 has its point on the x-Axis and series 2 has its points one the y-Axis.
I want both series showing the values on the same Axis.
Also in LibreOffice the problem is something complete different:
Screenshot_20190621_133406.png (106.7 KB)
all values are wrong on libre office in the second series.
Any ideas about that problems?
@rkunschke,
I have observed the issue in LibreOffice. As requested earlier please share an expected output file created by Excel. This file shall also open fine in LibreOffice. We will use this file as reference for our testing and analysis.
I think I misunderstood the way how these mixed-charts are working in Excel. It seems like it isn’t possible to create a Line-Chart with the values in the x-axis. So its also not possible to do this in a mixed chart.
I can not create the expected outputfile.
The issue seems to be a missing feature in excel
@rkunschke,
Thank you for the feedback and yes your understanding is right. Please feel free to write us back if you have any other query related to Aspose.Cells.