aliases: []
tags   : " #QA #Java "
summary: [POI生成Excel超出的單元格樣式的最大數量]
author : [yaenli]
notekey: [20230322-100908]

問題現象

使用Apache POI生成Excel時，如果創建的單元格樣式過多，會報樣式超出最大數的錯誤，

.xls的異常錯誤：

java.lang.IllegalStateException: The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:1144)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:88)

.xlsx的異常錯誤：

java.lang.IllegalStateException: The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook
	at org.apache.poi.xssf.model.StylesTable.createCellStyle(StylesTable.java:830)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.createCellStyle(XSSFWorkbook.java:750)

問題分析

同一個Workbook創建CellStyle有最大數限制，其中.xls（Excel 97） 的最大數是4000，.xlsx（Excel 2007） 的最大數是64000 。

xls 參數限制於org.apache.poi.hssf.usermodel.HSSFWorkbook :

private static final int MAX_STYLES = 4030;

public HSSFCellStyle createCellStyle() {
    if (this.workbook.getNumExFormats() == MAX_STYLES) {
      throw new IllegalStateException("The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook");
    }
    
    ExtendedFormatRecord xfr = this.workbook.createCellXF();
    short index = (short)(getNumCellStyles() - 1);
    return new HSSFCellStyle(index, xfr, this);
}

xlsx 參數限制於org.apache.poi.xssf.model.StylesTable :

private static final int MAXIMUM_STYLE_ID = SpreadsheetVersion.EXCEL2007.getMaxCellStyles();// 64000

public XSSFCellStyle createCellStyle() {
    if (getNumCellStyles() > MAXIMUM_STYLE_ID) {
      throw new IllegalStateException("The maximum number of Cell Styles was exceeded. You can define up to " + MAXIMUM_STYLE_ID + " style in a .xlsx Workbook");
    }
    
    int xfSize = this.styleXfs.size();
    CTXf xf = CTXf.Factory.newInstance();
    xf.setNumFmtId(0L);
    xf.setFontId(0L);
    xf.setFillId(0L);
    xf.setBorderId(0L);
    xf.setXfId(0L);
    int indexXf = putCellXf(xf);
    return new XSSFCellStyle(indexXf - 1, xfSize - 1, this, this.theme);
}

因此，在生成Excel時，如果同一個Workbook不停的創建CellStyle，超限時就會產生樣式最大數異常，最直接的體現就是在某些代碼中，對每個單元格去獨立的設置樣式，生成大數據量的Excel報錯。

解決方案

網上最熱門的解決方案是所謂的將createCellStyle 放在迴圈外面，這隻能應付表格樣式單一的情況。

由於單元格樣式CellStyle 並不是單元獨立擁有的，每個單元格只是保存了樣式的索引，一般的Excel真正使用到的樣式也不會超過4000/64000 ，因此更好的解決方案是實現單元格樣式的復用（註意不同的Workbook創建的CellStyle是不能混用的）。

方案1：緩存樣式實現復用

提取樣式關鍵字作為key，將CellStyle緩存至Map：

Workbook workBook = new HSSFWorkbook();// or new XSSFWorkbook();
Sheet sheet = workBook.createSheet(strSheetName);
Map cellStyleMap = new HashMap<String, CellStyle>();// 緩存樣式

// 樣式代碼
for (int rowIndex = 0; rowIndex < maxRow; rowIndex++) {
    Row row = sheet.createRow(rowIndex);
    for (int colIndex = 0; colIndex < maxCol; colIndex++) {
        Cell cell = row.createCell((short) colIndex);
        String styKey = getCellStyleKey(rowIndex, colIndex);// 根據獲取樣式key
        CellStyle cellStyle = (CellStyle) cellStyleMap.computeIfAbsent(styKey, k-> workBook.createCellStyle());// 獲取樣式
        cell.setCellStyle(cellStyle);
    }
}

方案2：修改限制參數

修改POI中的限制參數（ org.apache.poi.hssf.usermodel.HSSFWorkbook.MAX_STYLES 或org.apache.poi.ss.SpreadsheetVersion.EXCEL2007）。

過多的創建樣式會影響性能，建議僅在真正使用的樣式超過限制時再去修改此參數。

方案3：延遲指定單元格樣式實現復用

參見文章：
POI 操作Excel的單元格樣式超過64000的異常問題解決
根據模版填充Excel並導出的工具 · GitCode