如何通过Apache POI更改Excel森伯斯特图中点的图形属性 [英] How to change the graphical attributes of a point in an Excel sunburst chart through Apache POI

问题描述

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

I have a requirement to color the different data points in an Excel sunburst chart programatically. By default, Excel creates the chart looking like this.

我需要能够做出这样的事情.

I need to be able to make something like this.

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

I've been able to load the chart and series, what I have not been able to work out is how to get to each of the points and change the fill color, and is that even possible.

创建此图表的数据是:

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 并将其填充颜色设置为红色?

How do I now get to the say Point C.b.1 and set it's fill color to red?

提前致谢.

推荐答案

如果目标真的是修改一个 Excel 旭日图，那么获得旭日图 XML 只能在非常低的级别通过直接解析 XML.

If the goal is really to modify an Excel sunburst chart, then getting the sunburst chart XML will only be possible very low level by parsing the XML directly.

您甚至不会使用 List 获得旭日图.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 的基础.

You even will not get the sunburst chart using List <XSSFChart> charts = drawing.getCharts();. A sunburst chart is not a XSSFChart. XSSFChart is of type application/vnd.openxmlformats-officedocument.drawingml.chart+xml while sunburst chart is of type application/vnd.ms-office.chartex+xml. This is because the sunburst chart is an extended chart type which is not available in versions of Office Open XML up to year 2007. But those old versions of Office Open XML is what apache poi is developed on.

但是我们至少可以使用 apache poi 的一部分，并且必须编写 XSSFChartEx 类而不是我们自己的 XSSFChart 类.不幸的是，还需要一个 XSSFChartExRelation 类，因为这样的关系类当然也不存在.

But we can using at least parts of apache poi and must programming the XSSFChartEx class instead the XSSFChart our own then. Unfortunately also a class XSSFChartExRelation is needed because such an relation class of course also not exists already.

示例:

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.

Note: apache poi version 4.0.0 is used here.

Excel 结果:

这篇关于如何通过Apache POI更改Excel森伯斯特图中点的图形属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！