| Summary: | Error retrieving cell background colour in Excel 2003 | ||
|---|---|---|---|
| Product: | POI | Reporter: | Stonefox <thestonefox> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | P2 | ||
| Version: | 2.5-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
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. Jason |
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()); }