java - Apache POI plot area with multiple charts

Question

I'm trying to use a PowerPoint file as template to produce another powerpoint replacing carts data. To keep the look as similar as possible with the template, I'm just trying to replace data I need to replace, replicating all other data from the first CT*Ser of the chart. Everything works fine with most kind of charts (at the moment I'm interested in pie charts, bar charts and line charts) when plot areas have only one chart inside.

When plot areas have more than one chart (in my case a BarChart with several series and a LineChart with just one), something looks to be wrong and Microsoft PowerPoint can't open the file or part of it because it seems to be managed.

To accomplish this, I'm executing the following code for every chart, and chart type (graph is an object I use to keep references in template file).

if (graph.getType().equals(CTArea3DChart.class.getName())) {
    CTArea3DChart c = plot.getArea3DChartArray()[graph.getIndex()];
    int removeSeries = c.getSerArray().length;
    for (int s = 0; s < graph.getSeries().size(); s++) {
        CTAreaSer ser = (CTAreaSer) c.getSerArray(0).copy();
        ser.getIdx().setVal(s);
        ser.getOrder().setVal(s);
        //ser.unsetSpPr();
        Serie serie = graph.getSeries().get(s);
        boolean delete = ser.isSetDLbls() && ser.getDLbls().getDLblArray().length > 0;
        if (delete) {
            int cntDlbls = ser.getDLbls().getDLblArray().length;
            for (int d = 0; d < cntDlbls; d++) {
                ser.getDLbls().removeDLbl(0);
            }
        }
        ser.unsetTx();
        CTStrRef serTitle = ser.addNewTx().addNewStrRef();
        serTitle.setF(graph.getSheetName() + "!$" + (char) ('B' + s) + "$1");
        CTStrData serTitleData = serTitle.addNewStrCache();
        serTitleData.addNewPtCount().setVal(1);
        CTStrVal serTitleDataVal = serTitleData.addNewPt();
        serTitleDataVal.setIdx(0);
        serTitleDataVal.setV(serie.getTitle());
        // Cat
        CTAxDataSource cat = ser.getCat();
        if (cat.isSetNumRef()) {
            cat.unsetNumRef();
        }
        if (cat.isSetStrRef()) {
            cat.unsetStrRef();
        }
        if (serie.isNumericCategories()) {
            CTNumRef numRef = cat.addNewNumRef();
            numRef.setF(graph.getSheetName() + "!$A$2:$A$" + (serie.getCategories().size() + 1));
            CTNumData data = numRef.addNewNumCache();
            data.setFormatCode("General");
            CTUnsignedInt cnt = data.addNewPtCount();
            cnt.setVal(serie.getCategories().size());
            int r = 0;
            for (String v : serie.getCategories()) {
                CTNumVal pt = data.addNewPt();
                pt.setIdx(r++);
                pt.setV(StringUtils.isBlank(v) ? "0" : v);
                if (delete) {
                    CTDLbl dlbl = ser.getDLbls().addNewDLbl();
                    dlbl.addNewIdx().setVal(r - 1);
                    dlbl.addNewDelete().setVal(true);
                }
            }
        }
        else {
            CTStrRef strRef = cat.addNewStrRef();
            strRef.setF(graph.getSheetName() + "!$A$2:$A$" + (serie.getCategories().size() + 1));
            CTStrData data = strRef.addNewStrCache();
            CTUnsignedInt cnt = data.addNewPtCount();
            cnt.setVal(serie.getCategories().size());
            int r = 0;
            for (String v : serie.getCategories()) {
                CTStrVal pt = data.addNewPt();
                pt.setIdx(r++);
                pt.setV(v);
                if (delete) {
                    CTDLbl dlbl = ser.getDLbls().addNewDLbl();
                    dlbl.addNewIdx().setVal(r - 1);
                    dlbl.addNewDelete().setVal(true);
                }
            }
        }
        // Values
        CTNumDataSource val = ser.getVal();
        val.unsetNumRef();
        CTNumRef numRef = val.addNewNumRef();
        char letter = (char) ('B' + s);
        numRef.setF(graph.getSheetName() + "!$" + letter + "$2:$" + letter + "$" + (serie.getValues().size() + 1));
        CTNumData data = numRef.addNewNumCache();
        data.setFormatCode("General");
        CTUnsignedInt cnt = data.addNewPtCount();
        cnt.setVal(serie.getValues().size());
        int r = 0;
        for (String v : serie.getValues()) {
            CTNumVal pt = data.addNewPt();
            pt.setIdx(r++);
            pt.setV(StringUtils.isBlank(v) ? "0" : v);
        }
        c.addNewSer().set(ser);
    }
    for (int s = 0; s < removeSeries; s++) {
        c.removeSer(0);
    }
}

Summarizing:

1. Cloning the first CT*Ser;
2. Replacing idx, order and tx;
3. If the CT*Ser has DLbls with DLbl tags inside, I remove the DLbl and then add them with delete set to true;
4. Modify Cat;
   1. If categories are all numeric values, I use NumRefs, else StrRefs;
5. Modify Val;
6. Remove previous series

I change the backing Excel resource too, with just the data to build the graph (with one sheet per graph in the plot area). I checked references to cells and cell ranges too and they are ok.

I checked the produced file with the following software, with reported results:

• Libre Office (works as I expect)
• Onlyoffice (works, but it seems not to support multiple charts per plot area?)
• Microsoft Powerpoint desktop (errors opening the file, tries to restore, but fails and does not load slides contents from the wrong slide on)
• Microsoft Powerpoint online (does not open the file)

Is there a relatively simple way to validate powerpoint files and find the errors preventing the file to work with Powerpoint? Is there any way to reliably diagnose those problems? Working on linux?

The template file has been created with Powerpoint. I'm sorry if I don't report software versions, but I don't remember them (Powerpoint is not on my PC).

Please help me to understand what is wrong, because I'm actually starting to feel stupid. Any suggestion will be absolutely appreciated. Thank you so much.

Answer 1

I finally found what is wrong (or at least it seems to be): as in posted code, for every graph I set a progressive ID as idx and order for the element, but I started from 0 for every graph in the plot area. Keeping those idx and order unique for elements inside each plot area, it worked.

Hope this can help someone in the future.