左右列的单元格合并

╰+攻爆jí腚メ 2024-02-05 16:51 126阅读 0赞

EasyExcel导出合并单元格，左右列的单元格合并
1、导出的实体类，也就是表头

  1. @Data
  2. public class CityCapacityPo {
  6.     @ExcelProperty(value = "时间",index = 0)
  7.     private String time;
  10.     @ExcelProperty(value = "出口",index = 1)
  11.     private String export;
  14.     @ExcelProperty(value = "地市",index = 2)
  15.     private String direction;
  18.     @ExcelProperty(value = "数据1",index = 3)
  19.     private Double data1;
  22.     @ExcelProperty(value = "数据2",index = 4)
  23.     private Double data2;
  26.     @ExcelProperty(value = "数据3",index = 5)
  27.     private Double data3;
  30.     @ExcelProperty(value = "数据4",index = 6)
  31.     private Double data4;
  33.     @ExcelProperty(value = "数据5",index = 7)
  34.     private Double data5;
  36.     @ExcelProperty(value = "数据6",index = 8)
  37.     private Double data6;
  39. }

2、行合并工具类

  1. public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {
  4.     /**
  5.      * 需要合并列的下标，从0开始
  6.      */
  7.     private int[] mergeColumnIndex;
  8.     /**
  9.      * 从第几行开始合并，表头下标为0
  10.      */
  11.     private int mergeRowIndex;
  13.     public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) {
  15.         this.mergeRowIndex = mergeRowIndex;
  16.         this.mergeColumnIndex = mergeColumnIndex;
  17.     }
  19.     @Override
  20.     public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
  23.     }
  25.     @Override
  26.     public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
  29.     }
  31.     @Override
  32.     public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
  35.     }
  37.     @Override
  38.     public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
  41.         //当前行
  42.         int curRowIndex = cell.getRowIndex();
  43.         //当前列
  44.         int curColIndex = cell.getColumnIndex();
  46.         if (curRowIndex > mergeRowIndex) {
  48.             for (int i = 0; i < mergeColumnIndex.length; i++) {
  50.                 if (curColIndex == mergeColumnIndex[i]) {
  52.                     mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
  53.                     break;
  54.                 }
  55.             }
  56.         }
  58.     }
  60.     private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
  62.         //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
  63.         Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
  64.                 cell.getNumericCellValue();
  65.         Row preRow = cell.getSheet().getRow(curRowIndex - 1);
  66.         if (preRow == null) {
  68.             // 当获取不到上一行数据时，使用缓存sheet中数据
  69.             preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
  70.         }
  71.         Cell preCell=preRow.getCell(curColIndex);
  72.         Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
  73.                 preCell.getNumericCellValue();
  74.         // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
  75.         if (curData.equals(preData)) {
  77.             Sheet sheet = writeSheetHolder.getSheet();
  78.             List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
  79.             boolean isMerged = false;
  80.             for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
  82.                 CellRangeAddress cellRangeAddr = mergeRegions.get(i);
  83.                 // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
  84.                 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
  86.                     sheet.removeMergedRegion(i);
  87.                     cellRangeAddr.setLastRow(curRowIndex);
  88.                     sheet.addMergedRegion(cellRangeAddr);
  89.                     isMerged = true;
  90.                 }
  91.             }
  92.             // 若上一个单元格未被合并，则新增合并单元
  93.             if (!isMerged) {
  95.                 CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,
  96.                         curColIndex);
  97.                 sheet.addMergedRegion(cellRangeAddress);
  98.             }
  99.         }
  105.     }
  107. }

