Apache POI supports only ScatterCharts and LineCharts why?

Question

I see that only LineChart and Scatter Charts are supported by default with Apache POI.

Question: How can we add Other Chart types in spreadhsheet..

Any Idea or any help on this? or Is there Any reason for apache supporting only these two types of charts.

Because it's an Open Source, Volunteer driven project, and those are the only chart types which people have volunteered their time + effort to contribute? — Gagravarr
Ok. So I guess Other Chart types can also be implemented but not yet done by anyone. — Manikandan Arunachalam

Axel Richter Axel Richter · Accepted Answer · 2016-01-12T19:04:17

Apache poi provides poi-ooxml-schemas also. This are the underlying objects for XML based office documents. So one can try solving his requirements directly with those objects as long as they are not provided at higher level. The problem is the documentation of those objects. As far as I know there is none. But there is http://grepcode.com/snapshot/repo1.maven.org/maven2/org.apache.poi/ooxml-schemas/1.1/.

So coming from the XSSFChart from the existing chart examples we can get CTChart and then using http://grepcode.com/file/repo1.maven.org/maven2/org.apache.poi/ooxml-schemas/1.1/org/openxmlformats/schemas/drawingml/x2006/chart/CTChart.java#CTChart shimmy up from object to object.

It is helpful to create a simple *.xlsx file first directly with Excel and have a look at its XML content. There we can get hints what objects are needed. For this we can unzip the *.xlsx simply with a ZIP software.

In this example create a workbook with one sheet and the simplest possible pie chart in Excel. Unzip the *.xlsx and look at /xl/charts/chart1.xml.

Example Pie Chart:

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;

import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;


public class PieChart {

    public static void main(String[] args) throws Exception {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");

        Row row;
        Cell cell;
        for (int r = 0; r < 3; r++) {
            row = sheet.createRow(r);
            cell = row.createCell(0);
            cell.setCellValue("S" + r);
            cell = row.createCell(1);
            cell.setCellValue(r+1);
        }

        Drawing drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 20);

        Chart chart = drawing.createChart(anchor);

        CTChart ctChart = ((XSSFChart)chart).getCTChart();
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        CTPieChart ctPieChart = ctPlotArea.addNewPieChart();
        CTBoolean ctBoolean = ctPieChart.addNewVaryColors();
        ctBoolean.setVal(true);
        CTPieSer ctPieSer = ctPieChart.addNewSer();

        ctPieSer.addNewIdx().setVal(0);     

        CTAxDataSource cttAxDataSource = ctPieSer.addNewCat();
        CTStrRef ctStrRef = cttAxDataSource.addNewStrRef();
        ctStrRef.setF("Sheet1!$A$1:$A$3"); 
        CTNumDataSource ctNumDataSource = ctPieSer.addNewVal();
        CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
        ctNumRef.setF("Sheet1!$B$1:$B$3"); 

System.out.println(ctChart);

        FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }
}

Disclaimer: tested with Excel 2007, works not with Libreoffice and Openoffice.

This example needs the full jar of all of the schemas ooxml-schemas-1.3.jar as mentioned in the FAQ-N10025.

To make this working using apache poi 4.1.0 following needs to be changed:

...
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
//import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
...
        XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 20);

        XSSFChart chart = drawing.createChart(anchor);
...

And it then needs the full jar of all of the schemas ooxml-schemas-1.4.jar.

Apache POI supports only ScatterCharts and LineCharts why?

3 Answers