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);
}

Answer 1

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>