左右列的单元格合并

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

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();
            }
     
        }