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

问题描述：

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 批量设置单元格样式（字体样式、背景颜色、边框样式、对齐方式、自动换行）