POI Excel: get style name

Question

I want to read the name given to a style applied to a cell in a xlsx document. I have extracted the file and in the xl/styles.xml i can find the style name:

<cellStyles count="3">
  <cellStyle xfId="2" builtinId="7" name="Currency [0]"/>
  <cellStyle xfId="0" builtinId="0" name="Normal"/>
  <cellStyle xfId="1" name="test style"/>
</cellStyles>

The style name i want is "test style". Currently i have the following code, and i can get the xfId but not the name:

@Override
public String getName(Workbook table, XSSFCell cell, String value) {
   XSSFCellStyle cellStyle = cell.getCellStyle();
   CellColor cellColor = new CellColor(cellStyle);
   int xfId = cellStyle.getCoreXf().getFillId();

   //todo: fint name, not xfId

   return null;
}

Does anyone know if i can get the style name with poi, and how i would go about going it?

If this is not possible can i then get the background color based on the xfId as rgb ?

regards

Answer 1

After a lot of digging i found a solution. And i thought i would share it here. I still haven't found the name of the style. But i have found a way to get the color.

The CellStyle has an xf object witch holds a reference index for the used fill. You can get the fills from the Workbooks StylesTable.

The fill references colors in different ways depending on what color it is. Either it just has an rgb string that you can parse or it has a theme id and a tint value.

You can get the themes from the StylesTable just like the fills. and the theme has an rgb value. I am not sure how to apply the tint, but in my tests it hasn't been necessary.

private int red;
private int green;
private int blue;

public void loadRgb(XSSFWorkbook table, XSSFCellStyle variableStyle) {
   long fillId = variableStyle.getCoreXf().getFillId();
   StylesTable stylesSource = table.getStylesSource();
   XSSFCellFill fill = stylesSource.getFillAt((int) fillId);
   CTColor fgColor = fill.getCTFill().getPatternFill().getFgColor();
   if (fgColor != null) {
      if (fgColor.xgetRgb() != null) {
         convert(fgColor.xgetRgb().getStringValue());
      } else {
         convert(stylesSource.getTheme().getThemeColor((int) fgColor.getTheme()).getRgb());
      }
   }
}

private void convert(String stringValue) {
   // the string value contains an alpha value, so we skip the first 2 chars
   red = Integer.valueOf(stringValue.substring(2, 4), 16).intValue();
   green = Integer.valueOf(stringValue.substring(4, 6), 16).intValue();
   blue = Integer.valueOf(stringValue.substring(6, 8), 16).intValue();
}

private void convert(byte[] rgb) {
   if (rgb != null) {
      // Bytes are signed, so values of 128+ are negative!
      // 0: red, 1: green, 2: blue
      red = (rgb[0] < 0) ? (rgb[0] + 256) : rgb[0];
      green = (rgb[1] < 0) ? (rgb[1] + 256) : rgb[1];
      blue = (rgb[2] < 0) ? (rgb[2] + 256) : rgb[2];
   }
}

Answer 2

I found this question while looking for the part that has remained unanswered: How do you find the name of the style?

First of all, it seems that the styles returned from XSSFCells do not correlate to the cellStyle section in the styles.xml. Rather it seems to be another section called cellStyleXfs. Anyhow, I ended up digging into the CT styles to find the information.

Long story short, the following code worked for me to find the names of the styles:

XSSFWorkbook wb = new XSSFWorkbook(...);
StylesTable stylesTable = wb.getStylesSource();
CTStylesheet ct = stylesTable.getCTStylesheet();

CTCellStyles cellStyles = ct.getCellStyles();

// Prints the count from: <cellStyles count="3516">
System.out.println("Number of CT styles: " + cellStyles.getCount());

for (CTCellStyle style : cellStyles.getCellStyleList()) {
    // Prints the name
    // Example: <cellStyle name="Note 2" xfId="3506"/>
    // Prints:  Note 2
    System.out.println(style.getName());
}

However, to get this to work, you must use ooxml-schemas.jar instead of the stripped-down version shipped with POI (poi-ooxml-schemas.jar). I found it here. Otherwise, classes like CTCellStyles and CTCellStyle will not be found (this e-mail thread discusses different options).