使用 Apache POI 更新瀑布图 [英] Updating Waterfall Chart with Apache POI

问题描述

我正在使用 Apache POI Java 库 (v4.1.1) 创建包括 Excel 图表在内的精彩 Word 文档.对于 Excel 图表，我使用 Excel 模板，并通过 Apache POI 库通过

I am using the Apache POI Java library (v4.1.1) to create wonderfull Word documents including Excel Charts. For the Excel Charts I use an Excel template and via the Apache POI library access the charts in the Word document via

List<XWPFChart> chartList = wordDoc.getCharts();  

这适用于条形图/柱形图/折线图等.但是，我无法通过此方法获取瀑布图.所以，我想知道是否有人对此有解决方案?我看到了一些选择；

This is working fine for Bar / Column / Line etc. I can't get, however, the Waterfall chart via this method. So, I am wondering if anybody has got a solution for this? I see some options;

1. 等待 Apache POI 支持它
2. 创建另一种图表类型作为模板，通过 getCharts 方法访问该图表并以编程方式更改图表类型
3. 创建自己的代码以生成瀑布图

我希望有人已经对第二个甚至第三个选项有经验?

I am hoping somebody already has experience with the second or even third option?

推荐答案

XWPFChart 类型为 application/vnd.openxmlformats-officedocument.drawingml.chart+xml 而瀑布图表的类型为 application/vnd.ms-office.chartex+xml.这是因为瀑布图是一种扩展图表类型，在 apache poi 提供的 Office Open XML 版本中不可用.我不相信 apache poi 在不久的将来会提供这样的扩展图表，因为它甚至没有提供 application/vnd.openxmlformats-officedocument.drawingml.chart+xml 直到现在.

XWPFChart is of type application/vnd.openxmlformats-officedocument.drawingml.chart+xml while waterfall chart is of type application/vnd.ms-office.chartex+xml. This is because the waterfall chart is an extended chart type which is not available in versions of Office Open XML which apache poi provides. I don't believe apache poi will provide such extended charts in near future since it not even provides all native charts of type application/vnd.openxmlformats-officedocument.drawingml.chart+xml until now.

所以到目前为止，只有通过直接更改 XML 来在非常低的级别上处理这些图表类型的选项.我在这里为旭日图展示了这一点 如何通过 Apache POI 更改 Excel 森伯斯特图中点的图形属性 和这里 如何在Excel中设置单个数据标签的文本属性通过 Apache POI 的 Sunburst 图表?.

So until now there only is the option to work on those chart types on very low level by changing the XML directly. I have shown this for a sunburst chart here How to change the graphical attributes of a point in an Excel sunburst chart through Apache POI and here How to set the text attributes of the individual data labels in an Excel Sunburst Chart through Apache POI?.

以下工作草案提供了一个非常基本的类XWPFChartEx，目前只提供方法getChartExXmlObject，该方法返回扩展图表的纯XML作为 XmlObject.可以使用低级 XML 方法以编程方式更改此 XML.因为 XWPFChartEx 扩展了 POIXMLDocumentPart，它的 commit 方法会将更改写入新的 Word 文档，然后 document.write(out).它提供了 getWorkbookPart，它返回包含图表数据的 XSSFWorkbook 的 PackagePart.此工作簿内容也必须更改(如果存在).

Following working draft provides a very basic class XWPFChartEx which up to now only provides method getChartExXmlObject which returns the plain XML of the extended chart as a XmlObject. This XML could be programmatically changed using low level XML methods. Because XWPFChartEx extends POIXMLDocumentPart, it's commit method will write that changes into the new Word document then while document.write(out). And it provides getWorkbookPart which returns the PackagePart of the XSSFWorkbook containing the chart's data. This workbook content also would mus be changed, if present.

文件 Waterfall_Chart.docx 必须至少有一个瀑布图.

The file Waterfall_Chart.docx must have at least one waterfall chart.

import java.io.IOException;
import java.io.OutputStream;
import java.io.FileOutputStream;
import java.io.FileInputStream;

import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFRelation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ooxml.POIXMLRelation;
import org.apache.poi.openxml4j.opc.PackagePart;

import org.apache.xmlbeans.XmlObject;

public class WordGetWaterfallChart {

 static XWPFChartEx getFirstXWPFChartEx(XWPFDocument document) throws Exception {
  XWPFChartEx xwpfChartEx = null;
  for (POIXMLDocumentPart dpart : document.getRelations()) {
   PackagePart ppart = dpart.getPackagePart();
   if ("application/vnd.ms-office.chartex+xml".equals(ppart.getContentType())) {
    xwpfChartEx = new XWPFChartEx(dpart);
    String rId = document.getRelationId(dpart);
    document.addRelation(
     rId, 
     new XSSFChartExRelation(
      "application/vnd.ms-office.chartex+xml",
      "http://schemas.microsoft.com/office/2014/relationships/chartEx",
      "/word/charts/chartEx#.xml"),
      xwpfChartEx
    );
    return xwpfChartEx;
   }
  }
  return xwpfChartEx;
 }

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

  XWPFDocument document = new XWPFDocument(new FileInputStream("Waterfall_Chart.docx"));

  XWPFChartEx waterfallChart = getFirstXWPFChartEx(document);
  System.out.println(waterfallChart.getChartExXmlObject());
  //TODO: change the XML

  System.out.println(waterfallChart.getWorkbookPart());
  if (waterfallChart.getWorkbookPart() != null) {
   XSSFWorkbook workbook = new XSSFWorkbook(waterfallChart.getWorkbookPart().getInputStream());
   for (Sheet sheet : workbook) {
    for (Row row : sheet) {
     for (Cell cell : row) {
      System.out.println(cell);
      //TODO: change the cell contents
     }
    }
   }
   OutputStream wbOut = waterfallChart.getWorkbookPart().getOutputStream();
   workbook.write(wbOut);
   wbOut.close();
   workbook.close();
  }

  FileOutputStream out = new FileOutputStream("Waterfall_Chart_Changed.docx");
  document.write(out);
  out.close();
  document.close();
 }

 private static class XWPFChartEx extends POIXMLDocumentPart {

  private XmlObject chartExXmlObject;
  private PackagePart workbookPart;

  private XWPFChartEx(POIXMLDocumentPart dpart) throws Exception {
   super(dpart.getPackagePart());
   this.chartExXmlObject = XmlObject.Factory.parse(dpart.getPackagePart().getInputStream());
   for (POIXMLDocumentPart.RelationPart rpart : dpart.getRelationParts()) {
    if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
         .equals(rpart.getDocumentPart().getPackagePart().getContentType())) {
     this.addRelation(
      rpart.getRelationship().getId(), 
      XWPFRelation.getInstance(rpart.getRelationship().getRelationshipType()),
      rpart.getDocumentPart()
     );
     this.workbookPart = rpart.getDocumentPart().getPackagePart();
    }
   }
  }

  private XmlObject getChartExXmlObject() {
   return this.chartExXmlObject;
  }

  private PackagePart getWorkbookPart() {
   return this.workbookPart;
  }

  @Override
  protected void commit() throws IOException {
   PackagePart part = getPackagePart();
   OutputStream out = part.getOutputStream();
   chartExXmlObject.save(out);
   out.close();
  }
 }

 private static class XSSFChartExRelation extends POIXMLRelation {
  private XSSFChartExRelation(String type, String rel, String defaultName) {
   super(type, rel, defaultName);
  }
 }
}

这篇关于使用 Apache POI 更新瀑布图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！