easyExcel使用模版填充excel，合并单元格

一、最终效果

在这里插入图片描述

二、制作模版

1、制作填充模版
在这里插入图片描述
模版在代码中保存的位置

2、Controller

/*** 下载模板*/
@RequestMapping(value = "exportData")
public void exportData(KqKqb kqKqb,HttpServletResponse response, HttpServletRequest request) throws IOException {kqKqbService.exportData(kqKqb,response,request);
}

3、Service（获取数据的逻辑不重要，重点单元格合并）

public void exportData(KqKqb kqKqb,HttpServletResponse response, HttpServletRequest request) throws IOException {//获取填充数据KqKqb entity = dao.get(kqKqb);Integer ycqts = entity.getYcqts();//本月应出勤天数String officeName = entity.getOfficeName();//单位名称List<Map<String, Object>> mapLit = ListUtils.newArrayList();KqKqbZb zbSql = new KqKqbZb();zbSql.setMonth(entity.getMonth());zbSql.setOfficeCode(entity.getOfficeCode());List<KqKqbZb> zbList = zbDao.findList(zbSql);String year =  kqKqb.getMonth().split("-")[0];String month =  kqKqb.getMonth().split("-")[1];//list填充数据封装String empCode = "";Integer xh = 0;for (KqKqbZb kqKqbZb : zbList) {//工号不重复序列加号加自增1if(!empCode.equals(kqKqbZb.getEmpCode())){empCode = kqKqbZb.getEmpCode();xh++;}kqKqbZb.setXh(xh);mapLit.add(JSON.parseObject(JSON.toJSONString(kqKqbZb), Map.class));}//模版所在位置String templateName = "员工考勤表上传模板1.xlsx";String serverPath = request.getSession().getServletContext().getRealPath("/");String ftlPath = serverPath + "ftl\\kh\\";String templateFileName = ftlPath + templateName;//文件名封装String fileName = month + "月-" + officeName + "-员工考勤表";response.setHeader("Content-disposition", "attachment;filename=" + String.valueOf(URLEncoder.encode(fileName, "UTF-8")) + ".xlsx");// 设置文件头编码格式response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");// 设置类型//定义合并规则List<Integer> mergeColumnIndex = ListUtils.newArrayList(0, 1, 2,35,36,37,38,39,40);//第几列所在行开始合并ExcelMergeStrategy loopMergeStrategy = new ExcelMergeStrategy(4, 2, mergeColumnIndex); // 从第4行开始，每隔2行合并，mergeColumnIndex需要合并行所在的列//开始填充ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream());excelWriterBuilder.registerWriteHandler(loopMergeStrategy);excelWriterBuilder.autoCloseStream(true);ExcelWriter excelWriter = excelWriterBuilder.withTemplate(templateFileName).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();excelWriter.fill(mapLit, fillConfig, writeSheet);Map<String, Object> map = MapUtils.newHashMap();map.put("officeName", officeName);map.put("year", year);map.put("month", month);map.put("ycqts", ycqts);excelWriter.fill(map, writeSheet);excelWriter.finish();
}

核心：从第0、1、2等列和第4行开始，每两行合并单元格

//定义合并规则
List<Integer> mergeColumnIndex = ListUtils.newArrayList(0, 1, 2,35,36,37,38,39,40);//第几列所在行开始合并
ExcelMergeStrategy loopMergeStrategy = new ExcelMergeStrategy(4, 2, mergeColumnIndex); // 从第4行开始，每隔2行合并，mergeColumnIndex需要合并行所在的列

4、新建合并策略类

package com.jeesite.modules.util;import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/*** @Description EasyExcel 导出合并单元格*/
@Slf4j
@Data
public class ExcelMergeStrategy implements RowWriteHandler {/** 要合并的列 (下表也是从0开始)*/private List<Integer> mergeColumnIndex;/** 用第几行开始合并 ，默认为1，因为第0行是标题，EasyExcel 的默认也是*/private int mergeBeginRowIndex = 1;/*** Each row*/private int eachRow;private int columnExtend = 1;public ExcelMergeStrategy(int mergeBeginRowIndex, int eachRow, List<Integer> mergeColumnIndex) {this.mergeBeginRowIndex = mergeBeginRowIndex;this.eachRow = eachRow;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void afterRowDispose(RowWriteHandlerContext context) {if (context.getHead() || context.getRelativeRowIndex() == null) {return;}//当前行int curRowIndex = context.getRowIndex();//当前列if (curRowIndex > mergeBeginRowIndex) {if (context.getRelativeRowIndex() % eachRow == 0) {for (Integer columnIndex : mergeColumnIndex) {CellRangeAddress cellRangeAddress = new CellRangeAddress(context.getRowIndex(),context.getRowIndex() + eachRow - 1,columnIndex, columnIndex + columnExtend - 1);context.getWriteSheetHolder().getSheet().addMergedRegionUnsafe(cellRangeAddress);}}}}
}