java - 如何通过 Apache POI 更改 Excel 旭日图中点的图形属性

Question

我需要以编程方式为 Excel 旭日图中的不同数据点着色。默认情况下，Excel 创建的图表如下所示。

我需要能够制作这样的东西。

我已经能够加载图表和系列，但我无法解决的是如何到达每个点并更改填充颜色，这是否可能。

创建此图表的数据是:

Level 1,Level 2,Level 3,Series 1
A,A.a,A.a.1,5
A,A.a,A.a.2,5
A,A.b,A.b.1,5
A,A.b,A.b.2,5
B,B.a,B.a.1,5
B,B.a,B.a.2,5
B,B.b,B.b.1,5
B,B.b,B.b.2,5
C,C.a,C.a.1,5
C,C.a,C.a.2,5
C,C.b,C.b.1,5
C,C.b,C.b.2,5

到目前为止我的代码

import java.io.IOException;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;

public class Format {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        try {
            XSSFWorkbook xwb = new XSSFWorkbook("ChartExample.xlsx");
            XSSFSheet sheet = xwb.getSheetAt(0);
            System.out.println("Loaded sheet is " + sheet.getSheetName());
            XSSFDrawing drawing = sheet.getDrawingPatriarch();
            List <XSSFChart> charts = drawing.getCharts();
            System.out.println("No of Charts " + charts.size());
            XSSFChart chart = charts.get(0);
            List<XDDFChartData> series = chart.getChartSeries();
            System.out.println("No of Data Series " + series.size());
            XDDFChartData data = series.get(0);
            // How do I now get to the data points and then set the fill color for that point?
            xwb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

我现在如何到达 C.b.1 点并将其填充颜色设置为红色？

提前致谢。

Answer 1

如果目标确实是修改 Excel旭日图，那么只有通过直接解析 XML 才能在非常低的级别上获取旭日图 XML。

您甚至无法使用 List <XSSFChart> charts = drawing.getCharts(); 获得旭日图。 。旭日图不是 XSSFChart 。 XSSFChart类型为application/vnd.openxmlformats-officedocument.drawingml.chart+xml而旭日图的类型为 application/vnd.ms-office.chartex+xml 。这是因为旭日图是一种扩展图表类型，2007 年之前的 Office Open XML 版本中不提供该图表类型。但是这些旧版本的 Office Open XML 就是 apache poi 。开发于。

但我们至少可以使用 apache poi 的部分内容并且必须对 XSSFChartEx 进行编程类而不是 XSSFChart那么我们自己的。可惜也是一类XSSFChartExRelation之所以需要，是因为这样的关系类当然还不存在。

示例:

Excel 来源:

代码:

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

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFDrawing;

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

import org.apache.xmlbeans.XmlObject;
import org.apache.xmlbeans.XmlCursor;
import javax.xml.namespace.QName;

public class FormatSunBurstChart {

 private static void setDataPointColor(XmlObject series, int number, String colorHex) {
  XmlCursor cursor = series.newCursor();
  cursor.toLastChild();
  cursor.beginElement(new QName("http://schemas.microsoft.com/office/drawing/2014/chartex", "dataPt", "cx"));
  cursor.insertAttributeWithValue("idx", "" + number);
  cursor.beginElement(new QName("http://schemas.microsoft.com/office/drawing/2014/chartex", "spPr", "cx"));
  cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "solidFill", "a"));
  cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "srgbClr", "a"));
  cursor.insertAttributeWithValue("val", colorHex);

  cursor.dispose();
 }

 public static void main(String[] args) {
  try {
   XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("ChartExample.xlsx"));
   XSSFSheet sheet = workbook.getSheetAt(0);
   System.out.println("Loaded sheet is " + sheet.getSheetName());
   XSSFDrawing drawing = sheet.getDrawingPatriarch();
   if (drawing != null) {
    for (POIXMLDocumentPart dpart : drawing.getRelations()) {
     PackagePart ppart = dpart.getPackagePart();
     if ("application/vnd.ms-office.chartex+xml".equals(ppart.getContentType())) {
      XSSFChartEx xssfChartEx = new XSSFChartEx(ppart);
      String rId = drawing.getRelationId(dpart);
      drawing.addRelation(
       rId, 
       new XSSFChartExRelation(
        "application/vnd.ms-office.chartex+xml",
        "http://schemas.microsoft.com/office/2014/relationships/chartEx",
        "/xl/charts/chartEx#.xml"),
       xssfChartEx
      );
      XmlObject series = xssfChartEx.getSeries(0);
      setDataPointColor(series, 1, "FF0000");
      setDataPointColor(series, 2, "FFFF00");
      setDataPointColor(series, 3, "00FF00");
      setDataPointColor(series, 14, "FFFF00");
      setDataPointColor(series, 16, "00FF00");
      setDataPointColor(series, 18, "00FF00");
      setDataPointColor(series, 19, "FF0000");
      setDataPointColor(series, 20, "00FF00");
System.out.println(series);
     }
    }
   }
   FileOutputStream out = new FileOutputStream("ChartExampleChanged.xlsx");
   workbook.write(out);
   workbook.close();
   out.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }

 private static class XSSFChartEx extends POIXMLDocumentPart {

  private XmlObject chartExXmlObject;

  private XSSFChartEx(PackagePart part) throws Exception {
   super(part);
   chartExXmlObject = XmlObject.Factory.parse(part.getInputStream());
  }

  private XmlObject getChartExXmlObject() {
   return chartExXmlObject;
  }

  private XmlObject getSeries(int number) {
   XmlObject[] result = chartExXmlObject.selectPath(
    "declare namespace cx='http://schemas.microsoft.com/office/drawing/2014/chartex' " +
    ".//cx:chart/cx:plotArea/cx:plotAreaRegion/cx:series"
   );
   return result[number];
  }

  @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版本4.0.0此处使用。

Excel 结果: