EasyExcel合并单元格
需求
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中的这个接口
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代码如下
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();
这篇好文章是转载于:学新通技术网
- 版权申明: 本站部分内容来自互联网,仅供学习及演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,请提供相关证据及您的身份证明,我们将在收到邮件后48小时内删除。
- 本站站名: 学新通技术网
- 本文地址: /boutique/detail/tanhgcabjj
系列文章
更多
同类精品
更多
-
photoshop保存的图片太大微信发不了怎么办
PHP中文网 06-15 -
《学习通》视频自动暂停处理方法
HelloWorld317 07-05 -
Android 11 保存文件到外部存储,并分享文件
Luke 10-12 -
word里面弄一个表格后上面的标题会跑到下面怎么办
PHP中文网 06-20 -
photoshop扩展功能面板显示灰色怎么办
PHP中文网 06-14 -
微信公众号没有声音提示怎么办
PHP中文网 03-31 -
excel下划线不显示怎么办
PHP中文网 06-23 -
excel打印预览压线压字怎么办
PHP中文网 06-22 -
怎样阻止微信小程序自动打开
PHP中文网 06-13 -
TikTok加速器哪个好免费的TK加速器推荐
TK小达人 10-01