easyexcel升级3.3.4失败的经历

原本想通过easyexcel从2.2.6升级到3.3.3解决一部分问题，结果之前的可以用的代码，却无端的出现bug
1 Sheet index (1) is out of range (0…0)
什么都没有改，就出了问题，那么问题肯定出现在easyexcel版本自身.使用模板填充的方式进行多sheet填错，报错out of range #2731
从easyexcel一个模板动态多个sheet填充，easyexcel2,2,10就有这个问题。

异常如下

java.lang.IllegalArgumentException: Sheet index (1) is out of range (0..0)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.validateSheetIndex(HSSFWorkbook.java:569)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetAt(HSSFWorkbook.java:1003)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.getSheetAt(HSSFWorkbook.java:131)
	at com.alibaba.excel.context.WriteContextImpl.initSheet(WriteContextImpl.java:184)
	at com.alibaba.excel.context.WriteContextImpl.currentSheet(WriteContextImpl.java:135)
	at com.alibaba.excel.write.ExcelBuilderImpl.addContent(ExcelBuilderImpl.java:54)
	at com.alibaba.excel.ExcelWriter.write(ExcelWriter.java:73)
	at com.whty.acc.accountbook.service.impl.AccountBookExcelServiceImpl.exportExcelList(AccountBookExcelServiceImpl.java:146)
1
2
3
4
5
6
7
8
9

代码如下

public void exportExcelList(OutputStream outputStream, String pathName, List<ExcelSheetVO> list, boolean isShowCount, int lastCol) {
        Resource resource = new ClassPathResource(pathName);
        InputStream inputStream = null;
        String fileName = DateUtil.getDateRandom() + ".xls";
        File file = new File(TmpDic.url + File.separator + fileName);
        try {
            inputStream = resource.getInputStream();
            FileUtils.copyInputStreamToFile(inputStream, file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(file).build();
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        StyleCellHander styleCellHander = new StyleCellHander(false);
        for (ExcelSheetVO vo : list) {
            if (vo.getList() != null) {
                WriteSheet writeSheet = EasyExcel.writerSheet(vo.getSheetNo(), vo.getSheetName())
                        //标题
                        .registerWriteHandler(new SubsidiaryLedgerSheetWriteHander(vo.getExcelHeadVO(), isShowCount, lastCol))
                        //单元格
                        .registerWriteHandler(new SubsidiaryLedgerSheetStyleCellHander(isShowCount, false))
                        .registerWriteHandler(new CustomCellWriteHandler())
                        .build();
                excelWriter.fill(vo.getList(), fillConfig, writeSheet);
            } else {
                WriteSheet writeSheet = EasyExcel.writerSheet(vo.getSheetNo(), vo.getSheetName()).build();
                List<List> multipleList = vo.getMultipleList();
                int titleRownum = 0;
                int num = 0;
                for (int i = 0; i < multipleList.size(); i++) {
                    List<LedgerDto> data = multipleList.get(i);
                    if (i > 0) {
                        titleRownum = num;
                    }
                    num = num + data.size() + 3;
                    ExcelHeadVO headVo = new ExcelHeadVO();
                    BeanUtils.copyProperties(vo.getExcelHeadVO(), headVo);
                    if (i > 0) {
                        headVo.setTitle(data.get(0).getSubFullName());
                        headVo.setSubName(data.get(0).getSubCodeName());
                    }

                    WriteTable writeTable = EasyExcel.writerTable(i).head(vo.getClazz())
                            //标题
                            .registerWriteHandler(new TitleSheetWriteHandler(headVo, titleRownum, lastCol))
                            //单元格
                            .registerWriteHandler(styleCellHander)
                            .build();
                    excelWriter.write(data, writeSheet, writeTable);
                }
            }
        }
        excelWriter.finish();
        file.delete();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

easyexcel一个模板动态多个sheet填充，从这篇文章中找到了解决方案。但结果并没有对方的好，奇怪。
第一个页签空白

第二页签，把上面的模板也导出来了，奇怪了，easyexcel没有结果测试吗？

xlsx格式文件比xls格式文件，体积小一半

一个Sheet多个table，设置表头不需要的话

显示效果如下，上面的模板还在，但是表头信息没有了

easyexcel centos 导出excel无数据 在windows上有 easyexcel导出复杂excel
首先猜想是不是跟sheet名称有关系,于是根据名称来匹配

得到的效果是,页签名称与页签中的内容是匹配的

如果使用,则得到的效果是乱的

 WriteSheet writeSheet = EasyExcel.writerSheet(vo.getSheetNo()).needHead(Boolean.TRUE).build();
1

由于模板也多了一个页签，于是修正

for (int i = 0; i < list.size(); i++){
       ExcelSheetVO vo = list.get(i);
       if (i==0){
           workbook.setSheetName(0,vo.getSheetName());
       } else{
           workbook.cloneSheet(0,vo.getSheetName());
       }
   }
1
2
3
4
5
6
7
8

为什么下面的方式填写数据会为空呢？

WriteSheet writeSheet = EasyExcel.writerSheet(vo.getSheetName())
                        //标题
                        .registerWriteHandler(new SubsidiaryLedgerSheetWriteHander(vo.getExcelHeadVO(), isShowCount, lastCol))
                        //单元格
                        .registerWriteHandler(new SubsidiaryLedgerSheetStyleCellHander(isShowCount, false))
                        .registerWriteHandler(new CustomCellWriteHandler())
                        .build();
                excelWriter.fill(vo.getList(), fillConfig, writeSheet);
1
2
3
4
5
6
7
8

最终无奈退回到2.2.6，应该说2.2.6相对最好用了，现在系统报下面的错误

18:57:14.600 ERROR c.w.a.a.controller.AccSubsidiaryLedgerController - The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook
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:1298)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.createCellStyle(HSSFWorkbook.java:131)
	at com.alibaba.excel.util.StyleUtil.buildDefaultCellStyle(StyleUtil.java:27)
	at com.alibaba.excel.util.StyleUtil.buildHeadCellStyle(StyleUtil.java:49)
	at com.alibaba.excel.write.style.AbstractVerticalCellStyleStrategy.setHeadCellStyle(AbstractVerticalCellStyleStrategy.java:47)
	at com.alibaba.excel.write.style.AbstractCellStyleStrategy.afterCellDispose(AbstractCellStyleStrategy.java:58)
	at com.alibaba.excel.util.WriteHandlerUtils.afterCellDispose(WriteHandlerUtils.java:176)
1
2
3
4
5
6
7
8
9

查看代码，原来是poi的限制，跟easyexcel没有直接的关系，因此升不升级没有影响。

单月明细账36个sheet，每个sheet又有20~50个明细科目。
如下面改成xlsx格式

ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(file).excelType(ExcelTypeEnum.XLSX).build();
1

提示新的错误

Caused by: org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)
	at org.apache.poi.openxml4j.opc.internal.ZipHelper.verifyZipHeader(ZipHelper.java:179)
	at org.apache.poi.openxml4j.opc.internal.ZipHelper.openZipStream(ZipHelper.java:209)
	at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:98)
	at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:324)
	at org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:295)
	at com.alibaba.excel.util.WorkBookUtil.createWorkBook(WorkBookUtil.java:32)
