The easiest way to implement a barchart with 2 col

Question

I'm trying to create n bar chart of 2 columns in a xlsx file like below.

But It's so confuse to me , to understand how the class inside the org.openxmlformats.schemas.drawingml.x2006.chart works.

I've already tryied but the generate file does not get the chart that I have drawed.

I have this code:

        Drawing drawing = planilha.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 15);

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

           CellRangeAddress rangeAreas =  new CellRangeAddress(1,3,1,1);
        CellRangeAddress rangeTotais = new CellRangeAddress(1,3,5,5);

        CTBarSer ctBarSer = ctBarChart.addNewSer();
        CTSerTx ctSerTx = ctBarSer.addNewTx();
        CTStrRef ctStrRef = ctSerTx.addNewStrRef();
        ctStrRef.setF("Gráfico!"+rangeAreas.formatAsString());

        CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
        CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
        ctNumRef.setF("Gráfico!"+rangeTotais.formatAsString());
        //this code below I copied of an example and I don't know what is necessary 
        ctBarChart.addNewAxId().setVal(123456);
        ctBarChart.addNewAxId().setVal(123457);

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

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

Answer 1

Using apache poi 4.0.0, the last stable version, creating bar charts is possible without using the low level underlying beans. For this Package org.apache.poi.xddf.usermodel is used.

Some parts of the XDDF stuff are buggy until now. So we need to repair something. But nevertheless we should using those classes rather than the low level underlying beans.

Example for your requirement:

Source:

Code:

import java.io.FileOutputStream;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.WorkbookFactory;
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.AxisCrossBetween;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
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 ExcelBarChartFromExistingData {

  public static void main(String[] args) throws IOException {
    try (XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("Workbook.xlsx"))) {
      XSSFSheet sheet = wb.getSheet("Sheet1");

      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);
      leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

      XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet,
          new CellRangeAddress(1, 3, 1, 1));
      XDDFNumericalDataSource<Double> ys = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
          new CellRangeAddress(1, 3, 5, 5));

      XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
      data.addSeries(xs, ys);
      chart.plot(data);

      //Since XDDF stuff is buggy until now, we need to repair something.

      //repairing set the kind of bar char, either bar chart or column chart:
      chart.getCTChart().getPlotArea().getBarChartArray(0).addNewBarDir().setVal(
       org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir.COL);
       //org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir.BAR);

      //repairing telling the axis Ids in bar chart:
      chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(bottomAxis.getId());
      chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(leftAxis.getId());

      XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
      XDDFChartData.Series firstSeries = data.getSeries().get(0);
      XDDFShapeProperties properties = firstSeries.getShapeProperties();
      if (properties == null) {
        properties = new XDDFShapeProperties();
      }
      properties.setFillProperties(fill);
      firstSeries.setShapeProperties(properties);

      // Write the output to a file
      try (FileOutputStream fileOut = new FileOutputStream("WorkbookNew.xlsx")) {
        wb.write(fileOut);
      }
    }
  }
}

Result:

To answer your implicit question how to understand the classes inside the org.openxmlformats.schemas.drawingml.x2006.chart:

To get this known we need knowing how Excel stores it's data. The *.xlsx files are simply ZIP archives. So we can simply unzip them and having a look into.

There we find XML files. For charts /xl/charts/chart1.xml for example. Now first we need understanding that XML.

Then we need information about the org.openxmlformats.schemas.drawingml.x2006.chart package. We can download ooxml-schemas-1.4-sources.jar and then doing javadoc of this. Now we have a API documentation for all the underlying beans inclusive package org.openxmlformats.schemas.drawingml.x2006.chart.