Search code examples
javachartsapache-poi

How to setup Apache POI Doughnut Chart Label outside of Chart using LeaderLine

I would like to know if there is any way to setup the Label of chart outside of the chart with a LeaderLine.

enter image description here

Currently I can produce the left chart. But I want the chart look like the Right sided chart.

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

Hier is my implementation for the Label.

Solution

  • Your add-data-labels-code sets default data labels to all data points. But there is no default setting for positioning all data points for a doughnut chart.

    In Excel GUI one need drag each single data label outside the chart to place them. The same would must be done using Apache POI. Much code and much math will be needed to calculate the single positions.

    Given a radius to shift the single data labels. This is in percent of plot area.

    Given the sum of all values which fill the full circle of 360 degrees.

    Then for each data points a data label must be set.

    The position shifting (dX and dY) needs to be calculated using trigonometric functions where the trigonometry looks like so:

    enter image description here

    The current angle alpha is dependent on relation of cumulative value to full circle. Take cumulative values at half value - because data labels are positioned at half of segment.

    Then sin(angle) = opposite side / hypotenuse, where opposite side is dX, hypotenuse is radius.

    Also cos(angle) = adjacent side / hypotenuse, where adjacent side is dY, hypotenuse is radius. Because y grows top down, not buttom up, this must be negated (*-1).

    Complete Example:

    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.chart.LegendPosition;
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.XDDFDoughnutChartData;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
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;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;

public class DoughnutChartXDDF {

  public static void main(String[] args) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      XSSFSheet sheet = wb.createSheet("doughnutChart");
      final int NUM_OF_ROWS = 2;
      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);
          if (rowIndex == 0) cell.setCellValue("Cat " + (colIndex + 1));
          else cell.setCellValue((colIndex + 1) * (rowIndex + 1));
          //else cell.setCellValue((NUM_OF_COLUMNS - colIndex) * (rowIndex + 1));
        }
      }

      XSSFDrawing drawing = sheet.createDrawingPatriarch();
      XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 4, 10, 25);

      XSSFChart chart = drawing.createChart(anchor);
      chart.setTitleText("Doughnut Chart");
      chart.setTitleOverlay(false);
      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.TOP_RIGHT);

      XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
          new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
      XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
          new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));

      //XDDFDoughnutChartData data = new XDDFDoughnutChartData(chart, chart.getCTChart().getPlotArea().addNewDoughnutChart());
      XDDFDoughnutChartData data = (XDDFDoughnutChartData)chart.createData(ChartTypes.DOUGHNUT, null, null);
      data.setVaryColors(true);
      data.setHoleSize(50);
      XDDFChartData.Series series = data.addSeries(cat, val);
      chart.plot(data);

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

      // Data point colors; is necessary for showing data points in Calc
      int pointCount = series.getCategoryData().getPointCount(); 
      for (int p = 0; p < pointCount; p++) {
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDPt().addNewIdx().setVal(p);
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDPtArray(p)
          .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(DefaultIndexedColorMap.getDefaultRGB(p+10));
      }
                 
      // Add data labels
      if (chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).isSetDLbls()) {
                   chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).unsetDLbls();  
      }
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDLbls();
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls()
                   .addNewShowVal().setVal(true);
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls()
                   .addNewShowSerName().setVal(false);
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls()
                   .addNewShowCatName().setVal(false);
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls()
                   .addNewShowPercent().setVal(false);
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls()
                   .addNewShowLegendKey().setVal(false); 

      // Shift data labels outside
      double valSum = 0.0;
      for (int p = 0; p < pointCount; p++) { 
        valSum = valSum + val.getPointAt(p); 
      }
      double valCumulative = 0.0d;
      double radius = 0.15d; // radius to shift in percent of plot area - to play around with
      double fullCircle = Math.toRadians(360d);
      // Set data labels for each data point
      for (int p = 0; p < pointCount; p++) {
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewDLbl();
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
          .addNewIdx().setVal(p);
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
          .addNewShowVal().setVal(true);
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
          .addNewShowSerName().setVal(false);
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
          .addNewShowCatName().setVal(false);
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
          .addNewShowPercent().setVal(false);
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
          .addNewShowLegendKey().setVal(false);
                              
        // Set cumulative val at half value - because data labels are positioned at half of segment               
        valCumulative = valCumulative + val.getPointAt(p) / 2d;
        double angle = fullCircle * (valCumulative / valSum); // current angle is dependent on relation of cumulative val to full circle
        // Set cululative val at full value as start for next data point                                 
        valCumulative = valCumulative + val.getPointAt(p) / 2d;
        // sin(angle) = opposite side / hypotenuse - opposite side is dX, hypotenuse is radius
        double dX = Math.sin(angle) * radius;
        // cos(angle) = adjacent side / hypotenuse - adjacent side is dY, hypotenuse is radius
        double dY = Math.cos(angle) * radius * -1d; // y grows top down, not buttom up, thus *-1
                                                              
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
                                .addNewLayout().addNewManualLayout();
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
                                .getLayout().getManualLayout().addNewX().setVal(dX);
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().getDLblArray(p)
                                .getLayout().getManualLayout().addNewY().setVal(dY);

      }

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

    Result:

    enter image description here