How to create Apache POI stacked Barchart

Question

I want to create "stacked bar chart" using apache poi library. Need some documentation.

     row1 = worksheet.createRow(r);
      row['data'].flatten.each_with_index do |data, index|
        cell = row1.createCell(index);
        cell.setCellValue(data);
      end
    drawing = worksheet.createDrawingPatriarch();
    anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 8, 20);

    chart = drawing.createChart(anchor);
    ctChart = chart.getCTChart();
    ctPlotArea = ctChart.getPlotArea();
    ctBarChart = ctPlotArea.addNewBarChart();

    ctBoolean = ctBarChart.addNewVaryColors();
    ctBoolean.setVal(true);
    ctBarChart.addNewBarDir().setVal(STBarDir.BAR);
    ctBarChart.addNewGrouping().setVal(STBarGrouping.STACKED);

       ctBarSer = ctBarChart.addNewSer();
       ctSerTx = ctBarSer.addNewTx();
       ctStrRef = ctSerTx.addNewStrRef();
       ctStrRef.setF("Sheet1!$A$#{r+1}");
       ctBarSer.addNewIdx().setVal(r+1);  
       cttAxDataSource = ctBarSer.addNewCat();
       ctStrRef = cttAxDataSource.addNewStrRef();
       length = row['data'].flatten.length
       ctStrRef.setF("Sheet1!$B$#{r+1}:$#{CellReference.convertNumToColString(length-1)}$#{r + 1}"); 
       ctNumDataSource = ctBarSer.addNewVal();
       ctNumRef = ctNumDataSource.addNewNumRef();
       ctNumRef.setF("Sheet1!$B$#{r+1}:$#{CellReference.convertNumToColString(length-1)}$#{r+1}");

       ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal([0,0,0]);   

Using above code I am getting bar chart but need to convert to stacked bar chart.

enter image description here

Answer 1

While I can't tell you exactly what the magic incantation to make your chart work is, I can help you work through your issue.

If the type of chart you want to create is not supported by POI. You are going to have to create it manually. Seems you have a good start on that. You can find specification documents here http://www.ecma-international.org/publications/standards/Ecma-376.htm. POI uses the 1st edition of the spec.

While these documents are very good, you might need some help determining just how to structure the XML. The way I go about it is to create a simple document using Excel or Word, then rename the saved document as *.zip. Now you can inspect the xml package using Notepad++ or some other viewer.

Once you have something generated with POI, you can also view the resulting XML in a similar manner to se what the differences are if the results are not as expected.