1
2
3
4
5
6
7
8

这个错误说的我使用的是xls的模板，好吧，更正为xlsx的模板，结果提示,EasyExcel实战与笔记,也有人跟我遇到同样的问题。

java.lang.RuntimeException: NotImplemented
	at org.apache.poi.xssf.streaming.SXSSFSheet.shiftRows(SXSSFSheet.java:1000)
	at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.shiftRows(ExcelWriteFillExecutor.java:158)
	at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.fill(ExcelWriteFillExecutor.java:115)
	at com.alibaba.excel.write.ExcelBuilderImpl.fill(ExcelBuilderImpl.java:78)
	at com.alibaba.excel.ExcelWriter.fill(ExcelWriter.java:185)
1
2
3
4
5
6

更正模板后，又出现新的问题

java.lang.NullPointerException: null
	at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.doFill(ExcelWriteFillExecutor.java:191)
	at com.alibaba.excel.write.executor.ExcelWriteFillExecutor.fill(ExcelWriteFillExecutor.java:118)
	at com.alibaba.excel.write.ExcelBuilderImpl.fill(ExcelBuilderImpl.java:78)
	at com.alibaba.excel.ExcelWriter.fill(ExcelWriter.java:185)
1
2
3
4
5

还是改回xls格式，尝试解决单元格样式的问题。回头又发现一个问题，就是第一个页签，与页面内容不匹配的问题。EasyExcel修改模板sheet的名称(解决EasyExcel第一个sheet名称无法修改bug)，有人跟我一样的问题，按照他的方式解决即可。

接下来的问题在于怎么将CellStyle的创建数量降低,poi workbook 的
因为HSSFCellStyle不能直接被实例化，被保护了。

cellstyle 创建不能超过4000的解决方法，这个里面没有提供CacheManage的代码，或许也不需要这么干吧。无非是创建CellStyle,另外一种解决方案

    private CellStyle getStyle(ExcelWriter excelWriter){
        CellStyle cellStyle = excelWriter.writeContext().writeWorkbookHolder().getWorkbook().createCellStyle();
        //背景为白色
        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //右对齐
        cellStyle.setAlignment(HorizontalAlignment.RIGHT);
        // 下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // 左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // 上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        return cellStyle;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

把样式定义在外层，然后传进去。明天搞个工具再仔细验证，至少目前上面出现的问题，没有出现

照着这个思路继续探究,再体外创建样式，通过构造函数写入进去，这个问题就解决了，需要将getStyle封装工具类里面，这样就可以循环使用了