POI and HyperlinksI 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);
The result works very well. Now all I gotta do is color the link blue and give it the familar hyperlink underline!