项目场景:
公司的一个小的报表填报和导出的演示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();
}
}