How do i create excel chart with SXSSFSheet using apache POI?

Question

My project is using SXSSFWorkbook in order to generate excel files as the excel files generated in the application can be of large size as well. Now I need to include excel charts in these excel files as well and i am not able to create charts with SXSSFWorkbook. The only examples I get is with XSSFWorkbook. Is there a way I can have excel chart with SXSSFWorkbook?

The examples I get for XSSFWorkbook is shown below. I am looking for similar example with SXSSFWorkbook

    XSSFWorkbook wb = new XSSFWorkbook()        
    XSSFSheet sheet = wb.createSheet("barchart");
    XSSFDrawing drawing = sheet.createDrawingPatriarch();
    XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 1, 10, DATA_START_ROW - 2);

    XSSFChart chart = drawing.createChart(anchor);
    chart.setTitleText("Trend (Claim Type)");
    chart.setTitleOverlay(false);

    XDDFChartLegend legend = chart.getOrAddLegend();
    legend.setPosition(LegendPosition.TOP_RIGHT);

    XDDFCategoryAxis bottomAxis = chart
            .createCategoryAxis(AxisPosition.BOTTOM);
    bottomAxis.setTitle("Period");
    XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
    leftAxis.setTitle("Cost");

    XDDFDataSource<String> periods = XDDFDataSourcesFactory
            .fromStringCellRange(sheet,
                    new CellRangeAddress(DATA_START_ROW, DATA_START_ROW + data.size(), 1, 1));

    XDDFNumericalDataSource<Double> allowed = XDDFDataSourcesFactory
            .fromNumericCellRange(sheet,
                    new CellRangeAddress(DATA_START_ROW, DATA_START_ROW + data.size(), 2, 2));

    XDDFNumericalDataSource<Double> total = XDDFDataSourcesFactory
            .fromNumericCellRange(sheet,
                    new CellRangeAddress(DATA_START_ROW, DATA_START_ROW + data.size(), 3, 3));

    XDDFLineChartData pdata = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

    XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) pdata.addSeries(periods, total);
    series1.setTitle("Total Paid", null);
    series1.setSmooth(false);
    series1.setMarkerStyle(MarkerStyle.STAR);

    XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) pdata.addSeries(periods, allowed);
    series2.setTitle("Allowed", null);
    series2.setSmooth(true);
    series2.setMarkerSize((short) 6);
    series2.setMarkerStyle(MarkerStyle.SQUARE);

    chart.plot(pdata);


    // Write output to an excel file
    try (FileOutputStream fileOut = new FileOutputStream(
            getReportFolderPath())) {

        //wb.write(fileOut);
    } catch (Exception ex) {

    }

UPDATE -> I have tried doing as below but still getting array out of bound while doing chart.plot(pdata). What I am trying to do is create a XSSFSheet using SXSSFWorkbook and plot graph in this sheet. However, creating a ClientAnchor using SXSSFDrawing and creating XSSFChart like XSSFChart chart = sxssfDrawing.createChart(anchor)

        sxssfSheet.createDrawingPatriarch();
        XSSFSheet xssfSheet = workbook.getXSSFWorkbook().getSheet(sheet.getSheetName());
        chart.drawChart(sxssfSheet, xssfSheet, xssfSheet.getDrawingPatriarch());

    public void drawChart(SXSSFSheet sSheet, XSSFSheet xSheet, XSSFDrawing drawing) {
    SXSSFDrawing sxssfDrawing = sSheet.createDrawingPatriarch();
    ClientAnchor anchor = sxssfDrawing.createAnchor(0, 0, 0, 0, 3, 1, 10, chartIndexDTO.getDataRowStartIndex() - 2);

    XSSFChart chart = drawing.createChart(anchor);
    chart.setTitleText("Claim Type vs Allowed Amount");
    chart.setTitleOverlay(false);

    XDDFChartLegend legend = chart.getOrAddLegend();
    legend.setPosition(LegendPosition.TOP_RIGHT);

    XDDFCategoryAxis bottomAxis = chart
            .createCategoryAxis(AxisPosition.BOTTOM);
    bottomAxis.setTitle("Claim Type");
    XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
    leftAxis.setTitle("Allowed");
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

    XDDFDataSource<String> xAxis = XDDFDataSourcesFactory
            .fromStringCellRange(xSheet,
                    new CellRangeAddress(chartIndexDTO.getDataRowStartIndex(), chartIndexDTO.getDataRowEndIndex(), 0, 0));

    XDDFNumericalDataSource<Double> yAxis = XDDFDataSourcesFactory
            .fromNumericCellRange(xSheet,
                    new CellRangeAddress(chartIndexDTO.getDataRowStartIndex(), chartIndexDTO.getDataRowEndIndex(), 1, 1));

    XDDFChartData pdata = chart
            .createData(ChartTypes.BAR, bottomAxis, leftAxis);
    XDDFChartData.Series series1 = pdata.addSeries(xAxis, yAxis);
    series1.setTitle("Allowed", null);
    pdata.setVaryColors(true);
    chart.plot(pdata);

    XDDFBarChartData bar = (XDDFBarChartData) pdata;
    bar.setBarDirection(BarDirection.COL);
}

The culprit here is [SXSSFDrawing](https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFDrawing.html) which does not provide creating charts. But why `SXSSFWorkbook` needs to be used? If all the data shall be showed in a chart that data amount should not be that big, should it? Because if the data amount is that big that a `XSSFWorkbook` cannot be used, how shall a chart be able showing it? — Axel Richter, Jan 06 '22 at 13:56

Amit Pokhrel · Accepted Answer · 2022-01-10T14:53:03.493

I am answering to my own question as I have found the solution that works. Creating a XSSFSheet from SXSSFSheet.getDrawingPatriarch() and doing everything as before worked for me. I am posting the sample code below:

SXSSFWorkbook wb = new SXSSFWorkbook(null, 100, true);  
SXSSFSheet sheet = workbook.createSheet("barchart");
sheet.createDrawingPatriarch();
XSSFDrawing drawing = sheet.getDrawingPatriarch();

XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 1, 10, DATA_START_ROW - 2);
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText("Trend (Claim Type)");
chart.setTitleOverlay(false);

XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);

XDDFCategoryAxis bottomAxis = chart
        .createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setTitle("Period");
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("Cost");

XDDFDataSource<String> periods = XDDFDataSourcesFactory
        .fromStringCellRange(sheet,
                new CellRangeAddress(DATA_START_ROW, DATA_START_ROW + data.size(), 1, 1));

XDDFNumericalDataSource<Double> allowed = XDDFDataSourcesFactory
        .fromNumericCellRange(sheet,
                new CellRangeAddress(DATA_START_ROW, DATA_START_ROW + data.size(), 2, 2));

XDDFNumericalDataSource<Double> total = XDDFDataSourcesFactory
        .fromNumericCellRange(sheet,
                new CellRangeAddress(DATA_START_ROW, DATA_START_ROW + data.size(), 3, 3));

XDDFLineChartData pdata = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) pdata.addSeries(periods, total);
series1.setTitle("Total Paid", null);
series1.setSmooth(false);
series1.setMarkerStyle(MarkerStyle.STAR);

XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) pdata.addSeries(periods, allowed);
series2.setTitle("Allowed", null);
series2.setSmooth(true);
series2.setMarkerSize((short) 6);
series2.setMarkerStyle(MarkerStyle.SQUARE);

chart.plot(pdata);


// Write output to an excel file
try (FileOutputStream fileOut = new FileOutputStream(
        getReportFolderPath())) {

    //wb.write(fileOut);
} catch (Exception ex) {

}

I also had to make changes to my pom.xml as below:

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.1.0</version>
        <exclusions>
            <exclusion>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-lite</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-full</artifactId>
        <version>5.1.0</version>
    </dependency>

score 0 · Answer 2 · answered Jan 06 '22 at 18:18

The problem is that SXSSFDrawing does not provide creating charts until now.

But SXSSFWorkbook can be created from a XSSFWorkbook. And this XSSFWorkbook can have the chart already. Up to apache poi 4 (maybe up to apache poi 5.0.0) that was not possible using the XDDF since it was not possible creating a chart from empty data ranges. But using apache poi 5.1.0 it is possible now.

So if the position and size of the data range for the chart is known, then a XSSFWorkbook having that chart can be created. The chart points to an empty data range at first. So the used memory size will not be that big. Then this XSSFWorkbook can be used to create the SXSSFWorkbook and the SXSSFWorkbook can be used to stream the big data amount in.

Following complete example shows this.

import java.io.FileOutputStream;

import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

public final class SXSSFLineChart {
    
