如何使用 Apache POI 创建链接到长 url 的 excel 超链接

Question

Excel 的超链接大小限制为 255。

现在我正在使用 Apache POI 以编程方式填写 excel，但使用的 s3 预签名 url 远远超过 255 个字符，长度超过 1350。

当我单击在 excel 中创建的超链接时，它显示如下警报：“发生意外错误。”

这是我对应的代码：

import org.apache.logging.log4j.LogManager;

import org.apache.logging.log4j.Logger;

import org.apache.poi.common.usermodel.HyperlinkType;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CreationHelper;

import org.apache.poi.ss.usermodel.FillPatternType;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFFont;

import org.apache.poi.xssf.usermodel.XSSFHyperlink;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

  public void generateExcel(List<FunctionalTestCaseResult> data) {

      XSSFWorkbook workbook = new XSSFWorkbook();

      CreationHelper createHelper = workbook.getCreationHelper();

      XSSFSheet sheet = workbook.createSheet("Sheet1");

      int rowNum = 0;

      Row row = sheet.createRow(rowNum++);

      int cellNum = 0;

      CellStyle captionStyle = workbook.createCellStyle();

      captionStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());

      captionStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

      //create hyper link style

      XSSFCellStyle hlinkstyle = workbook.createCellStyle();

      XSSFFont hlinkfont = workbook.createFont();

      hlinkfont.setUnderline(XSSFFont.U_SINGLE);

      hlinkfont.setColor(IndexedColors.BLUE.index);

      hlinkstyle.setFont(hlinkfont);

      Cell cell = row.createCell(cellNum++);

      XSSFHyperlink link = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.URL);

      link.setAddress(recordingS3Url);

      cell.setHyperlink(link);

      cell.setCellValue("Recording url");

      cell.setCellStyle(hlinkstyle);

  }

Answer 1

您提到的限制是关于=HYPERLINK公式中的函数。Excel公式的长度不能超过 255 个字符。默认单元格超链接的 URL 长度没有限制。以下代码创建一个单元格，该单元格具有指向长度为 1554 个字符的 URL 的超链接。import java.io.FileOutputStream;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.common.usermodel.HyperlinkType;class CreateExcelHyperlinkLongURL {&nbsp;public static void main(String[] args) throws Exception {&nbsp; String url = "https://www.google.de/search?&q=Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.+Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.&oq=Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.+Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.+Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.";&nbsp; int urlLength = url.length();&nbsp; System.out.println(urlLength);&nbsp; try (Workbook workbook = new XSSFWorkbook();&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {&nbsp; &nbsp;Sheet sheet = workbook.createSheet();&nbsp;&nbsp; &nbsp;Hyperlink link = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);&nbsp; &nbsp;link.setAddress(url);&nbsp;&nbsp; &nbsp;Row row = sheet.createRow(0);&nbsp; &nbsp;Cell cell = row.createCell(0);&nbsp; &nbsp;String cellText = "Open link to long URL having length of " + urlLength + " characters.";&nbsp; &nbsp;cell.setCellValue(cellText);&nbsp; &nbsp;cell.setHyperlink(link);&nbsp;&nbsp; &nbsp;sheet.setColumnWidth(0, cellText.length() * 256);&nbsp; &nbsp;workbook.write(fileout);&nbsp; }&nbsp;}}这适用于我使用apache poi 4.1.0and Excel 2016。如果 URL 太长，则某些Excel版本无法*.xlsx正确打开文件。发生这种情况的确切 URL 长度似乎因 Excel版本而异。