前言
记录使用EasyExcel导出Excel合并多行数据
一、话不多说上代码
代码如下:这是比较初始版本的、没做优化,如有大佬多多提点
package com.ffcs.oss.config;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
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;
public class ExcelFileCellsMerge implements CellWriteHandler {
/**
* 用第几行开始合并 ,默认为1,因为第0行是标题,EasyExcel 的默认也是
*/
private int mergeRowIndex = 1;
@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<CellData> list,
Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
// 前五列需要合并 所以需要读取到第五列
if (curColIndex == 5) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex);
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell 当前单元格
* @param curRowIndex 当前行
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据,通过上一行数据是否相同进行合并
Cell curCell = cell.getSheet().getRow(curRowIndex).getCell(0);
Cell curCell1 = cell.getSheet().getRow(curRowIndex).getCell(1);
Cell curCell2 = cell.getSheet().getRow(curRowIndex).getCell(2);
Cell curCell3 = cell.getSheet().getRow(curRowIndex).getCell(3);
Cell curCell4 = cell.getSheet().getRow(curRowIndex).getCell(4);
Cell curCell5 = cell.getSheet().getRow(curRowIndex).getCell(5);
Object curData = curCell.getCellTypeEnum() == CellType.STRING ? curCell.getStringCellValue() : curCell.getNumericCellValue();
Object curData1 = curCell1.getCellTypeEnum() == CellType.STRING ? curCell1.getStringCellValue() : curCell1.getNumericCellValue();
Object curData2 = curCell2.getCellTypeEnum() == CellType.STRING ? curCell2.getStringCellValue() : curCell2.getNumericCellValue();
Object curData3 = curCell3.getCellTypeEnum() == CellType.STRING ? curCell3.getStringCellValue() : curCell3.getNumericCellValue();
Object curData4 = curCell4.getCellTypeEnum() == CellType.STRING ? curCell4.getStringCellValue() : curCell4.getNumericCellValue();
Object curData5 = curCell5.getCellTypeEnum() == CellType.STRING ? curCell5.getStringCellValue() : curCell5.getNumericCellValue();
// 获取上一行数据
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
Cell preCell1 = cell.getSheet().getRow(curRowIndex - 1).getCell(1);
Cell preCell2 = cell.getSheet().getRow(curRowIndex - 1).getCell(2);
Cell preCell3 = cell.getSheet().getRow(curRowIndex - 1).getCell(3);
Cell preCell4 = cell.getSheet().getRow(curRowIndex - 1).getCell(4);
Cell preCell5 = cell.getSheet().getRow(curRowIndex - 1).getCell(5);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
Object preData1 = preCell1.getCellTypeEnum() == CellType.STRING ? preCell1.getStringCellValue() : preCell1.getNumericCellValue();
Object preData2 = preCell2.getCellTypeEnum() == CellType.STRING ? preCell2.getStringCellValue() : preCell2.getNumericCellValue();
Object preData3 = preCell3.getCellTypeEnum() == CellType.STRING ? preCell3.getStringCellValue() : preCell3.getNumericCellValue();
Object preData4 = preCell4.getCellTypeEnum() == CellType.STRING ? preCell4.getStringCellValue() : preCell4.getNumericCellValue();
Object preData5 = preCell5.getCellTypeEnum() == CellType.STRING ? preCell5.getStringCellValue() : preCell5.getNumericCellValue();
String curString = String.valueOf(curData) + curData1 + curData2 + curData3 + curData4 + curData5;
String preString = String.valueOf(preData) + preData1 + preData2 + preData3 + preData4 + preData5;
// 比较当前行的单元格与上一行是否相同,相同合并当前单元格与上一行
if (curString.equals(preString)) {
Sheet sheet = writeSheetHolder.getSheet();
// 获取合并信息
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
// 下标
int index = 0;
for (int i = 0; i < mergeRegions.size(); i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并,则先移出原有的合并单元,再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, index)) {
sheet.removeMergedRegion(mergeRegions.size() - 6);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
index++;
// 这个是第几列
if (index > 5) {
index = 0;
}
}
if (!isMerged) {
for (int i = 0; i <= 5; i++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, i, i);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
}
2.导出使用
代码如下:
List<ExportExcel> list =new ArrayList<>();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode("Excel文件名", "utf-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), ExportExcel.class)
.registerWriteHandler(new ExcelFileCellsMerge())
.sheet(0, "sheet页名称").doWrite(list);
3、导出效果如下
总结
找了很久没找到合并多行表格的代码,这是我根据查找的样例、结合自身项目需求进行修改而来的。使用范围有所限制,当然有大佬可以进行优化一下。万分感谢。