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

Excel数据处理与美化：合并单元格与添加超链接示例

最新推荐文章于 2025-09-03 14:37:17 发布

原创最新推荐文章于 2025-09-03 14:37:17 发布 · 4.7k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#java #开发语言

EasyExcel 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了如何使用EasyExcel API实现动态单元格合并和超链接功能。处理器分别针对特定列进行操作，适用于标题行后的数据处理。

处理器一定要实现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);