如何更新pptx散点图的ext列表值

Question

关联的excel数据

xVal和yVal可以成功替换，但如何替换C列（extList）？

xVal和yVal替换为以下方式

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);

Answer 1

使用当前的apache poi版本，人们不应该尝试使用低级别的CT来操作图表 类。对于这种情况，现在有XDDF。

如果涉及PowerPoint图表，则需要始终更新嵌入工作簿中的数据并更新图表中的数据。有关使用条形图的示例，请参阅ppt中的Java edit bar chart by using poi。

当然散点图是另一种情况，因为它没有类别轴，但有两个值轴。但这也可以使用XDDF更新。

最大的问题是数据标签。到目前为止，XDDF中还不完全支持图表数据标签。由于您正在谈论extLst，并且您的Excel表显示了单元格范围内的数据标签，我怀疑您设置了来自单元格范围的数据标签。这是Microsoft发布Office Open XML时没有的一项新功能。因此，即使是低水平的CT 类能够支持该功能。

唯一的方法是使用基于组织的纯XML操作来操作XML。阿帕奇。xmlbeans。XmlObject。

下面在一个模板示例上显示了这一点，您似乎根据您的问题使用了该模板。

ScatterChartSample。pptx：

代码：

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();
 }

}

结果<代码>散点图Sample\u New。pptx：

注意：使用当前的apache poi 5.2.0测试并工作。

能够将XPath作为使用//c： 扩展[c15:datalabelsRange]它需要net。旧金山。萨克森-saxon-HE（saxon-HE-10.6.jar在我的例子中）。

它需要poi-ooxml-full-5.2.0。jar，而不仅仅是ooxml模式的精简版本。