如何通过Apache POI设置Excel Sunburst图表中各个数据标签的文本属性? [英] How to set the text attributes of the individual data labels in an Excel Sunburst Chart through Apache POI?

问题描述

在这个 post 提供了一个解决方案来设置数据点的图形属性.

我现在需要访问各个数据点标签并更改文本属性.我的首要任务是调整字体大小，但是，调整其他属性(例如字体、粗体、斜体、对齐等)的能力也会很有用.此方法设置数据点填充颜色.需要添加什么来设置该数据点的数据标签的字体大小?

private static void setDataPointColor(XmlObject series, int number, String colorHex) {XmlCursor 游标 = 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 Name("http://schemas.openxmlformats.org/drawingml/2006/main", "srgbClr", "a"));cursor.insertAttributeWithValue("val", colorHex);游标.处置()；}

解决方案

如问题中所述，这与 如何通过Apache POI更改Excel森伯斯特图中点的图形属性.在我的回答中，已经有人告诉我，旭日图的类型是 application/vnd.ms-office.chartex+xml，因此不能是 XSSFChart，因为它属于 application/vnd.ms-office.chartex+xml 类型代码>应用程序/vnd.openxmlformats-officedocument.drawingml.chart+xml.我的回答还提供了一种方法，可以在读写模式下获取旭日图的 XML.

当然，如果需要更改 XML，则必须了解 XML 的含义.可以阅读以 2.24.3.11 CT_ChartSpace.但我的方法如下:

*.xlsx 文件只不过是一个 ZIP 存档.因此，我使用 Excel 创建了一个简单的森伯斯特图表并将其保存在 *.xlsx 文件中.然后我解压那个 *.xlsx 文件并查看 /xl/charts/chartEx1.xml.在那里我可以看到使用的 XML.现在我使用 Excel 对森伯斯特图表进行额外的格式化，保存并查看 /xl/charts/chartEx1.xml 中的 XML已经改变.所以我可以确定使用的XML的含义.

使用这种方法，我得出的结论是，每个数据标签都可以使用 <cx:dataLabel idx="0"> 格式化，其中 idx与数据点 idx 相同.格式化在 中完成，其中包含段落 和文本运行 然后格式化.

知道了这一点，我们得出以下方法:

<预><代码>...private static void setDataLabelFontSettings(XmlObject series, int number,int fontSizePt、boolean b、boolean i、String u、Stringstrike、String typeface、String colorHex) {XmlObject[] xmlObjects = series.selectPath("声明命名空间 cx='http://schemas.microsoft.com/office/drawing/2014/chartex'" +".//cx:dataLabels");如果(xmlObjects.length == 1){XmlObject 数据标签 = xmlObjects[0];XmlCursor cursor = dataLabels.newCursor();cursor.toLastChild();cursor.beginElement(new QName("http://schemas.microsoft.com/office/drawing/2014/chartex", "dataLabel", "cx"));cursor.insertAttributeWithValue("idx", "" + number);cursor.beginElement(new QName("http://schemas.microsoft.com/office/drawing/2014/chartex", "txPr", "cx"));cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "p", "a"));cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "r", "a"));cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "t", "a"));cursor.toParent();cursor.setTextValue("dummy");cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "rPr", "a"));cursor.insertAttributeWithValue("sz", "" + (fontSizePt * 100));cursor.insertAttributeWithValue("b", ((b)?"1":"0"));cursor.insertAttributeWithValue("i", ((i)?"1":"0"));cursor.insertAttributeWithValue("u", u);cursor.insertAttributeWithValue("罢工"，罢工);cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "latin", "a"));cursor.insertAttributeWithValue("字体", 字体);cursor.toParent();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.toParent();cursor.toParent();cursor.toParent();cursor.toParent();cursor.toParent();cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "bodyPr", "a"));游标.处置()；}}...

可以在我上面链接的相关问答中的代码中使用，如下所示:

<预><代码>...//setDataLabelFontSettings(XmlObject series, int number, int fontSizePt, boolean b, boolean i,//字符串 u、字符串罢工、字符串字体、字符串 colorHex)setDataLabelFontSettings(series, 0, 14, false, true, "sng", "noStrike", "Calibri", "FFFFFF");setDataLabelFontSettings(series, 4, 12, false, true, "none", "sngStrike", "Calibri", "FFFFFF");setDataLabelFontSettings(series, 5, 8, false, true, "dbl", "noStrike", "Calibri", "FFFFFF");setDataLabelFontSettings(series, 6, 8, false, true, "none", "dblStrike", "Calibri", "FFFFFF");...

