POI进行excel导出文件打开报错

一、问题来源

  最近做通州公安的项目，需求包含好几个excel导出的功能，导出我采用POI进行excel导出，引用如下pom：

<!--excel-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>

代码如下：

public BaseResult<String> exportStaticsAccessRecord(AccessRecordDto accessRecordDto, HttpServletResponse response) {
        BaseResult<String> baseResultResult = null;
        ServletOutputStream out = null;
        try {
            BaseResult<List<BaseResultVo>> baseResult = staticsAccessRecord(accessRecordDto);
            if (baseResult != null && CollectionUtils.isNotEmpty(baseResult.getData())) {
                String fileName = "access_record_" + DateUtils.format(new Date(), DateUtils.DATE_TIME_FORMAT_YYYYMMDDHHMISS);
                ExportParams exportParams = new ExportParams("各单位访问记录", "各单位访问记录", ExcelType.XSSF);
                Workbook workbook = ExcelExportUtil.exportExcel(exportParams, BaseResultVo.class, baseResult.getData());
                out = response.getOutputStream();
                response.reset();
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
                response.setContentType("application/msexcel");
                workbook.write(out);
                out.flush();
                out.close();
            } else {
                baseResultResult = BaseResult.fail("-1", "没有需要导出的数据！");
            }
        } catch (IOException e) {
            logger.error("访问记录导出失败！请联系管理员！", e);
            baseResultResult = BaseResult.fail("-1", "访问记录导出失败！请联系管理员！");
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                logger.error("流关闭异常！", e);
            }
        }
        return baseResultResult;
    }

导出代码运行正常，但是打开excel文件后报如下错：

 

 

 点击是继续打开，文件可以正常打开，内容也是正确的。

二、解决方法

  由于ExcelType类型与实际生成的excel文件版本不一致，导致报错，修改文件后缀为xlsx即可，修改后代码：

public BaseResult<String> exportStaticsAccessRecord(AccessRecordDto accessRecordDto, HttpServletResponse response) {
        BaseResult<String> baseResultResult = null;
        ServletOutputStream out = null;
        try {
            BaseResult<List<BaseResultVo>> baseResult = staticsAccessRecord(accessRecordDto);
            if (baseResult != null && CollectionUtils.isNotEmpty(baseResult.getData())) {
                String fileName = "access_record_" + DateUtils.format(new Date(), DateUtils.DATE_TIME_FORMAT_YYYYMMDDHHMISS);
                ExportParams exportParams = new ExportParams("各单位访问记录", "各单位访问记录", ExcelType.XSSF);
                Workbook workbook = ExcelExportUtil.exportExcel(exportParams, BaseResultVo.class, baseResult.getData());
                out = response.getOutputStream();
                response.reset();
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
                response.setContentType("application/msexcel");
                workbook.write(out);
                out.flush();
                out.close();
            } else {
                baseResultResult = BaseResult.fail("-1", "没有需要导出的数据！");
            }
        } catch (IOException e) {
            logger.error("访问记录导出失败！请联系管理员！", e);
            baseResultResult = BaseResult.fail("-1", "访问记录导出失败！请联系管理员！");
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                logger.error("流关闭异常！", e);
            }
        }
        return baseResultResult;
    }

问题解决。

三、总结

  HSSF类，只支持2007以前的excel，文件扩展名为xls，XSSF类支持07以后的excel，07以后的excel工作簿默认格式是xlsx，所以ExcelType和excel文件后缀要保持一致。