[Excel::Writer::XLSX] Hyperlink Limit of 65,530

[rboisvert]

Greetings,

I ran into a small bug with Excel::Writer::XLSX, Excel 2007 and
hyperlinks. The program below creates two files, one with 65,530
hyperlinks and the other with 65,531. I can open the first with no
problem but the second one Excel identifies as corrupted. The repair
function identifies the hyperlinks as the problem and removes them.
If I open the working file and add more links it has no problem so I
think it has something to do with the Writer. My guess is that it
involves a bug reaching the magic number of 64K.

For the time being, I’m limiting each sheet to 65,530 hyperlinks but
it would be nice to open up the full functionality if possible. My
apologies if this bug already exists in the discussion posts. I
looked and was unable to find something that matched.

Thank you for looking into this,
Bob

Environment
Excel 2007 (12.0.6565.5003) SP2
Perl version : 5.012004
OS name : MSWin32, Win7
Module versions: (not all are required)
Excel::Writer::XLSX 0.45
Parse::RecDescent 1.965001
File::Temp 0.22
OLE::Storage_Lite 0.19
IO::Stringy 2.110

Program
##########
# create 2 files
# one with bug, one without
##########

use strict;
use warnings;

use Excel::Writer::XLSX;

my %oBugs = (Works => 65530, Fails => 65531);
foreach my $sKey (keys %oBugs)
{
my $sFile = "Hyperlink-$sKey.xlsx";
print "Creating $sFile\n";
my $oFile = Excel::Writer::XLSX->new ($sFile);
my $oFmtLink = $oFile->add_format ();
$oFmtLink->set_format_properties (color => 'blue', underline => 2);
my $oSheet = $oFile->add_worksheet ('Hyperlinks');
for (my $nRow = 0; $nRow < $oBugs {$sKey}; $nRow++)
{
$oSheet->write_url ($nRow, 0, sprintf ('internal:B%d', $nRow + 1),
$nRow + 1, $oFmtLink);
}
$oFile->close ();
}

[jmcnamara]

Hi Bob,

First off, that is a great bug report. It couldn't be better.

I'll dig into this a little later but you may be hitting an
undocumented Excel limit here. I remember something like this from
Spreadsheet::WriteExcel.

You can test this yourself in Excel. See if you can create and save a
workbook with more than 65k hyperlinks.

I'll test this myself a little later.

John.
--

[rboisvert]

John,

I don't think it's an Excel 2007 limitation because 1) I can create a
file with more links using Excel and 2) Microsoft doesn't identify it
as a limit (see http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx).

Thanks,
Bob

[jmcnamara]

On Feb 9, 8:51 pm, rboisvert <rdboisv...@gmail.com> wrote:
> John,
>
> I don't think it's an Excel 2007 limitation because 1) I can create a
> file with more links using Excel and 2) Microsoft doesn't identify it

> as a limit (seehttp://office.microsoft.com/en-us/excel-help/excel-specifications-and...).

Hi Bob,

In relation to 2) Not all of Excel's limitations are documented
unfortunately.

In relation to 1) I was able to create more than 65,530 non-unique
hyperlinks but I wasn't able to create more than 65,530 unique links.
I verified this as follows:

1. Opened your Hyperlink-Works.xlsx generated file in Excel 2007.
2. Moved to cell B65,531 (although any empty cell is probably
okay).
3. Tried to insert a new hyperlink (www.perl.com).
4. Excel inserted a string and not a link.
5. Deleted the link in cell 65,530.
6. Moved back to cell B65,531.
7. Successfully inserted a new hyperlink.


Google is quiet on the issue but there are some people reporting this
issue in Excel 2007 (and before) such as this one:

http://www.excelforum.com/excel-programming/787677-hyperlink-issue-with-excel-2007-a.html

So, I think this is an Excel limitation.

Regards,

John.
--

[rboisvert]

John,

You are correct on both counts. :{)

Excel is somewhat inconsistent in the way it deals with the limit. I
was able in one instance to create the 65,531st link but it
disappeared after I made other changes. I found a post by another
programmer that confirms the same 65,530 link limit (http://
www.excelforum.com/excel-programming/787677-hyperlink-issue-with-excel-2007-a.html)
so I would agree that it seems to be an undocumented Excel
limitation. That article suggested a workaround which I may try.

Thank you for your help in resolving this issue and for taking on
support of Excel::Writer. I'm sure many other programmers also
appreciate your efforts.
Bob

>  http://www.excelforum.com/excel-programming/787677-hyperlink-issue-wi...