左右列的单元格合并

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;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic 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;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic 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();}}