SpringBoot+easyExcel 按照模板导出（动态合并单元格）

1、引入pom 文件

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
</dependency>

2、模板示例

用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"{","}"代替
填充list 的时候还要注意 模板中{.} 多了个点 表示list

3、服务端接口

/**
 * @return
 * @Date 2022/5/18 15:48
 * @Author 青鸟
 * @Description //TODO 模板导出
 **/
@GetMapping("complexFill")
public void complexFill(HttpServletRequest request, HttpServletResponse response, TaskInfoVo taskInfoVo) throws IOException {
    // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"{","}"代替
    // {} 代表普通变量 {.} 代表是list的变量
    Project project = get(taskInfoVo.getInsideParentId());
    // 填充数据
    Map<String, Object> map = new HashMap<>();
    map.put("parentProjectName", project.getProjectName());
    map.put("projectEndTime", DateUtils.formatDate(project.getProjectEndTime()));
    List<Role> roles = UserUtils.getUser().getRoleList();
    List<String> roleName = roles.stream().map(Role::getEnname).collect(Collectors.toList());
    if (roleName.contains("department_head")) {
        map.put("deptName", OfficeCacheUtils.getOfficeName(UserUtils.getUser().getOffice().getId()));
    } else {
        if (StringUtils.isNotEmpty(taskInfoVo.getExecutiveDepartmentId())) {
            map.put("deptName", OfficeCacheUtils.getOfficeName(taskInfoVo.getExecutiveDepartmentId()));
        }
    }
    List<TaskInfoVo> projectVos = projectService.selectProjectTask(taskInfoVo);
  
    // 处理数据 (此处是业务需要，可以自行更改)
    List<TaskInfoVo> finalList = handlerList(projectVos);
    String basicPath = JeePlusProperites.newInstance().getUserfilesBaseDir();
    //获取模板 (我是将模板放到 resource文件下面了)
    ClassPathResource classPathResource = new ClassPathResource("oppm.xlsx");
    InputStream inputStream = classPathResource.getInputStream();
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName = basicPath + File.separator + System.currentTimeMillis() + ".xlsx";
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    //需要合并的列
    int[] mergeColumeIndex = {0};
    //从第5行后开始合并
    int mergeRowIndex = 4;
    // 调用合并单元格工具类，此工具类是根据工程名称相同则合并后面数据
    ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex);
    // 生成sheet
    WriteSheet sheet1 = EasyExcel.writerSheet(0, "oppm").build();
    ExcelWriter write = EasyExcel.write(response.getOutputStream(), TaskInfoVo.class).withTemplate(inputStream).registerWriteHandler(excelFillCellMergeStrategy).build();
    // 填充数据
    write.fill(finalList, sheet1);
    write.fill(map, sheet1);
    write.close();
}

处理数据的方法

/**
 * @return
 * @Date 2022/5/19 15:21
 * @Author 青鸟
 * @Description //TODO 处理数据
 **/

private List<TaskInfoVo> handlerList(List<TaskInfoVo> projectVos) {
    Integer month = LocalDate.now().getMonth().getValue();
    for (TaskInfoVo projectVo : projectVos) {
        String value = projectVo.getTaskState();
        projectVo.setTaskState(DictUtils.getDictLabel(value, "task_status", "--"));
        if ("6".equals(value)) {
            // 未月份赋值
            Date date = projectVo.getReviewTime();
            int mon = date.getMonth();
            initMonth(mon + 1, projectVo);
        } else {
            initMonth(month, projectVo);
        }
    }
    return projectVos;
}

private void initMonth(Integer month, TaskInfoVo projectVo) {
    switch (month) {
        case 1:
            projectVo.setJanuary(projectVo.getTaskState());
            break;
        case 2:
            projectVo.setFebruary(projectVo.getTaskState());
            break;
        case 3:
            projectVo.setMarch(projectVo.getTaskState());
            break;
        case 4:
            projectVo.setApril(projectVo.getTaskState());
            break;
        case 5:
            projectVo.setMay(projectVo.getTaskState());
            break;
        case 6:
            projectVo.setJune(projectVo.getTaskState());
            break;
        case 7:
            projectVo.setJuly(projectVo.getTaskState());
            break;
        case 8:
            projectVo.setAugust(projectVo.getTaskState());
            break;
        case 9:
            projectVo.setSeptember(projectVo.getTaskState());
            break;
        case 10:
            projectVo.setOctober(projectVo.getTaskState());
            break;
        case 11:
            projectVo.setNovember(projectVo.getTaskState());
            break;
        case 12:
            projectVo.setDecember(projectVo.getTaskState());
            break;
    }
}

