How to set the text attributes of the individual data labels in an Excel Sunburst Chart through Apache POI?

Question

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

Axel Richter Axel Richter · Accepted Answer · 2019-03-04T16:51:25

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

How to set the text attributes of the individual data labels in an Excel Sunburst Chart through Apache POI?

1 Answers