如何使用 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 {

 public static void main(String[] args) throws Exception {

  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.";

  int urlLength = url.length();

  System.out.println(urlLength);

  try (Workbook workbook = new XSSFWorkbook(); 

       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   Sheet sheet = workbook.createSheet(); 

   Hyperlink link = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);

   link.setAddress(url);

 

   Row row = sheet.createRow(0);

   Cell cell = row.createCell(0);

   String cellText = "Open link to long URL having length of " + urlLength + " characters.";

   cell.setCellValue(cellText);

   cell.setHyperlink(link);

 

   sheet.setColumnWidth(0, cellText.length() * 256);

   workbook.write(fileout);

  }

 }

}

这适用于我使用apache poi 4.1.0and Excel 2016。

如果 URL 太长，则某些Excel版本无法*.xlsx正确打开文件。发生这种情况的确切 URL 长度似乎因 Excel版本而异。