apache poi 4.0 bar chart example does not work

Question

When I'm trying apache poi4.0's project example, I encounter a problem of creating bar chart, after running the code,I open the xlsx file with a error msg
Excel found unreadable content in 'xxx.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of the workbook, click Yes.
click yes and then get another msg
Excel was able to open the file by repairing or removing the unreadable content. Removed Part: /xl/drawings/drawing1.xml part (Drawing shape).
Click close and then no chart is add in the excel but the data is fine.
And then I try the lineChart and scatterChart example, face the same problem.
What's the most possible reason of this error and how to fix this?
Thanks in advance. :)

package org.apache.poi.xssf.usermodel.examples;

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class BarChart {

  public static void main(String[] args) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      XSSFSheet sheet = wb.createSheet("barchart");
      final int NUM_OF_ROWS = 3;
      final int NUM_OF_COLUMNS = 10;

      // Create a row and put some cells in it. Rows are 0 based.
      Row row;
      Cell cell;
      for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
        row = sheet.createRow((short) rowIndex);
        for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
          cell = row.createCell((short) colIndex);
          cell.setCellValue(colIndex * (rowIndex + 1));
        }
      }

      XSSFDrawing drawing = sheet.createDrawingPatriarch();
      XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);

      XSSFChart chart = drawing.createChart(anchor);
      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.TOP_RIGHT);

      // Use a category axis for the bottom axis.
      XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
      XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
      leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

      XDDFDataSource<Double> xs = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
          new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
      XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
          new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
      XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
          new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));

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

      XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHARTREUSE));
      XDDFChartData.Series firstSeries = data.getSeries().get(0);
      XDDFShapeProperties properties = firstSeries.getShapeProperties();
      if (properties == null) {
        properties = new XDDFShapeProperties();
      }
      properties.setFillProperties(fill);
      firstSeries.setShapeProperties(properties);

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

maybe start from process of elimination... if you remove the chart, do you still get the data loaded? — Paul Bastide, Sep 14 '18 at 08:40
Maybe the same problem as with `LineChart` and `ScatterChart`, see https://stackoverflow.com/questions/52263619/problem-running-official-examples-linechars-and-scatterchart-with-apache-poi-4-0/52279067#52279067? Cannot test it myself now. But give `chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(bottomAxis.getId()); chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(leftAxis.getId());` a try. — Axel Richter, Sep 14 '18 at 08:47
@PaulBastide If just add data with `for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)`,the file open without error msg, so I guess the problem most probably came from the chart related code. — jacky, Sep 14 '18 at 08:52
@AxelRichter thanks,but it still shows the same error msg when deal with barChart, but in the lineChart and scatterChart example,it works fine with adding the new code.So maybe the poi4.0 not support barChart yet? — jacky, Sep 14 '18 at 09:37
I am confident we get this running somehow. Seems the `BarChart` lacks more than only the `axId` settings then. Will check this as soon as I am able to. But of course providing untested "examples" for new features such as bar charts does not shed a good light on the project `apache poi`. At least such new features should be tested though before providing. — Axel Richter, Sep 14 '18 at 09:54

Axel Richter · Accepted Answer · 2018-09-14T13:38:45.827

The new XDDF code lacks the setting the axIds in the barChart, as well as the setting of the bar direction barDir which either can be COL for a column chart or BAR for a bar chart.

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

<c:barChart>
 <c:barDir val="bar"/>
 ...
 <c:axId val="0"/>
 <c:axId val="1"/>
</c:barChart>

for a bar chart

and

<c:barChart>
 <c:barDir val="col"/>
 ...
 <c:axId val="0"/>
 <c:axId val="1"/>
</c:barChart>

for a column chart.

Do adding:

...
      chart.plot(data);

      chart.getCTChart().getPlotArea().getBarChartArray(0).addNewBarDir().setVal(
       //org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir.COL);
       org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir.BAR);
      chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(bottomAxis.getId());
      chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(leftAxis.getId());
...

and it will at least work.

There are other issues too with the axis settings. That's why I would say that the bar chart creation, which is using only high level classes of apache poi, is not ready yet.

apache poi 4.0 bar chart example does not work

1 Answers1