Pie Chart Apache POI (4.1.1) - How to get the number format of data labels to 0,00

Question

I did some searching around the internet (and the code inspection), but it seems I cannot find what im looking for...

What I do: I´m creating a pie chart in an excel which works fine, EXCEPT that the percent values are shown with top many decimal places:

I played a bit around in the generated Excel and it would help if I could access the "Category" - Numbers property programmatically:

In my code I already tried to set the number format, which doesn´t change anything for me:

public void createPieChartInSheet(Workbook wb, XSSFDrawing drawing, String chartTitle, XSSFClientAnchor anchor, LinkedList<Triple<String, XDDFDataSource<String>, XDDFNumericalDataSource<Double>>> chartSeries, LinkedList<byte[]> colorScheme) {
    XSSFChart chart = drawing.createChart(anchor);
    chart.setTitleText(chartTitle);
    chart.setTitleOverlay(false);

    XDDFDataSource<String> cat = chartSeries.get(0).getMiddle();
    XDDFNumericalDataSource<Double> val = chartSeries.get(0).getRight();

    XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
    Series series = data.addSeries(cat, val);
    series.setTitle(chartSeries.get(0).getLeft(), null);

    // Add data labels
    if (!chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).isSetDLbls()) {
        chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
    }
    chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
    chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
    chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
    chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls().addNewShowPercent().setVal(false);
    chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);
    chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls().addNewNumFmt().setFormatCode("0,00");

    setPieChartColors(colorScheme, chart, series);
    chart.plot(data);
}

Am I even searching for the right thing? Any help appreciated - thank you!

Answer 1

It is always difficult to figure out problem related to Excel, as documentation is not clear and there are so many XML tag with meaningless name.

One way I find useful is to compare the actual xml of working and not working excel file.

So I created a not working excel using program, and then edit using MS Excel to apply formatting to create a working excel. Then by comparing xl\charts\chart1.xml after unzip the excel, The problem is found and due to the following line
From program

<c:numFmt formatCode="0.00"/>

After edit with MS Excel

<c:numFmt formatCode="0.00" sourceLinked="0"/>

"0" is false and the default value is "1"(true).

From the document of sourceLinked, I guess the formatting will follow the source(cell original format) when set to true, so we need to set to false to make our formatting effective. We can do so by calling CTNumFmt#setSourceLinked(boolean) with false.

Following program demonstrate how to generate Pie chart with custom number format.(Base on Axel Richter answer), running on POI 4.1.1.

import java.io.FileNotFoundException;
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.ChartTypes;
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.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.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumFmt;

public class CustomDecimalPlace {
    public static void main(String[] args) throws FileNotFoundException, IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("piechart");
            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(((double) 101) / ((double) (colIndex + 1)));
                    }
                }
            }

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

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Pie 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));
            XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
            data.setVaryColors(true);
            data.addSeries(cat, val);
            chart.plot(data);
            if (!chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).isSetDLbls()) {
                chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();
            }
            CTDLbls dLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDLbls();
            dLbls.addNewShowVal().setVal(true);
            dLbls.addNewShowSerName().setVal(false);
            dLbls.addNewShowCatName().setVal(false);
            dLbls.addNewShowPercent().setVal(false);
            dLbls.addNewShowLegendKey().setVal(false);
            CTNumFmt addNewNumFmt = dLbls.addNewNumFmt();
            addNewNumFmt.setFormatCode("0.00");
            // Set false to not follow source format
            addNewNumFmt.setSourceLinked(false);
            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("ooxml-pie-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }
}

Answer 2

An Excel chart get it's data label number format from the number format of the cells where the values are stored. That's called "source linked" and is the default. So simply do formatting those cells using the number format you want.

But if you want to have the data labels a special other number format, you need add a NumFmt element to the DLbls element. You have done tis alrady. But this NumFmt must not be source linked then. And it must have it's own FormatCode, which is always en_US in the XML. The localizing is done by the Excel GUI and not in the source XML. So it would must be 0.00 in your case since dot is decimal separator while comma is thousands separator in en_US. But if you want have it the default decimal number format having two decimal digits, it would must be #,##0.00.

Change your code as follows:

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

This works for me and leads to have data labels the number format #.##0,00 in my German Excel, which is the default decimal number format having two decimal digits.