How to retrieve the hexadecimal value (like Hex: #E5FFCC) from XSSFColor object

Question

I am using Apache POI to read .xlsx file. I want to retrieve the Hexadecimal code of the cell background color. I can see the function to print rgb values but I am looking for Hex code.

Workbook workbook = WorkbookFactory.create(new FileInputStream (new File(SAMPLE_XLSX_FILE_PATH)));
    Sheet sheet = workbook.getSheetAt(0);
    DataFormatter dataFormatter = new DataFormatter();
    for (Row row: sheet) {
        for(Cell cell: row) {
            String cellValue = dataFormatter.formatCellValue(cell);
            XSSFCellStyle cellStyle = (XSSFCellStyle)cell.getCellStyle();
            XSSFColor cellColor = cellStyle.getFillForegroundXSSFColor();
            if(cellValue.equals("sh")){
                System.out.print(cellValue + "\t");
                System.out.println(" cellColor 0: " + cellColor.getRgb()[0]);
                System.out.println(" cellColor 1: " + cellColor.getRgb()[1]);
                System.out.println(" cellColor 2: " + cellColor.getRgb()[2]);
         }
     }

Update: 1

As suggested by @Zardo:

if(cellValue.equals("sh")){
                System.out.print(cellValue + "\n");
                System.out.println(" cellColor 0: " + cellColor.getRgb()[0]);
                System.out.println(" cellColor 1: " + cellColor.getRgb()[1]);
                System.out.println(" cellColor 2: " + cellColor.getRgb()[2]);
                String hex = String.format("#%02x%02x%02x", cellColor.getRgb()[0],cellColor.getRgb()[1], cellColor.getRgb()[2]);
                System.out.println(hex.toUpperCase());
            }

Output is:

sh
cellColor 0: -1
cellColor 1: -52
cellColor 2: -1
#FFCCFF

I do not think its giving correct color info.

Doesn't the [getARGBHex()](https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/ExtendedColor.html#getARGBHex--) method do what you need? — Gagravarr, Sep 11 '18 at 14:15
I am using 3.6 version of Apache poi. I do not have this method. Which version has are you using @Gagravarr — Manvi, Sep 11 '18 at 14:17
Apache POI 3.6 is ancient, and has a huge number of known bugs, and a few known security issues! You really need to update ASAP! POI 3.11 looks to be the first version with that method in though — Gagravarr, Sep 11 '18 at 15:13

score 1 · Accepted Answer · answered Aug 01 '19 at 17:36

The reason you're getting that color is because the getRgb() function that you are using actually returns transparency as well (argb). This value comes first, and you just need to account for the extra value in the array:

if(cellValue.equals("sh")){
    System.out.print(cellValue + "\n");
    System.out.println(" cellColor 0: " + cellColor.getRgb()[0]);
    System.out.println(" cellColor 1: " + cellColor.getRgb()[1]);
    System.out.println(" cellColor 2: " + cellColor.getRgb()[2]);
    System.out.println(" cellColor 3: " + cellColor.getRgb()[3]);
    String hex = String.format("#%02x%02x%02x%02x", cellColor.getRgb()[0],cellColor.getRgb()[1], cellColor.getRgb()[2],cellColor.getRgb()[3]);
    System.out.println(hex.toUpperCase());
}

Most color systems don't like the extra hex transparency though. Either skip the first value or chop it off when you're satisfied the color was read correctly. If you want to turn it into a regular java.awt.Color, it might look like this:

Color.decode("0x"+colorHexStr.substring(2, colorHexStr.length()))

In addition, you might find that XSSFColor cellColor = cellStyle.getFillForegroundXSSFColor(); gives a null result unexpectedly. I believe this has to do with differing schemes for encoding the color in excel. I was able to get the color via:

CTColor ctFillColor=((XSSFCellStyle) cell.getCellStyle()).getFillBackgroundXSSFColor().getCTColor();
byte[] argb=ctFillColor.getRgb();
String hexColor=String.format("#%02x%02x%02x%02x", argb[0],argb[1], argb[2],argb[3]);
System.out.println(hexColor.toUpperCase());

score 0 · Answer 2 · answered Sep 11 '18 at 13:46

0

If you have RGB values you can convert them to hex color (or I'm missing something?)

Convert a RGB Color Value to a Hexadecimal

answered Sep 11 '18 at 13:46

Neo

1,337
4
21
50

How to retrieve the hexadecimal value (like Hex: #E5FFCC) from XSSFColor object

2 Answers2