使用POI 3.17操作XLSX时列格式丢失问题求助

Hey Giuseppe, great question—this is a super common gotcha when moving from HSSF (XLS) to XSSF/SXSSF (XLSX) in Apache POI, and it all boils down to how the two formats handle default column styles under the hood.

Why This Happens #

• HSSF (XLS, POI 3.9): The old binary XLS format automatically inherits column-level styles to new cells in that column. When you set a column's format (color, number style, etc.), any new row you add will pick up that style without extra work.
• XSSF/SXSSF (XLSX, POI 3.17): The OOXML-based XLSX format treats column styles as default metadata rather than an automatic inheritance rule. Even if you set a column's style upfront, new cells won't apply it unless you explicitly assign the style to each cell. This aligns with the official OOXML specification, which doesn't enforce automatic style inheritance for new rows.

Solutions for Non-Streaming (XSSF) #

If you're using XSSFWorkbook without streaming, you can directly fetch the pre-defined column style and apply it to each new cell:

// Assume you have an existing XSSFWorkbook with pre-formatted columns
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("your-formatted-file.xlsx"));
XSSFSheet sheet = wb.getSheetAt(0);

// Get the pre-defined style for column 0 (adjust the column index as needed)
XSSFCellStyle columnStyle = sheet.getColumnStyle(0);

// Create a new row at the end of the sheet
int newRowNum = sheet.getLastRowNum() + 1;
XSSFRow newRow = sheet.createRow(newRowNum);

// Create cells and explicitly apply the column style
XSSFCell cell = newRow.createCell(0);
cell.setCellStyle(columnStyle);
cell.setCellValue(1234.56); // This will use your pre-set number format

// Save and clean up
FileOutputStream out = new FileOutputStream("updated-file.xlsx");
wb.write(out);
out.close();
wb.close();

Solutions for Streaming (SXSSF) #

When using SXSSFWorkbook for large files, you need to retain access to the underlying XSSFWorkbook to fetch or define styles (since SXSSF optimizes memory by flushing rows to disk):

// Create SXSSFWorkbook with a reference to the underlying XSSFWorkbook
XSSFWorkbook xssfWB = new XSSFWorkbook(new FileInputStream("your-formatted-file.xlsx"));
SXSSFWorkbook sxssfWB = new SXSSFWorkbook(xssfWB);
SXSSFSheet sheet = sxssfWB.getSheetAt(0);

// Fetch the pre-defined column style from the underlying XSSFSheet
XSSFCellStyle columnStyle = xssfWB.getSheetAt(0).getColumnStyle(0);

// Create new row and cells, apply the saved style
SXSSFRow newRow = sheet.createRow(sheet.getLastRowNum() + 1);
SXSSFCell cell = newRow.createCell(0);
cell.setCellStyle(columnStyle);
cell.setCellValue(789.12);

// Clean up and save (don't forget to dispose SXSSF to free temp files)
sxssfWB.write(new FileOutputStream("streaming-updated.xlsx"));
sxssfWB.dispose();
xssfWB.close();

Pro Tip #

If you're setting up styles from scratch (not using a pre-formatted file), define your CellStyle once at the workbook level, apply it to the column, and reuse it for all new cells in that column. This avoids creating duplicate styles, which can bloat your XLSX file unnecessarily.

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