 static void createXSSFSheetWithLineChart(XSSFSheet sheet,  String chartTitle, String catAxisTitle, String yAxisTitle, XSSFCell[] headers, CellRangeAddress dataRange, XSSFClientAnchor anchor) {

  XSSFDrawing drawing = sheet.createDrawingPatriarch();

  XSSFChart chart = drawing.createChart(anchor);
  chart.setTitleText(chartTitle);
  chart .setTitleOverlay(false);

  XDDFChartLegend legend = chart.getOrAddLegend();
  legend.setPosition(LegendPosition.TOP_RIGHT);

  XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
  bottomAxis.setTitle(catAxisTitle);
  XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
  leftAxis.setTitle(yAxisTitle);
    
  XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(
                                                                                 dataRange.getFirstRow(), dataRange.getLastRow(), 
                                                                                 dataRange.getFirstColumn(), dataRange.getFirstColumn()));
  XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress( 
                                                                                            dataRange.getFirstRow(), dataRange.getLastRow(), 
                                                                                            dataRange.getFirstColumn()+1, dataRange.getFirstColumn()+1));
  XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(
                                                                                            dataRange.getFirstRow(), dataRange.getLastRow(), 
                                                                                            dataRange.getFirstColumn()+2, dataRange.getFirstColumn()+2));

  XDDFChartData data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
  XDDFChartData.Series series1 = data.addSeries(xs, ys1);
  series1.setTitle(headers[0].getStringCellValue(), new CellReference(headers[0]));
  ((XDDFLineChartData.Series )series1).setSmooth(false);
  ((XDDFLineChartData.Series )series1).setMarkerStyle(MarkerStyle.STAR);
            
  XDDFChartData.Series series2 = data.addSeries(xs, ys2);
  series2.setTitle(headers[1].getStringCellValue(), new CellReference(headers[1]));
  ((XDDFLineChartData.Series )series2).setSmooth(true);
  ((XDDFLineChartData.Series )series2).setMarkerSize((short) 6);
  ((XDDFLineChartData.Series )series2).setMarkerStyle(MarkerStyle.SQUARE);

  chart.plot(data);
 }     

 static void streamDataIntoSXSSFWorkbook(SXSSFSheet sheet, int dataStartRow, int dataSize) {
  for (int r = dataStartRow; r < dataStartRow + dataSize; r++) {
   SXSSFRow row = sheet.createRow(r);
   SXSSFCell cell = row.createCell(0); cell.setCellValue("Per" + r);
   double d = new java.util.Random().nextDouble() * 1000;
   cell = row.createCell(1); cell.setCellValue(d);
   cell = row.createCell(2); cell.setCellValue(d / (new java.util.Random().nextDouble()+1));
  }
 }

 public static void main(String[] args) throws Exception {
        
  final int DATA_START_ROW = 1;
  final int DATA_SIZE = 100;

  XSSFWorkbook wb = new XSSFWorkbook();
  XSSFSheet sheet = wb.createSheet();
  XSSFRow row = sheet.createRow(0);
  XSSFCell cell = row.createCell(0); cell.setCellValue("Period");
  XSSFCell[] headers = new XSSFCell[2];
  cell = row.createCell(1); cell.setCellValue("Total Paid");
  headers[0] = cell;
  cell = row.createCell(2); cell.setCellValue("Allowed");
  headers[1] = cell;
 
  createXSSFSheetWithLineChart(sheet, "Trend (Claim Type)", "Period", "Cost", headers,
   new CellRangeAddress(DATA_START_ROW, DATA_START_ROW + DATA_SIZE - 1, 0, 2), 
   new XSSFClientAnchor(0, 0, 0, 0, 3, 1, 20, DATA_START_ROW + 20)
  );
  
  SXSSFWorkbook sWb = new SXSSFWorkbook(wb);
  SXSSFSheet sSheet = sWb.getSheetAt(0);
  streamDataIntoSXSSFWorkbook(sSheet, DATA_START_ROW, DATA_SIZE);
  
  FileOutputStream fileOut = new FileOutputStream("ooxml-line-chart-sxssf.xlsx");
  sWb.write(fileOut);
  fileOut.close();
  sWb.dispose();
 }
}

The position and size of the data range for the chart is not known prior. The index are tracked while data is being populated in the excel and later that index is used to plot chart. What I was attempting to do was create a XSSFSheet using SXSSFSheet.getDrawingPatriarch().getSheet() and plot graph in this sheet. However, creating a ClientAnchor using SXSSFDrawing and creating XSSFChart like XSSFChart chart = sxssfDrawing.createChart(anchor); I am getting arrayindexoutofbound while doing chart.plot() I am posting my code below: — Amit Pokhrel, Jan 06 '22 at 19:20
@Amit Pokhrel: Not possible that way. The chart must be in the `XSSFSheet` ot the `XSSFWorkbook` **before** the `SXSSFWorkbook` gets created from that `XSSFWorkbook`. After the creation the sheets of the `SXSSFWorkbook` are in temporary files without having possibility to create a chart. — Axel Richter, Jan 06 '22 at 19:39

How do i create excel chart with SXSSFSheet using apache POI?

2 Answers2