使用Apache POI设置XLS单元格样式时模板整体被修改的问题

Hey there! I see the frustrating issue you're hitting—when you try to apply a date style to your bean data rows, the entire template's styling (including that light gray 14th row) gets messed up. Let's break down why this happens and how to fix it cleanly.

The Root Cause #

In Apache POI, CellStyle objects are workbook-level resources. That means if you modify an existing style that’s used elsewhere in your template (like the light gray style on row 14), every cell that uses that style will get updated too. You’re probably reusing or altering the original template style instead of creating a separate, independent style for your date cells.

The Solution: Clone the Original Style #

Instead of touching the template’s existing styles, create a cloned copy of the row 14 style, then add your date formatting to this new style. This way, you keep the original light gray styling intact while applying the date format only to your new data rows.

Here’s a step-by-step implementation:

1. Grab the original style from your template row
   First, get the cell style from the 14th row (remember, POI uses 0-based indexing, so row 14 is index 13):

   // Load your workbook and sheet as you were doing
   workbook = (HSSFWorkbook) WorkbookFactory.create(new FileInputStream(finalFilePath));
   sheet = workbook.getSheetAt(0);
   workbook.setActiveSheet(0);
   
   // Get the style from the 14th row (use any cell in the row, since the whole row is light gray)
   HSSFCellStyle originalGrayStyle = (HSSFCellStyle) sheet.getRow(13).getCell(0).getCellStyle();
   

2. Clone the style and add date formatting
   Create a new cell style, copy all properties from the original gray style, then set your desired date format:

   CreationHelper creationHelper = workbook.getCreationHelper();
   // Clone the original style to preserve the light gray formatting
   HSSFCellStyle dateCellStyle = (HSSFCellStyle) workbook.createCellStyle();
   dateCellStyle.cloneStyleFrom(originalGrayStyle);
   
   // Apply your custom date format to the cloned style
   HSSFDataFormat dataFormat = (HSSFDataFormat) workbook.createDataFormat();
   dateCellStyle.setDataFormat(dataFormat.getFormat("yyyy-MM-dd")); // Adjust the format string to your needs
   

3. Apply the cloned style to your data rows
   When populating your bean data into new rows, use this cloned style for date cells instead of modifying the original:

   // Example: Loop through your bean data and populate rows starting from row 15 (index 14)
   int startingRow = 14; // Corresponding to Excel's 15th row
   for (int i = 0; i < yourBeanList.size(); i++) {
       Row dataRow = sheet.createRow(startingRow + i);
       YourBean bean = yourBeanList.get(i);
   
       // Populate the date cell with the cloned style
       Cell dateCell = dataRow.createCell(0); // Adjust column index to match your template
       dateCell.setCellValue(bean.getDateProperty());
       dateCell.setCellStyle(dateCellStyle);
   
       // Populate other cells with appropriate styles (if needed)
       // ...
   }
   

Key Tips #

• Always clone styles when you need to modify them for specific cells—never edit the original template styles directly.
• The cloneStyleFrom() method copies all existing style properties (fill color, font, borders, alignment) so your new rows will match the light gray look of row 14, plus your custom date format.

内容的提问来源于stack exchange，提问作者Mahesh