easyExcel实现sheet页的跳转（兼容wps）

1.跳转实现

import lombok.extern.log4j.Log4j2;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;

import java.util.List;
@Log4j2
public class CustomCellWriteHandler extends AbstractCellWriteHandler {
  
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        int zero = 0;
        int two = 2;
        // 步骤：1.从第1行开始，第三列添加超链接  2.去掉跟踪记录为空的超链接
        log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
        //cell中的行和列都是从0开始的，符合行数大于0，列是第三列的数据添加超链接，链接到sheet名称为“客户跟进记录”D列对应的行中
        if (cell.getRowIndex() > zero && cell.getColumnIndex() == two) {
            log.info("cell = {}, rowIndex = {}, columnIndex = {}",cell,cell.getRowIndex(),cell.getColumnIndex());
            CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
            Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.FILE); 
            // 类型设置为 FILE
            hyperlink.setAddress("#客户跟进记录!D" + (cell.getRowIndex()+1));
            cell.setHyperlink(hyperlink);
        }

        //index为30的列只是辅助列，用来进行辅助sheet页的跳转；如果后期字段扩张，该字段需要扩大
        if (cell.getRowIndex() > zero && cell.getColumnIndex() == 30 ) {
            log.info("cell.getNumericCellValue = {}",cell.getNumericCellValue());
            Row row = cell.getRow();
            //获取到第三列的cell
            Cell cell1 = row.getCell(two);
            if (zero == (int)cell.getNumericCellValue()) {
                //去掉该列超链接
                cell1.removeHyperlink();
            } else {
                //有跟踪记录的高亮展示
                Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                CellStyle cellStyle = workbook.createCellStyle();
                //字体
                Font cellFont = workbook.createFont();
                cellFont.setUnderline(Font.U_SINGLE);
                cellFont.setColor(IndexedColors.BLUE.getIndex());
                cellStyle.setFont(cellFont);
                cell1.setCellStyle(cellStyle);
            }
        }

    }
}

导出新增红框中代码：

导出excel效果：

说明：企业名称为添加超链接列，含有跟踪记录的可以跳转，否则不跳转

2.问题描述

在excel中实现了sheet页间的跳转，但是在wps中跳转失败

wps跳转异常，提示如下

3.解决wps跳转问题

调整点：

hyperlink.setAddress(fileName+"#客户跟进记录!D" + (cell.getRowIndex()+1));

调整后的完整代码：

import lombok.extern.log4j.Log4j2;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.*;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;

import java.util.List;
@Log4j2
public class CustomCellWriteHandler extends AbstractCellWriteHandler {
    private String fileName;

    public CustomCellWriteHandler(String fileName) {
        this.fileName = fileName;
    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        int zero = 0;
        int two = 2;
        // 步骤：1.从第1行开始，第三列添加超链接  2.去掉跟踪记录为空的超链接
        log.info("第{}行，第{}列写入完成。", cell.getRowIndex(), cell.getColumnIndex());
        //cell中的行和列都是从0开始的，符合行数大于0，列是第三列的数据添加超链接，链接到sheet名称为“客户跟进记录”D列对应的行中
        if (cell.getRowIndex() > zero && cell.getColumnIndex() == two) {
            log.info("cell = {}, rowIndex = {}, columnIndex = {}",cell,cell.getRowIndex(),cell.getColumnIndex());
            CreationHelper createHelper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
            Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.FILE); // 类型设置为 FILE
            hyperlink.setAddress(fileName+"#客户跟进记录!D" + (cell.getRowIndex()+1));
            cell.setHyperlink(hyperlink);
        }

        //index为30的列只是辅助列，用来进行辅助sheet页的跳转；如果后期字段扩张，该字段需要扩大
        if (cell.getRowIndex() > zero && cell.getColumnIndex() == 30 ) {
            log.info("cell.getNumericCellValue = {}",cell.getNumericCellValue());
            Row row = cell.getRow();
            //获取到第三列的cell
            Cell cell1 = row.getCell(two);
            if (zero == (int)cell.getNumericCellValue()) {
                //去掉该列超链接
                cell1.removeHyperlink();
            } else {
                //有跟踪记录的高亮展示
                Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                CellStyle cellStyle = workbook.createCellStyle();
                //字体
                Font cellFont = workbook.createFont();
                cellFont.setUnderline(Font.U_SINGLE);
                cellFont.setColor(IndexedColors.BLUE.getIndex());
                cellStyle.setFont(cellFont);
                cell1.setCellStyle(cellStyle);
            }
        }

    }
}

参考文献：

(167条消息) java利用poi包 为excel生成超链接_weixin_30519071的博客-CSDN博客