How to draw a 2-line time series with POI 4.0.0?

Question

Can anyone share working code that draws a timeseries (e.g x=dates,y=numeric values) chart with POI 4.0.0? I am trying to draw a 2-line chart that share dates on the X axis with POI 4.0.0 but this code:

        ...
        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);
        int chartStartRow = SHEET_START_ROW + 1;
        int chartEndRow = chartStartRow + mergedData.size() - 1;

        // This is selecting data from a column of dates
        XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(chartStartRow, chartEndRow, 0, 0));
        // This is time series 1
        XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(chartStartRow, chartEndRow, 1, 1));
        // This is time series 2
        XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(chartStartRow, chartEndRow, 2, 2));


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

results in exception:

Caused by: java.lang.NullPointerException: null
at org.apache.poi.xddf.usermodel.chart.XDDFChartData$Series.fillStringCache(XDDFChartData.java:260) ~[poi-ooxml-4.0.0.jar:4.0.0]
at org.apache.poi.xddf.usermodel.chart.XDDFChartData$Series.plot(XDDFChartData.java:153) ~[poi-ooxml-4.0.0.jar:4.0.0]
at org.apache.poi.xddf.usermodel.chart.XDDFChart.plot(XDDFChart.java:287) ~[poi-ooxml-4.0.0.jar:4.0.0]

I would even appreciate code snippets for older POI versions, as I suspect POI 4.0.0 is buggy. Thanks

A "column of dates" does not containing strings in Excel. Dates are numeric values in Excel where 1 = 1/1/1900 and +1 = + 1 day, +1/24 = +1 h, +1/24/60 = +1 m, +1/24/60/60 = +1 s. — Axel Richter, Sep 27 '18 at 03:35
Thanks, that helped! But the main issue is that POI 4.0.0 charts are not ready yet an one needs to add the code hacks from here: — Hristo Stoyanov, Sep 27 '18 at 19:56

score 0 · Answer 1 · answered Sep 27 '18 at 19:57

0

Alex's comment to treat dates as number is correct, but POI 4.0.0 charts are not ready yet and you need to apply the code hacks from here:

Problem running official examples LineChars and ScatterChart with Apache POI 4.0

answered Sep 27 '18 at 19:57

Hristo Stoyanov

1,508
3
15
24

How to draw a 2-line time series with POI 4.0.0?

1 Answers1