Create Bar Chart in Excel with Apache POI
I'm trying to create a Bar Chart in an XLSX Spreadsheet using Apache POI, but Excel keeps saying that there's a problem with the content and deleting the chart when I try to open the file. Here's the full code of what I'm trying to do:
public static void createBarChart() {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet2");
Row row;
Cell cell;
row = sheet.createRow(0);
row.createCell(0);
row.createCell(1).setCellValue("HEADER 1");
row.createCell(2).setCellValue("HEADER 2");
row.createCell(3).setCellValue("HEADER 3");
for (int r = 1; r < 5; r++) {
row = sheet.createRow(r);
cell = row.createCell(0);
cell.setCellValue("Serie " + r);
cell = row.createCell(1);
cell.setCellValue(123);
cell = row.createCell(2);
cell.setCellValue(456);
cell = row.createCell(3);
cell.setCellValue(789);
}
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();
ctChart.addNewTitle();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
ctBarChart.addNewGrouping().setVal(STBarGrouping.CLUSTERED);
ctBarChart.addNewVaryColors().setVal(false);
// First series
CTBarSer ctBarSer = ctBarChart.addNewSer();
ctBarSer.addNewIdx().setVal(0);
ctBarSer.addNewOrder().setVal(0);
// [01] Title
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctSerTxStrRef = ctSerTx.addNewStrRef();
ctSerTxStrRef.setF("Sheet2!$A$2");
CTStrData strCache = ctSerTxStrRef.addNewStrCache();
strCache.addNewPtCount().setVal(1);
CTStrVal newPt = strCache.addNewPt();
newPt.setV("Serie 1");
newPt.setIdx(0);
// [01] SpPr
CTShapeProperties spPr = ctBarSer.addNewSpPr();
CTSolidColorFillProperties solidFill = spPr.addNewSolidFill();
solidFill.addNewSchemeClr().setVal(STSchemeColorValImpl.ACCENT_1);
spPr.addNewLn().addNewNoFill();
spPr.addNewEffectLst();
// [01] Invert if negative
ctBarSer.addNewInvertIfNegative().setVal(false);
// [01] Series titles
CTAxDataSource newCat = ctBarSer.addNewCat();
CTStrRef addNewStrRef = newCat.addNewStrRef();
addNewStrRef.setF("Sheet2!$B$1:$D$1");
CTStrData addNewStrCache = addNewStrRef.addNewStrCache();
addNewStrCache.addNewPtCount().setVal(3);
CTStrVal pt1 = addNewStrCache.addNewPt();
pt1.setIdx(0);
pt1.setV(sheet.getRow(0).getCell(1).getStringCellValue());
CTStrVal pt2 = addNewStrCache.addNewPt();
pt2.setIdx(1);
pt2.setV(sheet.getRow(0).getCell(2).getStringCellValue());
CTStrVal pt3 = addNewStrCache.addNewPt();
pt3.setIdx(2);
pt3.setV(sheet.getRow(0).getCell(3).getStringCellValue());
// [01] Series values
CTNumDataSource newVal = ctBarSer.addNewVal();
CTNumRef numRef = newVal.addNewNumRef();
numRef.setF("Sheet2!$B$2:$D$2");
CTNumData numCache = numRef.addNewNumCache();
numCache.addNewPtCount().setVal(3);
CTNumVal numpt1 = numCache.addNewPt();
numpt1.setIdx(0);
numpt1.setV(String.valueOf(sheet.getRow(1).getCell(1).getNumericCellValue()));
CTNumVal numpt2 = numCache.addNewPt();
numpt2.setIdx(1);
numpt2.setV(String.valueOf(sheet.getRow(1).getCell(2).getNumericCellValue()));
CTNumVal numpt3 = numCache.addNewPt();
numpt3.setIdx(2);
numpt3.setV(String.valueOf(sheet.getRow(1).getCell(3).getNumericCellValue()));
// dLbls
CTDLbls dLbls = ctBarChart.addNewDLbls();
dLbls.addNewShowBubbleSize().setVal(false);
dLbls.addNewShowLegendKey().setVal(false);
dLbls.addNewShowCatName().setVal(false);
dLbls.addNewShowSerName().setVal(false);
dLbls.addNewShowPercent().setVal(false);
dLbls.addNewShowVal().setVal(false);
FileOutputStream fileOut = new FileOutputStream("barchart.xlsx");
wb.write(fileOut);
wb.close();
fileOut.close();
}
Can anyone help me find (and, well, solve) the issue? Thanks in advance!
Phew, difficult birth ;-). For users without knowledge of the background Apache POI supports only ScatterCharts and LineCharts why?. There is described how to proceed in principle.
As I said. Take the simplest possible bar chart for testing first. That is one series with two values. Then you would have seen that you have completely forgotten the axes in your code. A pie chart has no axes. Thats why there they are not necessary. But a bar chart will not work without axes.
Instead you can unscrupulously forget all things what is named "cache".
Whether other things are needed is determined by try and error. Also one could read the Office OpenXML recommendation. Probably the missing of all elements having minOccurs <> "0"` leads to deleting the Drawing while opening the Workbook.
Simple Example for a bar 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.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
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;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
public class BarChart {
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
Row row;
Cell cell;
row = sheet.createRow(0);
row.createCell(0);
row.createCell(1).setCellValue("HEADER 1");
row.createCell(2).setCellValue("HEADER 2");
row.createCell(3).setCellValue("HEADER 3");
for (int r = 1; r < 5; r++) {
row = sheet.createRow(r);
cell = row.createCell(0);
cell.setCellValue("Serie " + r);
cell = row.createCell(1);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(2);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(3);
cell.setCellValue(new java.util.Random().nextDouble());
}
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);
Chart chart = drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
for (int r = 2; r < 6; r++) {
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("Sheet1!$A$" + r);
ctBarSer.addNewIdx().setVal(r-2);
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
ctStrRef.setF("Sheet1!$B$1:$D$1");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r);
//at least the border lines in Libreoffice Calc ;-)
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});
}
//telling the BarChart that it has axes and giving them Ids
ctBarChart.addNewAxId().setVal(123456);
ctBarChart.addNewAxId().setVal(123457);
//cat axis
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
CTScaling ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(false);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//val axis
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(123457); //id of the val axis
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//legend
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
System.out.println(ctChart);
FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx");
wb.write(fileOut);
fileOut.close();
}
}
This example needs the full jar of all of the schemas ooxml-schemas-1.3.jar as mentioned in the FAQ-N10025.
Above code has worked until apache poi 3.17.
Following code works using apache poi 4.1.0. It needs the full jar of all of the schemas ooxml-schemas-1.4.jar.
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer;
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;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;
public class BarChart {
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
Row row;
Cell cell;
row = sheet.createRow(0);
row.createCell(0);
row.createCell(1).setCellValue("HEADER 1");
row.createCell(2).setCellValue("HEADER 2");
row.createCell(3).setCellValue("HEADER 3");
for (int r = 1; r < 5; r++) {
row = sheet.createRow(r);
cell = row.createCell(0);
cell.setCellValue("Serie " + r);
cell = row.createCell(1);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(2);
cell.setCellValue(new java.util.Random().nextDouble());
cell = row.createCell(3);
cell.setCellValue(new java.util.Random().nextDouble());
}
XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);
XSSFChart chart = drawing.createChart(anchor);
CTChart ctChart = ((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);
for (int r = 2; r < 6; r++) {
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("Sheet1!$A$" + r);
ctBarSer.addNewIdx().setVal(r-2);
CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
ctStrRef = cttAxDataSource.addNewStrRef();
ctStrRef.setF("Sheet1!$B$1:$D$1");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF("Sheet1!$B$" + r + ":$D$" + r);
//at least the border lines in Libreoffice Calc ;-)
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});
}
//telling the BarChart that it has axes and giving them Ids
ctBarChart.addNewAxId().setVal(123456);
ctBarChart.addNewAxId().setVal(123457);
//cat axis
CTCatAx ctCatAx = ctPlotArea.addNewCatAx();
ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
CTScaling ctScaling = ctCatAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctCatAx.addNewDelete().setVal(false);
ctCatAx.addNewAxPos().setVal(STAxPos.B);
ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//val axis
CTValAx ctValAx = ctPlotArea.addNewValAx();
ctValAx.addNewAxId().setVal(123457); //id of the val axis
ctScaling = ctValAx.addNewScaling();
ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
ctValAx.addNewDelete().setVal(false);
ctValAx.addNewAxPos().setVal(STAxPos.L);
ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);
//legend
CTLegend ctLegend = ctChart.addNewLegend();
ctLegend.addNewLegendPos().setVal(STLegendPos.B);
ctLegend.addNewOverlay().setVal(false);
System.out.println(ctChart);
FileOutputStream fileOut = new FileOutputStream("BarChart.xlsx");
wb.write(fileOut);
fileOut.close();
}
}
with Apache-Poi 4.0 we can achive it with minimal code. Below example is available with apache poi example. Now createChart Method is available with all format(Excel/DOCX/PPTX).
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.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
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.0));
}
}
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText("BarChart");
chart.setTitleOverlay(false);
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP_RIGHT);
// Use a category axis for the bottom axis.
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setTitle("x");
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("f(x)");
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);
XDDFChartData.Series series1 = data.addSeries(xs, ys1);
series1.setTitle("2x", null);
XDDFChartData.Series series2 = data.addSeries(xs, ys2);
series2.setTitle("3x", null);
chart.plot(data);
// in order to transform a bar chart into a column chart, you just need to change the bar direction
XDDFBarChartData bar = (XDDFBarChartData) data;
bar.setBarDirection(BarDirection.COL);
// looking for "Stacked Bar Chart"? uncomment the following line
// bar.setBarGrouping(BarGrouping.STACKED);
solidFillSeries(data, 0, PresetColor.CHARTREUSE);
solidFillSeries(data, 1, PresetColor.TURQUOISE);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
wb.write(fileOut);
}
}
}
private static void solidFillSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setFillProperties(fill);
series.setShapeProperties(properties);
}
}