EasyExcel 合并单元格

3步实现

1.RowRangeDto

package com.zw.zwzc.utils.excel;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto {
    private int start;
    private int end;
}

2.BizMergeStrategy

package com.zw.zwzc.utils.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
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 BizMergeStrategy extends AbstractMergeStrategy {
 
    private Map<String, List<RowRangeDto>> strategyMap;
    private Sheet sheet;
 
    public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
        this.strategyMap = strategyMap;
    }
 
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            /**
             * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
             * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
             * 但此时A2,A3已经是合并的单元格了
             */
            for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
                Integer columnIndex = Integer.valueOf(entry.getKey());
                entry.getValue().forEach(rowRange -> {
                    //添加一个合并请求
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                            rowRange.getEnd(), columnIndex, columnIndex));
                });
            }
        }
    }
}

使用示例：

    @GetMapping("/excel")
    @ApiOperation("统计当日各个环节办件数量")
    public void statisticsOneDayInfo(StatisticsOneDayInfoItemVo itemVo,HttpServletResponse response) throws IOException {
        StatisticsOneDayInfoVo dayInfoVo = statisticsService.statisticsOneDayInfo(itemVo);
        List<StatisticsOneDayInfoItemVoExcel> excels = new ArrayList<>();
        List<StatisticsOneDayInfoItemVo> statisticsOneDayInfoItemVos = dayInfoVo.getStatisticsOneDayInfoItemVos();
        for (StatisticsOneDayInfoItemVo statisticsOneDayInfoItemVo : statisticsOneDayInfoItemVos) {
            StatisticsOneDayInfoItemVoExcel excel = new StatisticsOneDayInfoItemVoExcel();
            BeanUtil.copyProperties(statisticsOneDayInfoItemVo, excel);
            excels.add(excel);
        }
        String key = DateUtil.format(new Date(), "yyyy-MM-dd-HH-mm-ss");
        // 配置文件下载
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
      response.setHeader("Content-disposition", "attachment;filename=" + key + ".xlsx");
//        ExcelUtil.writeExcel(response, excels, key,
//          "sheet1", new StatisticsOneDayInfoItemVoExcel());
      Map<String, List<RowRangeDto>> strategyMap = addMerStrategy(excels);
      EasyExcel.write(response.getOutputStream(), StatisticsOneDayInfoItemVoExcel.class)
            .sheet("sheet1")
            .registerWriteHandler(new BizMergeStrategy(strategyMap)) //这一行就是用来合并单元格的，去除的话就是正常的单条导出
            .doWrite(excels);
    }

　　/**
     * @description: 列表导出--添加合并策略（EasyExcel）
     * @author: panda
     * @date: 2021/4/1
     */
    public static Map<String, List<RowRangeDto>> addMerStrategy(List<StatisticsOneDayInfoItemVoExcel> excelDtoList) {
        Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();
        StatisticsOneDayInfoItemVoExcel preExcelDto = null;
        for (int i = 0; i < excelDtoList.size(); i++) {
            StatisticsOneDayInfoItemVoExcel currDto = excelDtoList.get(i);
            if (preExcelDto != null) {
                //从第二行开始判断是否需要合并
                if (currDto.getDeptName().equals(preExcelDto.getDeptName()) && currDto.getTacheName().equals(preExcelDto.getTacheName())) {
                    //如果Id一样，则可合并一列
//                    fillStrategyMap(strategyMap, "0", i);
//                    fillStrategyMap(strategyMap, "1", i);
                    fillStrategyMap(strategyMap, "2", i);
//                    fillStrategyMap(strategyMap, "3", i);
//                    fillStrategyMap(strategyMap, "4", i);
//                    fillStrategyMap(strategyMap, "5", i);
//                    fillStrategyMap(strategyMap, "6", i);
                    fillStrategyMap(strategyMap, "7", i);
                }
            }
            preExcelDto = currDto;
        }
        return strategyMap;
    }

    /**
     * @description: 新增或修改合并策略map(EasyExcel)
     * @author: panda
     * @date: 2021/4/1
     */
    private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index) {
        List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);
        boolean flag = false;
        for (RowRangeDto dto : rowRangeDtoList) {
            //分段list中是否有end索引是上一行索引的，如果有，则索引+1
            if (dto.getEnd() == index) {
                dto.setEnd(index + 1);
                flag = true;
            }
        }
        //如果没有，则新增分段
        if (!flag) {
            rowRangeDtoList.add(new RowRangeDto(index, index + 1));
        }
        strategyMap.put(key, rowRangeDtoList);
    }

 

效果展示