EasyExcel自定义合并单元格导出

问题

根据业务需求会有导出中需要合并的功能，但是当根据数据合并时又不能使用注解来进行合并，只能自定义合并，根据官网中介绍可以使用第二种方式，官网地址https://www.yuque.com/easyexcel/doc/write#cac25459

导出的格式：

 

解决思路

实际上是使用此类中重写 afterCellDispose() 方法；

RowMergeStrategy类：

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.Map;

public class RowMergeStrategy extends AbstractCellWriteHandler {

    private Map<String, List<MergeRowBean>> strategyMap;
    private Sheet sheet;

    public RowMergeStrategy(Map<String, List<MergeRowBean>> strategyMap) {
        this.strategyMap = strategyMap;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead.booleanValue()) {
            return;
        }
        merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
    }

    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
            /**
             * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
             * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
             * 但此时A2,A3已经是合并的单元格了
             */
            for (Map.Entry<String, List<MergeRowBean>> entry : strategyMap.entrySet()) {
                Integer columnIndex = Integer.valueOf(entry.getKey());
                entry.getValue().forEach(rowRange -> {
                    //添加一个合并请求
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getFirstRow(),
                            rowRange.getLastRow(), columnIndex, columnIndex));
                });
            }
        }
    }
}

MergeRowBean类：

public class MergeRowBean {
    /**
     * 起始行
     */
    private int firstRow;
    /**
     * 结束行
     */
    private int lastRow;



    public MergeRowBean(int firstRow, int lastRow) {
        this.firstRow = firstRow;
        this.lastRow = lastRow;
    }

    public int getFirstRow() {
        return firstRow;
    }

    public void setFirstRow(int firstRow) {
        this.firstRow = firstRow;
    }

    public int getLastRow() {
        return lastRow;
    }

    public void setLastRow(int lastRow) {
        this.lastRow = lastRow;
    }
}

 ExcelUtil类：

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

    /**
     * 添加
     * @param excelDtoList 要导出的集合
     * @return
     */
    public static Map<String, List<MergeRowBean>> addMerStrategy(List<ThirdTypeTableExcelResponseBean> excelDtoList) {
        Map<String, List<MergeRowBean>> strategyMap = new HashMap<>();

        ThirdTypeTableExcelResponseBean preExcelDto = null;
        //行坐标
        int startRow = 0;
        for (int i = 0; i < excelDtoList.size(); i++) {
            ThirdTypeTableExcelResponseBean currDto = excelDtoList.get(i);
            if (preExcelDto != null) {
                if (currDto.getFirstTypeId() != null) {
                    //当firstTypeId相同时
                    if (currDto.getFirstTypeId().equals(preExcelDto.getFirstTypeId())) {
                        fillStrategyMap(strategyMap, "0", i + startRow);
                    }
                    //当secondTypeId相同时
                    if (currDto.getSecondTypeId().equals(preExcelDto.getSecondTypeId())) {
                        fillStrategyMap(strategyMap, "1", i + startRow);
                    }
                }
            }
            preExcelDto = currDto;
        }

        return strategyMap;
    }

    /**
     * 合并
     *
     * @param strategyMap Map
     * @param key         列
     * @param index       行坐标
     */
    private static void fillStrategyMap(Map<String, List<MergeRowBean>> strategyMap, String key, int index) {
        List<MergeRowBean> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
        boolean flag = false;
        for (MergeRowBean dto : rowRangeDtoList) {
            //分段list中是否有end索引是上一行索引的，如果有，则索引+1
            if (dto.getLastRow() == index) {
                dto.setLastRow(index + 1);
                flag = true;
            }
        }
        //如果没有，则新增分段
        if (!flag) {
            rowRangeDtoList.add(new MergeRowBean(index, index + 1));
        }
        strategyMap.put(key, rowRangeDtoList);
    }

}

代码中可以看到思路为比较上一个类和当前类中同属性是否相同，如果相同则合并

导出代码：

//要导出的集合
List<ThirdTypeTableExcelResponseBean> thirdTypeTableExcelResponseBeans = BeanUtil.listObjToListObj(thirdTypeListByDate, ThirdTypeTableExcelResponseBean.class);
//设置表名
        StringBuilder title = new StringBuilder();
        title.append("xxx")
                .append(statisticsQueryRequestBean.getStartTime())
                .append("至")
                .append(statisticsQueryRequestBean.getEndTime())
                .append(".xlsx");
        try {
            String encodeFileName = URLEncoder.encode(title.toString(), "utf-8");
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            response.setHeader("Content-disposition", "attachment;filename=" + encodeFileName + "");
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Access-Control-Expose-Headers", title.toString());
            response.setHeader("fileName", encodeFileName);
            //设置合并规则
            Map<String, List<MergeRowBean>> stringListMap = ExcelUtil.addMerStrategy(thirdTypeTableExcelResponseBeans);
            ExcelWriter excelWriter = EasyExcelFactory.write(response.getOutputStream(), xxx.class).registerWriteHandler(new RowMergeStrategy(stringListMap)).build();
            /* 这里注意 如果同一个sheet只要创建一次 */
            WriteSheet writeSheet = EasyExcelFactory.writerSheet("xxx").build();
            excelWriter.write(thirdTypeTableExcelResponseBeans, writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            log.error("导出失败 {}", e.getMessage());
            response.reset();
            response.setContentType(MediaType.APPLICATION_JSON_VALUE);
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            Map<String, String> map = new HashMap<>(8);
            map.put("status", "failure");
            map.put("message", "导出失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }