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 Workbookat 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 (xlsx后缀的Excel文件）版本的实际上线为64000。

问题分析和解决

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

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

简单的Excel文件生成Demo

@Testpublic 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个单元格样式。而如果我们只想要创建一个单元格样式，其他的单元格都使用这个一个单元格样式，代码就要调整，不能每个格子都创建单元格样式了。修改后的代码如下：

@Testpublic 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 单元格样式的设置有很多，这个参数的传入和构建都是比较繁杂的，对使用人员来说非常不友好。因此新的方案就必须要解决下面的问题：

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

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

最终的解决方案

对于问题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 -03-01*/private Object value;/*** 单元格自定义数据* @author 徐明龙 XuMingLong -03-03*/private ExcelCellCustomData cellCustomData;}

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

public interface ExcelCellCustomData {/*** 默认的设置值的方法* @author 徐明龙 XuMingLong -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 -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 -03-03* @return org.apache.poi.xssf.usermodel.XSSFCellStyle*/default ExcelCellFormatter getCellFormatter(){return ExcelCellFormatter.DEFAULT_FORMATTER;};}

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

@FunctionalInterfacepublic interface ExcelCellFormatter {/*** 默认的格式化方法* @author 徐明龙 XuMingLong -03-03*/ExcelCellFormatter DEFAULT_FORMATTER = cell->{};/*** 格式化单元格* @author 徐明龙 XuMingLong -03-03* @param cell* @return void*/void format(XSSFCell cell);}

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

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

完整的源码见 /qiutongcunyi/excel-export-utils

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