How to get cell's background color using apach

Question

How do we get background color of a XSSFCell. I tried using XSSFCellStyle but no luck.

FileInputStream fis = new FileInputStream(fileName);
XSSFWorkbook book = new XSSFWorkbook(fis);
XSSFSheet sheet = book.getSheetAt(0);
XSSFRow row = sheet.getRow(0);

System.out.println(row.getCell(0).getCellStyle().getFillForegroundColor());

Using these steps I am not able to get background color representation in Short type.

Answer 1

The following is in Scala but it does show exactly how to get the colour from the object model. I wanted to instantiate a java.awt.Color object from the actual rgb values (which is useful partly because my debugger displays for me the actual colour of the object when I stop at breakpoints, and partly because this is for export to systems that have nothing to do with Excel). I'm ignoring the colour's alpha value and my Scala may be a bit naive. I'd suggest that if this doesn't work for you, you should set a break-point and examine the result of closely related method calls such as getFillBackgroundColorColor()

val rgb: Array[Byte] = cell.getCellStyle.getFillForegroundColorColor.getRgb
def toInt(b: Byte): Int = {
  if (b<0) 256+b else b
}
val rgbInts = rgb.map(toInt)
val color = new Color(rgbInts(0),rgbInts(1),rgbInts(2))

Answer 2

Try this:

row.getCell(0).getCellStyle().getFillForegroundColorColor().getARGBHex()

Notice that Color is used twice

Answer 3

All the time its giving me no 64 this is my code

   for(Row r : my_sheet) {
        for (Cell c : r) {

            System.out.println(c.getCellStyle().getFillBackgroundColor() );
            //if foreground filter color is not green then hide the record
            if ( c.getColumnIndex()==1  && c.getCellStyle().getFillBackgroundColor() !=17){
                r1=(XSSFRow) c.getRow();
                if (r1.getRowNum()!=0) { /* Ignore top row */
                    /* Hide Row that does not meet Filter Criteria */
                    r1.getCTRow().setHidden(true); }
            }
        }
    }

Answer 4

Checkout this URL:

https://issues.apache.org/bugzilla/show_bug.cgi?id=45492

Cell cell = row.getCell(1);
            CellStyle cellStyle = cell.getCellStyle();          
            System.out.println("color = " + getColorPattern(cellStyle.getFillForegroundColor()));




private short[] getColorPattern(short colorIdx){        
    short[] triplet = null;
    HSSFColor color = palette.getColor(colorIdx);
    triplet = color.getTriplet();       
    System.out.println("color : " + triplet[0] +"," + triplet[1] + "," + triplet[2]);
    return triplet;
}

This returns the RGB codes but not exact ones. But its more or less the same color returned when compared with the actual color code in the XLS custom color picker.

Answer 5

I'm working in scala but it's the same. Your code is right.

This is my, see if you can find differences:

val wb = new XSSFWorkbook(path)
for (id <- 0.until(sheetTot)) {
    val sh = wb.getSheetAt(id)    
    print(sh.rowIterator().next().cellIterator().next().getCellStyle().getFillBackgroundColor())
}

in my case the result is 64