Unable to read/write a hyperlink to an external file with a bookmark

[michaelgorman]

Read and complete the full issue template

Do not randomly delete sections. They are here for a reason.

Do you want to request a feature or report a bug?

• [x] Bug
• [ ] Feature
• [ ] Question

Did you test against the latest CI build?

• [X] Yes
• [ ] No

If you answered No, please test with the latest development build first.

Version of ClosedXML

0.95.1

What is the current behavior? I looked at https://github.com/ClosedXML/ClosedXML/wiki/Using-Hyperlinks but there is no example to create a hyperlink to an external doc. I'm Unable to get the bookmark in a hyperlink that is bookmarked to a word doc The Book1.xlsm files includes some vba code that creates the hyperlink Adding a hash to the end of the file name directs to a bookmark within the word doc That bookmark is stored in the SubAddress property of the excel hyperlink

What is the expected behavior or new feature?

to get the bookmark value from the SubAddress property

Is this a regression from the previous version?

no

Reproducibility

This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.

Without a code sample, it is unlikely that your issue will get attention. Don't be lazy. Do the effort and assist the developers to reproduce your problem. Code samples should be minimal complete and verifiable. Sample spreadsheets should be attached whenever applicable. Remove sensitive information.

Code to reproduce problem:

using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using ClosedXML.Excel;

namespace WindowsFormsApp3
{
   static class Program
   {
      /// <summary>
      /// The main entry point for the application.
      /// </summary>
      [STAThread]
      static void Main()
      {
         Application.EnableVisualStyles();
         Application.SetCompatibleTextRenderingDefault(false);

         //string fileName = Path.Combine(Path.GetDirectoryName( Application.ExecutablePath), "TestCaseTemplate1.xlsx");
         string fileName = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "Book1.xlsm");
         using (var wb = new XLWorkbook(fileName))
         {
            var cell1 = wb.Worksheet(1).Cell(1, 1);
            var cell2 = wb.Worksheet(1).Cell(2, 1);
            var cell3 = wb.Worksheet(1).Cell(2, 2);
            var path = GetHyperlinkValue(cell1);


            Int32 pos = path.IndexOf("#");
            String file = (pos > 0 ? path.Substring(0, pos) : path);
            String bookmark = (pos > 0 ? path.Substring(pos + 1) : null);
            bookmark = "b3";
            AddHyperlink(cell3, path, "foo", bookmark);

            wb.Save();
            Console.WriteLine(path);
         }
      }

      public static String GetHyperlinkValue(IXLCell cell)
      {
         if (cell != null && cell.HasHyperlink)
         {
            //if (!String.IsNullOrEmpty(cell.Hyperlink.Tooltip))
            //   return cell.Hyperlink.Tooltip;
            if (!String.IsNullOrEmpty(cell.Hyperlink.ExternalAddress?.OriginalString))
               return cell.Hyperlink.ExternalAddress.OriginalString;
            if (!String.IsNullOrEmpty(cell.Hyperlink.InternalAddress))
               return cell.Hyperlink.InternalAddress;
         }
         return null;
      }

      public static void AddHyperlink(IXLCell cell, String path, String displayText, String bookmark)
      {
         if (!String.IsNullOrWhiteSpace(path))
         {
            //file = System.Net.WebUtility.UrlDecode(file);
            cell.Hyperlink = new XLHyperlink($"{path}!{bookmark}");
            cell.Hyperlink.Tooltip = path;
            cell.SetValue<String>(displayText);
         }
      }
   }
}
 

you will need to remove the txt ext cause github wont allow xlsm If you dont want to open the xlsm just copy the vba code below and paste into a new excel doc Book1.xlsm.txt

Private Sub foo()
    TCPath = "doc1.docx#b3"
    
    Set Cell = Sheet1.Cells(1, 1)
    
    For Each h In Cell.Hyperlinks: h.Delete: Next
    
    Call Sheet1.Cells(1, 1).Hyperlinks.Add(Sheet1.Cells(1, 1), TCPath)
    Cell.Hyperlinks(1).ScreenTip = "tooltip"
    Cell.Hyperlinks(1).TextToDisplay = "display"
    Debug.Print Cell.Hyperlinks(1).SubAddress
End Sub

doc1.docx

• [x] I attached a sample spreadsheet. (You can drag files on to this issue)