POI 操作Excel的单元格样式超过64000的异常问题解决

问题描述

在用POI 生成Excel文件时，如果自定义的单元格的样式超过64000行，就出抛出异常

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)

问题原因

在调用org.apache.poi.xssf.usermodel.XSSFWorkbook#createCellStyle 的方法时，最终会调用到org.apache.poi.xssf.model.StylesTable#createCellStyle 方法来实现，在这个方法中有如下的校验。

    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 = styleXfs.size();
        CTXf xf = CTXf.Factory.newInstance();
        xf.setNumFmtId(0);
        xf.setFontId(0);
        xf.setFillId(0);
        xf.setBorderId(0);
        xf.setXfId(0);
        int indexXf = putCellXf(xf);
        return new XSSFCellStyle(indexXf - 1, xfSize - 1, this, theme);
    }

其中MAXIMUM_STYLE_ID 的值取自org.apache.poi.ss.SpreadsheetVersion

因此，对于Excel 97 (也就是xls 后缀的Excel文件）版本的实际的上限是4000，而对于Excel 2007 (xlsx 后缀的Excel文件）版本的实际上线为64000。

问题分析和解决

实际上我们平时操作Excel时，单元格样式CellStyle 并不是每个单元格都是独立的。Excel文件在WorkBook中保存了所有的单元格样式，而每个单元格只是使用了保存的单元格样式的索引。例如单元格A1的样式使用的是保存的样式中的第1号样式，单元格A2也可以使用保存的样式中的第1号样式。所以实际我们不太可能会出现超过64000个样式的表格，除非你对每个单元格去独立的设置样式。

但在通过代码生成Excel文件时，比较容易实现的方式就是每个单元格创建独立的样式，例如以下的写法

简单的Excel文件生成Demo

    @Test
    public void test_AlgorithmForGeneratingSimpleExcel(){
        try {
            //创建一个空白的Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook();
            System.out.println("初始的样式个数："+workbook.getNumCellStyles());
            XSSFSheet sheet = workbook.createSheet("测试Sheet1");
            //填充表格
            for(int rowNo=0;rowNo<10;rowNo++){
                XSSFRow row = sheet.createRow(rowNo);
                //创建单元格
                for(int cellNo=0;cellNo<10;cellNo++){
                    XSSFCell cell = row.createCell(cellNo);
                    //设置每个单元格的值，并设置每个单元格的样式
                    XSSFCellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置实心填充
                    cellStyle.setFillForegroundColor(IndexedColors.BLUE1.index); //设置填充的背景色
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(((char)(rowNo+'A'))+ ":"+ (cellNo+1));
                }
            }
            System.out.println("最终的样式个数："+workbook.getNumCellStyles());
            String outFile = "D:\\test_AlgorithmForGeneratingSimpleExcel_1.xlsx";
            workbook.write(new FileOutputStream(outFile));
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

测试控制台输出的内容

初始的样式个数：1
最终的样式个数：101

生成的Excel文件内容：

上面的代码中，虽然所有的单元格的样式都一样的，但实际仍然创建了100个单元格样式。而如果我们只想要创建一个单元格样式，其他的单元格都使用这个一个单元格样式，代码就要调整，不能每个格子都创建单元格样式了。修改后的代码如下：

    @Test
    public void test_AlgorithmForGeneratingSimpleExcel2(){
        try {
            //创建一个空白的Excel文件
            XSSFWorkbook workbook = new XSSFWorkbook();
            System.out.println("初始的样式个数："+workbook.getNumCellStyles());
            XSSFSheet sheet = workbook.createSheet("测试Sheet1");
            //提前创建好单元格样式
            XSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //设置实心填充
            cellStyle.setFillForegroundColor(IndexedColors.BLUE1.index); //设置填充的背景色

            //填充表格
            for(int rowNo=0;rowNo<10;rowNo++){
                XSSFRow row = sheet.createRow(rowNo);
                //创建单元格
                for(int cellNo=0;cellNo<10;cellNo++){
                    XSSFCell cell = row.createCell(cellNo);
                    //设置每个单元格的值，并设置每个单元格的样式
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(((char)(rowNo+'A'))+ ":"+ (cellNo+1));
                }
            }
            System.out.println("最终的样式个数："+workbook.getNumCellStyles());
            String outFile = "D:\\test_AlgorithmForGeneratingSimpleExcel_2.xlsx";
            workbook.write(new FileOutputStream(outFile));
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

测试控制台输出的内容

初始的样式个数：1
最终的样式个数：2

生成的Excel文件内容：

通过提前创建好单元格样式，就可以复用单元格样式了。但这个方案对于一些Excel文件操作和Excel数据操作是在同一个代码里是可以这么写的，但对于Excel数据准备和Excel文件生成拆分的代码就不合适，因为每个单元格的样式设置成什么只有数据准备的地方知道，但样式的创建又只有Excel文件生成的地方才能操作。因此需要一个机制，能让Excel文件生成的地方可以知道每个单元格的单元格样式。所以Excel数据准备的单元格数据就不能是单纯的表格数据了，还得还有单元格的样式数据。类似于这样的数据结构：

public class ExcelCellData {
    /**
     * 单元格的值
     */
    private Object value;
    
    /**
     * 单元格的样式 
     */
    private XSSFCellStyle cellStyle;
}	

但是这样又出现一个新的问题，在数据准备阶段是没法操作Excel文件的，也就没有办法创建出一个cellStyle 。因此只能传一些参数，让Excel文件创建阶段根据参数来创建cellStyle ，但这个方案有无法解决最开始的，单元格样式复用的问题。并且Excel 单元格样式的设置有很多，这个参数的传入和构建都是比较繁杂的，对使用人员来说非常不友好。因此新的方案就必须要解决下面的问题：

1. 必须支持在数据准备阶段设置每个单元格的样式；
2. 必须支持单元格样式的复用；

因此最终的解决方案如下：

最终的解决方案

对于问题2，可以通过参数指定复用哪个单元格的样式来解决，也就是告诉Excel文件创建阶段，在处理该单元格的样式时，直接让它引用指定的单元格样式即可。核心代码如下：

    XSSFCell referCell = sheet.getRow(referStyleCell.getRow()).getCell(referStyleCell.getColumn());
    cell.setCellStyle(referCell.getCellStyle());

对于问题1，则可以采用延迟指定单元格样式的方法来解决，也就是ExcelCellData 对象里不再存储具体cellStyle，而是一个cellStyle的处理方法，让Excel文件创建阶段去执行这段处理代码来完成延迟设置单元格样式问题。

最终ExcelCellData 的如下:

public class ExcelCellData {

    /**
     * 单元格的值
     * @author 徐明龙 XuMingLong 2023-03-01
     */
    private Object value;

    /**
     * 单元格自定义数据
     * @author 徐明龙 XuMingLong 2023-03-03
     */
    private ExcelCellCustomData cellCustomData;


}

而ExcelCellCustomData 是一个接口，提供了两个方法，一个是设置单元格的值，一个是设置单元格的样式

public interface ExcelCellCustomData {
    /**
     * 默认的设置值的方法
     * @author 徐明龙 XuMingLong 2023-03-03
     */
    BiConsumer<XSSFCell,Object> DEFAULT_SET_VALUE_FUN = (cell,data)->{
        if(data!=null){
            if(data instanceof  Boolean){
                cell.setCellValue(((Boolean)data).booleanValue());
            }else if(data instanceof Number) {
                cell.setCellValue(((Number)data).doubleValue());
            }else if(data instanceof List){
                cell.setCellValue(StringUtils.join((List<String>)data, "\n"));
            }else{
                cell.setCellValue(String.valueOf(data));
            }
        }
    };

    /**
     * 获取设置单元格值的方法
     * @author 徐明龙 XuMingLong 2023-03-03
     * @return java.util.function.BiConsumer<org.apache.poi.xssf.usermodel.XSSFCell,java.lang.Object>
     */
    default BiConsumer<XSSFCell,Object> getSetValueFun(){
        return DEFAULT_SET_VALUE_FUN;
    }

    /**
     * 获取单元格的格式化处理器
     * @author 徐明龙 XuMingLong 2023-03-03
     * @return org.apache.poi.xssf.usermodel.XSSFCellStyle
     */
    default ExcelCellFormatter getCellFormatter(){
        return ExcelCellFormatter.DEFAULT_FORMATTER;
    };
}

ExcelCellFormatter 也是一个接口，同时也是一个FunctionalInterface ，只提供了一个接口方法，用于设置单元格样式。

@FunctionalInterface
public interface ExcelCellFormatter {

    /**
     * 默认的格式化方法
     * @author 徐明龙 XuMingLong 2023-03-03
     */
    ExcelCellFormatter DEFAULT_FORMATTER = cell->{};

    /**
     * 格式化单元格
     * @author 徐明龙 XuMingLong 2023-03-03
     * @param cell
     * @return void
     */
    void format(XSSFCell cell);
}

ExcelCellCustomData 接口提供了3个的实现类，分别用于3个常用的场景

1. ExcelCellValueTypeData 用于只需要设置单元格值，不需要设置单元格样式的场景。单元格样式使用默认的样式。
2. ExcelReferCellStyleData 用于复用指定单元格样式的场景，也支持设置单元格的值。
3. ExcelCustomValueAndStyleData 用于创建独立的单元格样式，支持克隆指定单元格的样式，并以此为基础进行样式的调整，也支持设置单元格的值。

完整的源码见 https://gitcode.net/qiutongcunyi/excel-export-utils

这个源码的项目也是一个可用的导出复杂Excel模版的工具。