吐槽：网络上有很多人在贡献自己的代码，但是极其相似可以作为参考。如下：
（https://cloud.tencent.com/developer/article/1671316，https://www.it610.com/article/1296421123914801152.htm 等等）
前言：网络上easyexcel合并的文章都使用的是“CellWriteHandler”,其实还有“RowWriteHandler”。此片文章将为大家提供一个按照行来合并单元格的做法
1.按行合并的策略ExcelMergeRowByRowUtil
public class ExcelMergeRowByRowUtil implements RowWriteHandler {
private int mergeRowIndex;//从哪一行开始合并
private int[] mergeColumnIndex;//excel合并的列
private int signNum;//合并的唯一标识
private int total;//总行数
private int lastRow;
private int firstCol;
private int lastCol;
private int firstRow;
private int mergeCount =1;
public ExcelMergeRowByRowUtil(int mergeRowIndex, int[] mergeColumnIndex, int signNum, int total) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
this.signNum = signNum;
this.total = total;
}
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
//当前行
int curRowIndex = row.getRowNum();
//每一行的最大列数
short lastCellNum = row.getLastCellNum();
if(curRowIndex == 1){
//赋初值 第一行
firstRow = curRowIndex;
}
//开始合并位置
if(curRowIndex > mergeRowIndex && !row.getCell(0).getStringCellValue().equals("")){
for (int i = 0; i < lastCellNum; i++) {
if(i == mergeColumnIndex[i]){
//当前行号 当前行对象 合并的标识位
mergeWithPrevAnyRow(writeSheetHolder.getSheet(),curRowIndex,row,signNum);
break;//已经进入到合并单元格操作里面了，执行一次就行
}
}
}
}
public void mergeWithPrevAnyRow(Sheet sheet,int curRowIndex,Row row,int signNum){
Object currentData = row.getCell(signNum).getCellTypeEnum() == CellType.STRING ? row.getCell(signNum).getStringCellValue() : row.getCell(signNum).getNumericCellValue();
Row preRow = row.getSheet().getRow(curRowIndex - 1);
Object preData = preRow.getCell(signNum).getCellTypeEnum() == CellType.STRING ? preRow.getCell(signNum).getStringCellValue() : preRow.getCell(signNum).getNumericCellValue();
//判断是否合并单元格
boolean curEqualsPre = currentData.equals(preData);
//判断前一个和后一个相同 并且 标识位相同
if (curEqualsPre){
lastRow = curRowIndex;
mergeCount++;
}
//excel过程中合并
if(!curEqualsPre && mergeCount>1){
mergeSheet(firstRow,lastRow,mergeColumnIndex,sheet);
mergeCount = 1;
}
//excel结尾处合并
if (mergeCount>1 && total==curRowIndex){
mergeSheet(firstRow,lastRow,mergeColumnIndex,sheet);
mergeCount = 1;
}
if (!curEqualsPre){
firstRow = curRowIndex;
}
}
private void mergeSheet(int firstRow,int lastRow,int[] mergeColumnIndex,Sheet sheet){
for (int colNum : mergeColumnIndex){
firstCol = colNum;
lastCol = colNum;
CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow,lastRow,firstCol,lastCol);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
2.easyexcel组装的model
@Getter
@Setter
@ContentRowHeight(15) //内容行高
@HeadRowHeight(20)//表头行高
public class ExcelModel {
/**oss模板名称*/
public static final String RESEXCELNAME = "document.xlsx";
public static final String TEMPLATEEXCELNAME = "文章管理";
public static final String SUFFIX = ".xlsx";
/**
* notice
* 当采用模板上传Excel且.needHead(false)设置了不生成标题头 @ColumnWidth(10)标签将无效，根据模板头的长度来走
*/
@ColumnWidth(10)//单元格长度
@ExcelProperty(value = "序号", index = 0)
private String order;
@ColumnWidth(20)//单元格长度
@ExcelProperty(value = "文章标题", index = 1)
private String title;
@ColumnWidth(15)//单元格长度
@ExcelProperty(value = "单位", index = 2)
private String company;
@ColumnWidth(15)//单元格长度
@ExcelProperty(value = "编号", index = 3)
private String documentCode;
@ColumnWidth(12)//单元格长度
@ExcelProperty(value = "发文日期", index = 4)
private String publishDate;
@ColumnWidth(25)//单元格长度
@ExcelProperty(value = "意见", index = 5)
private String idea;
@ColumnWidth(15)//单元格长度
@ExcelProperty(value = "事件详情事项", index = 6)
private String workDetails;
@ColumnWidth(15)//单元格长度
@ExcelProperty(value = "截止时间", index = 7)
private String dueTime;
@ColumnWidth(20)//单元格长度
@ExcelProperty(value = "负责人", index = 8)
private String name;
@ColumnWidth(15)//单元格长度
@ExcelProperty(value = "备注", index = 9)
private String remarks;
3.list组装excel并合并单元格
public void getEasyExcel(List<ExcelModel> list){
int[] mergeColumeIndex = {0,1,2,3,4,5};//需要合并的列
int mergeRowIndex = 1;// 从那一行开始合并
//合并单元格
// ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex,mergeColumeIndex);
ExcelMergeRowByRowUtil excelMergeRowByRowStrategy = new ExcelMergeRowByRowUtil(mergeRowIndex,mergeColumeIndex,mergeColumeIndex[0],documentExcelList.size());
//设置头样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置内容格式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
//2.excel转outputStream
//拼装excel字节数组outputStream
ByteArrayOutputStream out = new ByteArrayOutputStream();
EasyExcel.write(out, DocumentExcelModel.class)
// .withTemplate(in)
// .needHead(false)//是否需要excel自定义模板的标题头
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(excelMergeRowByRowStrategy)
// .registerWriteHandler(excelFillCellMergeStrategy)//此处为网络上按照每个单元格进行合并
.sheet("sheet")
.doWrite(list);
}
ExcelMergeRowByRowUtil合并单元格的思路：
1.读取每一行，如果上一行和当前行唯一标识位相同，mergeCount累加。当上一行和当前行唯一标识位不相同的时候进行合并单元格。
2.当读取到最后一行符合合并条件的时候进行合并单元格。
ExcelMergeRowByRowUtil implements RowWriteHandler 与网上 ExcelMergeUtil implements CellWriteHandler的区别
CellWriteHandler实现的方法是按照cell为单元进行处理的，假如每一行有十列，则需要比对十次。
RowWriteHandler实现的方法是按照row为单位尽心处理的，粒度更粗一写。控制好操作excel速度会更快。
网友评论