easyexcel进行模版数据填充并导出遇到的“Create workbook failure”

时间:2025-03-18 15:01:00

项目场景:

公司的一个小的报表填报和导出的演示demo。项目采用springboot搭建,导出功能使用alibaba的EasyExcel工具包进行excel报表模版读取并填充后导出,使用过程中出现两个名称为“Create workbook failure”的错误。


问题描述

1. 读取resource目录的数据的时候,出现“: Unexpected record signature: 0XEFBDBFEF”问题,日志如下:

: Create workbook failure
	at .<init>(:98)
	at .<init>(:36)
	at .<init>(:39)
	at (:133)
	at (:149)
	at (:137)
	at (:116)
	at $$FastClassBySpringCGLIB$$(<generated>)
	at (:218)
	at $DynamicAdvisedInterceptor.intercept(:687)
	at $$EnhancerBySpringCGLIB$$(<generated>)
	at (:187)
	at $$FastClassBySpringCGLIB$$(<generated>)
	at (:218)
	at $CglibMethodInvocation.invokeJoinpoint(:771)
	at (:163)
	at $CglibMethodInvocation.proceed(:749)
	at (:95)
	at (:186)
	at $CglibMethodInvocation.proceed(:749)
	at $DynamicAdvisedInterceptor.intercept(:691)
	at $$EnhancerBySpringCGLIB$$(<generated>)
	at .invoke0(Native Method)
	at (:62)
	at (:43)
	at (:498)
	at (:190)
	at (:138)
	at (:105)
	at (:878)
	at (:792)
	at (:87)
	at (:1040)
	at (:943)
	at (:1006)
	at (:898)
	at (:626)
	at (:883)
	at (:733)
	at (:231)
	at (:166)
	at (:53)
	at (:193)
	at (:166)
	at (:61)
	at (:108)
	at (:137)
	at (:125)
	at (:66)
	at (:108)
	at (:137)
	at (:125)
	at (:66)
	at (:450)
	at $1.call(:365)
	at (:90)
	at (:83)
	at (:387)
	at (:362)
	at (:125)
	at (:193)
	at (:166)
	at (:92)
	at (:119)
	at (:193)
	at (:166)
	at (:124)
	at (:193)
	at (:166)
	at (:88)
	at (:119)
	at (:193)
	at (:166)
	at (:100)
	at (:119)
	at (:193)
	at (:166)
	at (:93)
	at (:119)
	at (:193)
	at (:166)
	at (:93)
	at (:119)
	at (:193)
	at (:166)
	at (:201)
	at (:119)
	at (:193)
	at (:166)
	at (:202)
	at (:97)
	at (:542)
	at (:143)
	at (:92)
	at (:78)
	at (:343)
	at .http11.(:374)
	at (:65)
	at $ConnectionHandler.process(:868)
	at $SocketProcessor.doRun(:1590)
	at (:49)
	at (:1149)
	at $Worker.run(:624)
	at $WrappingRunnable.run(:61)
	at (:748)
Caused by: .: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file
	at .(:145)
	at ..<init>(:49)
	at ..<init>(:106)
	at .(:307)
	at (:47)
	at .<init>(:309)
	at (:35)
	at .<init>(:96)
	... 104 more
Caused by: : Unexpected record signature: 0XEFBDBFEF
	at (:260)
	at .(:141)
	... 111 more

1.1. 解决方式

出现该问题疑似是模版文件在编译打包后发生变化,系统加载的模版资源文件与原文件不一致。添加如下maven-build插件,该问题消失。

<!-- 避免font文件的二进制文件格式压缩破坏 -->
            <plugin>
                <groupId></groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <nonFilteredFileExtensions>
                        <nonFilteredFileExtension>xls</nonFilteredFileExtension>
                        <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
                    </nonFilteredFileExtensions>
                </configuration>
            </plugin>

2. 读取文件没问题后,又出现另外一个问题,“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)”,日志如下:

: Create workbook failure
	at .<init>(:98)
	at .<init>(:36)
	at .<init>(:39)
	at (:133)
	at (:149)
	at (:137)
	at (:116)
	at $$FastClassBySpringCGLIB$$(<generated>)
	at (:218)
	at $DynamicAdvisedInterceptor.intercept(:687)
	at $$EnhancerBySpringCGLIB$$(<generated>)
	at (:187)
	at $$FastClassBySpringCGLIB$$(<generated>)
	at (:218)
	at $CglibMethodInvocation.invokeJoinpoint(:771)
	at (:163)
	at $CglibMethodInvocation.proceed(:749)
	at (:95)
	at (:186)
	at $CglibMethodInvocation.proceed(:749)
	at $DynamicAdvisedInterceptor.intercept(:691)
	at $$EnhancerBySpringCGLIB$$(<generated>)
	at .invoke0(Native Method)
	at (:62)
	at (:43)
	at (:498)
	at (:190)
	at (:138)
	at (:105)
	at (:878)
	at (:792)
	at (:87)
	at (:1040)
	at (:943)
	at (:1006)
	at (:898)
	at (:626)
	at (:883)
	at (:733)
	at (:231)
	at (:166)
	at (:53)
	at (:193)
	at (:166)
	at (:61)
	at (:108)
	at (:137)
	at (:125)
	at (:66)
	at (:108)
	at (:137)
	at (:125)
	at (:66)
	at (:450)
	at $1.call(:365)
	at (:90)
	at (:83)
	at (:387)
	at (:362)
	at (:125)
	at (:193)
	at (:166)
	at (:92)
	at (:119)
	at (:193)
	at (:166)
	at (:124)
	at (:193)
	at (:166)
	at (:88)
	at (:119)
	at (:193)
	at (:166)
	at (:100)
	at (:119)
	at (:193)
	at (:166)
	at (:93)
	at (:119)
	at (:193)
	at (:166)
	at (:93)
	at (:119)
	at (:193)
	at (:166)
	at (:201)
	at (:119)
	at (:193)
	at (:166)
	at (:202)
	at (:97)
	at (:542)
	at (:143)
	at (:92)
	at (:78)
	at (:343)
	at .http11.(:374)
	at (:65)
	at $ConnectionHandler.process(:868)
	at $SocketProcessor.doRun(:1590)
	at (:49)
	at (:1149)
	at $Worker.run(:624)
	at $WrappingRunnable.run(:61)
	at (:748)
Caused by: ..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 .(:147)
	at .(:175)
	at ..<init>(:104)
	at .(:307)
	at (:47)
	at .<init>(:309)
	at (:35)
	at .<init>(:96)
	... 104 more

2.1. 解决方式

从日志可以明显看出,资源模版读取成功后,使用easyexcel获取模版中的sheet页时出现问题,没有明确的识别excel文件的版本。从easyexcel文档中找到了类型参数设置方法(excelType.(ExcelTypeEnum)),配置相应的模版文件版本后该问题消失。代码如下:

try (InputStream inputStream = classPathResource.getInputStream();
             ServletOutputStream outputStream = response.getOutputStream()) {
            EasyExcel.write(outputStream)
                    //设置模版类型
                    .excelType(ExcelTypeEnum.XLS)
                    .withTemplate(inputStream)
                    .sheet()
                    .doFill(resultXls);
 } catch (Exception e) {
      e.printStackTrace();
 }

附上下载方法代码:

@Override
    public void exportXls(TongtechResultDto tongtechResult, HttpServletResponse response) throws Exception {
        //查询出来的对象
        List<String> ids = Lists.newArrayList();
        TongtechResultXls resultXls = getExcelData(tongtechResult, ids);

        // 配置响应流,
        response.setContentType("application/-excel");
        response.setCharacterEncoding("utf-8");
        // 这里可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("*******统计表", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        //获取模板
        ClassPathResource classPathResource = new ClassPathResource("/templates/*******统计表.xls");
        try (InputStream inputStream = classPathResource.getInputStream();
             ServletOutputStream outputStream = response.getOutputStream()) {
            EasyExcel.write(outputStream)
                    //设置模版类型
                    .excelType(ExcelTypeEnum.XLS)
                    .withTemplate(inputStream)
                    .sheet()
                    .doFill(resultXls);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }