How to get cell's background color using apache poi?

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.

@AAA: Your code seems correct... what you are getting in this case... default it is showing 64 on my end... — Sankumarsingh, Oct 21 '13 at 11:11
@Sankumarsingh I am also getting 64 which is AUTOMATIC Color code it doesn't make any sense while your workbook is having some different color. — AAA, Oct 21 '13 at 11:37
but try this `cell.getCellStyle().setFillForegroundColor(HSSFColor.GOLD.index);` `System.out.println(cell.getCellStyle().getFillForegroundColor());` Will give you 51... means it's working and changing as per the color it gets. — Sankumarsingh, Oct 21 '13 at 11:39
@Sankumarsingh I tried this one also I am able to get other values other than 64 but if I set in Excel using any tool but not POI I am not able to fetch the background color. The excel files I am using are not made through POI but tool like Microsoft Excel. — AAA, Oct 21 '13 at 12:42

score 3 · Answer 1 · answered Dec 30 '13 at 07:55

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.

How would this work when cellStyle.getFillForegroundColor() or cellStyle.getFillBackgroundColor() always return default values? — Larsen, Apr 05 '20 at 04:38

Ascalonian · Answer 2 · 2014-12-23T14:31:25.123

2

Try this:

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

Notice that Color is used twice

edited Dec 23 '14 at 14:31

answered Dec 23 '14 at 14:06

Ascalonian

14,409
18
71
103

score 0 · Answer 3 · answered Oct 21 '13 at 10:59

0

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

answered Oct 21 '13 at 10:59

YoBre

2,520
5
27
37

If you will check every time and for different background colors it will give you value 64 Which is `Automatic` color – AAA Oct 21 '13 at 11:35
1

Yes, 64 refers Automatic. But how do we get actual color? – Larsen Apr 04 '20 at 18:59

score 0 · Answer 4 · answered Oct 06 '15 at 10:40

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))

score 0 · Answer 5 · answered Aug 20 '18 at 07:34

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

score 0 · Answer 6 · answered Jul 25 '22 at 06:15

There seem to be several options of getting the background color. When the color of the cell is specified in the following way:

The following returns the color code:

public static String getBackgroundColor(XSSFCell cell) {
  byte[] rgbWithTint = cell.getCellStyle().getFillForegroundColorColor().getRGBWithTint();
  if (rgbWithTint == null) {
    return null;
  }
  return Hex.encodeHexString(rgbWithTint);
}

How to get cell's background color using apache poi?

6 Answers6

Linked