SpringBoot项目导出Excel表的两种办法——EasyExcel&Hutool

最新推荐文章于 2024-02-29 17:09:17 发布

上弦暮光

最新推荐文章于 2024-02-29 17:09:17 发布

阅读量415

点赞数

本文链接：https://blog.csdn.net/qq_44769705/article/details/132859867

版权

本文介绍了如何在SpringBoot项目中，使用Hutool和EasyExcel库实现数据的分页查询并导出为Excel文件，包括处理ExcelProperty注解、设置Content-Type和Content-Disposition头以及两种库的简单和复杂版本的区别。

摘要由CSDN通过智能技术生成

准备工作

查询结果类：ProjectVo

@Data
public class ProjectVo {
    @ExcelProperty(value = "ID")
	private Integer id;

    @ExcelProperty(value = "项目代码")
	private String code;

    @ExcelProperty(value = "项目名称")
	private String name;

	
    @ExcelProperty(value = "token")
	private String tokenStr;

    @ExcelProperty(value = "描述信息")
	private String remark;

    
}

复杂版本：Hutool

@ApiOperation(value = "32、export导出")
    @GetMapping(value = "/exportExcel", produces = "application/octet-stream")
    public void exportExcel(ProjectQuery query, HttpServletResponse response) throws IOException {
        // 查询数据，正常的SpringBoot项目的分页查询操作
        // 自己进行测试时可以采用自己构造一个List<Object>用来模拟数据库读出情况

        List<ProjectVo> voList = this.projectService.listForExport(query);

        List<Map<String, Object>> list = new ArrayList<>(voList.size());
        for (ProjectVo projectVo : voList) {
            Map<String, Object> row = new HashMap<>();

            row.put("项目名称", projectVo.getName());
            row.put("项目代码", projectVo.getCode());
            row.put("ID", projectVo.getId());
            row.put("tokenStr", projectVo.getTokenStr());
            row.put("REMARK", projectVo.getRemark());

            list.add(row);
        }
        ExcelWriter writer = ExcelUtil.getWriter(true);

        
        //下面这五行其实可以不加，加了是为了确定导出后的Excel表的每一列的顺序
        //否则可能会出现id在第五列这种情况

        writer.addHeaderAlias("ID", "ID");
        writer.addHeaderAlias("项目代码", "项目代码");
        writer.addHeaderAlias("项目名称", "项目名称");
        writer.addHeaderAlias("tokenStr", "tokenStr");
        writer.addHeaderAlias("REMARK", "REMARK");

        writer.write(list, true);

        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename=projectlist.xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        writer.flush(outputStream, true);
        writer.close();
        IoUtil.close(System.out);

    }

简单版本：EasyExcel

是真的方便多了若要给每一行重命名则只需要在ProjectVo的数据上面加入@ExcelProperty(value = "自定义的名称")

@ApiOperation(value = "33、export导出")
    @GetMapping(value = "/exportExcelNew", produces = "application/octet-stream")
    public void exportExcelNew(ProjectQuery query, HttpServletResponse response) throws IOException {
        // 查询数据
        List<ProjectVo> voList = this.projectService.listForExport(query);

        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");

        // String fileName= URLEncoder.encode("测试","UTF-8").replace("\\+","%20");
        //上面那句话是用来进行中文表格命名的，由于最终没有使用，所以把它注释掉了
        response.setHeader("Content-Disposition", "attachment;filename=projecttest.xlsx");
        EasyExcel.write(response.getOutputStream()).head(ProjectVo.class).excelType(ExcelTypeEnum.XLSX).sheet("测试")
            .doWrite(voList);

    }