Excel Generation [Java] - BarChart - Enable Multi Category Label

Question

I have excel generation using org.apache.poi library. Here i need to generate a bar chart which should show like below:

And my excel look like:

Code:

    //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
    XDDFDataSource<String> department = XDDFDataSourcesFactory.fromStringCellRange(sheet,
            new CellRangeAddress(7, 17, 1, 1));

If i set CellRangeAddress firstCol - 0 and lastCol - 1 its not working?

Is any option to enable this in java:

Answer 1

Multi-level category labels are not possible using the XDDF stuff of apache poi. The XDDF always uses StrRef as references for chart data sources. To create multi-level category labels MultiLvlStrRef would must be used. So the task only is solvable using the low level org.openxmlformats.schemas.drawingml.x2006.chart.* classes directly.

That's why following code needs the full jar of all of the ooxml-schemas, which is ooxml-schemas-1.4.jar for current apache poi 4.1.2, in class path. The poi-ooxml-schemas-4.1.2.jar does not contain org.openxmlformats.schemas.drawingml.x2006.chart.CTMultiLvlStrRef.

And because of the crude logic of Microsoft, which uses a setting NoMultiLvlLbl-true as the default for category axes, one now must tell the category axis that it not has no multi level labels. So, if the axis shall have multi-level category labels, NoMultiLvlLbl must be set false. Well, seems that Microsoft wants to check whether programmers have learned their logic lessons well ;-).

Complete example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xssf.usermodel.*;

import org.openxmlformats.schemas.drawingml.x2006.chart.*;

public class BarChartMultiLevelCategories {

 public static void main(String[] args) throws Exception {

  String[] series = new String[]{"January","February","March"};
  String[] charges = new String[]{"Charges not Tally","Deposit","High Charges", "Quotation"};
  String[] treatment = new String[]{"Diagnosis","Explanation","Procedure"};
  String[] behaviour = new String[]{"Miscommunication","Not Attentive","Not Friendly", "Rude"};
  Double[][] values = new Double[][]{
   new Double[]{1d, 2d, 3d},
   new Double[]{1d, 3d, 2d},
   new Double[]{2d, 1d, 3d},
   new Double[]{2d, 3d, 1d},
   new Double[]{3d, 1d, 2d},
   new Double[]{3d, 2d, 1d},
   new Double[]{1d, 2d, 3d},
   new Double[]{1d, 3d, 2d},
   new Double[]{2d, 1d, 3d},
   new Double[]{2d, 3d, 1d},
   new Double[]{3d, 1d, 2d},
  };

  try (XSSFWorkbook wb = new XSSFWorkbook()) {
   //the sheet for the chart
   XSSFSheet chartSheet = wb.createSheet("barchart");
   //the sheet for the data
   XSSFSheet dataSheet = wb.createSheet("data");

   Row row;
   Cell cell;
   row = dataSheet.createRow(0);
   for (int i = 0; i < series.length; i++) {
    cell = row.createCell(i+2); cell.setCellValue(series[i]);
   }
   for (int i = 0; i < charges.length; i++) {
    row = dataSheet.createRow(i+1);
    if (i == 0) { cell = row.createCell(0); cell.setCellValue("Charges"); }
    cell = row.createCell(1); cell.setCellValue(charges[i]);
   }
   dataSheet.addMergedRegion(new CellRangeAddress(1, charges.length, 0, 0));
   for (int i = 0; i < treatment.length; i++) {
    row = dataSheet.createRow(i+1+charges.length);
    if (i == 0) { cell = row.createCell(0); cell.setCellValue("Treatment"); }
    cell = row.createCell(1); cell.setCellValue(treatment[i]);
   }
   dataSheet.addMergedRegion(new CellRangeAddress(1+charges.length, charges.length+treatment.length, 0, 0));
   for (int i = 0; i < behaviour.length; i++) {
    row = dataSheet.createRow(i+1+charges.length+treatment.length);
    if (i == 0) { cell = row.createCell(0); cell.setCellValue("Behaviour"); }
    cell = row.createCell(1); cell.setCellValue(behaviour[i]);
   }
   dataSheet.addMergedRegion(
   new CellRangeAddress(1+charges.length+treatment.length, charges.length+treatment.length+behaviour.length, 0, 0));
   for (int i = 0; i < values.length; i++) {
    Double[] valuesRow = values[i];
    row = dataSheet.getRow(i+1);
    for (int j = 0; j < valuesRow.length; j++) {
     cell = row.createCell(j+2); cell.setCellValue(valuesRow[j]);
    }
   }

   XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
   XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 10, 20);

   XSSFChart chart = drawing.createChart(anchor);
   chart.setTitleText("Chart title");
   chart.setTitleOverlay(false);
   //do not auto delete the title; is necessary for showing title in Calc
   if (chart.getCTChart().getAutoTitleDeleted() == null) chart.getCTChart().addNewAutoTitleDeleted();
   chart.getCTChart().getAutoTitleDeleted().setVal(false);

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

   CTChart ctChart = chart.getCTChart();
   CTPlotArea ctPlotArea = ctChart.getPlotArea();
   CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
   CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
   ctBoolean.setVal(true);
   ctBarChart.addNewBarDir().setVal(STBarDir.COL);

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

   //telling the category axis that it not has no multi level labels ;-)
   ctCatAx.addNewNoMultiLvlLbl().setVal(false);

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

   //series
   byte[][] seriesColors = new byte[][] {
    new byte[]{(byte)255, 0, 0}, //red
    new byte[]{0, (byte)255, 0}, //green
    new byte[]{0, 0, (byte)255}  //blue
   };
   for (int i = 0; i < series.length; i++) {
    CTBarSer ctBarSer = ctBarChart.addNewSer();
    CTSerTx ctSerTx = ctBarSer.addNewTx();
    CTStrRef ctStrRef = ctSerTx.addNewStrRef();
    ctStrRef.setF(
     new CellRangeAddress(0, 0, i+2, i+2)
          .formatAsString(dataSheet.getSheetName(), true)); //data!R1C(i+2)
    ctBarSer.addNewIdx().setVal(i);  

    CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();
    //do using MultiLvlStrRef instead of StrRef 
    CTMultiLvlStrRef ctMultiLvlStrRef = cttAxDataSource.addNewMultiLvlStrRef();
    ctMultiLvlStrRef.setF(
     new CellRangeAddress(1, charges.length+treatment.length+behaviour.length, 0, 1)
          .formatAsString(dataSheet.getSheetName(), true)); //data!$A$2:$B$12

    CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
    CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
    ctNumRef.setF(
     new CellRangeAddress(1, charges.length+treatment.length+behaviour.length, i+2, i+2)
          .formatAsString(dataSheet.getSheetName(), true)); //data!R2C(i+2):R12C(i+2)

    ctBarSer.addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(seriesColors[i]);   

   } 

   try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
    wb.write(fileOut);
   }
  }
 }
}