Bug 38486 - Error retrieving cell background colour in Excel 2003
Summary: Error retrieving cell background colour in Excel 2003
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2006-02-02 11:19 UTC by Stonefox
Modified: 2006-07-24 05:18 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Stonefox 2006-02-02 11:19:42 UTC
When I try and use the getFillBackgroundColor() method it does not return the
correct index

it returns 64 if the cell has a colour (regardless of what the colour is)
and it returns 65 if the cell has no colour

getFillForegroundColor() returns the correct colour index though, so I assume
this is a bug in getFillBackgroundColour()

I am using this on Excel 2003 if that makes any difference.

example code

int maxRows=sheet.getPhysicalNumberOfRows();

for (int i=0;i<maxRows;i++)
	//check background colour
	HSSFRow checkRow = sheet.getRow(i);
	HSSFCell checkCell = checkRow.getCell((short)0);
	HSSFCellStyle bgCol=checkCell.getCellStyle();
	String myCol=String.valueOf(bgCol.getFillBackgroundColor());
	System.out.println(i+" "+myCol+ " : "+checkCell.getNumericCellValue());
Comment 1 Jason Height 2006-07-24 12:18:13 UTC
Ok i have looked into the issue and committed a change to svn.

From experiments, i have found that the value 64 (dec) is a special case. It is
 actually the AUTOMATIC or default color in excel. This is true for everything
except the font color (Doh!) which retains 7FFF as its default color.

HOWEVER, for some reason excel uses 65 (dec) for the background when a
foreground is set to AUTOMATIC ie No Fill. Wierd.

I have added a HSSFColor.AUTOMATIC class with the 64 special case. In addition i
have "hidden" the 65 case in the HSSFCellStyle, it will not return 64 when 65
and perform the necessary conversion automatically and internally. A byproduct
of the conversion is that Foreground needs to be set before Background. this is
noted in the javadoc.

Seems to work OK, reopen if you have any issues.