In this post a solution was provided to set the graphical attributes of the data points.

I now need to access the individual data point labels and change the text attributes as well. My priority is to adjust the font size, however, the ability to adjust other attributes (e.g. font, bold, italic, alignment, etc) as well would be useful. This method sets the data point fill colour. What needs to be added to set the font size of the data label for that data point?

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  Name("http://schemas.openxmlformats.org/drawingml/2006/main", "srgbClr", "a"));
    cursor.insertAttributeWithValue("val", colorHex);

    cursor.dispose();
}

解决方案

As told in the question already, this is related to How to change the graphical attributes of a point in an Excel sunburst chart through Apache POI. In my answer there was told already that sunburst chart is of type application/vnd.ms-office.chartex+xml and so cannot be a XSSFChartas this is of type application/vnd.openxmlformats-officedocument.drawingml.chart+xml. My answer there also provides a method to nevertheless get the XML of the sunburst chart in read and write mode.

But of course if one needs changing the XML one must know about what the XML is about. One could read Microsofts documentation starting with 2.24.3.11 CT_ChartSpace. But my approach is the following:

The *.xlsx file is nothing else than a ZIP archive. So I create a simple sunburst chart using Excel and save this in a *.xlsx file. Then I unzip that *.xlsx file and have a look at /xl/charts/chartEx1.xml. There I can see the XML used. Now I do additional formatting the sunburst chart using Excel, save and have a look at how the XML in /xl/charts/chartEx1.xml has changed. So I can determine the meaning of the used XML.

Using this approach I come to the conclusion that each single data label can be formatted using a <cx:dataLabel idx="0"> where the idx is the same as the data point idx. The formatting is done in <cx:txPr> which contains paragraph <a:p> and text run <a:r> which is formatted then.

Knowing this, we come to following method:

...
 private static void setDataLabelFontSettings(XmlObject series, int number,
  int fontSizePt, boolean b, boolean i, String u, String strike, String typeface, String colorHex) {

  XmlObject[] xmlObjects = series.selectPath(
   "declare namespace cx='http://schemas.microsoft.com/office/drawing/2014/chartex' " +
   ".//cx:dataLabels");
  if (xmlObjects.length == 1) {
   XmlObject dataLabels = xmlObjects[0];
   XmlCursor cursor = dataLabels.newCursor();
   cursor.toLastChild();
   cursor.beginElement(new QName("http://schemas.microsoft.com/office/drawing/2014/chartex", "dataLabel", "cx"));
   cursor.insertAttributeWithValue("idx", "" + number);

   cursor.beginElement(new QName("http://schemas.microsoft.com/office/drawing/2014/chartex", "txPr", "cx"));

   cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "p", "a"));

   cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "r", "a"));

   cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "t", "a"));
   cursor.toParent();
   cursor.setTextValue("dummy");

   cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "rPr", "a"));
   cursor.insertAttributeWithValue("sz", "" + (fontSizePt * 100));
   cursor.insertAttributeWithValue("b", ((b)?"1":"0"));
   cursor.insertAttributeWithValue("i", ((i)?"1":"0"));
   cursor.insertAttributeWithValue("u", u);
   cursor.insertAttributeWithValue("strike", strike);
   cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "latin", "a"));
   cursor.insertAttributeWithValue("typeface", typeface);
   cursor.toParent();
   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.toParent();
   cursor.toParent();
   cursor.toParent();
   cursor.toParent();
   cursor.toParent();
   cursor.beginElement(new QName("http://schemas.openxmlformats.org/drawingml/2006/main", "bodyPr", "a"));

   cursor.dispose();
  }
 }
...

which can be used in my code in the related Q/A linked above as follows:

...
      //setDataLabelFontSettings(XmlObject series, int number, int fontSizePt, boolean b, boolean i, 
      // String u, String strike, String typeface, String colorHex)
      setDataLabelFontSettings(series, 0, 14, false, true, "sng", "noStrike", "Calibri", "FFFFFF");
      setDataLabelFontSettings(series, 4, 12, false, true, "none", "sngStrike", "Calibri", "FFFFFF");
      setDataLabelFontSettings(series, 5, 8, false, true, "dbl", "noStrike", "Calibri", "FFFFFF");
      setDataLabelFontSettings(series, 6, 8, false, true, "none", "dblStrike", "Calibri", "FFFFFF");
...

这篇关于如何通过Apache POI设置Excel Sunburst图表中各个数据标签的文本属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！