基与easyExcel实现excel模版导出

SSH_Han

已于 2024-11-18 14:50:58 修改

阅读量729

点赞数

分类专栏： EasyExcel 模版导出Excel 文章标签： excel

于 2022-12-02 15:39:08 首次发布

本文链接：https://blog.youkuaiyun.com/SSH_Han/article/details/128148462

版权

EasyExcel 同时被 2 个专栏收录

1 篇文章

订阅专栏

模版导出Excel

1 篇文章

订阅专栏

Easyexcel官方文档

excel模版位置：

模版数据填充处理：{.对应的实体对象字段名}

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
</dependency>

直接上代码：

  public void export(HttpServletResponse response) throws IOException {
        List<CustomerComplaintDetailVO> customerComplaintCategoryVOList = DataTransformer.transform(customerComplaintService.selectCustomerComplaintDetailList(), CustomerComplaintDetailVO.class);
        if (CollectionUtils.isEmpty(customerComplaintCategoryVOList)) {
            throw new BusinessException("无可用数据，导出失败");
        }
        Resource resource = new ClassPathResource("template/客诉单导出模版.xlsx");
        String templatePath = resource.getFile().getPath();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // URLEncoder.encode防止中文乱码
        String fileName = URLEncoder.encode("客诉单明细", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templatePath).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(customerComplaintCategoryVOList, writeSheet);
        // 关闭流
        excelWriter.finish();

该写法本地一点问题没有，放到服务器上，前端请求，报：找不到模版文件

经过一些了解，jar包的resource的位置发生改变，优化方案：

    public void export(HttpServletResponse response) throws IOException {
        List<CustomerComplaintDetailVO> customerComplaintCategoryVOList = DataTransformer.transform(customerComplaintService.selectCustomerComplaintDetailList(), CustomerComplaintDetailVO.class);
        if (CollectionUtils.isEmpty(customerComplaintCategoryVOList)) {
            throw new BusinessException("无可用数据，导出失败");
        }
        InputStream inputStream = ClassPathResource.class.getClassLoader().getResourceAsStream("template/客诉单导出模版.xlsx");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // URLEncoder.encode防止中文乱码
        String fileName = URLEncoder.encode("客诉单明细", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(inputStream).build();
//        ExcelWriter excelWriter = EasyExcel.write("D:/Users/wsh/Desktop/客诉单导出模版.xlsx").withTemplate(inputStream).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(customerComplaintCategoryVOList, writeSheet);
        // 关闭流
        excelWriter.finish();

最终结果