Bug 55555

Summary: Adding a border to a cell with background pattern sets the pattern color to black
Product: POI Reporter: Gilles <gguillaud>
Component: XSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal CC: Alexander.Riccio
Priority: P2    
Version: 3.10-dev   
Target Milestone: ---   
Hardware: PC   
OS: All   
Bug Depends on: 58787    
Bug Blocks:    
Attachments: The java file to reproduce the problem
The result file with some bordered cells
sample file generated using POI
Same sample file got from Excel

Description Gilles 2013-09-13 13:26:08 UTC
Created attachment 30835 [details]
The java file to reproduce the problem


I have a very strange problem with cells on which I want to have:
1) a colored background pattern
2) a border

I've just created a java class to reproduce the problem. First you can execute it and you will get a XSLX file in which there will be some cells (25x25). These cells have all a colored background pattern.

Now, in the java class, let's set boolean _DRAW_BORDER to true, compile and execute. In the result file, you will now see that some cells have a border. In these cells the background pattern is ok, but not the color which is now black.

This can be reproduced with POI1.9 and POI3.10b.

Comment 1 Gilles 2013-09-13 13:27:33 UTC
Created attachment 30836 [details]
The result file with some bordered cells
Comment 2 Gilles 2013-09-23 14:30:27 UTC
I have the same problem using POI 3.10-beta2
Comment 3 Gilles 2014-02-12 17:04:48 UTC
I still have the same issue using POI 3.10 final
I was hoping it would have been fixed. Will it be fix in POI 4.0 ?
Please answer me, I must say something to my applications's users ...
Comment 4 Nick Burch 2014-02-12 17:11:02 UTC
If you recreate the file in Excel, unzip the POI .xlsx and the Excel .xlsx, then compare the various xml files (especially style ones), how do they differ?
Comment 5 Gilles 2014-02-13 15:36:13 UTC
Created attachment 31309 [details]
sample file generated using POI
Comment 6 Gilles 2014-02-13 15:36:45 UTC
Created attachment 31310 [details]
Same sample file got from Excel
Comment 7 Gilles 2014-02-13 15:40:13 UTC
Hi Nick,
I've just uploaded the 2 XSLX files. There are a lot of differences between them. I do not understand everything, what I see is that the xl/styles.xml is bigger in the "from POI" than in the "From Excel".

If you want to reproduce the problem, I've already uploaded a Java source file which can do that.

Comment 8 Gilles 2014-04-04 07:28:39 UTC
Hi Nick, do you have any idea when this issue will be resolved ? Will it be in 3.11 final ? If it is not, I'll have to forget POI and choose another solution :-(
Comment 9 Nick Burch 2014-04-04 08:45:08 UTC
(In reply to Gilles from comment #8)
> Hi Nick, do you have any idea when this issue will be resolved ?

It'll be fixed when someone (be they a POI committer, or a community member) decides it's enough of a problem for them to sit down and write a patch...

If this bug matters to you, I'd suggest you join the dev list, and ask for advice on how to debug this style of problem
Comment 10 Gilles 2014-04-04 10:29:18 UTC
Ok Nick, I understand. Thank you for your answer.
Comment 11 Javen O'Neal 2016-04-06 04:02:48 UTC

Any chance you could trim your Excel files and Java test code down to the bare minimum needed to reproduce the problem? It would make diffing the Excel files easier to figure out what's going on.

If not, this bug will probably get closed as not reproducible.
Comment 12 Mark Murphy 2016-04-06 15:50:49 UTC
This may be easier to fix once #58787 is complete.
Comment 13 Javen O'Neal 2016-06-17 09:24:45 UTC
This bug might be able to be simplified into something with unrecognized attributed by CellUtil.getFormatProperties, which means searching for a matching style in the cell style table returns an imperfect match.
Comment 14 Qualtagh 2016-07-08 15:47:52 UTC
Added a pull request on GitHub with a fix of this bug: https://github.com/apache/poi/pull/33

I have tested this issue after applying this fix using a java file attached by Gilles. It solves the problem.

Fix description:
CellUtil was setting background color BEFORE setting foreground and fill pattern. And HSSFCellStyle.setFillBackgroundColor javadoc says that background color should be set AFTER setting fill pattern and foreground.
Comment 15 Qualtagh 2016-07-08 18:27:11 UTC
I'm sorry. It fixes only similar HSSF issue. Just a half of a problem.
Comment 16 Javen O'Neal 2016-07-08 18:46:07 UTC
Applied pull request from comment 14 in r1751955 with corresponding unit test.