CODEWITHSUNDEEP
javaexcelapache-poiapache-poi-4
james
james

Reputation: 201

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

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

  }

Upvotes: 2

Views: 8829

Answers (1)

Axel Richter
Axel Richter

Reputation: 61945

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.

Upvotes: 7

Related Questions