easyExcel通用导出工具类

12/15/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 filenamePrefix   文件名前缀
     * @param sheetName        sheet工作簿名称
     * @param converter        转换器
     * @throws IOException ioexception
     */
    public static void writeSheetUseTemplate(HttpServletResponse response, Class<?> elementType, Collection<?> data, String serverMbPath, String templateFileName, String filenamePrefix, String sheetName, Converter<?> converter) throws IOException {
        String tmpName = serverMbPath + templateFileName;
        String fileName = FileNameUtil.mainName(tmpName);
        setResponse(response,filenamePrefix + fileName);
        try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), elementType).withTemplate(tmpName).registerConverter(converter).build())
        {
            WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetName).build();
            excelWriter.fill(data,writeSheet);
            excelWriter.finish();
            response.flushBuffer();
        } catch (IOException e) {
            writeWhenNotData(response,e,"下载文件失败");
        }
    }
    
    /**
     * 写表中使用模板
     *
     * @param response         响应
     * @param data             数据
     * @param serverMbPath     模板存放路径
     * @param templateFileName 模板文件名字
     * @param elementType      元素类型
     * @param filenamePrefix   文件名前缀
     * @param sheetName        sheet工作簿名称
     * @param converter        转换器
     * @param mergeCellIndex   合并单元序号
     * @throws IOException ioexception
     */
    public static void writeSheetUseTemplate(HttpServletResponse response, Class<?> elementType, Collection<?> data, String serverMbPath, String templateFileName, String filenamePrefix, String sheetName, Converter<?> converter,int... mergeCellIndex) throws IOException {
        String tmpName = serverMbPath + templateFileName;
        String fileName = FileNameUtil.mainName(tmpName);
        setResponse(response,filenamePrefix + fileName);
        try(ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), elementType).withTemplate(tmpName).registerConverter(converter).registerWriteHandler(new EasyExcelMergeStrategy(data.size(),mergeCellIndex)).build())
        {
            WriteSheet writeSheet = EasyExcel.writerSheet(0,sheetName).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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138

# EasyExcel合并单元格策略

/**
 * @version 1.0.0
 * @className: EasyExcelMergeStrategy
 * @description: EasyExcel通用合并单元格策略实现
 * @author: LiJunYi
 * @create: 2022/12/15 10:49
 */
public class EasyExcelMergeStrategy extends AbstractMergeStrategy {

    /**
     * 合并的列编号,从0开始,指定的index或自己按字段顺序数
     */
    private Set<Integer> mergeCellIndex = new HashSet<>();

    /**
     * 数据集大小,用于区别结束行位置
     */
    private Integer maxRow = 0;

    /**
     * 禁止无参声明
     */
    private EasyExcelMergeStrategy() {
    }

    /**
     * 有参构造
     *
     * @param maxRow         最大行
     * @param mergeCellIndex 合并哪几列的行序号
     */
    public EasyExcelMergeStrategy(Integer maxRow, int... mergeCellIndex) {
        Arrays.stream(mergeCellIndex).forEach(item -> {
            this.mergeCellIndex.add(item);
        });
        this.maxRow = maxRow;
    }

    /**
     * 记录上一次合并的信息
     */
    private Map<Integer, MergeRange> lastRow = new HashedMap();

    /**
     * 合并
     *
     * @param sheet            表
     * @param cell             细胞
     * @param head             头
     * @param relativeRowIndex 相对行序号
     */
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        int currentCellIndex = cell.getColumnIndex();
        // 判断该列是否需要合并
        if (mergeCellIndex.contains(currentCellIndex)) {
            // 动态获取cell的值,避免cell的类型不同获取方式不同
            String currentCellValue = getCellValueByCellType(cell);
            int currentRowIndex = cell.getRowIndex();
            if (!lastRow.containsKey(currentCellIndex)) {
                // 记录首行起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
                return;
            }
            //有上行这列的值了,拿来对比.
            MergeRange mergeRange = lastRow.get(currentCellIndex);
            if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
                // 结束的位置触发下合并.
                // 同行同列不能合并,会抛异常
                if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
                }
                // 更新当前列起始位置
                lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
            }
            // 合并行 + 1
            mergeRange.endRow += 1;
            // 结束的位置触发下最后一次没完成的合并
            if (relativeRowIndex.equals(maxRow - 1)) {
                MergeRange lastMergeRange = lastRow.get(currentCellIndex);
                // 同行同列不能合并,会抛异常
                if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));
                }
            }
        }
    }

    /**
     * 获取不同类型的cell的Value,转换成String返回
     * 可根据CellType的枚举自行补全对应的类型
     * @return {@link String}
     */
    private static String getCellValueByCellType(Cell cell)
    {
        CellType cellType = cell.getCellType();
        int ordinal = cellType.ordinal();
        Object value;
        switch (ordinal)
        {
            case 1:
                value = cell.getNumericCellValue();
                break;
            case 2:
                value = cell.getStringCellValue();
                break;
            default:
                value = StrUtil.EMPTY;
        }
        return Convert.toStr(value);
    }
}

class MergeRange{

    /**
     * 开始行
     */
    public int startRow;

    /**
     * 结束行
     */
    public int endRow;

    /**
     * 开始单元格
     */
    public int startCell;

    /**
     * 结束单元格
     */
    public int endCell;

    /**
     * 最后值
     */
    public String lastValue;

    /**
     * 构造函数
     *
     * @param lastValue 最后值
     * @param startRow  开始行
     * @param endRow    结束行
     * @param startCell 开始单元格
     * @param endCell   结束单元格
     */
    public MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {
        this.startRow = startRow;
        this.endRow = endRow;
        this.startCell = startCell;
        this.endCell = endCell;
        this.lastValue = lastValue;
    }
}

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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158

# 使用实例

@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);
        }
    }
    

   /**
     * 使用模板导出并合并行
     *
     * @param request  请求
     * @param response 响应
     * @throws IOException ioexception
     */
    @GetMapping("dept")
    public void downloadDeptCarDataFile(HttpServletRequest request, HttpServletResponse response) throws IOException {
        List<CarModel> carModels = carService.getDownloadDeptCarDataFile();
        if (carModels.isEmpty())
        {
            EasyExcelExportUtil.writeWhenNotData(response,null,"暂无数据");
        }else
        {
            EasyExcelExportUtil.writeSheetUseTemplate(response, DormModel.class,carModels,globalProperties.getServerMbPath(), FileConstants.DEPT_CAR_EXCEL_FILE,"","xx一览表",new LocalDateConverter(),0,1);
        }
    }
}
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