Invalid xlsx hyperlinks in Office 2007

[d3nton]

Hi.

xlsx files with blanks in the hyperlinks target name seems not to be valid in Office 2007.
If I create a hyperlink with XLSReadWrite with address e.g. 'C:\New folder', the xlsx file is created correctly and can be opened in
Excel 2010. But Excel 2007 displayes an error saying that the file was corrupted.
After extracting the xlsx file, I found out that in folder "\xl\worksheets\_rels" the *.xml.rels file
contains Target="file:///C:\New folder\".
If I create the same hyperlink in Excel itsself, the target is Target="file:///C:\New%20folder\"

It seems that Office 2007 does not support empty spaces in the hyperlink target. You have to use '%20' as whitespace.
As a workaround I replaced all whitespaces with '%20'in my code before setting the address property of a hyperlink, which fixes the error.
But it would be nice, if you could include
such a fix in your component.

Best regards

[larsa]

Hello

Thank you. This fix will be included in the next update.

[d3nton]

Thank you for the fix. Works well.
Unfortunately it seems that there are also other characters which cannot be read in Excel 2007:
Here is a (incomplete) list:

• '%' has to be replaced with %25
  '[' has to be replaced with %5b
  ']' has to be replaced with %5d
  '^' has to be replaced with %5e
  '`' has to be replaced with %60
  '{' has to be replaced with %7b
  '}' has to be replaced with %7d
  ....

I used this website to find out the correct replacements:
http://schneegans.de/asp.net/url-escape/
It seems that the first column (Uri.EscapeUriString) displayed the correct replacements.
(successfully tested for the symbols above)
Could you please add the fixes to XLSReadWrite?
Thank you.

Best regards.

[larsa]

Hello

This is fixed in update. 5.20.03

[d3nton]

Hi!

Thank you very much for the fix.
Unfortunately old hyperlinks are not recognized correctly if you add hyperlinks to an already existing file with hyperlinks
which has previously been created with XLSReadWrite. This worked in XLSReadWrite 5.20.02.
I have seen that you implemented the fix in XLSReadWriteOPC5.pas function TOPC_XLSX.CreateHyperlink.
The problem seems to be, that in this function the replacements are not interpreted correctly if they are read by XLSReadWrite.

[larsa]

Hello

The problem was that characters in the url prefix (file:///) was translated as well. Fixed in update 5.20.11

[d3nton]

In the current release 5.20.17 the xlsx files are corrupted in Excel2007 for UNC path hyperlinks with empty spaces.
In XLSHyperlinks5.pas TXLSHyperlink.SetAddress the hyperlink is not encoded for UNC paths:
line 265:

Code: Select all

else if BeginWith('\\') then begin
    FHyperlinkType := xhltUNC;
  end
...
 FAddress := S;

should be:

Code: Select all

  else if BeginWith('\\') then begin
    FHyperlinkType := xhltUNC;
    S := 'file:///' + EncodeFileHLink(S); //Excel adds file:/// to unc paths like: file:///\\intranet\temp\ , if you create an 'native' UNC hyperlink in Excel itsself
  end

Could you please fix this.
Tank you.

[larsa]

Hello

Fixed in update 5.20.19

[d3nton]

Hi!

I am sorry, that I have to open this ticket again but there is still a problem with the hyperlinks.
The function TXLSHyperlink.EncodeFileHLink, which obviously handles the hyperlink addresses,
is called each time I create a hyperlink. But it is also called if I read the file which corrupts some hyperlinks
An example:
Hyperlink "file:///C:\New folder\" will be converted to "file:///C:\New%20folder\" if I write a hyperlink. Correct so far.
Since TXLSHyperlink.EncodeFileHLink contains the case '%': Result := Result + '%25'; the hyperlink
"file:///C:\New%20folder\ will further be converted to "file:///C:\New%2520folder\ if I read the file, which corrupts the hyperlink.

Can you please fix this?
Thank you!

[larsa]

Hello

This is fixed in update 5.20.37

[d3nton]

Thank you!
But the fix seems not to be correct.
I think % must be treated like this:

Code: Select all

    
   %': begin
        if (StrToIntDef('$'+Copy(ALink,i + 1,2),MAXINT) = MAXINT) and not (Copy(ALink,i + 1,2)= '25')  then
          Result := Result + '%25'
        else
         Result := Result + ALink[i];
      end;

You need '$' because the values are stored in hex. Otherwise StrToIntDef returns MAXINT for other sepcial characters e.g. '+' ( = %2B) which would be wrong.
Also a string like 'Hello%25hello' should not be converted at all. The else condition is also necessary, otherwise a single % will be lost.

Can you please fix this.
Thank you.

[larsa]

Hello

Your fix is included in update 5.20.38

[d3nton]

Thank you!