Bug 59442

Summary: CellUtil.setCellStyleProperties erases foreground and background fill colors
Product: POI Reporter: Javen O'Neal <onealj>
Component: SS CommonAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: normal CC: Alexander.Riccio, andersaa, jcwu200, marcin.kruglik, tom.opichal
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Javen O'Neal 2016-05-08 22:35:43 UTC
Reported by Anders Ã…berg on user@poi mailing list.[1]

> I am trying to set RGB background colors on cells and in addition set
> borders on regions using RegionUtil. When doing this, all the cells with
> borders end up completely black.
> It seems that using XSSFColor on XSSFCellStyle causes problems with the way
> CellUtil.setCellStyleProperties works. CellUtil is used by RegionUtil.
> This is a minimal test to reproduce the problem:
>    XSSFWorkbook workbook = new XSSFWorkbook();
>    XSSFCell cell = workbook.createSheet().createRow(0).createCell(0);
>    XSSFColor red = new XSSFColor(Color.RED);
>    XSSFCellStyle style = workbook.createCellStyle();
>    style.setFillForegroundColor(red);
>    style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
>    cell.setCellStyle(style);
>    // Everything is fine at this point, cell is red
>    Map<String, Object> properties = new HashMap<>();
>    properties.put(CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN);
>    CellUtil.setCellStyleProperties(cell, properties);
>    // Now the cell is all black
>    XSSFColor actual = cell.getCellStyle().getFillForegroundColorColor();
>    assertEquals(red.getARGBString(), actual.getARGBString());

The implementation of CellUtil.setCellStyleProperties uses putShort/getShort for working with fillForegroundColor and fillBackgroundColor, which means this function will only work on indexed colors. Furthermore, the unit test only covers HSSFWorkbooks.

public static void setCellStyleProperties(Cell cell, Map<String, Object> properties) {
    Map<String, Object> values = getFormatProperties(originalStyle);
    // the desired style does not exist in the workbook. Create a new style with desired properties.
    if (newStyle == null) {
        newStyle = workbook.createCellStyle();
        // overwrites non-indexed fill foreground/background colors
        setFormatProperties(newStyle, workbook, values);

private static Map<String, Object> getFormatProperties(CellStyle style) {
    Map<String, Object> properties = new HashMap<String, Object>();
    putShort(properties, FILL_BACKGROUND_COLOR, style.getFillBackgroundColor());
    putShort(properties, FILL_FOREGROUND_COLOR, style.getFillForegroundColor());

private static void setFormatProperties(CellStyle style, Workbook workbook, Map<String, Object> properties) {
    style.setAlignment(getShort(properties, ALIGNMENT));
    style.setFillBackgroundColor(getShort(properties, FILL_BACKGROUND_COLOR));
    style.setFillForegroundColor(getShort(properties, FILL_FOREGROUND_COLOR));

[1] http://apache-poi.1045710.n5.nabble.com/XSSF-Problem-with-CellUtil-and-custom-RGB-background-color-td5722925.html
[2] https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/CellUtil.java?revision=1739536&view=markup#l236
[3] https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/CellUtil.java?revision=1739536&view=markup#l324
[4] https://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/util/CellUtil.java?revision=1739536&view=markup#l357
Comment 1 Javen O'Neal 2016-05-09 01:27:45 UTC
Added test case in r1742862.
Comment 2 Dominik Stadler 2018-10-21 18:36:08 UTC
Did some investigation on this:
* CellUtils currently only supports the indexed colors, not the newer full colors
* When setting properties via setCellStyleProperties(), internally a new style is created with only indexed colors being copied over
* Adding full support would require interface methods for setting/getting full color in class Color, however support for full color in setters in HSSFCellStyle is not available, so this would be needed as well here.
Comment 3 bengt 2022-03-23 16:46:10 UTC
I couldn't find a way to vote for this issue so I'll write a comment instead. I have found the exact same problem and would really appreciate if this could be fixed.
Comment 4 PJ Fanning 2022-03-23 17:10:36 UTC
The best way to get something fixed is to contribute the fix yourself. There are only a small number of active volunteers working on POI.