Search code examples
apache-poipowerpoint

how to update ext list value of pptx scatter chart

Read pptx template then use new data to replace it, there is a scatter chart enter image description here

associated excel data

enter image description here

xVal and yVal could replace successfully but how to replace C column (extList) ?

xVal and yVal replace by below manner

final CTScatterSer ser = serList.get(0);
final CTAxDataSource xVal = ser.getXVal();
final CTNumDataSource yVal = ser.getYVal();
final CTExtension ctExtension = ser.getExtLst().getExtList().get(0);
final long ptCount = xVal.getNumRef().getNumCache().getPtCount().getVal();
for (int i = 0; i < scData.size(); i++) {
    SCNameDouble data = scData.get(i);

    CTNumVal xNumVal = ptCount > i ? xVal.getNumRef().getNumCache().getPtArray(i)
            : xVal.getNumRef().getNumCache().addNewPt();
    xNumVal.setIdx(i);
    xNumVal.setV(String.format("%.2f", data.xValue));

    CTNumVal yNumVal = ptCount > i ? yVal.getNumRef().getNumCache().getPtArray(i)
            : yVal.getNumRef().getNumCache().addNewPt();
    yNumVal.setIdx(i);
    yNumVal.setV(String.format("%.2f", data.yValue));

}

final int newSize = scData.size();
xVal.getNumRef().setF(
        replaceRowEnd(xVal.getNumRef().getF(),
                ptCount,
                newSize));
yVal.getNumRef().setF(
        replaceRowEnd(yVal.getNumRef().getF(),
                ptCount,
                newSize));

xVal.getNumRef().getNumCache().getPtCount().setVal(newSize);
yVal.getNumRef().getNumCache().getPtCount().setVal(newSize);
Solution

  • Using current apache poi versions one should not trying manipulating charts using the low level CT... classes. There is XDDF for such cases now.

    If it comes to PowerPoint charts, then the need is always updating the data in the embedded workbook and updating the data in the chart. See Java edit bar chart in ppt by using poi for an example using bar chart.

    Of course a scatter chart is another case then as it not has a category axis but has two value axes. But this also can be updated using XDDF.

    The biggest problem you have is the data labels. There is not full support for chart data labels in XDDF upto now. And since you are talkig about extLst and your Excel table shows the data labels in a cell range, I suspect you have set the data labels comming from a cell range. This is a new feature which was not present when Microsoft had published Office Open XML. So not even the low level CT... classes are able to support that feature.

    The only way is to manipulate the XML using pure XML manupulating based on org.apache.xmlbeans.XmlObject.

    The following shows this on sample of a template you seems to use according to your question.

    ScatterChartSample.pptx:

    enter image description here

    Code:

    import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.AreaReference;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;

public class PowerPointChangeScatterChartData {

 //patched version of XSSFTable.updateHeaders, see https://stackoverflow.com/questions/55532006/renaming-headers-of-xssftable-with-apache-poi-leads-to-corrupt-xlsx-file/55539181#55539181
 static void updateHeaders(XSSFTable table) {
  XSSFSheet sheet = (XSSFSheet)table.getParent();
  CellReference ref = table.getStartCellReference();

  if (ref == null) return;

  int headerRow = ref.getRow();
  int firstHeaderColumn = ref.getCol();
  XSSFRow row = sheet.getRow(headerRow);
  DataFormatter formatter = new DataFormatter();

  if (row != null /*&& row.getCTRow().validate()*/) {
   int cellnum = firstHeaderColumn;
   CTTableColumns ctTableColumns = table.getCTTable().getTableColumns();
   if(ctTableColumns != null) {
    for (CTTableColumn col : ctTableColumns.getTableColumnList()) {
     XSSFCell cell = row.getCell(cellnum);
     if (cell != null) {
      col.setName(formatter.formatCellValue(cell));
     }
     cellnum++;
    }
   }
  }
 }

