需求描述
实现导出Excel，一行对应多行，单元格合并，导出的格式如下：
使用注解实现，提高使用便捷度方便复用。
实现方案
自定义注解，在对应的列上增加注解标识该列需要合并
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelMerge {
}
实现合并策略
public class ExcelMergeStrategy extends AbstractMergeStrategy {
/**
* 主键列下标
*/
private Integer pkIndex;
/**
* 需要合并的列的下标集合
*/
private final Set<Integer> mergeColumnIndexSet;
/**
* 数据类型
*/
private final Class<?> elementType;
public ExcelMergeStrategy(Class<?> elementType) {
this.elementType = elementType;
this.mergeColumnIndexSet = new HashSet<>();
init();
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
int currColIndex = cell.getColumnIndex();
if (!mergeColumnIndexSet.contains(currColIndex)) {
return;
}
int currRowIndex = cell.getRowIndex();
Row currRow = cell.getRow();
Row prevRow = sheet.getRow(currRowIndex - 1);
// 比较主键列，只有主键列单元格值相同时才会合并
if (isCellEquals(currRow.getCell(pkIndex), prevRow.getCell(pkIndex))) {
// 比较当前列，只有当前单元格和上一行的单元格值相同时才合并
if (isCellEquals(cell, prevRow.getCell(currColIndex))) {
mergeWithPrevRow(sheet, currRowIndex, currColIndex);
}
}
}
/**
* 合并单元格
*/
private void mergeWithPrevRow(Sheet sheet, int currRowIndex, int currColIndex) {
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
// 检查上一个单元格是否是已合并单元格
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size(); i++) {
CellRangeAddress address = mergeRegions.get(i);
// 若上一个单元格已经被合并，则修改合并单元，将当前行也加入进去。
if (address.isInRange(currRowIndex - 1, currColIndex)) {
sheet.removeMergedRegion(i);
address.setLastRow(currRowIndex);
sheet.addMergedRegion(address);
isMerged = true;
break;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
sheet.addMergedRegion(new CellRangeAddress(currRowIndex - 1, currRowIndex, currColIndex, currColIndex));
}
}
/**
* 比较两个单元格是否相等
*/
private boolean isCellEquals(Cell cell1, Cell cell2) {
String cell1Value = getCellValue(cell1);
String cell2Value = getCellValue(cell2);
return cell1Value != null && cell1Value.equals(cell2Value);
}
/**
* 获取单元格的值
*/
private String getCellValue(Cell cell) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
return String.valueOf(cell.getNumericCellValue());
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
default:
return null;
}
}
/**
* 初始化，解析主键列和需要合并的列
*/
private void init() {
// 获取字段在导出时的顺序
Map<Integer, Field> exportSortedFieldMap = ExcelUtils.getExportSortedFieldMap(elementType);
exportSortedFieldMap.forEach((index, field) -> {
// 查找需要合并的列
ExcelMerge rowMergeAnno = field.getAnnotation(ExcelMerge.class);
if (null == rowMergeAnno) {
return;
}
// 主键取合并列的第一列
if (pkIndex == null || index < pkIndex) {
pkIndex = index;
}
mergeColumnIndexSet.add(index);
});
}
}
工具类
public class ExcelUtils {
/**
* 获取字段在导出时的顺序
* 参考EasyExcel源码中提取出来的
*/
public static Map<Integer, Field> getExportSortedFieldMap(Class<?> clazz) {
Map<Integer, List<Field>> orderFieldMap = new TreeMap<>();
Map<Integer, Field> indexFieldMap = new TreeMap<>();
ExcelIgnoreUnannotated excelIgnoreUnannotated = clazz.getAnnotation(ExcelIgnoreUnannotated.class);
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
ExcelIgnore excelIgnoreAnno = field.getAnnotation(ExcelIgnore.class);
if (excelIgnoreAnno != null) {
continue;
}
ExcelProperty excelPropertyAnno = field.getAnnotation(ExcelProperty.class);
boolean noExcelProperty = excelPropertyAnno == null && excelIgnoreUnannotated != null;
if (noExcelProperty) {
continue;
}
boolean isStaticFinalOrTransient =
(Modifier.isStatic(field.getModifiers()) && Modifier.isFinal(field.getModifiers()))
|| Modifier.isTransient(field.getModifiers());
if (excelPropertyAnno == null && isStaticFinalOrTransient) {
continue;
}
if (excelPropertyAnno != null && excelPropertyAnno.index() >= 0) {
indexFieldMap.put(excelPropertyAnno.index(), field);
continue;
}
int order = Integer.MAX_VALUE;
if (excelPropertyAnno != null) {
order = excelPropertyAnno.order();
}
List<Field> orderFieldList = orderFieldMap.computeIfAbsent(order, key -> ListUtils.newArrayList());
orderFieldList.add(field);
}
return buildSortedAllFieldMap(orderFieldMap, indexFieldMap);
}
private static Map<Integer, Field> buildSortedAllFieldMap(Map<Integer, List<Field>> orderFieldMap,
Map<Integer, Field> indexFieldMap) {
Map<Integer, Field> sortedAllFieldMap = new HashMap<>();
Map<Integer, Field> tempIndexFieldMap = new HashMap<>(indexFieldMap);
int index = 0;
for (List<Field> fieldList : orderFieldMap.values()) {
for (Field field : fieldList) {
while (tempIndexFieldMap.containsKey(index)) {
sortedAllFieldMap.put(index, tempIndexFieldMap.get(index));
tempIndexFieldMap.remove(index);
index++;
}
sortedAllFieldMap.put(index, field);
index++;
}
}
sortedAllFieldMap.putAll(tempIndexFieldMap);
return sortedAllFieldMap;
}
}
数据测试
实体类
@Data
@AllArgsConstructor
public static class DataVO {
@ExcelProperty("物品种类")
@ExcelMerge
private String category;
@ExcelProperty("物品名称")
@ExcelMerge
private String name;
@ExcelProperty("物品颜色")
private String color;
@ExcelProperty("物品产期")
@ColumnWidth(20)
private Date produceDate;
}
单元测试
@Test
public void mergeWriteTest() {
List<DataVO> datas = getDataList();
String fileName = "mergeWrite" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName, DataVO.class)
.useDefaultStyle(false)
.registerWriteHandler(new ExcelMergeStrategy(DataVO.class))
.sheet()
.doWrite(datas);
}
private List<DataVO> getDataList() {
List<DataVO> list = new ArrayList<>();
list.add(new DataVO("水果", "苹果", "红色", new Date()));
list.add(new DataVO("水果", "苹果", "绿色", new Date()));
list.add(new DataVO("水果", "香蕉", "黄色", new Date()));
list.add(new DataVO("水果", "香蕉", "青色", new Date()));
list.add(new DataVO("蔬菜", "菠菜", "绿色", new Date()));
list.add(new DataVO("蔬菜", "芹菜", "绿色", new Date()));
list.add(new DataVO("蔬菜", "西红柿", "绿色", new Date()));
list.add(new DataVO("蔬菜", "西红柿", "红色", new Date()));
return list;
}
