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
Added test case in r1742862.
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.
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.
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.