 static void updateScatterChart(XSLFChart chart, Object[][] data) throws Exception {
  // get chart's data source which is a Excel sheet
  XSSFWorkbook chartDataWorkbook = chart.getWorkbook();
  String sheetName = chartDataWorkbook.getSheetName(0);
  XSSFSheet chartDataSheet = chartDataWorkbook.getSheet(sheetName);
  // current Office uses a table as data source
  // so get that table if present
  XSSFTable chartDataTable = null;
  if (chartDataSheet.getTables().size() > 0) {
   chartDataTable = chartDataSheet.getTables().get(0);
  }

  if (chart.getChartSeries().size() == 1) { // we will process only one chart data
   XDDFChartData chartData = chart.getChartSeries().get(0);
   if (chartData.getSeriesCount() == 1) { // we will process only templates having one series

    int rMin = 1; // first row (0) is headers row
    int rMax = data.length - 1;

    // column 0 is X-Values
    int c = 0;
    // set new x data
    XDDFDataSource xs = null;
    for (int r = rMin; r <= rMax; r++) {
     XSSFRow row = chartDataSheet.getRow(r); if (row == null) row = chartDataSheet.createRow(r);
     XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
     cell.setCellValue((Double)data[r][c]); // in sheet
    }
    xs = XDDFDataSourcesFactory.fromNumericCellRange(chartDataSheet, new CellRangeAddress(rMin,rMax,c,c)); // in chart
    
    // set new x-title in sheet
    String xTitle = (String)data[0][c];
    chartDataSheet.getRow(0).getCell(c).setCellValue(xTitle); // in sheet

    // column 1 is Y-Values 
    c = 1;
    // set new y data in sheet and in chart
    XDDFNumericalDataSource<Double> ys = null;
    for (int r = rMin; r <= rMax; r++) {
     XSSFRow row = chartDataSheet.getRow(r); if (row == null) row = chartDataSheet.createRow(r);
     XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
     cell.setCellValue((Double)data[r][c]); // in sheet
    }
    ys = XDDFDataSourcesFactory.fromNumericCellRange(chartDataSheet, new CellRangeAddress(rMin,rMax,c,c)); 
    XDDFChartData.Series series1 = chartData.getSeries(0);
    series1.replaceData(xs, ys); // in chart

    // set new y-title in sheet and in chart
    String yTitle = (String)data[0][c];
    chartDataSheet.getRow(0).getCell(c).setCellValue(yTitle); // in sheet
    series1.setTitle(yTitle, new CellReference(sheetName, 0, c, true, true)); // in chart

    series1.plot(); 
    
    // column 2 is data-labels-range
    c = 2;
    // set new data labels data in sheet and in chart
    XDDFDataSource dataLabelsRangeSource = null;
    for (int r = rMin; r <= rMax; r++) {
     XSSFRow row = chartDataSheet.getRow(r); if (row == null) row = chartDataSheet.createRow(r);
     XSSFCell cell = row.getCell(c); if (cell == null) cell = row.createCell(c);
     cell.setCellValue((String)data[r][c]); // in sheet
    }
    dataLabelsRangeSource = XDDFDataSourcesFactory.fromStringCellRange(chartDataSheet, new CellRangeAddress(rMin,rMax,c,c)); // in chart
    updateDataLabelsRange(chart, dataLabelsRangeSource); // in chart

    // set new data-labels-title in sheet
    String descrTitle = (String)data[0][c];
    chartDataSheet.getRow(0).getCell(c).setCellValue(descrTitle); // in sheet
    
    // update the table if present
    if (chartDataTable != null) {
     CellReference topLeft = new CellReference(chartDataSheet.getRow(0).getCell(0));
     CellReference bottomRight = new CellReference(chartDataSheet.getRow(rMax).getCell(c));
     AreaReference tableArea = chartDataWorkbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
     chartDataTable.setArea(tableArea);
     updateHeaders(chartDataTable);
    }

   }
  }
 }  
 
