How to Use Apache POI to create excel hyper link that links to long url

Question

Excel is having a hyper link size limit of 255.

Now I'm using Apache POI to programmatically fill in a excel, but with a s3 pre-signed url that is much longer than 255 characters, 1350+ in length.

And when I click the hyper link created in excel, it's showing alert as follows: "An unexpected error has occurred."

Here's my corresponding code:

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);

  }

The limit you mention is about a `=HYPERLINK` function in a formula. `Excel` formulas cannot be more than 255 characters in length. So the reason of your error must be somewhat else in your `recordingS3Url`. No help possible without knowing that exact URL. — Axel Richter, Aug 01 '19 at 03:28
The url is s3 presigned url, for sure larger than 255 characters. Just wondering whether there's any way to use Apache POI to programmatically work with url that is larger than 255 characters. It's usually around 1400 characters for reportS3url. — james, Aug 01 '19 at 07:28

Axel Richter · Accepted Answer · 2021-03-19T07:11:44.220

The limit you mention is about a =HYPERLINK function in a formula. Excel formulas cannot be more than 255 characters in length.

There is not a limit for the length of URLs for default cell hyperlinks. The folowing code creates a cell having a hyperling to a URL having length of 1554 characters.

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);
  }

 }
}

This works for me using apache poi 4.1.0 and Excel 2016.

If the URLs are much too long, then some Excel versions are not able opening the *.xlsx file properly. The exact URL length where this happens seems to be different dependent on the Excel versions.

My Excel version is 16.27. Apache poi is 4.0.1. Same code doesn't work either for my combination of excel version and apache poi. I don't see any differences between 4.0.1 and 4.1.0 on hyper link from [release notes](https://poi.apache.org/changes.html#4.1.0). Guess it has something to do with excel version. One question anyway, for your excel, if you manually make a hyper link with the url in your code, does it work when you click it? Thx! For mine, the answer is no. — james, Aug 01 '19 at 18:14
Works for me as I told in my answer using apache poi 4.1.0 and Excel 2016. Works also using Excel 365. And yes, I can click the hyperlink and it opens in system's (Windows 10) default browser (Chrome). — Axel Richter, Aug 01 '19 at 18:37
Cool, then it looks more like excel bug to me. I tried both on mac and windows 10 desktop, both equipped with Excel 2016, 16.27 in specific. And you're right, hyper link 255 limit is different, not for this case. — james, Aug 01 '19 at 20:37

How to Use Apache POI to create excel hyper link that links to long url

1 Answers1