Given a XSSF Sheet. Then the following code leads to a out.xlsx in which conditional formatting does not work for the created cell A1 (at least not in Excel): XSSFWorkbook wbOut = new XSSFWorkbook("C:/out.xlsx"); XSSFWorkbook wbIn = new XSSFWorkbook("C:/in.xlsx"); XSSFSheet srcSheet = wbOut.getSheetAt(0); XSSFSheet dstSheet = wbIn.getSheetAt(0); XSSFRow srcRow = srcSheet.getRow(0); XSSFRow dstRow = dstSheet.getRow(0); XSSFCell oldCell = srcRow.getCell(0); XSSFCell newCell = dstRow.createCell(0); Map<String, Object> props = new HashMap<>(); props.put(CellUtil.ROTATION, oldCell.getCellStyle().getRotation()); CellUtil.setCellStyleProperties(newCell, props); The problem is similar to the one in bug 60845, setCellStyleProperties() creates new fills instead of using the already existing ones: That means, the corresponding Fill for A1 is now: <fill> <patternFill> <fgColor indexed="64"/> <bgColor indexed="64"/> </patternFill> </fill> instead of the default one: <fill> <patternFill patternType="none"/> </fill> --- A possible solution could be: 1) handle "indexed="64" as "none"/null, since 64 is the value for AUTOMATIC/NONE in the getFormatProperties/setFormatProperties functions 2) create a new derived CellUtil for XSSF, since many other things (like XSSFColors) don't work too
Bug 60845 is resolved now. Does this also fix this issue?
Created attachment 37175 [details] Source Excel file
Created attachment 37176 [details] Example code
Dominik, I am still running into this issue with POI 4.12. The problem is that CellUtil.setCellStyleProperty() changes the background fill color, the foreground fill color and the border colors. Here is a snippet of my minimal example: public class Demo { public static void main(String[] args) throws IOException { final Workbook wb = WorkbookFactory.create(new File("Excel.xlsx")); final XSSFSheet sheet = (XSSFSheet) wb.getSheet("Tabelle1"); XSSFRow row = sheet.getRow(1); if (row == null) { row = sheet.createRow(1); } final XSSFCell cell = row.createCell(0); cell.setCellValue(2.0); final DataFormat dataFormat = wb.createDataFormat(); System.out.println(printCellStyle(cell)); CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dataFormat.getFormat("0")); System.out.println(printCellStyle(cell)); wb.write(new FileOutputStream(new File("/Users/dirk/IdeaProjects/temp/poi-example/src/main/resources/Output.xlsx"))); } // . . . } The output shows for the cell style: XSSFStyle (Index 0) { fillPattern: NO_FILL fillForeground: null fillBackground: null bottomBorder: NONE (null) borderLeft: NONE (null) borderTop: NONE (null) borderRight: NONE (null) horizontal alignment: GENERAL vertical alignment: BOTTOM hidden: false locked: true rotation: 0° indention: 0 wrap text: false dataFormat: 'General' (0) font (Index 0) { name: Calibri size: 12 bold: false italics: false strikeout: false underline: 0 color: BLACK1 typeoffset SS_NONE } } XSSFStyle (Index 1) { fillPattern: NO_FILL fillForeground: AUTOMATIC fillBackground: AUTOMATIC bottomBorder: NONE (BLACK) borderLeft: NONE (BLACK) borderTop: NONE (BLACK) borderRight: NONE (BLACK) horizontal alignment: GENERAL vertical alignment: BOTTOM hidden: false locked: true rotation: 0° indention: 0 wrap text: false dataFormat: '0' (1) font (Index 0) { name: Calibri size: 12 bold: false italics: false strikeout: false underline: 0 color: BLACK1 typeoffset SS_NONE } } The consequence is that the cell is shown with a white background color in Excel. This destroys the formatting of Excel data tables. You will then have a light blue line with white cells in it (those that were created by POI).
I would like to suggest that this is a general problem with the current style handling in POI. Here are the XML snippets from before and after the call to CellUtil.setCellStyleProperty(CellUtil.DATA_FORMAT): BEFORE: <xml-fragment numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"/> AFTER: <xml-fragment numFmtId="1" fontId="0" fillId="4" borderId="4" xfId="0" applyBorder="true" applyNumberFormat="true" applyFill="true" applyFont="true" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <main:alignment horizontal="general" vertical="bottom" indent="0" textRotation="0" wrapText="false"/> <main:protection hidden="false" locked="true"/> </xml-fragment> Observations: - A new fill was created (ID 4) and is now being used for that cell - New borders were created (ID 4) and are now being used - Borders, fill and font are applied through this style This is a major problem for all use cases where you want to enter data into existing sheets without breaking the optical appearance of the sheet. In addition to that, it seems to be more limited to what Excel is doing with the styles: If you set a font for a cell, Excel seems to create a cell style that only references that particular font and does not touch all other formatting settings. As a consequence, all underlying formatting will be untouched (e.g. formatting provided by a data table). POI sets all available formatting attributes, even if that is not what the user intends to do.