使用EasyExcel动态合并单元格和对指定列添加超链接

梦里梦外; 2023-09-28 23:24 30阅读 0赞

处理器一定要实现CellWriteHandler接口  
**动态合并单元格的处理器：**

import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.metadata.data.WriteCellData;
    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.util.List;
    
    //动态合并单元格
    public class ExcelFillCellMergeHandler implements CellWriteHandler{
    
        /**
         * 合并字段的下标
         */
        private int[] mergeColumnIndex;
        /**
         * 从第几行开始合并，0代表标题行
         */
        private int mergeRowIndex;
    
        public ExcelFillCellMergeHandler() {
        }
    
        public ExcelFillCellMergeHandler(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, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> 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;
                    }
                }
            }
        }
    
        // curRowIndex 当前行 curColIndex当前列
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
    
            //获取当前行的第一列的数据和上一行的第一列的数据，比较上一行数据是否相同进行合并
            Cell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);
            Cell preFirstCell = cell.getSheet().getRow(curRowIndex-1).getCell(0);
            Object curId = curFirstCell.getCellType() == CellType.NUMERIC ? curFirstCell.getNumericCellValue() : curFirstCell.getStringCellValue();
            Object perId = preFirstCell.getCellType() == CellType.NUMERIC ? preFirstCell.getNumericCellValue() : preFirstCell.getStringCellValue();
            // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
            // 之所以比较第一列，是因为第一列导出的是数据的id，具有唯一性，如果想比较其他单元格只需要指定不同的行和列的位置即可
            if (curId.equals(perId)) {
                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);
                }
            }
        }
    }

**对指定列添加超链接的处理器**

import cn.hutool.core.util.StrUtil;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.metadata.data.WriteCellData;
    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.common.usermodel.HyperlinkType;
    import org.apache.poi.ss.usermodel.*;
    
    import java.util.List;
    
    //动态添加超链接
    public class ExcelHyperlinkHandler implements CellWriteHandler {
        /**
         * 添加超链接的字段的下标
         */
        private int[] mergeColumnIndex;
        /**
         * 从第几行开始添加，0代表标题行
         */
        private int mergeRowIndex;
    
        public ExcelHyperlinkHandler() {
        }
    
        public ExcelHyperlinkHandler(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, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> 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;
                    }
                }
            }
        }
    
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
    
            //获得超链接，当前单元格的内容就是一个超链接
            String stringCellValue = cell.getCellType()==CellType.STRING ? cell.getStringCellValue():null;
            if (StrUtil.isNotBlank(stringCellValue)) {
                CreationHelper creationHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
                Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
                hyperlink.setAddress(stringCellValue);
                cell.setHyperlink(hyperlink);//添加超链接
            }
        }
    }

**调用方法，使用 registerWriteHandler 指定，然后在 new 处理器的时候，传入指定的行和列即可**

EasyExcel.write(response.getOutputStream(), Entity.class)
                .head(Entity.class)
                    .registerWriteHandler(new ExcelFillCellMergeHandler(1, new int[]{0,1,2,3,4,5,6,7}))//从第一行开始，对第0到7列的单元格进行判断是否进行合并
                    .registerWriteHandler(new ExcelHyperlinkHandler(1, new int[]{8}))//从第一行开始，对第8列的单元格添加超链接
                .excelType(ExcelTypeEnum.XLSX)
                .sheet("文件名")
                .doWrite(list);