需求
EasyExcel默认情况下写Excel是一行一行的,单元格不会自动合并,现在需求是合并这几列中相同的数据
思考
开始自己也不想做过多改动代码,经过阅读EasyExce官方文档和大量博客之后总结了一个通用的工具类,重写接口中的merge方法,然后自己在写入Excel的时候指定实体类属性以及对应的列就能实现功能
实现
/**
* @author William.Shi
* @date 2022/12/14
* @description 自定义单元格合并策略
*/
public class CustomMergeStrategy extends AbstractMergeStrategy {
/**
* 分组,每几行合并一次
*/
private List<Integer> exportFieldGroupCountList;
/**
* 目标合并列index
*/
private Integer targetColumnIndex;
// 需要开始合并单元格的首行index
private Integer rowIndex;
// exportDataList为待合并目标列的值
public CustomMergeStrategy(List<String> exportDataList, Integer targetColumnIndex) {
this.exportFieldGroupCountList = getGroupCountList(exportDataList);
this.targetColumnIndex = targetColumnIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (null == rowIndex) {
rowIndex = cell.getRowIndex();
}
// 仅从首行以及目标列的单元格开始合并,忽略其他
if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {
mergeGroupColumn(sheet);
}
}
private void mergeGroupColumn(Sheet sheet) {
int rowCount = rowIndex;
for (Integer count : exportFieldGroupCountList) {
if (count == 1) {
rowCount += count;
continue;
}
// 合并单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
rowCount += count;
}
}
// 该方法将目标列根据值是否相同连续可合并,存储可合并的行数
private List<Integer> getGroupCountList(List<String> exportDataList) {
if (CollectionUtils.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < exportDataList.size(); i++) {
if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
return groupCountList;
}
}
这得益于EasyExcel中的这个接口文章来源:https://www.toymoban.com/news/detail-609369.html
public abstract class AbstractMergeStrategy implements CellWriteHandler {
@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 afterCellDataConverted(WriteSheetHolder writeSheetHolder,
WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex,
Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
return;
}
merge(writeSheetHolder.getSheet(), cell, head, relativeRowIndex);
}
/**
* merge
*
* @param sheet
* @param cell
* @param head
* @param relativeRowIndex
*/
protected abstract void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex);
}
我通过重写merge方法之后能实现这个需求,对应写Excel代码如下文章来源地址https://www.toymoban.com/news/detail-609369.html
ExcelWriter excelWriter = EasyExcel.write(file).useDefaultStyle(false).build();
WriteSheet sheet = EasyExcel.writerSheet("日报").head(AEDailyReport.class)
.registerWriteHandler(new CustomMergeStrategy(AEDailyReportList.stream().map(AEDailyReport::getRegion)
.collect(Collectors.toList()), 1))
.registerWriteHandler(new CustomMergeStrategy(AEDailyReportList.stream().map(i -> String.valueOf(i.getBudget()))
.collect(Collectors.toList()),4))
.registerWriteHandler(new CustomMergeStrategy(AEDailyReportList.stream().map(i -> String.valueOf(i.getRemainingBudget()))
.collect(Collectors.toList()),9)).build();
excelWriter.write(AEDailyReportList, sheet).finish();
到了这里,关于EasyExcel合并单元格的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!