POI reading cell background color

Question

I am using POI 3.8 version and trying to read the background color of a cell in an excel sheet (XLS). Ideally I wanted to copy this cell with styles to a different sheet. When I did a cloneFrom(..) method, its not copying the correct colors to the target sheet.

When I tried to read the cell's background color separately, I am not getting the correct RGB value. If I could get the correct RGB value I can create a custom palette and set the same to the target sheet's cell.

Is there a correct way of reading the background color from a cell?

I have tried the following

cell.getCellStyle().getFillBackgroundColor()
cell.getCellStyle().getFillBackgroundColorColor().getTriplet()

The above line should give me RGB value of 159/200/222, but is giving me an incorrect value of 51/204/204.

Could someone please help on the same. I have already checked this forum for all possible answers and still could not get what I want.

Howard Schutzman Howard Schutzman · Accepted Answer · 2012-06-20T17:48:27

The problem is that HSSF uses a color palette (essentially an array of colors) to define its RGB values. So the background color is actually an index into the palette. Here is some code to get the actual rgb values of the cell's background color:

    CellStyle style = cell.getCellStyle();
    short colorIdx = style.getFillForegroundColor();
    HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette();
    HSSFColor color = palette.getColor(colorIdx);
    short [] triplet = color.getTriplet();
    // triplet will contain the actual rgb values

In your output spreadsheet, you have to add a custom color to the palette. If there is an empty space in your output's palette, you can use HSSFPalette.addColor. Otherwise, you will have to replace an unused existing color using HSSFPalette.setColorAtIndex. Once you add the custom color, you use CellStyle.setFillBackgroundColor to set the cell's background color, passing in the index of the custom color you just created.

If you can use an XSSF (xlsx) file instead of an HSSF (xls) file, you would not need to fool around with a color palette. You can use actual rgb values.

POI reading cell background color

1 Answers