Bug 56548

Summary: XSSFCellStyle.getFillBackgroundColor should return 65 (not 64) when a fillBackgroundColor has not been explicitly set
Product: POI Reporter: Jerry Williamson <jerry.williamson>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal CC: basinilya
Priority: P2    
Version: 3.10-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on:    
Bug Blocks: 55752    
Attachments: Test case
sample file produced by the test.
second sample.xlsx

Description Jerry Williamson 2014-05-20 12:54:04 UTC
If a new CellStyle is created and the fillBackgroundColor and fillForegroundColor are set using the values from the column's default style, then both the foreground and background colors are set to 64 which is intended to represent AUTOMATIC. Microsoft's documentation, though, indicates that 64 is the system foreground color and 65 is the system background color.


In excel, everything looks fine until you edit the cell at which point, the cell becomes a black box (black foreground and background) while the cell is being edited.
Comment 1 Nick Burch 2014-05-20 13:22:55 UTC
Any chance you could use Excel to create a simple test file, which shows the 64 for the foreground but the 65 for the background? That could then be used towards a unit test for the problem
Comment 2 Jerry Williamson 2014-05-20 13:27:11 UTC
Not sure I can force a 65 from Excel directly. It appears to just leave the element unspecified which internally defaults. I can, though, create a test where an Excel file is created with both values and hopefully demonstrate the problem. I'll attach a couple of tests shortly
Comment 3 Jerry Williamson 2014-05-20 14:03:21 UTC
Created attachment 31639 [details]
Test case

test case that produces an xlsx file that demonstrates the problem.
Comment 4 Jerry Williamson 2014-05-20 14:04:57 UTC
Created attachment 31640 [details]
sample file produced by the test.

editing the first cell causes black box while editing
editing second cell which has background color set to 65 is fine
Comment 5 Jerry Williamson 2014-05-20 14:19:31 UTC
Added a testcase and a sample file that demonstrates the problem.
The test produces an XLSX file that has 2 cells. 
The first cell has a cell style whose fillBackground is set from the column style (which is 64).
The second cell has a fillBackground explicitly set to 65.

If you open the spreadsheet in Excel and edit the first cell, you'll see that the cell turns to a black box while editing.

I'm using Excel 2013 on Windows 7 (in case it matters)
Comment 6 Jerry Williamson 2014-05-20 16:31:31 UTC
Created attachment 31642 [details]
second sample.xlsx

I believe I inadvertently saved the previous sample.xlsx in excel.
Excel removed the <bgColor indexed="65"/> element from the styles.xml when it saved.

This attachment is what is actually produced by the test
Comment 7 Dominik Stadler 2015-09-14 17:54:59 UTC
For HSSF there is some very strange back-and-forth conversion which seems to try to handle this transparently, i.e. conver to 64 and 65 as needed, see e.g. HSSFCellStyle.checkDefaultBackgroundFills(), however I'm not sure if this does what it should always and if it would make sense to do similar things on the XSSF side. Usually I'd rather remove this special handling in HSSF as well, however it might cause compatibility issues with previous versions of POI...
Comment 8 Dominik Stadler 2015-09-22 19:26:27 UTC
A user on the ML confirmed that setting backgroundfillcolor to 65 is a valid workaround for this.