Apache POI Scatter Bubble Chart

Question

Currently, I'm trying to figure out two things in Apache POI API (4.1.1):

1. How to create a bubble chart with series data.
2. How to modify bubble chart axes series data in case bubble chart already exists in excel sheet.

I see a lot of examples of how to create a Scatter chart (x,y), but there is no for Bubble type (x,y,z)

Answer 1

There is no XDDFChartData prepared for a bubble chart up to now. So the first task would be to program a such.

One could take XDDFScatterChartData.java as a template.

But as often, the accessibility guidelines of Apache POI prevent classes from easy being extended. So in some cases using reflection will be necessary.

A draft for a XDDFBubbleChartData could look like so:

import org.apache.poi.xddf.usermodel.chart.*;

import java.util.List;
import java.util.Map;
import java.util.Collections;

import org.apache.poi.util.Beta;
import org.apache.poi.util.Internal;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTDPt;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBubbleChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTBubbleSer;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumData;;

@Beta
public class XDDFBubbleChartData extends XDDFChartData {
    private CTBubbleChart chart;
    
    public XDDFBubbleChartData(
           XDDFChart parent,
           CTBubbleChart chart,
           XDDFChartAxis category, 
           XDDFValueAxis values) {
        super(parent);
        this.chart = chart;
        Map<Long, XDDFChartAxis> categories = null;
        Map<Long, XDDFValueAxis> mapValues = null;
        categories = Collections.singletonMap(category.getId(), category);
        mapValues = Collections.singletonMap(values.getId(), values);
        for (CTBubbleSer series : chart.getSerList()) {
            this.series.add(new Series(series, series.getXVal(), series.getYVal()));
        }
        defineAxes(categories, mapValues);
    }

    @Internal
    protected XDDFBubbleChartData(
            XDDFChart parent,
            CTBubbleChart chart,
            Map<Long, XDDFChartAxis> categories,
            Map<Long, XDDFValueAxis> values) {
        super(parent);
        this.chart = chart;
        for (CTBubbleSer series : chart.getSerList()) {
            this.series.add(new Series(series, series.getXVal(), series.getYVal()));
        }
        defineAxes(categories, values);
    }

    private void defineAxes(Map<Long, XDDFChartAxis> categories, Map<Long, XDDFValueAxis> values) {
        if (chart.sizeOfAxIdArray() == 0) {
            for (Long id : categories.keySet()) {
                chart.addNewAxId().setVal(id);
            }
            for (Long id : values.keySet()) {
                chart.addNewAxId().setVal(id);
            }
        }
        defineAxes(chart.getAxIdArray(), categories, values);
    }

    @Internal
    @Override
    protected void removeCTSeries(int n) {
        chart.removeSer(n);
    }

    @Override
    public void setVaryColors(Boolean varyColors) {
        if (varyColors == null) {
            if (chart.isSetVaryColors()) {
                chart.unsetVaryColors();
            }
        } else {
            if (chart.isSetVaryColors()) {
                chart.getVaryColors().setVal(varyColors);
            } else {
                chart.addNewVaryColors().setVal(varyColors);
            }
        }
    }