 static void updateDataLabelsRange(XDDFChart chart, XDDFDataSource dataLabelsRangeSource) {
  String declareNameSpaces = "declare namespace c='http://schemas.openxmlformats.org/drawingml/2006/chart'; " 
                           + "declare namespace c15='http://schemas.microsoft.com/office/drawing/2012/chart' ";
  org.apache.xmlbeans.XmlObject[] selectedObjects = chart.getCTChart().selectPath(
   declareNameSpaces 
   + ".//c:ext[c15:datalabelsRange]"); // needs net.sf.saxon - Saxon-HE (Saxon-HE-10.6.jar)
 
  if (selectedObjects.length > 0) { // we have at least one ext containing datalabelsRange
   org.apache.xmlbeans.XmlObject ext = selectedObjects[0]; // get first ext containing datalabelsRange
   // get dataLabelsRange
   org.apache.xmlbeans.XmlObject[] datalabelsRanges = ext.selectChildren(new javax.xml.namespace.QName("http://schemas.microsoft.com/office/drawing/2012/chart", "datalabelsRange", "c15"));
   org.apache.xmlbeans.XmlObject dataLabelsRange = datalabelsRanges[0];
   // set formula
   org.apache.xmlbeans.XmlObject[] formulas = dataLabelsRange.selectChildren(new javax.xml.namespace.QName("http://schemas.microsoft.com/office/drawing/2012/chart", "f", "c15"));
   org.apache.xmlbeans.XmlObject formula = formulas[0];
   ((org.apache.xmlbeans.impl.values.XmlObjectBase)formula).setStringValue(dataLabelsRangeSource.getFormula());
   // get dlblRangeCache
   org.apache.xmlbeans.XmlObject[] dlblRangeCaches = dataLabelsRange.selectChildren(new javax.xml.namespace.QName("http://schemas.microsoft.com/office/drawing/2012/chart", "dlblRangeCache", "c15"));
   org.apache.xmlbeans.XmlObject dlblRangeCache = dlblRangeCaches[0];
   // empty the cache
   dlblRangeCache.newCursor().removeXmlContents();
   // create new cache from dataLabelsRangeSource
   org.openxmlformats.schemas.drawingml.x2006.chart.CTStrData cache = org.openxmlformats.schemas.drawingml.x2006.chart.CTStrData.Factory.newInstance();
   dataLabelsRangeSource.fillStringCache(cache);
   // set new cache
   dlblRangeCache.set(cache);   
  }
 }

 public static void main(String[] args) throws Exception {

  String filePath = "ScatterChartSample.pptx"; // has template scatter chart
  String filePathNew = "ScatterChartSample_New.pptx";

  Object[][] data = new Object[][] { // new data 1 series, 6 x-y-values and data labels
   {"X-Values", "Y-Values", "DataLabels"}, // series title
   {0.7d, 1.7d, "aa"}, // x1
   {1.8d, 3.2d, "bb"}, // x2
   {2.6d, 2.8d, "cc"}, // x3
   {1.7d, 3.7d, "dd"}, // x4
   {2.8d, 4.2d, "ee"}, // x5
   {3.6d, 1.8d, "ff"} // x6
  };

  XMLSlideShow slideShow = new XMLSlideShow(new FileInputStream(filePath));

  XSLFChart chart = slideShow.getCharts().get(0);

  updateScatterChart(chart, data);

  FileOutputStream out = new FileOutputStream(filePathNew); 
  slideShow.write(out);
  out.close();
  slideShow.close();
 }

}

    Resulting ScatterChartSample_New.pptx:

    enter image description here

    Note: Tested and works using current apache poi 5.2.0.

    To be able to use XPath as .//c:ext[c15:datalabelsRange] it needs net.sf.saxon - Saxon-HE (Saxon-HE-10.6.jar in my case).

    And it needs poi-ooxml-full-5.2.0.jar and not only the lite version of ooxml-schemas.