吐槽：網(wǎng)絡(luò)上有很多人在貢獻(xiàn)自己的代碼昔穴，但是極其相似可以作為參考。如下：
（https://cloud.tencent.com/developer/article/1671316椭住，https://www.it610.com/article/1296421123914801152.htm 等等）
前言：網(wǎng)絡(luò)上easyexcel合并的文章都使用的是“CellWriteHandler”,其實(shí)還有“RowWriteHandler”。此片文章將為大家提供一個(gè)按照行來(lái)合并單元格的做法
1.按行合并的策略ExcelMergeRowByRowUtil
public class ExcelMergeRowByRowUtil implements RowWriteHandler {
private int mergeRowIndex;//從哪一行開(kāi)始合并
private int[] mergeColumnIndex;//excel合并的列
private int signNum;//合并的唯一標(biāo)識(shí)
private int total;//總行數(shù)
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) {
//當(dāng)前行
int curRowIndex = row.getRowNum();
//每一行的最大列數(shù)
short lastCellNum = row.getLastCellNum();
if(curRowIndex == 1){
//賦初值 第一行
firstRow = curRowIndex;
}
//開(kāi)始合并位置
if(curRowIndex > mergeRowIndex && !row.getCell(0).getStringCellValue().equals("")){
for (int i = 0; i < lastCellNum; i++) {
if(i == mergeColumnIndex[i]){
//當(dāng)前行號(hào) 當(dāng)前行對(duì)象 合并的標(biāo)識(shí)位
mergeWithPrevAnyRow(writeSheetHolder.getSheet(),curRowIndex,row,signNum);
break;//已經(jīng)進(jìn)入到合并單元格操作里面了字逗，執(zhí)行一次就行
}
}
}
}
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);
//判斷前一個(gè)和后一個(gè)相同 并且 標(biāo)識(shí)位相同
if (curEqualsPre){
lastRow = curRowIndex;
mergeCount++;
}
//excel過(guò)程中合并
if(!curEqualsPre && mergeCount>1){
mergeSheet(firstRow,lastRow,mergeColumnIndex,sheet);
mergeCount = 1;
}
//excel結(jié)尾處合并
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) //內(nèi)容行高
@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
* 當(dāng)采用模板上傳Excel且.needHead(false)設(shè)置了不生成標(biāo)題頭 @ColumnWidth(10)標(biāo)簽將無(wú)效京郑，根據(jù)模板頭的長(zhǎng)度來(lái)走
*/
@ColumnWidth(10)//單元格長(zhǎng)度
@ExcelProperty(value = "序號(hào)", index = 0)
private String order;
@ColumnWidth(20)//單元格長(zhǎng)度
@ExcelProperty(value = "文章標(biāo)題", index = 1)
private String title;
@ColumnWidth(15)//單元格長(zhǎng)度
@ExcelProperty(value = "單位", index = 2)
private String company;
@ColumnWidth(15)//單元格長(zhǎng)度
@ExcelProperty(value = "編號(hào)", index = 3)
private String documentCode;
@ColumnWidth(12)//單元格長(zhǎng)度
@ExcelProperty(value = "發(fā)文日期", index = 4)
private String publishDate;
@ColumnWidth(25)//單元格長(zhǎng)度
@ExcelProperty(value = "意見(jiàn)", index = 5)
private String idea;
@ColumnWidth(15)//單元格長(zhǎng)度
@ExcelProperty(value = "事件詳情事項(xiàng)", index = 6)
private String workDetails;
@ColumnWidth(15)//單元格長(zhǎng)度
@ExcelProperty(value = "截止時(shí)間", index = 7)
private String dueTime;
@ColumnWidth(20)//單元格長(zhǎng)度
@ExcelProperty(value = "負(fù)責(zé)人", index = 8)
private String name;
@ColumnWidth(15)//單元格長(zhǎng)度
@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;// 從那一行開(kāi)始合并
//合并單元格
// ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(mergeRowIndex,mergeColumeIndex);
ExcelMergeRowByRowUtil excelMergeRowByRowStrategy = new ExcelMergeRowByRowUtil(mergeRowIndex,mergeColumeIndex,mergeColumeIndex[0],documentExcelList.size());
//設(shè)置頭樣式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//設(shè)置內(nèi)容格式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
//2.excel轉(zhuǎn)outputStream
//拼裝excel字節(jié)數(shù)組outputStream
ByteArrayOutputStream out = new ByteArrayOutputStream();
EasyExcel.write(out, DocumentExcelModel.class)
// .withTemplate(in)
// .needHead(false)//是否需要excel自定義模板的標(biāo)題頭
.registerWriteHandler(horizontalCellStyleStrategy)
.registerWriteHandler(excelMergeRowByRowStrategy)
// .registerWriteHandler(excelFillCellMergeStrategy)//此處為網(wǎng)絡(luò)上按照每個(gè)單元格進(jìn)行合并
.sheet("sheet")
.doWrite(list);
}
ExcelMergeRowByRowUtil合并單元格的思路：
1.讀取每一行，如果上一行和當(dāng)前行唯一標(biāo)識(shí)位相同葫掉，mergeCount累加些举。當(dāng)上一行和當(dāng)前行唯一標(biāo)識(shí)位不相同的時(shí)候進(jìn)行合并單元格。
2.當(dāng)讀取到最后一行符合合并條件的時(shí)候進(jìn)行合并單元格俭厚。
ExcelMergeRowByRowUtil implements RowWriteHandler 與網(wǎng)上 ExcelMergeUtil implements CellWriteHandler的區(qū)別
CellWriteHandler實(shí)現(xiàn)的方法是按照cell為單元進(jìn)行處理的户魏，假如每一行有十列，則需要比對(duì)十次挪挤。
RowWriteHandler實(shí)現(xiàn)的方法是按照row為單位盡心處理的叼丑，粒度更粗一寫】该牛控制好操作excel速度會(huì)更快鸠信。