    @Override
    public XDDFChartData.Series addSeries(XDDFDataSource<?> category,
            XDDFNumericalDataSource<? extends Number> values) {
        //final long index = this.parent.incrementSeriesCount();
        try {
            java.lang.reflect.Method incrementSeriesCount = XDDFChart.class.getDeclaredMethod("incrementSeriesCount");
            incrementSeriesCount.setAccessible(true);
            final long index = (long)incrementSeriesCount.invoke(this.parent);    
            final CTBubbleSer ctSer = this.chart.addNewSer();
            ctSer.addNewXVal();
            ctSer.addNewYVal();
            ctSer.addNewIdx().setVal(index);
            ctSer.addNewOrder().setVal(index);
            final Series added = new Series(ctSer, category, values);
            this.series.add(added);
            return added;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public class Series extends XDDFChartData.Series {
        private CTBubbleSer series;

        protected Series(CTBubbleSer series, XDDFDataSource<?> category, XDDFNumericalDataSource<?> values) {
            super(category, values);
            this.series = series;
        }

        protected Series(CTBubbleSer series, CTAxDataSource category, CTNumDataSource values) {
            super(XDDFDataSourcesFactory.fromDataSource(category), XDDFDataSourcesFactory.fromDataSource(values));
            this.series = series;
        }
        
        public void setBubbleSizes(XDDFNumericalDataSource<?> values) {
            try {
                if (series.isSetBubbleSize()) series.unsetBubbleSize();
                CTNumDataSource bubbleSizes = series.addNewBubbleSize();
                java.lang.reflect.Method retrieveNumCache = XDDFChartData.Series.class.getDeclaredMethod("retrieveNumCache", CTNumDataSource.class, XDDFDataSource.class);
                retrieveNumCache.setAccessible(true);
                CTNumData cache = (CTNumData)retrieveNumCache.invoke(this, bubbleSizes, values);
                values.fillNumericalCache(cache);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
        
        @Override
        protected CTSerTx getSeriesText() {
            if (series.isSetTx()) {
                return series.getTx();
            } else {
                return series.addNewTx();
            }
        }

        @Override
        public void setShowLeaderLines(boolean showLeaderLines) {
            if (!series.isSetDLbls()) {
                series.addNewDLbls();
            }
            if (series.getDLbls().isSetShowLeaderLines()) {
                series.getDLbls().getShowLeaderLines().setVal(showLeaderLines);
            } else {
                series.getDLbls().addNewShowLeaderLines().setVal(showLeaderLines);
            }
        }

        @Override
        public XDDFShapeProperties getShapeProperties() {
            if (series.isSetSpPr()) {
                return new XDDFShapeProperties(series.getSpPr());
            } else {
                return null;
            }
        }

        @Override
        public void setShapeProperties(XDDFShapeProperties properties) {
            if (properties == null) {
                if (series.isSetSpPr()) {
                    series.unsetSpPr();
                }
            } else {
                if (series.isSetSpPr()) {
                    series.setSpPr(properties.getXmlObject());
                } else {
                    series.addNewSpPr().set(properties.getXmlObject());
                }
            }
        }

        @Override
        protected CTAxDataSource getAxDS() {
            return series.getXVal();
        }

        @Override
        protected CTNumDataSource getNumDS() {
            return series.getYVal();
        }

        @Override
        protected void setIndex(long val) {
            series.getIdx().setVal(val);
        }

        @Override
        protected void setOrder(long val) {
            series.getOrder().setVal(val);
        }
        
        @Override
        protected List<CTDPt> getDPtList() {
            return series.getDPtList();
        }
    }
}

Having that, one can program a bubble chart example taking the other examples as templates:

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.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFChart;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFPieChartData;
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.XDDFValueAxis;
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.XSSFCellStyle;

import java.util.GregorianCalendar;

/**
 * Bubble chart example.
 */
public final class BubbleChart {
    private BubbleChart() {}

    public static void main(String[] args) throws IOException {
        
        Object[][] chartData = new Object[][]{
            new Object[]{"", "Category 1"},    
            new Object[]{"Bubble Size", "Bubble Date"},    
            new Object[]{1000, new GregorianCalendar(2020, 0, 1)},    
            new Object[]{10, new GregorianCalendar(2020, 0, 1)},    
            new Object[]{300, new GregorianCalendar(2021, 0, 1)},    
            new Object[]{"", ""},    
            new Object[]{"", "Category 2"},    
            new Object[]{"Bubble Size", "Bubble Date"},    
            new Object[]{100, new GregorianCalendar(2018, 0, 1)},    
            new Object[]{100, new GregorianCalendar(2020, 0, 1)}   
        };
        
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("bubblechart");
            DataFormat format = wb.createDataFormat();
            XSSFCellStyle dateStyle = wb.createCellStyle();
            dateStyle.setDataFormat(14);
            
            // put sheet data
            Row row;
            Cell cell;
            int rowIndex = 0;
            int colIndex = 0;
            for (Object[] dataRow : chartData) {
                row = sheet.createRow((short) rowIndex);
                colIndex = 0;
                for (Object value : dataRow) {
                    cell = row.createCell((short) colIndex);
                    if (value instanceof String) cell.setCellValue((String)value);
                    if (value instanceof Number) cell.setCellValue(((Number)value).doubleValue());
                    if (value instanceof GregorianCalendar) {
                        cell.setCellValue((GregorianCalendar)value);
                        cell.setCellStyle(dateStyle);
                    }
                    colIndex++;
                }
                rowIndex++;
            }
            
            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            
            // create the chart
            
            // chart data sources
            XDDFDataSource<Double> xs1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 4, 1, 1));
            XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 4, 0, 0));
            XDDFNumericalDataSource<Double> bSz1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 4, 0, 0));
            
            XDDFDataSource<Double> xs2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(8, 9, 1, 1));
            XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(8, 9, 0, 0));
            XDDFNumericalDataSource<Double> bSz2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(8, 9, 0, 0));

            // chart in drawing
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 3, 0, 15, 20);
            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Chart Title");
            chart.setTitleOverlay(false);
            chart.getFormattedTitle().getParagraph(0).addDefaultRunProperties().setFontSize(20d);
            
            // value axis x
            XDDFValueAxis valAxisX = chart.createValueAxis(AxisPosition.BOTTOM);
            valAxisX.setTitle("Axis Title");

            // value axis y
            XDDFValueAxis valAxisY = chart.createValueAxis(AxisPosition.LEFT);
            valAxisY.setTitle("Axis Title");
 
            // cross axes 
            valAxisY.setCrosses(AxisCrosses.AUTO_ZERO);

            // chart data
            //XDDFChartData data = chart.createData(ChartTypes.???, valAxisX, valAxisY);
            XDDFBubbleChartData data = new XDDFBubbleChartData(chart, chart.getCTChart().getPlotArea().addNewBubbleChart(), valAxisX, valAxisY);

            // series
            XDDFBubbleChartData.Series series1 = (XDDFBubbleChartData.Series)data.addSeries(xs1, ys1);
            series1.setTitle("Category 1", new CellReference(sheet.getSheetName(), 0, 1, true, true));
            // set bubble sizes
            series1.setBubbleSizes(bSz1);
            // add data labels
            // pos 8 = INT_R , showVal = true, showLegendKey= false, showCatName = true
            org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls ctDLbls = setDataLabels(series1, 8, true, false, true); 
            
            XDDFBubbleChartData.Series series2 = (XDDFBubbleChartData.Series)data.addSeries(xs2, ys2);
            series2.setTitle("Category 2", new CellReference(sheet.getSheetName(), 6, 1, true, true));            
            // set bubble sizes
            series2.setBubbleSizes(bSz2);
            // add data labels
            // pos 8 = INT_R , showVal = true, showLegendKey= false, showCatName = true
            ctDLbls = setDataLabels(series2, 8, true, false, true); 

            // plot chart
            chart.plot(data);
            
            // legend
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.RIGHT);

            // set series fill color
            solidFillSeries(data, 0, PresetColor.BLUE);
            solidFillSeries(data, 1, PresetColor.RED);
            
            // set rounded corners false
            setRoundedCorners(chart, false);
            
            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("./ooxml-bubble-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }

    private static void solidFillSeries(XDDFChartData data, int index, PresetColor color) {
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
        XDDFChartData.Series series = data.getSeries(index);
        XDDFShapeProperties properties = series.getShapeProperties();
        if (properties == null) {
            properties = new XDDFShapeProperties();
        }
        properties.setFillProperties(fill);
        series.setShapeProperties(properties);
    }
    
    private static org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls setDataLabels(XDDFChartData.Series series, int pos, boolean... show) {
        /*
        INT_BEST_FIT   1
        INT_B          2
        INT_CTR        3
        INT_IN_BASE    4
        INT_IN_END     5
        INT_L          6
        INT_OUT_END    7
        INT_R          8
        INT_T          9                             
        */
        try {
            org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls ctDLbls = null;
            if (series instanceof XDDFBarChartData.Series) {
                java.lang.reflect.Field _ctBarSer = XDDFBarChartData.Series.class.getDeclaredField("series");
                _ctBarSer.setAccessible(true);
                org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer ctBarSer =
                    (org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer)_ctBarSer.get((XDDFBarChartData.Series)series);
                if (ctBarSer.isSetDLbls()) ctBarSer.unsetDLbls();
                ctDLbls = ctBarSer.addNewDLbls();                                                        
                if (!(pos == 3 || pos == 4 || pos == 5 || pos == 7)) pos = 3; // bar chart does not provide other pos
                ctDLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.Enum.forInt(pos));
            } else if (series instanceof XDDFLineChartData.Series) {
                java.lang.reflect.Field _ctLineSer = XDDFLineChartData.Series.class.getDeclaredField("series");
                _ctLineSer.setAccessible(true);
                org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer ctLineSer =
                    (org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer)_ctLineSer.get((XDDFLineChartData.Series)series);
                if (ctLineSer.isSetDLbls()) ctLineSer.unsetDLbls();
                ctDLbls = ctLineSer.addNewDLbls();                                                      
                if (!(pos == 3 || pos == 6 || pos == 8 || pos == 9 || pos == 2)) pos = 3; // line chart does not provide other pos
                ctDLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.Enum.forInt(pos));
            } else if (series instanceof XDDFPieChartData.Series) {
                java.lang.reflect.Field _ctPieSer = XDDFPieChartData.Series.class.getDeclaredField("series");
                _ctPieSer.setAccessible(true);
                org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer ctPieSer =
                    (org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer)_ctPieSer.get((XDDFPieChartData.Series)series);
                if (ctPieSer.isSetDLbls()) ctPieSer.unsetDLbls();
                ctDLbls = ctPieSer.addNewDLbls();                                                      
                if (!(pos == 3 || pos == 1 || pos == 4 || pos == 5)) pos = 3; // pie chart does not provide other pos
                ctDLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.Enum.forInt(pos));
            } else if (series instanceof XDDFBubbleChartData.Series) {
                java.lang.reflect.Field _ctBubbleSer = XDDFBubbleChartData.Series.class.getDeclaredField("series");
                _ctBubbleSer.setAccessible(true);
                org.openxmlformats.schemas.drawingml.x2006.chart.CTBubbleSer ctBubbleSer =
                    (org.openxmlformats.schemas.drawingml.x2006.chart.CTBubbleSer)_ctBubbleSer.get((XDDFBubbleChartData.Series)series);
                if (ctBubbleSer.isSetDLbls()) ctBubbleSer.unsetDLbls();
                ctDLbls = ctBubbleSer.addNewDLbls();                                                      
                if (!(pos == 3 || pos == 2 || pos == 6 || pos == 8 || pos == 9)) pos = 3; // bubble chart does not provide other pos
                ctDLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos.Enum.forInt(pos));
            }// else if ...                                                          
                                                               
            if (ctDLbls != null) {
                ctDLbls.addNewShowVal().setVal((show.length>0)?show[0]:false);
                ctDLbls.addNewShowLegendKey().setVal((show.length>1)?show[1]:false);
                ctDLbls.addNewShowCatName().setVal((show.length>2)?show[2]:false);
                ctDLbls.addNewShowSerName().setVal((show.length>3)?show[3]:false);
                ctDLbls.addNewShowPercent().setVal((show.length>4)?show[4]:false);
                ctDLbls.addNewShowBubbleSize().setVal((show.length>5)?show[5]:false);
                ctDLbls.addNewShowLeaderLines().setVal((show.length>6)?show[6]:false);
                                
                return ctDLbls;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;        
    }    

    private static void setRoundedCorners(XDDFChart chart, boolean setVal) {
        if (chart.getCTChartSpace().getRoundedCorners() == null) chart.getCTChartSpace().addNewRoundedCorners();
        chart.getCTChartSpace().getRoundedCorners().setVal(setVal);
    }    
}

Specifics for a bubble chart are:

• All data sources need to be numeric.
• There is an additional data source for bubble sizes needed.
• There is not a category axis and a value axis but one value axis for x-values and one value axis for y-values.

Disclaimer: This code is tested and works using current apache poi 5.2.2. I know that the question mentions former version 4.1.1. But the Apache POI project is very fast in development. That's why one always should use the latest stable version, otherwise the code quickly become out of date.

This code needs the full jar of all of the schemas, which is poi-ooxml-full-5.2.2.jar for apache poi 5.2.2, as mentioned in FAQ. Note, since apache poi 5.* the formerly used ooxml-schemas-*.jar cannot be used anymore. There must not be any ooxml-schemas-*.jar in class path when using apache poi 5.*.