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

原创

J星星点灯 2022-05-01 22:05:19 ©著作权

©著作权归作者所有：来自51CTO博客作者J星星点灯的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、问题来源

最近做通州公安的项目，需求包含好几个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文件后报如下错：

POI进行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;
    }