Add hyper link in Excel using EPPlus

Many People has asked me that how they can add hyper link to another sheet through EPPlus. This is bit tricky because ws.Cells[1,1].Hyperlink takes Uri object which is generally used for external references only (file or website).

So after doing bit RnD I have developed a function through which you can easily add hyperlinks within your sheets. Here is the function:-

/// 

/// Adds the hyper link.
/// 
/// "ws">The ws.
/// "source">The source.
/// "destination">The destination.
/// "displayText">The display text.
private static void AddHyperLink(ExcelWorksheet ws, ExcelRange source, ExcelRange destination, string displayText)
{
	source.Formula = "HYPERLINK(\"[\"&MID(CELL(\"filename\"),SEARCH(\"[\",CELL(\"filename\"))+1, SEARCH(\"]\",CELL(\"filename\"))-SEARCH(\"[\",CELL(\"filename\"))-1)&\"]" + destination.FullAddress + "\",\"" + displayText + "\")";
}

Here is how you can call above function to add hyperlinks in your cell:-

ExcelWorksheet ws = CreateSheet(p,"Sample Sheet");
ExcelWorksheet sheet2 = CreateSheet(p, "Sample Sheet 2");
sheet2.Cells[1, 1].Value = "Hyperlink Target";
AddHyperLink(ws, ws.Cells[13, 1], sheet2.Cells[1,1], "Sample Hyperlink");

Hopefully this tip will be useful for you, Happy Coding !!!