EasyExcel 实现批量合并单元格（支持自定义）

╰+哭是因爲堅強的太久メ 2023-01-16 06:19 225阅读 0赞

### 1 pom.xml(Maven配置文件) ###

<dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.5.1</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>

### 2 CustomMergeHandler(自定义合并单元格处理器) ###

package com.easyexcel;
    
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.handler.AbstractRowWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.stream.Collectors;
    
    /**
     * 自定义合并单元格处理器
     * 每次合并需要sheet页名称、指定开始行号、开始列号、结束行号、结束列号
     * 支持批量合并单元格
     */
    public class CustomMergeHandler extends AbstractRowWriteHandler {
        /**
         * sheet名称KEY
         */
        public static final String SHEETNAME_NAME = "sheetName";
        /**
         * 开始行号KEY
         */
        public static final String STARTROMINDEX_NAME = "startRowIndex";
        /**
         * 开始列号KEY
         */
        public static final String STARTCOLUMNINDEX_NAME = "startColumnIndex";
        /**
         * 结束行号KEY
         */
        public static final String ENDROMINDEX_NAME = "endRowIndex";
        /**
         * 结束列号KEY
         */
        public static final String ENDCOLUMNINDEX_NAME = "endColumnIndex";
        /**
         * 合并单元格信息
         */
        private List<Map<String, Object>> mergeCellList;
        /**
         * 记录sheet页是否合并过单元格，不需要合并单元格的sheet页不会记录
         */
        private Map<String, Boolean> isFirstMap;
    
        public CustomMergeHandler(List<Map<String, Object>> mergeCellList) {
            if (mergeCellList == null || mergeCellList.size() <= 0) {
                return;
            }
            this.mergeCellList = mergeCellList.stream().filter(x ->
                    x.keySet().contains(SHEETNAME_NAME) == true && x.get(SHEETNAME_NAME) != null && StrUtil.isNotBlank(x.get(SHEETNAME_NAME).toString())
                            && x.keySet().contains(STARTROMINDEX_NAME) == true && x.get(STARTROMINDEX_NAME) != null && StrUtil.isNotBlank(x.get(STARTROMINDEX_NAME).toString())
                            && x.keySet().contains(STARTCOLUMNINDEX_NAME) == true && x.get(STARTCOLUMNINDEX_NAME) != null && StrUtil.isNotBlank(x.get(STARTCOLUMNINDEX_NAME).toString())
                            && x.keySet().contains(ENDROMINDEX_NAME) == true && x.get(ENDROMINDEX_NAME) != null && StrUtil.isNotBlank(x.get(ENDROMINDEX_NAME).toString())
                            && x.keySet().contains(ENDCOLUMNINDEX_NAME) == true && x.get(ENDCOLUMNINDEX_NAME) != null && StrUtil.isNotBlank(x.get(ENDCOLUMNINDEX_NAME).toString())
            ).collect(Collectors.toList());
            List<String> sheetNameList = this.mergeCellList.stream().map(x -> x.get(SHEETNAME_NAME).toString()).distinct().collect(Collectors.toList());
            if (sheetNameList == null || sheetNameList.size() <= 0) {
                return;
            }
            isFirstMap = new HashMap<>();
            for (String sheetName : sheetNameList) {
                isFirstMap.put(sheetName, false);
            }
        }
    
        /**
         * 生成合并单元格信息
         *
         * @param sheetName        sheet页名称
         * @param startRowIndex    开始行号
         * @param endRowIndex      结束行号
         * @param startColumnIndex 开始列号
         * @param endColumnIndex   结束列号
         * @return
         */
        public static Map<String, Object> createMergeCellMap(String sheetName, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) {
            Map<String, Object> map = new HashMap<>();
            //sheet页名称
            map.put(SHEETNAME_NAME, sheetName);
            //开始行号
            map.put(STARTROMINDEX_NAME, startRowIndex);
            //结束行号
            map.put(ENDROMINDEX_NAME, endRowIndex);
            //开始列号
            map.put(STARTCOLUMNINDEX_NAME, startColumnIndex);
            //结束列号
            map.put(ENDCOLUMNINDEX_NAME, endColumnIndex);
            return map;
        }
    
        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                    Integer relativeRowIndex, Boolean isHead) {
            Sheet sheet = writeSheetHolder.getSheet();
            //没有合并单元格信息
            if (mergeCellList == null || mergeCellList.size() <= 0) {
                return;
            }
            //当前sheet页不需要合并单元格或者当前sheet页已经合并过单元格了
            if (isFirstMap.keySet().contains(sheet.getSheetName()) == false || isFirstMap.get(sheet.getSheetName()) == true) {
                return;
            }
            isFirstMap.put(sheet.getSheetName(), true);
            List<Map<String, Object>> sheetMergeCellList = mergeCellList.stream().filter(x ->
                    StrUtil.equals(x.get(SHEETNAME_NAME).toString(), sheet.getSheetName())).collect(Collectors.toList());
            for (Map<String, Object> map : sheetMergeCellList) {
                //开始行号
                int startRowIndex = Integer.parseInt(map.get(STARTROMINDEX_NAME).toString());
                //结束行号
                int endRowIndex = Integer.parseInt(map.get(ENDROMINDEX_NAME).toString());
                //开始列号
                int startColumnIndex = Integer.parseInt(map.get(STARTCOLUMNINDEX_NAME).toString());
                //结束列号
                int endColumnIndex = Integer.parseInt(map.get(ENDCOLUMNINDEX_NAME).toString());
                //行号和列号非法(<0)
                if (startColumnIndex < 0 || endColumnIndex < 0 || startRowIndex < 0 || endRowIndex < 0) {
                    continue;
                }
                //合并单元格区域只有一个单元格时，不合并
                if (endRowIndex == startRowIndex && endColumnIndex == startColumnIndex) {
                    continue;
                }
                //开始行号大于结束行号，或者开始列号大于结束列号
                if (startColumnIndex > endColumnIndex || startRowIndex > endRowIndex) {
                    continue;
                }
                //添加合并单元格区域
                CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
            //删除合并单元格信息
            mergeCellList.removeAll(sheetMergeCellList);
        }
    
    }

