6
$\begingroup$

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

Improve this question
$\endgroup$
6
  • 1
    $\begingroup$ Looks like a bug. It works if you Export with extension ".xls": Export["myfile.xls", problem]. $\endgroup$
    – Alexey Popkov
    Apr 18 '17 at 15:22
  • $\begingroup$ It seems that the problem is the presence of "{" and "}" in the URL: removing these symbols makes Export working. $\endgroup$
    – Alexey Popkov
    Apr 18 '17 at 15:27
  • $\begingroup$ Right, the second URL works. I check again. The workaround with XLS helps a lot. $\endgroup$
    – Karsten W.
    Apr 18 '17 at 15:43
  • $\begingroup$ 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. $\endgroup$
    – Karsten W.
    May 4 '17 at 13:02
  • 1
    $\begingroup$ I updated the answer. Have you reported the original bug in Export? $\endgroup$
    – Alexey Popkov
    May 4 '17 at 14:11
5
$\begingroup$

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

Improve this answer
$\endgroup$
2
  • 2
    $\begingroup$ 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. $\endgroup$
    – innaiz
    Apr 18 '17 at 18:31
  • $\begingroup$ @innaiz Thanks, added this info to the bug header. $\endgroup$
    – Alexey Popkov
    Apr 18 '17 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.