APACHE POI setting cell style clears existing date format

I am new to Java programming and this is my first post so hopefully it's a valid question. I have searched the archives but can't find a suitable answer.

My class is reading and validating a spreadsheet. Cells in error are set to a style with a red background, valid cells are given a style with no backround. This all works fine until a date cell is encountered. The setCellStyle statement appears to be clearing the date formatting. Can anyone advise me how to prevent this please?

So, when viewing the cell in Excel to begin with, it shows 25/08/20.

After updating the style to background red in my program, then going back into Excel, the cell shows in red but with a numeric value like 92345678.0000 (I can't remember the exact value now).

Here's the relevant bit of code:

XSSFCellStyle styleBad = wb.createCellStyle();
XSSFCellStyle styleGood = wb.createCellStyle();
styleGood.setFillPattern(FillPatternType.NO_FILL);
styleBad.setFillForegroundColor(IndexedColors.RED.getIndex());
styleBad.setFillPattern(FillPatternType.SOLID_FOREGROUND);
if (!validateCell(cell, tca[cx], cellValues)) {
  cell.setCellStyle(styleBad);
  errorMessage += ("Cell " + (cx + 1) + " " + cellValues.cellMessage + ". ");
  wbValid = false;
  }
else {
  cell.setCellStyle(styleGood);                        Mucks up date formatting??                                                                     
}


</details>


# 答案1
**得分**: 2

数字格式模式是单元格样式的一部分。因此，当您设置新的单元格样式时，还会将数字格式模式设置为默认。

您可以使用 `CellUtil` 来向现有单元格样式**添加**属性，而不是设置新样式。方法 `CellUtil.setCellStyleProperties` 能够添加新的单元格样式属性，同时保留单元格已存在的样式属性。

    ...
    Map<String, Object> properties = new HashMap<String, Object>();
    ...
    
    如果无法验证单元格（cell），则：
      properties = new HashMap<String, Object>();
      properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
      properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.RED.getIndex());
      CellUtil.setCellStyleProperties(cell, properties); 
      errorMessage += "单元格 " + (cx + 1) + " " + cellValues.cellMessage + "。";
      wbValid = false;
    否则：
      properties = new HashMap<String, Object>();
      properties.put(CellUtil.FILL_PATTERN, FillPatternType.NO_FILL);
      CellUtil.setCellStyleProperties(cell, properties);       
    ...


<details>
<summary>英文:</summary>

The number format patterns are part of the cell style. So as you are setting the cell style new, you also set number format patterns to default.

You could using `CellUtil` to **add** properties to the existent cell style instead of setting it new. The method `CellUtil.setCellStyleProperties` is able adding new cell style properties but remaining the cell style properties which are already present for the cell.

    ...
    Map&lt;String, Object&gt; properties = new HashMap&lt;String, Object&gt;();
    ...
    
    if (!validateCell(cell, tca[cx], cellValues)) {
      properties = new HashMap&lt;String, Object&gt;();
      properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
      properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.RED.getIndex());
      CellUtil.setCellStyleProperties(cell, properties); 
      errorMessage += (&quot;Cell &quot; + (cx + 1) + &quot; &quot; + cellValues.cellMessage + &quot;. &quot;);
      wbValid = false;
    }
    else {
      properties = new HashMap&lt;String, Object&gt;();
      properties.put(CellUtil.FILL_PATTERN, FillPatternType.NO_FILL);
      CellUtil.setCellStyleProperties(cell, properties);       
    }
    ...



</details>