javaspring-bootapache-poi

Apache POI chart position vertically changed when more labels are added

I am writing code which create two Doughnut Charts in an Excel file. The first chart has 10 labels and second chart has 2 labels.

My issue is with the second chart. I want to have both charts vertically aligned in the anchor. But because of few label and value its position in the anchor changed vertically.

enter image description here

One can see from above screenshot, I have both charts side by side. But the position of the second chart vertically changed as it has only two labels.

How can I fix the second chart's position to be aligned with the first one?

public String picChart9() throws IOException {

    try (XSSFWorkbook wb = new XSSFWorkbook()) {
        XSSFSheet sheet = wb.createSheet("doughnutChart");
        sheet.setDisplayGridlines(false);
        final int NUM_OF_ROWS = 2;
        final int NUM_OF_COLUMNS = 10;

        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor1 = drawing.createAnchor(0, 0, 0, 0, 1, 5, 4, 28);
       // anchor1.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
        XSSFClientAnchor anchor2 = drawing.createAnchor(0, 0, 0, 0, 4, 5, 7, 28);
       // anchor2.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);

        XSSFChart chart1 = drawing.createChart(anchor1);
        chart1.setTitleText("chart1");
        chart1.setTitleOverlay(false);
        //chart1.createValueAxis(AxisPosition.TOP);

        XSSFChart chart2 = drawing.createChart(anchor2);
        chart2.setTitleText("chart2");
        chart2.setTitleOverlay(false);
        //chart2.createValueAxis(AxisPosition.LEFT);
        /**/
        System.out.println("chart1.getCTChart() --:"+chart1.getCTChart());
        System.out.println("chart1.getAxes() --:"+chart1.getAxes());
        System.out.println("chart2.getAxes() --:"+chart2.getAxes());
        /**/
        XDDFChartLegend legend1 = chart1.getOrAddLegend();
        legend1.setPosition(LegendPosition.BOTTOM);

        XDDFChartLegend legend2 = chart2.getOrAddLegend();
        legend2.setPosition(LegendPosition.BOTTOM);

        String[] stringArray1 = new String[]{"one","two","three","four","five","six","seven","eight","nine","ten"};
        XDDFDataSource<String> stringValue1 = XDDFDataSourcesFactory.fromArray(stringArray1);
        Long[] longArray1 = new Long[]{ 11L,2L,3L,4L,5L,6L,7L,8L,9L,10L };
        XDDFNumericalDataSource<Long> longValue1 = XDDFDataSourcesFactory.fromArray(longArray1);

        String[] stringArray2 = new String[]{"one","two"};
        XDDFDataSource<String> stringValue2 = XDDFDataSourcesFactory.fromArray(stringArray2);
        Long[] longArray2 = new Long[]{ 60L,40L };
        XDDFNumericalDataSource<Long> longValue2 = XDDFDataSourcesFactory.fromArray(longArray2);

        XDDFDoughnutChartData data1 = new XDDFDoughnutChartData(chart1, chart1.getCTChart().getPlotArea().addNewDoughnutChart());
        data1.setVaryColors(true);
        data1.setHoleSize((short) 50);
        data1.setFirstSliceAngle(10);

        XDDFDoughnutChartData data2 = new XDDFDoughnutChartData(chart2, chart2.getCTChart().getPlotArea().addNewDoughnutChart());
        data2.setVaryColors(true);
        data2.setHoleSize((short) 50);
        data2.setFirstSliceAngle(10);

        XDDFChartData.Series series1 = data1.addSeries(stringValue1, longValue1);
        XDDFChartData.Series series2 = data2.addSeries(stringValue2, longValue2);

        // Remove the Anchore border line
        (chart1).getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();
        (chart2).getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();

        // Data point colors; is necessary for showing data points in Calc

        // Add data labels-Chart-1
        if (!chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).isSetDLbls()) {
            chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDLbls();
        }
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowPercent().setVal(false);
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewNumFmt();
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getNumFmt().setSourceLinked(false);
        chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getNumFmt().setFormatCode("#,##0.00");

        // Add data labels-Chart-2
        if (!chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).isSetDLbls()) {
            chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDLbls();
        }
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowPercent().setVal(false);
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewNumFmt();
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getNumFmt().setSourceLinked(false);
        chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getNumFmt().setFormatCode("#,##0.00");

        //plot chart
        chart1.plot(data1);
        chart2.plot(data2);

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

I am using the code shown above.

Thank you. Please let me know if my issues are not clear.

Solution

  • Per default the plot area of an Excel chart uses as much size of chart space between title and legend as possible. If that is not wanted, then the plot area must be sized manually.

    To get how, one would create a chart using the default settings. After that unzip the *.xlsx and have a look into the /xl/charts/chart*.xml. The XML of this would look like:

    ...
<c:plotArea>
 <c:layout/>
...

    The layout of the plot area is not defined - the default.

    Then resize the plot area using Excel GUI and save. Again unzip the *.xlsx and have a look into the /xl/charts/chart*.xml. The XML of this then looks like:

    ...
<c:plotArea>
 <c:layout>
  <c:manualLayout>
   <c:layoutTarget val="inner"/>
   <c:xMode val="edge"/>
   <c:yMode val="edge"/>
   <c:x val="0.1"/>
   <c:y val="0.1"/>
   <c:w val="0.8"/>
   <c:h val="0.6"/>
  </c:manualLayout>
 </c:layout>
...

    The layout of the plot area is a manual layout targeted to the inner space of the chart space. It's x- and y-mode are edge mode, means x and y coordinates are oriented at the edges of the surrounding space. The x-position from left edge of the surrounding space is 0.1 (10% of surrounding space width). The y-position from top edge of the surrounding space is 0.1 (10% of surrounding space height). The width (w) is 0.8 (80% of surrounding space width). The height (h) is 0.6 (60% of surrounding space height).

    Using java code that would look like so:

    Let it be a XSSFChart chart, then:

    // set plot area size
if (!chart.getCTChart().getPlotArea().isSetLayout()) {
    chart.getCTChart().getPlotArea().addNewLayout();
}
if (chart.getCTChart().getPlotArea().getLayout().isSetManualLayout()) {
    chart.getCTChart().getPlotArea().getLayout().unsetManualLayout();
}
chart.getCTChart().getPlotArea().getLayout().addNewManualLayout();
chart.getCTChart().getPlotArea().getLayout().getManualLayout().addNewLayoutTarget().setVal(
 org.openxmlformats.schemas.drawingml.x2006.chart.STLayoutTarget.INNER);
chart.getCTChart().getPlotArea().getLayout().getManualLayout().addNewXMode().setVal(
 org.openxmlformats.schemas.drawingml.x2006.chart.STLayoutMode.EDGE);
chart.getCTChart().getPlotArea().getLayout().getManualLayout().addNewYMode().setVal(
 org.openxmlformats.schemas.drawingml.x2006.chart.STLayoutMode.EDGE);
chart.getCTChart().getPlotArea().getLayout().getManualLayout().addNewX().setVal(0.10); //10% from left
chart.getCTChart().getPlotArea().getLayout().getManualLayout().addNewY().setVal(0.10); //10% from top
chart.getCTChart().getPlotArea().getLayout().getManualLayout().addNewW().setVal(0.80); //80% width
chart.getCTChart().getPlotArea().getLayout().getManualLayout().addNewH().setVal(0.60); //60% height