Bug 57074

Summary: HSSFCell.getCellStyle().getFillForegroundColorColor() returns incorrect color for xls file created using Microsoft Excel 2007
Product: POI Reporter: chandran.poorna
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: critical    
Priority: P2    
Version: 3.11-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Attachments: MS Excel 2007 created .xls file
opened with MS Excel 2007
Screenshot of the Excel output when saving the fail in 1997-2003 format

Description chandran.poorna 2014-10-09 23:48:40 UTC
Created attachment 32100 [details]
MS Excel 2007 created .xls file

I have created an .xls file using Microsoft Excel 2007. The file contains a cell with:

Background color: RGB(215, 228, 188)
Font color: RGB(0, 128, 128)

as can be seen in the Custom Colors dialog in the Excel. I tried to read the background and foreground colors as shown below:

public class ColorTest2007 {
	public static void main(String arg[]) throws Exception
		File f = new File("david2.xls");
		FileInputStream fis = new FileInputStream(f);
		HSSFWorkbook workbook = new HSSFWorkbook(fis);
		HSSFSheet sheet = workbook.getSheet("Sheet1");
		HSSFRow row = sheet.getRow(0);
		HSSFCell cell = row.getCell(0);
		HSSFColor bgColor = cell.getCellStyle().getFillBackgroundColorColor();
		System.out.println(bgColor.getTriplet()[0]+", "+bgColor.getTriplet()[1]+", "+bgColor.getTriplet()[2]);

		HSSFColor fontColor = cell.getCellStyle().getFillForegroundColorColor();
		System.out.println(fontColor.getTriplet()[0]+", "+fontColor.getTriplet()[1]+", "+fontColor.getTriplet()[2]);

which resulted in:

0, 0, 0
247, 244, 239

I have also tried to use the cell.getCellStyle().getFillBackgroundColor() and tried to retrieve the color from the custom color palette. But that also did not return the colors seen in Excel.

I am attaching the david2.xls file. This definitely looks like a bug unless the reading of the color from xls file created using MS Excel 2007 is unsupported. Please let me know if there is any other way to retrieve the correct color values.
Comment 1 Nick Burch 2014-10-10 09:40:16 UTC
3.9 is a little old, can you re-try with 3.11 beta 2?
Comment 2 chandran.poorna 2014-10-10 16:10:18 UTC
I get the same result with 3.11-beta2 as well. 

output for the given code:

0, 0, 0
247, 244, 239
Comment 3 Dominik Stadler 2014-11-10 21:32:38 UTC
When I open the file in LibreOffice and actually type into the cell, it is actually using gray text on background, which is what POI is returning, e.g. background 0, 0, 0 and foreground some high values in all three, are you sure the file is set up with correct color for this cell?
Comment 4 chandran.poorna 2014-11-18 15:26:44 UTC
Yes, I have just checked it with LibreOffice. The foreground and background colors are different with MS Excel 2007 with the same document. 

I don't see this issue with document created with MS Excel 2003. Only when I create a .xls file with MS Excel 2007. XLS file created with MS Excel 2007 is by default in compatibility mode and that's when the problem is.

I will attach a screenshot of the attached document opened with MS Excel 2007.

To summarize, the colors are different when the attached document is opened with Apache OpenOffice or LibreOffice from MS Excel 2007.
Comment 5 chandran.poorna 2014-11-18 15:27:26 UTC
Created attachment 32212 [details]
opened with MS Excel 2007
Comment 6 Dominik Stadler 2015-01-07 14:10:24 UTC
Created attachment 32352 [details]
Screenshot of the Excel output when saving the fail in 1997-2003 format

FYI, when I try to save the Excel file as "1997-2003" I get the compatibility warning as shown in the screenshot. I suppose this is related to what we see here, seems POI does not yet read those newer colors/styles (and LibreOffice has similar limitations).
Comment 7 Dominik Stadler 2015-01-07 14:16:33 UTC
There is now a reproducing unit test for this in class TestUnfixedBugs.