This project has moved. For the latest updates, please go here.

Relative Uri's in Cell Hyperlinks

Sep 30, 2010 at 11:22 AM

Hi Jan,

I had a requirement to add hyperlinks pointing to files/folders located on the same folder as the excel file I have was creating.

By the latest source of EPPlus (66455) this is not possible because Hyperlink property is expecting an absolute Uri.

I've made the required modifications on source code to acomplish my requirement and want to share it with you, so you can replicate it to EPPlus.

Modifications on file ExcelCell.cs:

public Uri Hyperlink
{
	get
	{				
		return (_hyperlink);
	}
	set
	{
		_hyperlink = value;
		if ((Value == null || Value.ToString() == ""))
		{
			if (value is ExcelHyperLink)
			{
				Value = (value as ExcelHyperLink).Display;
			}
			else
			{
				//test for absolute or relative URL
				if (_hyperlink.IsAbsoluteUri)
					Value = _hyperlink.AbsoluteUri;
				else
					Value = _hyperlink.OriginalString;
			}
		}
	}
}

Modifications on file ExcelWorksheet.cs:

private void UpdateHyperLinks(StreamWriter sw)
{
		sw.Write("<hyperlinks>");
		Dictionary<string, string> hyps = new Dictionary<string, string>();
		foreach (ulong cellId in _hyperLinkCells)
		{
			ExcelCell cell = _cells[cellId] as ExcelCell;
			if (cell.Hyperlink is ExcelHyperLink && (cell.Hyperlink as ExcelHyperLink).ReferenceAddress != "")
			{
				ExcelHyperLink hl = cell.Hyperlink as ExcelHyperLink;
				sw.Write("<hyperlink ref=\"{0}\" location=\"{1}\" display=\"{2}\" />", 
						Cells[cell.Row, cell.Column, cell.Row+hl.RowSpann, cell.Column+hl.ColSpann].Address, 
						ExcelCell.GetFullAddress(Name, hl.ReferenceAddress),
						SecurityElement.Escape(hl.Display));

			}
			else
			{
				string id;
				//test for absolute or relative URL
				if (cell.Hyperlink.IsAbsoluteUri && hyps.ContainsKey(cell.Hyperlink.AbsolutePath))
				{
					id = hyps[cell.Hyperlink.AbsolutePath];
				}
				else if (!cell.Hyperlink.IsAbsoluteUri && hyps.ContainsKey(cell.Hyperlink.OriginalString))
				{
					id = hyps[cell.Hyperlink.OriginalString];
				}
				else
				{
					PackageRelationship relationship = Part.CreateRelationship(cell.Hyperlink, TargetMode.External, ExcelPackage.schemaHyperlink);
					sw.Write("<hyperlink ref=\"{0}\" r:id=\"{1}\" />",cell.CellAddress, relationship.Id);

					id = relationship.Id;
				}
				cell.HyperLinkRId = id;
			}
		}
		sw.Write("</hyperlinks>");
}
I haven't tested it thoroughly, but it accomplishes my goals: adding relative hyperlinks.
Keep with the amazing work you've been doing.
Regards.
Coordinator
Sep 30, 2010 at 7:56 PM

Thanx, I'll add this next time I'll check in.