3、列合并的工具类

  1. @Data
  2. @AllArgsConstructor
  3. public class CellLineRange {
  6.     /**
  7.      * 起始列
  8.      */
  9.     private int firstCol;
  11.     /**
  12.      * 结束列
  13.      */
  14.     private int lastCol;
  15. }
  17. public class ExcelFillCelMergeStrategy implements CellWriteHandler {
  20.     //自定义合并单元格的列 如果想合并   第4列和第5例 、第6列和第7例： [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
  21.     private List<CellLineRange> cellLineRangeList;
  23.     //自定义合并单元格的开始的行  一般来说填表头行高0 表示从表头下每列开始合并 ：如表头行高位为3则 int mergeRowIndex = 2  ;
  24.     private int mergeRowIndex;
  26.     public ExcelFillCelMergeStrategy(List<CellLineRange> cellLineRangeList, int mergeRowIndex) {
  28.         this.cellLineRangeList=cellLineRangeList;
  29.         this.mergeRowIndex=mergeRowIndex;
  30.     }
  32.     @Override
  33.     public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
  36.     }
  38.     @Override
  39.     public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
  42.     }
  44.     @Override
  45.     public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
  48.     }
  50.     @Override
  51.     public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
  54.         //当前单元格的行数
  55.         int curRowIndex = cell.getRowIndex();
  56.         // 当前单元格的列数
  57.         int curColIndex = cell.getColumnIndex();
  58.         if (curRowIndex > mergeRowIndex) {
  60.             if (curRowIndex > mergeRowIndex) {
  62.                 for (int i = 0; i < cellLineRangeList.size(); i++) {
  64.                     if (curColIndex > cellLineRangeList.get(i).getFirstCol()&&curColIndex<=cellLineRangeList.get(i).getLastCol()) {
  66.                         //单元格数据处理
  67.                         mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
  68.                         break;
  69.                     }
  70.                 }
  71.             }
  72.         }
  73.     }
  75.     /**
  76.      * @description 当前单元格向左合并
  77.      */
  78.     private void mergeWithLeftLine(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
  80.         //当前单元格中数据
  81.         Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
  82.         //获取当前单元格的左面一个单元格
  83.         Cell leftCell = cell.getSheet().getRow(curRowIndex).getCell(curColIndex - 1);
  84.         //获取当前单元格的左面一个单元格中的数据
  85.         Object leftData = leftCell.getCellTypeEnum() == CellType.STRING ? leftCell.getStringCellValue() : leftCell.getNumericCellValue();
  87.         // 将当前单元格数据与左侧一个单元格数据比较
  88.         if (leftData.equals(curData)) {
  90.             //获取当前sheet页
  91.             Sheet sheet = writeSheetHolder.getSheet();
  92.             //得到所有的合并单元格
  93.             List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
  94.             //是否合并
  95.             boolean isMerged = false;
  96.             for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
  98.                 //CellRangeAddress POI合并单元格
  99.                 //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
  100.                 //例子：CellRangeAddress（2, 6000, 3, 3）；
  101.                 //第2行起 第6000行终止 第3列开始 第3列结束。
  102.                 CellRangeAddress cellRangeAddr = mergeRegions.get(i);
  103.                 // cellRangeAddr.isInRange(int rowInd, int colInd)确定给定坐标是否在此范围的范围内。
  104.                 // 若左侧一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
  105.                 if (cellRangeAddr.isInRange(curRowIndex, curColIndex - 1)) {
  107.                     sheet.removeMergedRegion(i);
  108.                     cellRangeAddr.setLastColumn(curColIndex);
  109.                     sheet.addMergedRegion(cellRangeAddr);
  110.                     isMerged = true;
  111.                 }
  112.             }
  113.             // 若左侧一个单元格未被合并，则新增合并单元
  114.             if (!isMerged) {
  116.                 CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex , curRowIndex, curColIndex- 1, curColIndex);
  117.                 sheet.addMergedRegion(cellRangeAddress);
  118.             }
  119.         }
  121.     }
  123. }

4、调用工具类，开始合并：

  1. @PostMapping("/exportCityOutletCapacityList")
  2. @ApiOperation("导出Excel表")
  3. public void exportCityOutletCapacityList(@RequestBody OutletCapacityParam param, HttpServletResponse response) {
  5.          //获取需要导出的表数据
  6.          List<CityCapacityPo> list=capacityFlowDao.selectCityOutletCapacityList(param);
  7.  try {
  9.             String fileName = "测试合并单元格";
  10.             response.setContentType("application/octet-stream");
  11.             response.setCharacterEncoding("utf-8");
  12.             response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
  14.             CityCapacityPo capacityPo=new CityCapacityPo();
  15.             capacityPo.setExport("汇总");
  16.             capacityPo.setTime("汇总");
  17.             capacityPo.setDirection("汇总");
  18.             //遍历列表，求各数据汇总
  19.             capacityPo.setData1(list.stream().filter(Po-> Po.getData1()!=null).mapToDouble(CityCapacityPo::getData1).sum());
  20.             capacityPo.setData2(list.stream().filter(Po-> Po.getData2()!=null).mapToDouble(CityCapacityPo::getData2).sum());
  21.             capacityPo.setData3(list.stream().filter(Po-> Po.getData3()!=null).mapToDouble(CityCapacityPo::getData3).sum());
  22.             capacityPo.setData4(list.stream().filter(Po-> Po.getData4()!=null).mapToDouble(CityCapacityPo::getData4).sum());
  23.             capacityPo.setData5(list.stream().filter(Po-> Po.getData5()!=null).mapToDouble(CityCapacityPo::getData5).sum());
  24.             capacityPo.setData6(list.stream().filter(Po-> Po.getData6()!=null).mapToDouble(CityCapacityPo::getData6).sum());
  25.             list.add(capacityPo);
  27.             ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();
  28.             //设置第几列开始合并
  29.             int[] mergeColumnIndex = {
  30.     0, 1};
  31.             //设置第几行开始合并
  32.             int mergeRowIndex = 1;
  35.             cellLineRanges.add(new CellLineRange(0,2));
  36.                 EasyExcel.write(response.getOutputStream())              
  37.                         //设置行合并单元格
  38.                         .registerWriteHandler(new ExcelFillCellMergeStrategyUtils(mergeRowIndex,mergeColumnIndex))
  39.                         //设置行合并单元格
  40.                         .registerWriteHandler(new ExcelFillCelMergeStrategy(cellLineRanges,list.size()-1))
  41.                         .head(CityCapacityPo.class)
  42.                         .sheet("sheet1")
  43.                         .doWrite(list);
  45.         } catch (Exception e) {
  47.             e.printStackTrace();
  48.         }
  50.     }

发表评论

表情：
评论列表 （有 0 条评论，126人围观）

还没有评论，来说两句吧...

相关阅读