EasyExcel实现指定行列的相同内容单元格合并

╰+哭是因爲堅強的太久メ 2023-10-15 20:18 92阅读 0赞

## ExcelMergeUtil工具类 ##

package com.sdy.resdir.biz.util;
     
     
    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.math.BigDecimal;
    import java.util.List;
     
    import static org.apache.poi.ss.usermodel.CellType.NUMERIC;
     
    public class ExcelMergeUtil implements CellWriteHandler {
        
        private int[] mergeColumnIndex;
        private int mergeRowIndex;
     
        public ExcelMergeUtil() {
        
        }
     
        public ExcelMergeUtil(int mergeRowIndex, int[] mergeColumnIndex) {
        
            this.mergeRowIndex = mergeRowIndex;
            this.mergeColumnIndex = mergeColumnIndex;
        }
     
        @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 afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
     
        }
     
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
     
            //当前行
            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;
                    }
                }
            }
        }
     
     
        /**
         * 当前单元格向上合并
         *
         * @param writeSheetHolder
         * @param cell             当前单元格
         * @param curRowIndex      当前行
         * @param curColIndex      当前列
         */
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        
            Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
            // 将当前单元格数据与上一个单元格数据比较
            Boolean dataBool = preData.equals(curData);
            if (dataBool) {
        
                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);
                }
            }
        }
    }

## 控制层调用 ##

public void downLoadResItem(HttpServletResponse response, String resName, Integer isOnline, Integer resType, Integer resLevel, Integer resPower, Integer resPowerDept, Integer realmId) throws IOException {
        
            if (StringUtil.isNotBlank(resName)) {
        
                resName = URLDecoder.decode(resName, "UTF-8");
            }
            List<RdResourceDirExcelDTO> rdResourceDirList = rdResourceDirService.getList(resName, isOnline, resType, resLevel, resPower, resPowerDept, realmId);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("资源列表下载", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
            ServletOutputStream output = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(output, ExcelTypeEnum.XLS, true);
            Sheet sheet = new Sheet(1, 0, ResItemExcelVo.class);
            // sheet.setSheetName("第一页");
            List<ResItemExcelVo> voList = setResItemData(rdResourceDirList);
            // writer.write(voList, sheet);
            //需要合并的列
            int[] mergeColumeIndex = {
        0,1,2,3,4,5,6,7,8,9};
            // 从第二行后开始合并
            int mergeRowIndex = 2;
            EasyExcel.write(response.getOutputStream(), ResItemExcelVo.class)
                    .sheet("第一页")
                    // .registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
                    .registerWriteHandler(new ExcelMergeUtil(mergeRowIndex, mergeColumeIndex))
                    .doWrite(voList);
            writer.finish();
            output.flush();
        }

## 效果类似下图 ##

![在这里插入图片描述][f40bddfa69db41138add3f4eac658492.png]

[f40bddfa69db41138add3f4eac658492.png]: https://img-blog.csdnimg.cn/f40bddfa69db41138add3f4eac658492.png