ClosedXML / ClosedXML/ClosedXML

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

Open
#1,827 0 comments 1 reaction 0 assignees View on GitHub
Dominant language
C#
Stars
5.7k
Forks
933
Avg merge
12h 14m
Merged PRs (30d)
1

Description

## 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](https://ci.appveyor.com/project/ClosedXML/ClosedXML/branch/develop/artifacts) 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](https://stackoverflow.com/help/mcve). Sample spreadsheets should be attached whenever applicable. Remove sensitive information.

**Code to reproduce problem:**
```c#
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
{
///
/// The main entry point for the application.
///
[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(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](https://github.com/ClosedXML/ClosedXML/files/8993015/Book1.xlsm.txt)

```vba
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](https://github.com/ClosedXML/ClosedXML/files/8993009/doc1.docx)

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

Contributor guide

Open the contributing guide

Research direction

Start by tracing XLHyperlink and IXLCell.Hyperlink handling for external addresses and SubAddress values, using the provided C# reproduction. Reproduce with Book1.xlsm and doc1.docx, then verify that reading and writing an external hyperlink preserves the bookmark value and that the sample opens correctly.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
backend
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.