3

There's a problem with the official examples LineChart and ScatterChart for Apache POI 4.0. They compile and run without errors, but the created Excel file cannot be opened stating that there is unreadable content. Excel 2010 & 2016 are giving the option to recover data from the workbook and after clickin' yes, this dialog appears. What might be the problem?

Alain FAGOT BÉAREZ
  • 112
  • 5
trimtosize
  • 213
  • 2
  • 10

1 Answers1

2

The new XDDF code lacks the setting the axIds in the lineChart and scatterChart.

In /xl/charts/chart1.xml this looks like:

<c:lineChart>
 ...
 <c:axId val="0"/>
 <c:axId val="1"/>
</c:lineChart>

for a line chart..

Do adding:

...
            XDDFChartData data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
            data.addSeries(xs, ys1);
            data.addSeries(xs, ys2);
            chart.plot(data);

            //setting the axis Ids to the LineChart
            chart.getCTChart().getPlotArea().getLineChartArray(0).addNewAxId().setVal(bottomAxis.getId());
            chart.getCTChart().getPlotArea().getLineChartArray(0).addNewAxId().setVal(leftAxis.getId());


            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("ooxml-line-chart.xlsx")) {
                wb.write(fileOut);
            }
...

in LineChart.java

and

...
            XDDFChartData data = chart.createData(ChartTypes.SCATTER, bottomAxis, leftAxis);

            data.addSeries(xs, ys1);
            data.addSeries(xs, ys2);
            chart.plot(data);

            //setting the axis Ids to the ScatterChart
            chart.getCTChart().getPlotArea().getScatterChartArray(0).addNewAxId().setVal(bottomAxis.getId());
            chart.getCTChart().getPlotArea().getScatterChartArray(0).addNewAxId().setVal(leftAxis.getId());


            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("ooxml-scatter-chart.xlsx")) {
                wb.write(fileOut);
            }

...

in ScatterChart.java

and it will work.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Thank you, it works. Could you explain why this setting is so important? To me, it looks like redundant as there is alredy `XDDFChartData data = chart.createData(ChartTypes.SCATTER, bottomAxis, leftAxis);`. Note that I've been playing with settings for smoothing and varying colors. Smoothing is enabled by default but it can be switched off. No luck with varying colors. Check [this](https://stackoverflow.com/questions/50116862/apache-poi-scatter-chart-creation) post for info about that. – trimtosize Sep 11 '18 at 17:57
  • @trimtosize: The new `XDDF` code of `apache poi` is simply buggy. That's it. – Axel Richter Sep 11 '18 at 18:02
  • Does that mean that setting axis ids can be considered as a bug and that POI should have set it automatically under the hood of the `createData` method? – trimtosize Sep 11 '18 at 19:06
  • 1
    @trimtosize: Yes, because the `XSSFChart` code up to `apache poi` version 3.17 had done this properly. – Axel Richter Sep 12 '18 at 03:28