easyExcel通用导出工具类

9/9/2022 excel

# 需求前景

近期在做几个关于excel文件导出的操作,其中有需要分部门导出多个sheet的,也有需要根据模板进行导出的,于是便在Easyexcel的基础上继续造了个轮子。

# 轮子开始

# EasyExcelExportUtil

/**
 * @version 1.0.0
 * @className: EasyExcelExportUtil
 * @description: EasyExcelExportUtil 通用多sheet、使用模板进行导出的工具类封装
 * @author: LiJunYi
 * @create: 2022/9/5 15:55
 */
@Slf4j
public class EasyExcelExportUtil
{
    /**
     * 写多个sheet
     *
     * @param response                响应
     * @param fileName                文件名字
     * @param elementType             元素类型
     * @param dataMap                 数据Map集合
     * @param excludeColumnFieldNames 排除列字段名称
     * @throws IOException ioexception
     */
    public static void writeMultipleSheet(HttpServletResponse response, String fileName, Class<?> elementType, Map<String, ? extends Collection<?>> dataMap,Collection<String> excludeColumnFieldNames) throws IOException {
        try {
            setResponse(response,fileName);
            // 分sheet写入
            try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), elementType).excludeColumnFieldNames(excludeColumnFieldNames).build()) {
                AtomicInteger i = new AtomicInteger();
                dataMap.forEach((k, v) -> {
                    // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                    WriteSheet writeSheet = EasyExcel.writerSheet(i.get(), k).build();
                    excelWriter.write(v, writeSheet);
                    i.incrementAndGet();
                });
                if (dataMap.isEmpty())
                {
                    // 写入空表
                    WriteSheet writeSheet = EasyExcel.writerSheet(i.get(), "暂无数据").build();
                    excelWriter.write(Lists.newArrayList(), writeSheet);
                }
                excelWriter.finish();
                response.flushBuffer();
            }
        } catch (Exception e) {
            writeWhenNotData(response,e,"下载文件失败");
        }
    }

    /**
     * 写表中使用模板
     *
     * @param response         响应
     * @param data             数据
     * @param serverMbPath     模板存放路径
     * @param templateFileName 模板文件名字
     * @param elementType      元素类型
     * @param month            月
     * @throws IOException ioexception
     */
    public static void writeSheetUseTemplate(HttpServletResponse response,Class<?> elementType,Collection<?> data,String serverMbPath, String templateFileName,String month) throws IOException {
        String tmpName = serverMbPath + templateFileName;
        String fileName = FileNameUtil.mainName(tmpName);
        setResponse(response,month + fileName);
        try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), elementType).withTemplate(tmpName).build())
        {
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"待发表").build();
            excelWriter.fill(data,writeSheet);
            excelWriter.finish();
            response.flushBuffer();
        } catch (IOException e) {
            writeWhenNotData(response,e,"下载文件失败");
        }
    }

    /**
     * 设置响应
     *
     * @param response 响应
     * @param fileName 文件名字
     */
    public static void setResponse(HttpServletResponse response,String fileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String downFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + downFileName + ".xlsx");
    }

    /**
     * 当没有数据写
     *
     * @param response 响应
     */
    public static void writeWhenNotData(HttpServletResponse response,Exception e,String msg) throws IOException {
        // 重置response
        response.reset();
        response.setContentType("application/json");
        response.setCharacterEncoding("utf-8");
        if (null != e)
        {
            String s = msg + e.getMessage();
            response.getWriter().println(JSONUtil.toJsonStr(AjaxResult.error(s)));
        }else
        {
            response.getWriter().println(JSONUtil.toJsonStr(AjaxResult.warn(msg)));
        }

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

# 使用实例

@Slf4j
@RestController
@RequestMapping("payroll")
public class PayrollController
{
    /**
     * 多sheet进行导出
     * @param month 月
     * @return {@link AjaxResult}
     */
    @GetMapping(value = "download/job")
    public void downloadPayrollFileInJob(HttpServletResponse response) throws IOException
    {
        Map<String,List<PayrollModel>> dataMap =  payrollService.getPayrollsByMonth(month,type);
        // 文件名
        String fileName = FileConstants.FIVE_PAYROLL_JOB;
        // 要忽略的字段名称
        Set<String> excludeColumnFiledNames = new HashSet<String>();
        excludeColumnFiledNames.add("vestingYear");
        excludeColumnFiledNames.add("vestingMonth");
        EasyExcelExportUtil.writeMultipleSheet(response, fileName, PayrollModel.class, dataMap,excludeColumnFiledNames);
    }

    /**
     * 使用模板导出
     *
     * @param month    月
     * @param response 响应
     */
    @GetMapping("download/published/{month}")
    public void downloadForPublication(@PathVariable("month") String month, HttpServletResponse response) throws IOException {
        List<PayrollModel> models = payrollService.getPayrollsToPublicationByMonth(month);
        if (models.isEmpty())
        {
            EasyExcelExportUtil.writeWhenNotData(response,null,"暂无数据");
        }else
        {
            EasyExcelExportUtil.writeSheetUseTemplate(response,PayrollModel.class,models,globalProperties.getServerMbPath(), FileConstants.PAYROLL_FOR_PUBLICATION,month);
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41