Invalid Hyperlink: Malformed URI is embedded as a hyperlink in the document

Question

I'm using the OpenXml namespace in my application. I'm using this to read the XML within an Excel file. This works fine with certain excel files but on others I get a run time error saying

Invalid Hyperlink: Malformed URI is embedded as a hyperlink in the document.

I get the run time on the following line

using (var spreadsheet = 
      DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(filePathCopy, true))

I'm not sure why it works for some Excel files and doesn't work on others.

Answer 1

Solution is from Eric White's blog post

1. Import OpenXmlPowerTools from Nuget and use it.

   using OpenXmlPowerTools;
   

   The method needed is OpenXmlPowerTools.UriFixer.FixInvalidUri, or you could copy the UriFixer class from the link.

2. Add the FixUri() Function to handle the broken URI's with a new defined URI.

   private static Uri FixUri(string brokenUri)
   {
       return new Uri("http://broken-link/");
   }
   

2. Add code to open the document, if the exception occurs it fixes the URI's and re-opens the fixed document.

   WordprocessingDocument wDoc;
   try
   {
       using (wDoc = WordprocessingDocument.Open(newFileName, true))
       {
           //Try do something
       }
   }
   catch (OpenXmlPackageException e)
   {
       if (e.ToString().Contains("Invalid Hyperlink"))
       {
           using (FileStream fs = new FileStream(newFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
           {
               //Fix problematic URI's
               UriFixer.FixInvalidUri(fs, brokenUri => FixUri(brokenUri));
           }
           using (wDoc = WordprocessingDocument.Open(newFileName, true))
           {
               //Do something without error
           }
       }
   }
   

Answer 2

You can also fix the Excel worksheet in Excel with a quick VB script:

Easy way to remove Hyperlinks from Excel sheet:

• Open worksheet.

• Press Alt + F11 to open the VB macro editor.

• Add the VB sub below, then Save. If you don't want to save the macro - since you're probably only doing this once - ignore the macro-free worksheet errors if prompted.

• Close, then go to Macros and run the macro.

  Sub RemoveHyperlinks()

  ' Remove all hyperlinks from the active sheet. ActiveSheet.Hyperlinks.Delete

  End Sub

Based on: https://www.youtube.com/watch?time_continue=4&v=w8LyIJWIGj0&feature=emb_title