Bug 59442

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.

[2]
public static void setCellStyleProperties(Cell cell, Map<String, Object> properties) {
    ...
    Map<String, Object> values = getFormatProperties(originalStyle);
    values.putAll(properties);
    ...
    // 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);
    }
}


[3]
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());
    ...
}

[4]
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