Bug 60895 - CellUtil.setCellStyleProperties() corrupts conditional Formatting
Summary: CellUtil.setCellStyleProperties() corrupts conditional Formatting
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 3.15-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-03-21 10:52 UTC by dollinger.florian
Modified: 2020-04-17 07:59 UTC (History)
1 user (show)



Attachments
Source Excel file (9.29 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-04-16 16:14 UTC, Dirk Dittert
Details
Example code (4.92 KB, text/plain)
2020-04-16 16:15 UTC, Dirk Dittert
Details

Note You need to log in before you can comment on or make changes to this bug.
Description dollinger.florian 2017-03-21 10:52:53 UTC
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
Comment 1 Dominik Stadler 2018-12-30 16:43:19 UTC
Bug 60845 is resolved now. Does this also fix this issue?
Comment 2 Dirk Dittert 2020-04-16 16:14:47 UTC
Created attachment 37175 [details]
Source Excel file
Comment 3 Dirk Dittert 2020-04-16 16:15:41 UTC
Created attachment 37176 [details]
Example code
Comment 4 Dirk Dittert 2020-04-16 16:19:14 UTC
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).
Comment 5 Dirk Dittert 2020-04-17 07:59:30 UTC
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.