Apache poi add label in excel chart

Question

public class LineChart {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("linechart");
            final int NUM_OF_ROWS = 3;
            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);
                    cell.setCellValue(colIndex * (rowIndex + 1.0));
                }
            }

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);

            XSSFChart chart = drawing.createChart(anchor);
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.BOTTOM);

            // Use a category axis for the bottom axis.
            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            //bottomAxis.setTitle("x"); // https://stackoverflow.com/questions/32010765
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            //leftAxis.setTitle("f(x)");
            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

            XDDFDataSource<Double> xs = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
            XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
            XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));

            XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
            XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(xs, ys1);
            series1.setTitle("2x", null); // https://stackoverflow.com/questions/21855842
            series1.setSmooth(false); // https://stackoverflow.com/questions/29014848
            series1.setMarkerStyle(MarkerStyle.DOT); // https://stackoverflow.com/questions/39636138
            series1.setShowLeaderLines(true);
            XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(xs, ys2);
            series2.setTitle("3x", null);
            //series2.setSmooth(true);
            //series2.setMarkerSize((short) 6);
            //series2.setMarkerStyle(MarkerStyle.NONE); // https://stackoverflow.com/questions/39636138
            series2.setShowLeaderLines(false);

            chart.plot(data);

            // if your series have missing values like https://stackoverflow.com/questions/29014848
            // chart.displayBlanksAs(DisplayBlanks.GAP);

            // https://stackoverflow.com/questions/24676460
            solidLineSeries(data, 0, PresetColor.CHARTREUSE);
            solidLineSeries(data, 1, PresetColor.BLACK);

            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("ooxml-line-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }
    //CTPresetLineDashProperties
    private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
        XDDFLineProperties line = new XDDFLineProperties();
        //line.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));
        line.setFillProperties(fill);
        XDDFChartData.Series series = data.getSeries().get(index);
        XDDFShapeProperties properties = series.getShapeProperties();
        if (properties == null) {
            properties = new XDDFShapeProperties();
        }
        properties.setLineProperties(line);
        series.setShapeProperties(properties);
    }
}

This is an example from: http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/LineChart.java

And I use poi version is 4.1.1.

when I use series1.setShowLeaderLines(true) or series2.setShowLeaderLines(false) it will show label as picture 1, and contains Series Name, Category Name, Value and Legend Key, also label position is Right.

Is it possible to change it picture 2 which just contain Value and label position is Center? Thank you

current view what I want

Answer 1

The problem is that XDDF is not complete yet. The XDDFLineChartData.Series.setShowLeaderLines sets default data lables if not present.

As of current Excel versions default data lables means including value, legend key, category name and series name. So all this is shown.

XDDFLineChartData.Series lacks methods for customizing the data labels except of the leader lines. So this needs to be done using the low level ooxml-schemas classes and methods.

In your case:

...
            series1.setShowLeaderLines(true); // this sets default data lables
            // customizing data labels
            int seriesNr = 0;
            //chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls()
            // .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(new byte[]{(byte)255,(byte)255,0});
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls()
             .addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.CTR);
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowVal().setVal(true);
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowLegendKey().setVal(false);
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowCatName().setVal(false);
            chart.getCTChart().getPlotArea().getLineChartArray(0).getSerArray(seriesNr).getDLbls().addNewShowSerName().setVal(false);
...