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.