Setting the color of an Excel sheet scatter chart marker icon with Apache POI

Question

The scatter chart example included with Apache POI shows how to set the color of a line (that connects markers in a series), but I cannot figure out how to set the color of a marker for a series. I see that I can change the marker icon (with setMarkerStyle (javadoc), but that does not appear to have editable color properties).

From a previous SO question I suspect that it will be necessary to disable the vary colors configuration, but I still do not know how to set the colors after that step (if the following line is even necessary).

((XSSFChart)chart).getCTChart().getPlotArea().getScatterChartArray(0) .addNewVaryColors().setVal(false);

How can I specify the marker icon colors in the example below?

public class ScatterChart {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("Sheet 1");
            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.TOP_RIGHT);

            XDDFValueAxis bottomAxis = chart.createValueAxis(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));


            XDDFScatterChartData data = (XDDFScatterChartData) chart.createData(ChartTypes.SCATTER, bottomAxis, leftAxis);
            XDDFScatterChartData.Series series1 = (XDDFScatterChartData.Series) data.addSeries(xs, ys1);
            series1.setTitle("2x", null); // https://stackoverflow.com/questions/21855842
            series1.setSmooth(false); // https://stackoverflow.com/questions/39636138
            XDDFScatterChartData.Series series2 = (XDDFScatterChartData.Series) data.addSeries(xs, ys2);
            series2.setTitle("3x", null);
            chart.plot(data);

            solidLineSeries(data, 0, PresetColor.CHARTREUSE);
            solidLineSeries(data, 1, PresetColor.TURQUOISE);

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

    private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
        XDDFLineProperties line = new XDDFLineProperties();
        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);
    }
}

score 5 · Accepted Answer · answered Mar 15 '19 at 19:24

The previous question you linked is from May 2018 and is about the pure XSSFChart stuff from apache poi upto version 3.17. This is outdated since apache poi 4.0.0 introduced the new XDDF stuff.

Nevertheless setting the marker color is not supported until now using only the high level XDDF classes. The marker has shape properties having fill properties of the same kind as the series itself. So we can use XDDFSolidFillProperties and XDDFShapeProperties as for the line settings. But to get the marker, we need using the low level underlying ooxml-schemas-1.4 beans.

Example:

...
series2.setMarkerStyle(MarkerStyle.DIAMOND);
series2.setMarkerSize((short)15);
XDDFSolidFillProperties fillMarker = new XDDFSolidFillProperties(XDDFColor.from(new byte[]{(byte)0xFF, (byte)0xFF, 0x00}));
XDDFShapeProperties propertiesMarker = new XDDFShapeProperties();
propertiesMarker.setFillProperties(fillMarker);
chart.getCTChart().getPlotArea().getScatterChartArray(0).getSerArray(1).getMarker()
     .addNewSpPr().set(propertiesMarker.getXmlObject());
...

Works great but the marker border colors are still set to the default coloring. Is it a similar process to change the border color? — Ben Holland, Mar 15 '19 at 19:39
Yup ok I got it. `XDDFNoFillProperties noFill = new XDDFNoFillProperties();` `XDDFShapeProperties propertiesMarker = new XDDFShapeProperties();` `propertiesMarker.setFillProperties(fillMarker);` `XDDFLineProperties lineProperties = new XDDFLineProperties();` `lineProperties.setFillProperties(noFill);` `propertiesMarker.setLineProperties(lineProperties);` — Ben Holland, Mar 15 '19 at 19:51

Setting the color of an Excel sheet scatter chart marker icon with Apache POI

1 Answers1