| easyExcel实现sheet页的跳转（兼容wps） | 您所在的位置：网站首页 › 读取excel行数 › easyExcel实现sheet页的跳转（兼容wps） |
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 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 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博客 |
【本文地址】
公司简介
联系我们