POI The maximum number of Cell Styles was exceeded.You can define up to x style in a x Workbook问题解决

向右看齐 2022-09-10 02:24 657阅读 0赞

### 问题描述： ###

java.lang.IllegalStateException: The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook

### 问题分析： ###

1、使用EasyExcel创建单元格样式时，有64000个单元格创建了单元格样式，导致报错。

/**
         * 导出（设置单元格样式）
         *
         * @param response
         */
        @GetMapping("/exportCellStyle")
        public void exportCellStyle(HttpServletResponse response) {
            try {
                //生成表格数据
                List<List<Object>> dataList = new ArrayList<>();
                //导出文件
                String fileName = new String("文件名称.xlsx".getBytes(), "UTF-8");
     
                List<CellStyleModel> cellStyleList = new ArrayList<>();
                response.addHeader("Content-Disposition", "filename=" + fileName);
                //设置类型，扩展名为.xls
                response.setContentType("application/vnd.ms-excel");
                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellStyleHandler(cellStyleList)).build();
                WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
                excelWriter.write(dataList, writeSheet);
                //千万别忘记finish 会帮忙关闭流
                excelWriter.finish();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

解决办法：

当需要设置样式的单元格数量超过64000时，直接停止往Excel文档里面写入数据。

/**
         * 导出（设置单元格样式）
         *
         * @param response
         */
        @GetMapping("/exportCellStyle")
        public void exportCellStyle(HttpServletResponse response) {
            try {
                //生成表格数据
                List<List<Object>> dataList = new ArrayList<>();
                //导出文件
                String fileName = new String("文件名称.xlsx".getBytes(), "UTF-8");
     
                List<CellStyleModel> cellStyleList = new ArrayList<>();
                if(cellStyleList.size() > 64000){
                    return;
                }
                response.addHeader("Content-Disposition", "filename=" + fileName);
                //设置类型，扩展名为.xls
                response.setContentType("application/vnd.ms-excel");
                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomCellStyleHandler(cellStyleList)).build();
                WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
                excelWriter.write(dataList, writeSheet);
                //千万别忘记finish 会帮忙关闭流
                excelWriter.finish();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

注：

有关 CellStyleModel和CustomCellStyleHandler的源码请查看以下博客。

[EasyExcel 批量设置单元格样式（字体样式、背景颜色、边框样式、对齐方式、自动换行）][EasyExcel]

[EasyExcel]: https://blog.csdn.net/qq_38974638/article/details/114841208