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)".

Answer 1

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\" ЕООД, ЕИК 010652246"

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