相信大家在平时开发中一直会又遇到表格导出这种需求,但是有的时候会遇到各种奇葩需求,比如一个人有多个岗位,到处是要求个人信息合并,岗位信息不合并,这就需要我们不仅要理解业务需求,底层代码更是需要理解透彻
废话不多说,我们直接上代码看下实现的逻辑
public static <T> FileItem createWriteFileItemAndMergeRegion(List<T> list, String fileName, Class<T> targetClass) throws
IOException {
//需要合并的列
int[] mergeColumnIndex = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28};
//从第二行后开始合并
int mergeRowIndex = 1;
FileItemFactory factory = new DiskFileItemFactory(16, null);
FileItem item = factory.createItem(fileName, "application/vnd.ms-excel;charset=utf-8", true, fileName);
EasyExcel.write(item.getOutputStream(), targetClass)
.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumnIndex))
.sheet("Sheet1").doWrite(list);
return item;
}
最主要的是ExcelFillCellMergeStrategy这个类的实现
package com.itheima.listener;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
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 lombok.Data;
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.util.List;
/**
* @author: cbc
* @Date: 2023/09/26
* @Description: ExcelFillCellMergeStrategy
* @Version 1.0.0
*/
@Data
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
private int[] mergeColumnIndex;
private int mergeRowIndex;
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
* @param curColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//按照员工工号相同的数据进行合并
//因为我只用按照员工工号来合并就可以,如果只是和上一列进行比较,可以把下面的.getCell(0)方法入参改为curColIndex
Cell curCell = cell.getSheet().getRow(curRowIndex).getCell(0);
Object curData = curCell.getCellTypeEnum() == CellType.STRING ? curCell.getStringCellValue() : curCell.getNumericCellValue();
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 比较第一列的员工工号是否相同,相同则合并单元格
if (curData.equals(preData)) {
Sheet sheet = writeSheetHolder.getSheet();
//注意,这里拿到的合并单元格是这个sheet中所有合并过的单元格,数据量太大性能会很差,并且有可能会导不出来
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并,则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
}
最终实现的效果就是下面这样
个人信息被合并
不同岗位正常导出