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：延遲指定單元格樣式實現複用