Public Object: POI and Hyperlinks

POI and Hyperlinks

I have a program that generates an Excel file that can contain hundreds of sheets. To make this more manageable, the first sheet is an 'index' sheet that summarizes the sheets to follow. Each row in this index is hyperlinked to the a sheet that provides further detail.

Here's some tips on creating Hyperlinks in Excel .xls documents using Jakarta POI:

Excel hyperlinks are formulas with this syntax: HYPERLINK( "target", "label")
To link to the first cell in a sheet called sheetname, use "#sheetname!A1"
Sheet names in Excel can contain spaces and commas, but you cannot link to such sheets. I recommend sticking to letters, numbers and underscore for best results. Sheet names must also be 31 characters or less. That's 2^5-1.

And finally, a code example:

String targetSheetName = "Sheet3"

String targetCell = "A1";

String targetSheetLabel = "Third Sheet";

String linkFormula = "HYPERLINK(\"#" + targetSheetName + "!" + targetCell  + "\", \"" + targetSheetLabel + "\")";



HSSFRow linkRow = ...

HSSFCell linkCell = linkRow.createCell((short)1);

linkCell.setCellFormula(linkFormula);

The result works very well. Now all I gotta do is color the link blue and give it the familar hyperlink underline!

# posted by Jesse Wilson on Wednesday, October 27, 2004

"[..] can contain spaces and commas, but you cannot link to such sheets [..]"

In fact you can, but you need to put the name in a single quotes as shown below:
=HYPERLINK("#'Test sheet'!A1"; "Label")

# posted by Paweł Maniawski on April 13, 2006 6:33 AM

Using Sheets with undescores in the name doesn't work with my Excel 2003 German.

# posted by Anonymous on December 13, 2006 1:28 AM

"Now all I gotta do is color the link blue and give it the familar hyperlink underline!" - did you ever figure out how to do this?

Brian

# posted by Brian Pipa on February 19, 2007 12:38 PM

Man, you're my Savior.

Thanks a lot.

# posted by Kirill on November 19, 2007 5:37 AM