EasyExcel合并单元格策略样例

EasyExcel合并单元格策略样例

运行示例

处理前：

处理后：

说明

本次使用的是一次性绝对合并策略： List<OnceAbsoluteMergeStrategy> mergeStrategyList = new ArrayList<>();

OnceAbsoluteMergeStrategy四个参数：合并开始行，合并结束行，合并开始列，合并结束列

添加策略：builder.registerWriteHandler(strategy);

代码

1、模型层

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * @author CC
 * @description 工时导出excel实体对象
 * @className RespManHoursVO
 * @date 2022/6/21 14:21
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ContentRowHeight(18) //内容行高
@HeadRowHeight(25) //标题行高
public class RespManHoursVO implements Serializable {
    @ExcelProperty(value = {"项目名称"},index = 0)
    @ColumnWidth(18)
    private String project;

    @ExcelProperty(value = {"姓名"},index = 1)
    @ColumnWidth(7)
    private String name;

    @ExcelProperty(value = {"工时占比"},index = 2)
    @ColumnWidth(12)
    private BigDecimal manHour;

    @ExcelProperty(value = {"所属部门"},index = 3)
    @ColumnWidth(19)
    private String dept;

    @ExcelProperty(value = {"是否实习"},index = 4)
    @ColumnWidth(12)
    private String isIntern;
}

2、服务层

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.meb.imis.fiscal.api.IHrUserArchiveService;
import com.meb.imis.fiscal.pojo.vo.resp.RespManHoursVO;
import com.meb.imis.fiscal.service.FileExportService;
import com.meb.imis.framework.api.workteam.service.IWorkTeamService;
import lombok.SneakyThrows;
import lombok.extern.log4j.Log4j;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author CC
 * @description 文件导出服务实现类
 * @className FileExportServiceImpl
 * @date 2022/6/21 14:25
 */
@Log4j
@Service
public class FileExportServiceImpl implements FileExportService {
    @Resource
    private IWorkTeamService workTeamService;

    @Resource
    private IHrUserArchiveService hrUserArchiveService;

    @Override
    @SneakyThrows
    public void exportWorkTime(String date, HttpServletResponse response) {
        // 初始化数据
        List<RespManHoursVO> sheet = new ArrayList<RespManHoursVO>(); // 导出列表
        DateTime dateTime = DateUtil.parse(date, "yyyy-MM"); // 格式化日期
        
        // 获取sheet
        /**
        *	略，自行生成数据
        *	eg:
        *	RespManHoursVO raw1 = new RespManHoursVO();
        *	raw1.setProject("项目1");
        *	raw1.setName("张三");
        *	...
        * 	sheet.add(raw1);
        */

        // 设置response参数
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode( date.substring(0,7)+ "月数据", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 生成合并策略
        List<OnceAbsoluteMergeStrategy> mergeStrategyList = new ArrayList<>(); // 合并策略
        int position = 0; // 需合并列（project）下标

        int index = 0; // 遍历下标
        int curStartRowNum = 1; // 开始合并行
        int rowNum = 0; // 合并行数
        String tempProject = null; // 临时项目名
        boolean curMerge = false; // 当前行是否合并结束
        for (RespManHoursVO manHours : sheet) {
            if(index == 0){ // 第一次遍历
                tempProject = manHours.getProject();
            }else{
                if (tempProject.equals(manHours.getProject())){ // 当前项目名同上一行项目名相等
                    rowNum++;
                    if (index == sheet.size()-1){ // 最后一行
                        curMerge = true;
                }
                }else{
                    if(rowNum > 0){
                        curMerge = true; // 开始合并
                    }else {
                        curStartRowNum = index+1; // 无需合并，且更新开始合并行
                    }
                }
                tempProject = manHours.getProject(); // 更新临时项目名
            }

            // 以项目名为单位的合并单元格
            if (curMerge) {
                // OnceAbsoluteMergeStrategy参数：合并开始行，结束行，开始列，结束列
//                log.info("cc log======>合并策略坐标: "+"【"+curStartRowNum+","+(curStartRowNum + rowNum)+"】");
                OnceAbsoluteMergeStrategy strategyByName = new OnceAbsoluteMergeStrategy(curStartRowNum, curStartRowNum + rowNum, position, position);
                mergeStrategyList.add(strategyByName);
                curMerge = false; // 合并结束
                rowNum = 0 ;
                curStartRowNum = index+1;
            }
            index++;
        }

        // 生成excel
        ExcelWriterSheetBuilder builder = EasyExcel.write(response.getOutputStream(), RespManHoursVO.class).sheet("sheet1");
        if (CollectionUtils.isNotEmpty(mergeStrategyList)) { // 添加策略
            for (OnceAbsoluteMergeStrategy strategy : mergeStrategyList) {
                builder.registerWriteHandler(strategy);
            }
        }
        builder.doWrite(sheet); // 写入数据生成excel

    }
}

3、控制层

import com.meb.imis.fiscal.service.FileExportService;
import io.swagger.annotations.ApiOperation;
import lombok.SneakyThrows;
import lombok.extern.log4j.Log4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

/**
 * @author CC
 * @description 导出文件
 * @className FileExportController
 * @date 2022/6/21 10:02
 */
@Log4j
@RestController
@RequestMapping("export")
public class FileExportController {
    @Resource
    private FileExportService fileExportService;

    /**
     * 内部结算项目管理界面导出人员工时功能
     * @param date
     * @param response
     */
    @GetMapping("exportWorkTime")
    @ApiOperation("导出")
    @SneakyThrows
    public void exportWorkTime(@RequestParam(value = "date") String date, HttpServletResponse response){
        // 调用服务层生成excel
        fileExportService.exportWorkTime(date,response);
    }
}