Problem with Excel export with specific hyperlink

Question

Bug introduced in 8 or earlier and persisting through 12.2

Exporting Hyperlink[] to XLSX works usually just fine, but with this particular link

problem = Hyperlink["http://www.faulbach.de/komxpress/DynDox.asp?Orgid={F527CBB0-4493-42D0-AAD6-149A10DD8D88}&LinkID={58899F1E-D42B-481F-B507-C3A9B136A780}"];
Export["myfile.xlsx", problem]

I get the error message

Export::fmterr: Invalid XLSX format.

Other Hyperlinks that cannot be exported to Excel: Link1, Link2, Link3, Link4, Link5.

I am using "11.0.1 for Microsoft Windows (64-bit) (September 20, 2016)".

Looks like a bug. It works if you Export with extension ".xls": Export["myfile.xls", problem]. — Alexey Popkov, Apr 18 '17 at 15:22
It seems that the problem is the presence of "{" and "}" in the URL: removing these symbols makes Export working. — Alexey Popkov, Apr 18 '17 at 15:27
Right, the second URL works. I check again. The workaround with XLS helps a lot. — Karsten W., Apr 18 '17 at 15:43
I encountered another error when the link contained a space. I thought it would be impossible for an URL to contain a space, but it is not. However, for saving it to xlsx, it needs to be escaped as %20. — Karsten W., May 04 '17 at 13:02
I updated the answer. Have you reported the original bug in Export? — Alexey Popkov, May 04 '17 at 14:11
No, I just did report it now. Thanks for the workaround, again. — Karsten W., May 04 '17 at 16:27

score 5 · Accepted Answer · edited Jun 16 '20 at 09:23

It is a bug.

The source of the problem is the presence of symbols "{", "}", "[", "]" or "\\" in the URL (the list may be incomplete).

The simplest (and seemingly universal) workaround is to Export as XLS instead of XLSX:

Export["myfile.xls", problem]

Another method is to escape the problematic symbols (may not work for local file paths):

Export["myfile.xlsx", 
 problem /. s_String :> 
   StringReplace[s, {"{" -> "%7B", "}" -> "%7D", "[" -> "%5B", "]" -> "%5D"}]]

For local file paths backslashes should be replaced by forward slashes as shown here.

UPDATE

It seems that in the most cases it is sufficient is to pass URL through URLParse/URLBuild route:

urls={
"http://www.faulbach.de/komxpress/DynDox.asp?Orgid={F527CBB0-4493-42D0-AAD6-149A10DD8D88}&LinkID={58899F1E-D42B-481F-B507-C3A9B136A780}",
"http://www.metropolregion-nordwest.de/downloads/datei/OTAwMDAwNDQ4Oy07L3Vzci9sb2NhbC9odHRwZC92aHRkb2NzL2Ntc3gvbWV0cm9wb2xyZWdpb24vbWVkaWVuL2Rva3VtZW50ZS9wcmFlc2VudGF0aW9uX25ldHprb256ZXB0aW9uXzIwMzBbMV0ucGRm/praesentation_netzkonzeption_2030[1].pdf",
"http://www.sueddeutsches-institut.de/ENI/PriceWaterhouseCoopers [2006] Die wirtschaftlichen Chancen alternativer Antriebsenergien am Beispiel des Hybridantriebs.pdf"};
encodedURLs = URLBuild /@ URLParse[urls];
Export["myfile.xlsx", Hyperlink /@ encodedURLs]

But it doesn't currently work for URLs containing a "Fragment" what may indicate another bug:

URLParse["http://www.contractawards.eu/#!supplier=„Интер Бизнес 91\" ЕООД, ЕИК 010652246"]
URLBuild@%

<|"Scheme" -> "http", "User" -> None, "Domain" -> "www.contractawards.eu", "Port" -> None,
  "Path" -> {"", ""}, "Query" -> {}, 
 "Fragment" -> "!supplier=„Интер Бизнес 91\" ЕООД, ЕИК 010652246"|>
"http://www.contractawards.eu/#!supplier=„Интер Бизнес 91&quot; ЕООД, ЕИК 010652246"

One can try to use URLEncode to force encoding of the "Fragment".

Interesting, this is an old bug. In Mathematica 8.0.4 on Win7x64, when I enter Export["myfile.xlsx", problem], I get the error message Export::fmterr: Invalid XLS format (sic! XLS, not XLSX), while the documentation states "Import and Export are fully compatible with the Excel 2007 standard". The input Export["myfile.xls", problem] works fine. — innaiz, Apr 18 '17 at 18:31

Problem with Excel export with specific hyperlink

1 Answers1

UPDATE

Linked