ClosedXML / ClosedXML/ClosedXML
Unable to read/write a hyperlink to an external file with a bookmark
- 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
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