第一种方式实现
通过定义注解+实现RowWriteHandler接口中的afterRowDispose方法来动态合并行根据指定的key可以是单个字段也可以是多个字段也可以根据注解指定。注解方式使用参考原作者。
1 自定义注解
package com.test.utils;
import java.lang.annotation.*;
/**
* 自定义注解，用于判断是否需要合并以及合并的主键
*
*/
@Documented
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CustomMerge {
/**
* 是否是主键,即该字段相同的行合并
*/
boolean isPk() default false;
/**
* 需要合并单元格字段标识、标识一致的会分为同一组
*/
String[] value();
}
2 策略类
package com.test.utils;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;
/**
* 自定义单元格合并策略
*
*/
public class CustomMergeStrategy implements RowWriteHandler {
/**
* 主键下标集合
*/
private Map<String, List> pkColumnIndexMap = new LinkedHashMap<>();
/**
* 需要合并的列的下标集合
*/
private Map<String, List> mergeValueColumnIndexMap = new LinkedHashMap<>();
/**
* 需要合并的列
*/
private final Map cellRangeAddressMap = new HashMap<>();
/**
* 去重后的主键下标集合
*/
private final Set pkColumnIndexSet = new HashSet<>();
/**
* sheet数据总行数
*/
private final int totalDataRowCount;
/**
* DTO数据类型
*/
private final Class elementType;
// 增加自定义合并key如果自定义合并key为空按照原有策略走
private int[] mergeIndexs;
public CustomMergeStrategy(Class elementType, int totalDataRowCount, int[] mergeIndexs) {
this.elementType = elementType;
this.totalDataRowCount = totalDataRowCount;
this.mergeIndexs = mergeIndexs;
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
// 如果是标题，则直接返回
if (isHead) {
return;
}
// 获取当前sheet
Sheet sheet = writeSheetHolder.getSheet();
if (pkColumnIndexMap.isEmpty()) {
this.lazyInit();
}
// 判断是否需要和上一行进行合并，不能和标题合并，只能数据行之间合并
if (row.getRowNum() <= 1 return row lastrow='sheet.getRow(row.getRowNum()' - 1 pkcolumnindexmap.foreachk v -> {
// 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
boolean mergeBol = true;
for (Integer pkIndex : v) {
if (lastRow.getCell(pkIndex) == null || row.getCell(pkIndex) == null) {
continue;
}
String lastKey = null;
String currentKey = null;
if (ObjectUtils.isEmpty(mergeIndexs)) {
lastKey = getStringCellValue(lastRow.getCell(pkIndex));
currentKey = getStringCellValue(row.getCell(pkIndex));
} else {
lastKey = Arrays.stream(mergeIndexs).mapToObj(index -> getStringCellValue(lastRow.getCell(index))).collect(Collectors.joining());
currentKey = Arrays.stream(mergeIndexs).mapToObj(index -> getStringCellValue(row.getCell(index))).collect(Collectors.joining());
}
if (!StringUtils.equalsIgnoreCase(lastKey, currentKey)) {
mergeBol = false;
break;
}
}
List mergeValueColumnIndex = mergeValueColumnIndexMap.get(k);
if (mergeBol) {
// 如果需要合并，则处理合并逻辑
for (Integer needMerIndex : mergeValueColumnIndex) {
String key = String.format("%s-%s", k, needMerIndex);
// 处理已存在合并范围的情况，避免重复合并
if (pkColumnIndexSet.contains(needMerIndex)) {
long count = cellRangeAddressMap.entrySet().stream().filter(entry -> entry.getKey().contains("-" + needMerIndex)
&& !entry.getKey().contains(key)).count();
if (count > 0) {
continue;
}
}
// 创建或更新合并范围对象
CellRangeAddress cellAddresses = cellRangeAddressMap.get(key);
CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
needMerIndex, needMerIndex);
if (cellAddresses == null) {
cellRangeAddressMap.put(key, cellRangeAddress);
} else {
cellRangeAddress.setFirstRow(cellAddresses.getFirstRow());
cellRangeAddressMap.put(key, cellRangeAddress);
}
// 如果是最后一行，则将合并范围添加到sheet中
if (row.getRowNum() == totalDataRowCount) {
sheet.addMergedRegionUnsafe(cellRangeAddressMap.get(key));
}
}
} else {
// 如果不需要合并，则移除相应的合并范围对象
for (Integer needMerIndex : mergeValueColumnIndex) {
String key = String.format("%s-%s", k, needMerIndex);
CellRangeAddress cellAddresses = cellRangeAddressMap.get(key);
if (cellAddresses != null) {
sheet.addMergedRegionUnsafe(cellRangeAddressMap.get(key));
cellRangeAddressMap.remove(key);
}
}
}
});
}
/**
* 获取单元格的字符串内容
*
* @param cell 单元格对象
* @return 单元格的字符串内容
*/
private String getStringCellValue(Cell cell) {
if (cell.getCellType() == CellType.STRING) {
return cell.getStringCellValue();
} else {
return String.valueOf(cell.getNumericCellValue());
}
}
/**
* 初始化主键下标和需要合并字段的下标
*/
private void lazyInit() {
// 获取DTO所有的属性
Field[] fields = this.elementType.getDeclaredFields();
boolean pkInitialized = false;
// 遍历所有的字段
for (Field theField : fields) {
// 获取@ExcelProperty注解
ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
// 为空,则表示该字段不需要导入到excel,直接处理下一个字段
if (null == easyExcelAnno) {
continue;
}
// 获取自定义的注解
CustomMerge customMerge = theField.getAnnotation(CustomMerge.class);
// 没有@CustomMerge注解的默认不合并
if (null == customMerge) {
continue;
}
String[] pks = customMerge.value();
// 判断是否有主键标识
if (customMerge.isPk()) {
if (ObjectUtils.isEmpty(pks)) {
throw new IllegalStateException(String.format("字段[%s]使用@CustomMerge注解isPk为true但未指定合并值", theField.getName()));
}
for (String pk : pks) {
List pkColumnIndex = pkColumnIndexMap.get(pk);
if (pkColumnIndex == null) {
pkColumnIndex = new ArrayList<>();
}
pkColumnIndex.add(easyExcelAnno.index());
pkColumnIndexMap.put(pk, pkColumnIndex);
pkInitialized = true; // 标记主键已初始化
}
if (pks.length > 0) {
pkColumnIndexSet.add(easyExcelAnno.index());
}
}
// 判断是否需要合并
if (ObjectUtils.isNotEmpty(pks)) {
for (String pk : pks) {
List needMergeColumnIndex = mergeValueColumnIndexMap.get(pk);
if (needMergeColumnIndex == null) {
needMergeColumnIndex = new ArrayList<>();
}
needMergeColumnIndex.add(easyExcelAnno.index());
mergeValueColumnIndexMap.put(pk, needMergeColumnIndex);
}
}
}
// 没有指定主键，则异常
if (!pkInitialized) {
throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
}
}
} 3 使用方式
WriteSheet sheet2 = EasyExcel.writerSheet("卡明细补助").registerWriteHandler(new CustomMergeStrategy(LdYcTrafficAllowanceExportDetailVo.class, detailVos.size(), new int[]{2, 12, 15}))
.head(LdYcTrafficAllowanceExportDetailVo.class)
.build();4 实体类
package com.test.domain.vo;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.yc.utils.CustomMerge;
import com.yfld.common.core.annotation.Excel;
import lombok.Data;
import java.util.Date;
/**
* 公共交通补助明细列表
*/
@Data
public class TrafficAllowanceExportDetailVo {
...
//
/** 打卡日期 */
@JsonFormat(pattern = "yyyy-MM-dd")
@Excel(name = "打卡日期", width = 30, dateFormat = "yyyy-MM-dd")
@ExcelProperty(value = "打卡日期", index = 15)
@CustomMerge(value = {"serverRoomCode", "nickName", "checkInDate"}, isPk = true)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
private String checkInDate;
/** 交通补助（元） */
@Excel(name = "交通补助")
@ExcelProperty(value = "交通补助", index = 16)
@CustomMerge(value = {"serverRoomCode", "nickName", "checkInDate"}, isPk = true)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
private String transportAllowance;
}
第二种实现方式
通过定义注解+实现CellWriteHandler接口中的afterRowDispose根据指定的字段索引去做自定义key传入的数据需要做成map结构方便后续合并数据。
package com.test.utils.excel;
import cn.hutool.core.collection.CollectionUtil;
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 com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.yc.domain.vo.LdYcTrafficAllowanceExportDetailVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.*;
public class TrafficAllowanceCellWriteHandler implements CellWriteHandler {
private final Map<String, List> data;
private final Map groupRowMap; // 记录每个分组的起始行
private final WriteCellStyle centerStyle; // 居中样式
private final List groupKeyIndex;
public TrafficAllowanceCellWriteHandler(Map<String, List> data, List groupKeyIndex) {
this.data = data;
this.groupRowMap = new HashMap<>();
this.centerStyle = createCenterStyle();
this.groupKeyIndex = groupKeyIndex;
}
/**
* 创建居中样式
*/
private WriteCellStyle createCenterStyle() {
WriteCellStyle style = new WriteCellStyle();
style.setHorizontalAlignment(HorizontalAlignment.CENTER); // 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
return style;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<WriteCellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead || cell.getRowIndex() == 0) {
return;
}
Sheet sheet = writeSheetHolder.getSheet();
int currentRowIndex = cell.getRow().getRowNum();
String key = getRowKey(cell.getRow());
// 遍历分组后的数据
// for (Map.Entry<String, List> entry : data.entrySet()) {
//// String key = entry.getKey();
// // 移动到下一个分组的起始行
// }
List orders = data.get(key);
if (CollectionUtil.isNotEmpty(orders) && orders.size() > 1) {
// 如果当前行是该分组的起始行
if (!groupRowMap.containsKey(key)) {
groupRowMap.put(key, currentRowIndex); // 记录分组的起始行
if (orders.size() > 1) {
int lastRow = currentRowIndex + orders.size() - 1;
// 合并用户名和用户编码列
CellRangeAddress checkInDateRange = new CellRangeAddress(currentRowIndex, lastRow, 15, 15); // 用户名列
CellRangeAddress priceRange = new CellRangeAddress(currentRowIndex, lastRow, 16, 16); // 用户编码列
sheet.addMergedRegion(checkInDateRange);
sheet.addMergedRegion(priceRange);
// 设置合并区域的样式
setMergedRegionStyle(sheet, checkInDateRange, centerStyle);
setMergedRegionStyle(sheet, priceRange, centerStyle);
}
// currentRowIndex += orders.size();
}
}
}
/**
* 设置合并区域的样式
*/
private void setMergedRegionStyle(Sheet sheet, CellRangeAddress range, WriteCellStyle writeCellStyle) {
Workbook workbook = sheet.getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
// 设置水平对齐方式
if (writeCellStyle.getHorizontalAlignment() != null) {
cellStyle.setAlignment(writeCellStyle.getHorizontalAlignment());
}
// 设置垂直对齐方式
if (writeCellStyle.getVerticalAlignment() != null) {
cellStyle.setVerticalAlignment(writeCellStyle.getVerticalAlignment());
}
// 设置字体
if (writeCellStyle.getWriteFont() != null) {
Font font = workbook.createFont();
WriteFont writeFont = writeCellStyle.getWriteFont();
if (writeFont.getFontName() != null) {
font.setFontName(writeFont.getFontName());
}
if (writeFont.getFontHeightInPoints() != null) {
font.setFontHeightInPoints(writeFont.getFontHeightInPoints());
}
if (writeFont.getBold() != null) {
font.setBold(writeFont.getBold());
}
cellStyle.setFont(font);
}
// 设置背景颜色
if (writeCellStyle.getFillForegroundColor() != null) {
cellStyle.setFillForegroundColor(writeCellStyle.getFillForegroundColor());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
// 设置边框
if (writeCellStyle.getBorderLeft() != null) {
cellStyle.setBorderLeft(writeCellStyle.getBorderLeft());
}
if (writeCellStyle.getBorderRight() != null) {
cellStyle.setBorderRight(writeCellStyle.getBorderRight());
}
if (writeCellStyle.getBorderTop() != null) {
cellStyle.setBorderTop(writeCellStyle.getBorderTop());
}
if (writeCellStyle.getBorderBottom() != null) {
cellStyle.setBorderBottom(writeCellStyle.getBorderBottom());
}
// 应用样式到合并区域的所有单元格
for (int row = range.getFirstRow(); row <= range.getLastRow(); row++) {
Row sheetRow = sheet.getRow(row);
if (sheetRow == null) {
sheetRow = sheet.createRow(row);
}
for (int col = range.getFirstColumn(); col <= range.getLastColumn(); col++) {
Cell cell = sheetRow.getCell(col);
if (cell == null) {
cell = sheetRow.createCell(col);
}
cell.setCellStyle(cellStyle);
}
}
}
private String getRowKey(Row row) {
StringBuilder key = new StringBuilder();
for (int i : groupKeyIndex) {
Cell cell = row.getCell(i);
if (cell != null) {
key.append(cell.getStringCellValue());
}
}
return key.toString();
// Cell roomCodeCell = row.getCell(12);
// if (roomCodeCell != null) {
// key.append(roomCodeCell.getStringCellValue());
// }
// Cell nickNameCell = row.getCell(2);
// if (nickNameCell != null)
// key.append(nickNameCell.getStringCellValue());
// Cell chenkInDateCell = row.getCell(15);
// if (chenkInDateCell != null)
// key.append(chenkInDateCell.getStringCellValue());
// return key.toString();
}
} 第二种实现方式使用方法
// 创建并注册第二个Sheet（合并策略在此处处理）
Map<String, List> detailMap = detailVos.stream().collect(Collectors.groupingBy(exportData -> exportData.getServerRoomCode() + exportData.getNickName() + exportData.getCheckInDate()));
// 多个字段组合key标记excel中的位置index
List groupKeyIndex = Arrays.asList(12, 2, 15);
WriteSheet sheet2 = EasyExcel.writerSheet("项目打卡明细补助")
.head(LdYcTrafficAllowanceExportDetailVo.class)
.registerWriteHandler(new TrafficAllowanceCellWriteHandler(detailMap, groupKeyIndex))
.build(); 第一种实现方式原文转载自这里在此基础上进行了调整，增加了多个字段组合key的实现方式通过参数产地
EasyExcel合并单元格，通过注解方式实现自定义合并策略，RowWriteHandler高效率实现-CSDN博客