ecxel 工具类 (处理合并)

package com.jeeplus.common.utils;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;

/**
 * @Description:
 * @Company：
 * @Author: 青鸟
 * @Date: 2022/5/19 14:30
 * @Version 1.0
 */

public class ExcelMergeUtil implements CellWriteHandler {
    private int[] mergeColumnIndex;
    private int mergeRowIndex;

    public ExcelMergeUtil() {
    }

    public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
        this.mergeRowIndex = mergeRowIndex;
        this.mergeColumnIndex = mergeColumnIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }


    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        if (curRowIndex > mergeRowIndex) {
            for (int i = 0; i < mergeColumnIndex.length; i++) {
                if (curColIndex == mergeColumnIndex[i]) {
                    mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                    break;
                }
            }
        }
    }


    /**
     * 当前单元格向上合并
     * @param writeSheetHolder
     * @param cell 当前单元格
     * @param curRowIndex 当前行
     * @param curColIndex 当前列
     */
    private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        Boolean bool = false;
        Object preDate = null;
        // 注意这个地方，会有为null的情况
        if (cell.getSheet().getRow(curRowIndex - 1) == null) {
            preDate = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();
        }else{
            preDate = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();
        }
        Object curData = cell.getRow().getCell(0).getStringCellValue();
        bool = preDate.equals(curData);
        if (bool) {
            Sheet sheet = writeSheetHolder.getSheet();
            List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
            boolean isMerged = false;
            for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                    sheet.removeMergedRegion(i);
                    cellRangeAddr.setLastRow(curRowIndex);
                    sheet.addMergedRegion(cellRangeAddr);
                    isMerged = true;
                }
            }
            // 若上一个单元格未被合并，则新增合并单元
            if (!isMerged) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
                sheet.addMergedRegion(cellRangeAddress);
            }
        }
    }
}

注： cell.getSheet().getRow(curRowIndex - 1).getCell(0) 这行如果不做判断会报空指针的错误 原因：cell.getSheet() 行的下标第0到4的数据行，获取的是同一个 sheet 实例 当下标为5时，执行cell.getSheet()获取到的 sheet 实例不一样，而且里面的sheet存在的row数据，只有下标为5以后的

而下标0到3的行数据被存储到缓存的sheet 中 writeSheetHolder.getCachedSheet()

所有针对上述情况，做如下调整

if (cell.getSheet().getRow(curRowIndex - 1) == null) {
    preDate = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();
}else{
    preDate = cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue();
}

4、客户端代码

exportExcel () {
  this.$http({
    url: '/project/project/complexFill',
    method: 'get',
    params: {
      ...this.queryParam
    },
    responseType: 'blob'
  }).then(({ data }) => {
    // 这里返回的为压缩流，type:类型为"application/zip",
    // 也可以是其他文件类型。 application/pdf  ，application/x-ppt，application/vnd.ms-powerpoint，audio/vnd.rn-realaudio 音频 ，'application/vnd.ms-excel'
    const fileName = 'oppm.xlsx'
    let blob = new Blob([data], { type: 'application/vnd.ms-excel' })
    this.handleFileDown(blob, fileName)
  })
},
handleFileDown (blob, fileName) {
  if ('download' in document.createElement('a')) {
    // 非IE下载
    const elink = document.createElement('a')
    elink.download = fileName
    elink.style.display = 'none'
    elink.href = URL.createObjectURL(blob)
    document.body.appendChild(elink)
    elink.click()
    setTimeout(function () {
      URL.revokeObjectURL(elink.href) // 释放URL 对象
      document.body.removeChild(elink)
    }, 100)
  } else {
    // IE10+下载
    navigator.msSaveBlob(blob, fileName)
  }
}

5、效果图