目录

1 Maven配置文件

2MergeCellModel

3CustomMergeCellHandler

4 调试代码

5 调试结果

注：

1 Maven配置文件

<!--hutool工具包--><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.5.1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.8</version></dependency>

2MergeCellModel

合并单元格信息。

package mon.easyexcel.model;import lombok.Getter;/*** 合并单元格信息** @author xudongmaster*/@Getterpublic class MergeCellModel {/*** sheet名称*/private String sheetName;/*** 开始行号*/private int startRowIndex;/*** 开始列号*/private int startColumnIndex;/*** 结束行号*/private int endRowIndex;/*** 结束列号*/private int endColumnIndex;private void setSheetName(String sheetName) {this.sheetName = sheetName;}private void setStartRowIndex(int startRowIndex) {this.startRowIndex = startRowIndex;}private void setStartColumnIndex(int startColumnIndex) {this.startColumnIndex = startColumnIndex;}private void setEndRowIndex(int endRowIndex) {this.endRowIndex = endRowIndex;}private void setEndColumnIndex(int endColumnIndex) {this.endColumnIndex = endColumnIndex;}private MergeCellModel() {}/*** 生成合并列单元格信息** @param sheetName sheet页名称* @param rowIndex 行号* @param startColumnIndex 开始列号* @param endColumnIndex 结束列号* @return*/public static MergeCellModel createMergeColumnCellModel(String sheetName, int rowIndex, int startColumnIndex, int endColumnIndex) {return createMergeCellModel(sheetName, rowIndex, rowIndex, startColumnIndex, endColumnIndex);}/*** 生成合并单元格信息** @param sheetNamesheet页名称* @param startRowIndex 开始行号* @param endRowIndex 结束行号* @param columnIndex 列号* @return*/public static MergeCellModel createMergeRowCellModel(String sheetName, int startRowIndex, int endRowIndex, int columnIndex) {return createMergeCellModel(sheetName, startRowIndex, endRowIndex, columnIndex, columnIndex);}/*** 生成合并单元格信息** @param sheetName sheet页名称* @param startRowIndex 开始行号* @param endRowIndex结束行号* @param startColumnIndex 开始列号* @param endColumnIndex 结束列号* @return*/public static MergeCellModel createMergeCellModel(String sheetName, int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) {MergeCellModel mergeCellModel = new MergeCellModel();//sheet页名称mergeCellModel.setSheetName(sheetName);//开始行号mergeCellModel.setStartRowIndex(startRowIndex);//结束行号mergeCellModel.setEndRowIndex(endRowIndex);//开始列号mergeCellModel.setStartColumnIndex(startColumnIndex);//结束列号mergeCellModel.setEndColumnIndex(endColumnIndex);return mergeCellModel;}}

3CustomMergeCellHandler

自定义合并单元格处理器。

package mon.easyexcel.handler;import cn.hutool.core.collection.CollUtil;import cn.hutool.core.util.StrUtil;import com.alibaba.excel.write.handler.SheetWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;import mon.easyexcel.model.MergeCellModel;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;import java.util.stream.Collectors;/*** 自定义合并单元格处理器* 每次合并需要sheet页名称、指定开始行号、开始列号、结束行号、结束列号* 支持批量合并单元格** @author xudongmaster*/public class CustomMergeCellHandler implements SheetWriteHandler {/*** 合并单元格信息*/private List<MergeCellModel> mergeCellList;/*** sheet页名称列表*/private List<String> sheetNameList;public CustomMergeCellHandler(List<MergeCellModel> mergeCellList) {if (CollUtil.isEmpty(mergeCellList)) {return;}this.mergeCellList = mergeCellList.stream().filter(x ->StrUtil.isNotBlank(x.getSheetName()) && x.getStartRowIndex() >= 0 && x.getEndRowIndex() >= 0&& x.getStartColumnIndex() >= 0 && x.getEndColumnIndex() >= 0).collect(Collectors.toList());List<String> sheetNameList = this.mergeCellList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());if (CollUtil.isEmpty(sheetNameList)) {return;}this.sheetNameList = sheetNameList;}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}/*** sheet页创建之后调用** @param writeWorkbookHolder* @param writeSheetHolder*/@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Sheet sheet = writeSheetHolder.getSheet();//不需要合并单元格信息，或者当前sheet页不需要合并单元格信息if (CollUtil.isEmpty(mergeCellList) || sheetNameList.contains(sheet.getSheetName()) == false) {return;}List<MergeCellModel> sheetMergeCellList = mergeCellList.stream().filter(x ->StrUtil.equals(x.getSheetName(), sheet.getSheetName())).collect(Collectors.toList());for (MergeCellModel mergeCellModel : sheetMergeCellList) {//开始行号int startRowIndex = mergeCellModel.getStartRowIndex();//结束行号int endRowIndex = mergeCellModel.getEndRowIndex();//开始列号int startColumnIndex = mergeCellModel.getStartColumnIndex();//结束列号int endColumnIndex = mergeCellModel.getEndColumnIndex();//行号和列号非法(<0)if (startColumnIndex < 0 || endColumnIndex < 0 || startRowIndex < 0 || endRowIndex < 0) {continue;}//合并单元格区域只有一个单元格时，不合并if (endRowIndex == startRowIndex && endColumnIndex == startColumnIndex) {continue;}//开始行号大于结束行号，或者开始列号大于结束列号if (startColumnIndex > endColumnIndex || startRowIndex > endRowIndex) {continue;}//添加合并单元格区域CellRangeAddress cellRangeAddress = new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex);sheet.addMergedRegionUnsafe(cellRangeAddress);}//删除合并单元格信息mergeCellList.removeAll(sheetMergeCellList);sheetNameList = mergeCellList.stream().map(x -> x.getSheetName()).distinct().collect(Collectors.toList());}}

4 调试代码

/*** 测试合并单元格*/@Testpublic void testMergeCell(){try {File file = new File("D:/easyexcel/testMergeCell.xlsx");FileUtil.createNewFile(file);//生成表格数据List<List<Object>> dataList = new ArrayList<>();dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));//生成合并单元格信息List<MergeCellModel> mergeCellList = new ArrayList<>();String sheetName="模板";mergeCellList.add(MergeCellModel.createMergeCellModel(sheetName, 0, 1 , 0, 1));mergeCellList.add(MergeCellModel.createMergeColumnCellModel(sheetName, 0 , 2, 3));mergeCellList.add(MergeCellModel.createMergeRowCellModel(sheetName, 1 ,2, 2));FileOutputStream fileOutputStream = new FileOutputStream(file);ExcelWriter excelWriter = EasyExcel.write(fileOutputStream).registerWriteHandler(new CustomMergeCellHandler(mergeCellList)).build();WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();excelWriter.write(dataList, writeSheet);// 千万别忘记finish 会帮忙关闭流excelWriter.finish();} catch (Exception e) {e.printStackTrace();}}

5 调试结果

注：

如果需要源码请前往Gitee查看。

旭东怪/xudongbase/xudong_master/xudongbase