EasyExcel导出合并单元格
EasyExcel自定义导出合并单元格(行和列自动合并)
·
功能展示
功能类似这样：
开题序言
当时网上的资料大部分都是上下行相同的数据合并，唯独没有左右列的单元格合并。因此找资料花了一点时间，但功夫不负有心人，终于看到有两个和我要的功能差不多类似的文章。需要自取：
EasyExcel模板导出(行和列自动合并)_Lzfnemo2009的博客-CSDN博客_easyexcel模板导出
写这篇文章只是为了自己以后参考。
以下进入正文：
1、导出的实体类，也就是表头
@Data
public class CityCapacityPo {
@ExcelProperty(value = "时间",index = 0)
private String time;
@ExcelProperty(value = "出口",index = 1)
private String export;
@ExcelProperty(value = "地市",index = 2)
private String direction;
@ExcelProperty(value = "数据1",index = 3)
private Double data1;
@ExcelProperty(value = "数据2",index = 4)
private Double data2;
@ExcelProperty(value = "数据3",index = 5)
private Double data3;
@ExcelProperty(value = "数据4",index = 6)
private Double data4;
@ExcelProperty(value = "数据5",index = 7)
private Double data5;
@ExcelProperty(value = "数据6",index = 8)
private Double data6;
}2、行合并工具类
public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {
/**
* 需要合并列的下标，从0开始
*/
private int[] mergeColumnIndex;
/**
* 从第几行开始合并，表头下标为0
*/
private int mergeRowIndex;
public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@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) {
for (int i = 0; i < mergeColumnIndex.length; i++) {
if (curColIndex == mergeColumnIndex[i]) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
cell.getNumericCellValue();
Row preRow = cell.getSheet().getRow(curRowIndex - 1);
if (preRow == null) {
// 当获取不到上一行数据时，使用缓存sheet中数据
preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
}
Cell preCell=preRow.getCell(curColIndex);
Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
preCell.getNumericCellValue();
// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
if (curData.equals(preData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,
curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}3、列合并的工具类
@Data
@AllArgsConstructor
public class CellLineRange {
/**
* 起始列
*/
private int firstCol;
/**
* 结束列
*/
private int lastCol;
}
public class ExcelFillCelMergeStrategy implements CellWriteHandler {
//自定义合并单元格的列 如果想合并 第4列和第5例 、第6列和第7例： [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
private List<CellLineRange> cellLineRangeList;
//自定义合并单元格的开始的行 一般来说填表头行高0 表示从表头下每列开始合并 ：如表头行高位为3则 int mergeRowIndex = 2 ;
private int mergeRowIndex;
public ExcelFillCelMergeStrategy(List<CellLineRange> cellLineRangeList, int mergeRowIndex) {
this.cellLineRangeList=cellLineRangeList;
this.mergeRowIndex=mergeRowIndex;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@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 (curRowIndex > mergeRowIndex) {
for (int i = 0; i < cellLineRangeList.size(); i++) {
if (curColIndex > cellLineRangeList.get(i).getFirstCol()&&curColIndex<=cellLineRangeList.get(i).getLastCol()) {
//单元格数据处理
mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
}
/**
* @description 当前单元格向左合并
*/
private void mergeWithLeftLine(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
//当前单元格中数据
Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
//获取当前单元格的左面一个单元格
Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
//获取当前单元格的左面一个单元格中的数据
Object leftData = leftCell.getCellTypeEnum() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue();
// 将当前单元格数据与左侧一个单元格数据比较
if (leftData.equals(curData)) {
//获取当前sheet页
Sheet sheet = writeSheetHolder.getSheet();
//得到所有的合并单元格
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
//是否合并
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
//CellRangeAddress POI合并单元格
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
//例子：CellRangeAddress（2, 6000, 3, 3）；
//第2行起 第6000行终止 第3列开始 第3列结束。
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// cellRangeAddr.isInRange(int rowInd, int colInd)确定给定坐标是否在此范围的范围内。
// 若左侧一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex, curColIndex - 1)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastColumn(curColIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若左侧一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex , curRowIndex, curColIndex- 1, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}4、调用工具类，开始合并：
@PostMapping("/exportCityOutletCapacityList")
@ApiOperation("导出Excel表")
public void exportCityOutletCapacityList(@RequestBody OutletCapacityParam param, HttpServletResponse response) {
//获取需要导出的表数据
List<CityCapacityPo> list=capacityFlowDao.selectCityOutletCapacityList(param);
try {
String fileName = "测试合并单元格";
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
CityCapacityPo capacityPo=new CityCapacityPo();
capacityPo.setExport("汇总");
capacityPo.setTime("汇总");
capacityPo.setDirection("汇总");
//遍历列表，求各数据汇总
capacityPo.setData1(list.stream().filter(Po-> Po.getData1()!=null).mapToDouble(CityCapacityPo::getData1).sum());
capacityPo.setData2(list.stream().filter(Po-> Po.getData2()!=null).mapToDouble(CityCapacityPo::getData2).sum());
capacityPo.setData3(list.stream().filter(Po-> Po.getData3()!=null).mapToDouble(CityCapacityPo::getData3).sum());
capacityPo.setData4(list.stream().filter(Po-> Po.getData4()!=null).mapToDouble(CityCapacityPo::getData4).sum());
capacityPo.setData5(list.stream().filter(Po-> Po.getData5()!=null).mapToDouble(CityCapacityPo::getData5).sum());
capacityPo.setData6(list.stream().filter(Po-> Po.getData6()!=null).mapToDouble(CityCapacityPo::getData6).sum());
list.add(capacityPo);
ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();
//设置第几列开始合并
int[] mergeColumnIndex = {0, 1};
//设置第几行开始合并
int mergeRowIndex = 1;
cellLineRanges.add(new CellLineRange(0,2));
EasyExcel.write(response.getOutputStream())
//设置行合并单元格
.registerWriteHandler(new ExcelFillCellMergeStrategyUtils(mergeRowIndex,mergeColumnIndex))
//设置行合并单元格
.registerWriteHandler(new ExcelFillCelMergeStrategy(cellLineRanges,list.size()-1))
.head(CityCapacityPo.class)
.sheet("sheet1")
.doWrite(list);
} catch (Exception e) {
e.printStackTrace();
}
}以上就是行和列的合并过程了，主要的内容就是行和列合并的工具类，网上的资料都差不多。其实很多我确实也没参透，只是依样画葫芦，碰巧实现了。
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
13
2- 0
已为社区贡献2条内容
所有评论(0)