Bug 59442 - CellUtil.setCellStyleProperties erases foreground and background fill colors
Summary: CellUtil.setCellStyleProperties erases foreground and background fill colors
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2016-05-08 22:35 UTC by Javen O'Neal
Modified: 2018-10-21 18:36 UTC (History)
4 users (show)


Note You need to log in before you can comment on or make changes to this bug.
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.