### 3 调试代码 ###

/**
         * 导出（合并单元格）
         *
         * @param response
         */
        @GetMapping("/exportMergeCell")
        public void exportMergeCell(HttpServletResponse response) {
            try {
                //生成表格数据
                List<List<Object>> dataList = new ArrayList<>();
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));
                dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
    
                //生成合并单元格信息
                List<Map<String, Object>> mergeCellList = new ArrayList<>();
                List<Integer> rowIndexList = new ArrayList<>(Arrays.asList(new Integer[]{0, 2}));
                for (Integer rowIndex : rowIndexList) {
                    for (int i = 0; i < dataList.get(rowIndex).size(); i++) {
                        mergeCellList.add(CustomMergeHandler.createMergeCellMap("模板", rowIndex, rowIndex + 1, i, i));
                    }
                }
    
                //导出文件
                String fileName = new String("文件名称.xls".getBytes(), "UTF-8");
    
                response.addHeader("Content-Disposition", "filename=" + fileName);
                //设置类型，扩展名为.xls
                response.setContentType("application/vnd.ms-excel");
                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CustomMergeHandler(mergeCellList)).build();
                WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
                excelWriter.write(dataList, writeSheet);
                // 千万别忘记finish 会帮忙关闭流
                excelWriter.finish();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

### 4 调试结果 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70][]

![20210424181003492.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70]: /images/20221022/f7e1435c9a614542a70dc98818e550f1.png
[20210424181003492.png]: /images/20221022/233900f907ae488ba7346d2cdda9efe6.png