JAVA使用easyExcel 导出固定模板的文件

最近有个需要导出一个复杂格式的报表需求，思来想去决定还是用模板导出比较简单便捷，以下进入正题。

首先，maven配置如下：

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>easyexcel-core</artifactId>
     <version>3.2.1</version>
</dependency>

创建实体类

import lombok.Data;


@Data
public class UserExcelDTO {


    private String typeName;

    private String  finishedWork;

    private String  projectName;

    private String  sort;

    @Override
    public String toString() {
        return "User{" +
                "typeName=" + typeName +
                ", finishedWork='" + finishedWork + '\'' +
                ", projectName='" + projectName + '\'' +
                ", sort='" + sort + '\'' +
                '}';
    }

    public UserExcelDTO() {
    }

    public UserExcelDTO(String typeName,String  finishedWork,String  projectName,String  sort) {
        this.sort = sort;
        this.projectName = projectName;
        this.finishedWork = finishedWork;
        this.typeName = typeName;
    }


}

新建一个TestController,用于测试

@Controller
@RequestMapping("/exportTest")
public class TestController {
    @Autowired
    IPTProjectService iptProjectService;
    //模板路径
    private static final String RECORD_TEMPLATE = "D:/easyExcelTemplate.xlsx";

    @RequestMapping(value = "/export1", method = RequestMethod.GET)
    @ResponseBody
    public void export(HttpServletResponse response) {
        Map<String, Object> summary = new HashMap<>();
        summary.put("yearmonth", "2023-04-18");
        summary.put("dept", "部门");
        summary.put("username", "张三");
        List<UserExcelDTO> list = new ArrayList<>();
        list.add(new UserExcelDTO("disk status", "success", "磁盘状态","1"));
        list.add(new UserExcelDTO("network status", "success", "网络状态","2"));

        List<UserExcelDTO> list1 = new ArrayList<>();
        list1.add(new UserExcelDTO("disk status111", "1111", "磁盘状态","1"));
        list1.add(new UserExcelDTO("network status", "su1111ccess", "网络状态","2"));
        //如果想生成文件到某个路径而不是直接用浏览器下载，可以把 EasyExcel.write() 括号中的         
        response.getOutputStream()换成对应的路径参数
        ExcelWriter excelWriter = null;
        try  {
            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(RECORD_TEMPLATE).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().forceNewRow(true).build();
            //如果模板存在多个list ，必须要用 new FillWrapper()对应的list包起来 
            excelWriter.fill(new FillWrapper("list", list), fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("list1", list1),fillConfig, writeSheet);
            excelWriter.fill(summary, writeSheet);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String fileName = URLEncoder.encode("record-" + System.currentTimeMillis()+"", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (null!=excelWriter){
                excelWriter.finish();
            }
        }
    }

 如果不想在浏览器测试，可以把 EasyExcel.write() 括号里的参数改为你想保存的路径以及文件名，同时将方法放到main方法中直接执行测试即可：

String filePaht="D:\1.xlsx";
EasyExcel.write(filePaht).withTemplate(RECORD_TEMPLATE).build();

接下来，也是最重要的一环，就是设置模板信息：

最终导出效果如下：