Post
Ask Question

How To Create A Hyperlink Using Excel Hyperlink() Function In EPPlus .NET Application (C#) Part - Seven

Please download the EPPlus .NET library from the link - http://epplus.codeplex.com/ First of all, if we create a Hyperlink, using function, first we need to know the formula.

What is an Excel formula ?

  • A formula is an expression, which calculates the value of the cell. A formula performs the other action on the data in your worksheet. A formula always starts with an equal sign(=), which can be followed by the numbers, mathematical operators (like a '+' or '-' sign for addition or subtraction) and some built-in Excel functions, which can really expand the power of a formula.

What is an Excel function ?

  • A function is predefined in formulas and are already available in Excel or spreadsheets like SUM(), COUNT(), HYPERLINK() etc. These built-in functions are used for the specific purpose.
What is HYPERLINK() Function ?



HYPERLINK() is a predefined Excel function & it takes two parameters. First is link location & second is friendly name or display name. When we are clicking it, it will redirect to the link location.

Hyperlink function uses Formula property of ExcelRange class given below. 
  • ExcelRange Rng = wsSheet1.Cells["B19"];
  • String SiteLink = "https://www.google.com";
  • String DisTxt = "Go to GOOGLE";
  • Rng.Formula = "=HYPERLINK(\"" + SiteLink + "\", \"" + DisTxt + "\")";
In the above example, we can see formula property. By using formula property, we can set cell formula in an Excel sheet. It accepts a string as a formula. In Part 4 of this tutorial, we know that EPPlus don't have formula calculation engine, so if you type a wrong Excel formula in the code, the compiler does not show any compilation error but generated Excel sheet will show an error.

Hyperlink function works on
  • To another cell of an existing sheet.
  • To different sheet within same Excel file.
  • To any local file.
  • To any remote Server file.
  • To link with an E-mail address.
To another cell of an existing sheet
  • ExcelRange Rng = wsSheet1.Cells["B20"];
  • String Sht1_B10 = "#'Sheet1'!B2";
  • String B10 = "Go to Cell B2";
  • Rng.Formula = "=HYPERLINK(\"" + Sht1_B10 + "\", \"" + B10 +"\")";
Another sheet within same Excel file,
  • ExcelRange Rng = wsSheet1.Cells["B21"];
  • String Sht2_B1= "#'Sheet2'!B2";
  • String B1 = "Go to Cell B1 in Sheet2";
  • Rng.Formula = "=HYPERLINK(\"" + Sht2_B1 + "\", \"" + B1 +"\")";
To any local file,
  • ExcelRange Rng = wsSheet1.Cells["B22"];
  • String Local_File = @"D:\Sample.xlsx";
  • String File = "D:\\Sample.xlsx";
  • Rng.Formula = "=HYPERLINK(\"" + Local_File + "\", \"" + File + "\")";
To any remote Server file,
  • ExcelRange Rng = wsSheet1.Cells["B23"];
  • String AbsoLnk = "https://goo.gl/gOa0wm";
  • Rng.Formula = "=HYPERLINK(\"" + AbsoLnk+ "\", \"" + AbsoLnk + "\")";
To link with an E-mail address,
  • ExcelRange Rng = wsSheet1.Cells["B24"];
  • String MailLnk = "mailto:[email protected]";
  • String MailID = "[email protected]";
  • Rng.Formula = "=HYPERLINK(\"" + MailLnk + "\", \"" + MailID + "\")";
Output in an Excel sheet is given below.


Source code
  1. using System;  
  2. using OfficeOpenXml;  
  3. using System.IO;  
  4. using System.Drawing;  
  5. using OfficeOpenXml.Style;  
  6. using OfficeOpenXml.Style.XmlAccess;  
  7. namespace DemoEpplus {  
  8.     class Program {  
  9.         static void Main(string[] args) {  
  10.             ExcelPackage ExcelPkg = new ExcelPackage();  
  11.             ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");  
  12.             ExcelWorksheet wsSheet2 = ExcelPkg.Workbook.Worksheets.Add("Sheet2");  
  13.             using(ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2]) {  
  14.                 Rng.Value = "Everyday Be Coding - Excel HYPERLINK using EPPlus .Net Library";  
  15.                 Rng.Style.Font.Size = 16;  
  16.                 Rng.Style.Font.Bold = true;  
  17.                 Rng.Style.Font.Italic = true;  
  18.                 Rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;  
  19.                 Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;  
  20.                 Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;  
  21.                 Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;  
  22.             }  
  23.             //SET HYPERLINK STYLE-----------------------  
  24.             string StyleName = "HyperStyle";  
  25.             ExcelNamedStyleXml HyperStyle = wsSheet1.Workbook.Styles.CreateNamedStyle(StyleName);  
  26.             HyperStyle.Style.Font.UnderLine = true;  
  27.             HyperStyle.Style.Font.Size = 12;  
  28.             HyperStyle.Style.Font.Color.SetColor(Color.Blue);  
  29.             //------CREATE HYPERLINK USING EPPLUS HYPERLINK PROPERTY--------------  
  30.             using(ExcelRange Rng = wsSheet1.Cells[4, 1, 4, 1]) {  
  31.                 Rng.Value = "Option-1:";  
  32.                 Rng.Style.Font.Size = 13;  
  33.                 Rng.Style.Font.Bold = true;  
  34.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  35.             }  
  36.             using(ExcelRange Rng = wsSheet1.Cells[4, 2, 4, 2]) {  
  37.                 Rng.Value = "Using EPPlus Hyperlink property of ExcelRange class";  
  38.                 Rng.Style.Font.Size = 13;  
  39.             }  
  40.             //------HYPERLINK to a website.  
  41.             using(ExcelRange Rng = wsSheet1.Cells[6, 2, 6, 2]) {  
  42.                 Rng.Hyperlink = new Uri("http://www.google.com", UriKind.Absolute);  
  43.                 Rng.Value = "Go to GOOGLE";  
  44.                 Rng.StyleName = StyleName;  
  45.             }  
  46.             //------HYPERLINK to a cell within same sheet.  
  47.             using(ExcelRange Rng = wsSheet1.Cells[7, 2, 7, 2]) {  
  48.                 Rng.Hyperlink = new Uri("#'Sheet1'!B2", UriKind.Relative);  
  49.                 Rng.Value = "Go to Cell B2";  
  50.                 Rng.StyleName = StyleName;  
  51.             }  
  52.             //------HYPERLINK to another sheet within same excel file.  
  53.             using(ExcelRange Rng = wsSheet1.Cells[8, 2, 8, 2]) {  
  54.                 Rng.Hyperlink = new Uri("#'Sheet2'!B1", UriKind.Relative);  
  55.                 Rng.Value = "Go to Cell B1 in Sheet2";  
  56.                 Rng.StyleName = StyleName;  
  57.             }  
  58.             //------HYPERLINK with any local file.  
  59.             using(ExcelRange Rng = wsSheet1.Cells[9, 2, 9, 2]) {  
  60.                 Rng.Hyperlink = new Uri(@ "D:\sample.xlsx");  
  61.                 Rng.Value = "D:\\sample.xlsx";  
  62.                 Rng.StyleName = StyleName;  
  63.             }  
  64.             //------HYPERLINK with any remote server file.  
  65.             using(ExcelRange Rng = wsSheet1.Cells[10, 2, 10, 2]) {  
  66.                 Rng.Hyperlink = new Uri("https://goo.gl/gOa0wm", UriKind.Absolute);  
  67.                 Rng.Value = "https://goo.gl/gOa0wm";  
  68.                 Rng.StyleName = StyleName;  
  69.             }  
  70.             using(ExcelRange Rng = wsSheet1.Cells[11, 2, 11, 2]) {  
  71.                 Rng.Hyperlink = new Uri("mailto:[email protected]", UriKind.Absolute);  
  72.                 Rng.Value = "[email protected]";  
  73.                 Rng.StyleName = StyleName;  
  74.             }  
  75.             //------CREATE HYPERLINK USING EPPLUS wsSheet1.Drawings.AddPicture() Method  
  76.             using(ExcelRange Rng = wsSheet1.Cells[13, 1, 13, 1]) {  
  77.                 Rng.Value = "Option-2:";  
  78.                 Rng.Style.Font.Size = 13;  
  79.                 Rng.Style.Font.Bold = true;  
  80.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  81.             }  
  82.             using(ExcelRange Rng = wsSheet1.Cells[13, 2, 13, 2]) {  
  83.                 Rng.Value = "Using Epplus AddPicture() Method of ExcelDrawings class";  
  84.                 Rng.Style.Font.Size = 13;  
  85.             }  
  86.             Image img = Image.FromFile(@ "D:\EverydayBeCoding.png");  
  87.             ExcelPicture pic = wsSheet1.Drawings.AddPicture("Picture_Name", img, new Uri("http://www.google.com"));  
  88.             //ExcelPicture img = wsSheet1.Drawings.AddPicture("Picture_Name", new FileInfo(@"D:\EverydayBeCoding.png"), new Uri("http://www.google.com"));  
  89.             pic.SetPosition(14, 0, 1, 0);  
  90.             pic.SetSize(148, 26);  
  91.             //---CREATE HYPERLINK USING HYPERLINK FUNCTION  
  92.             using(ExcelRange Rng = wsSheet1.Cells[17, 1, 17, 1]) {  
  93.                 Rng.Value = "Option-3:";  
  94.                 Rng.Style.Font.Bold = true;  
  95.                 Rng.Style.Font.Size = 13;  
  96.                 Rng.Style.Font.Color.SetColor(Color.Red);  
  97.             }  
  98.             using(ExcelRange Rng = wsSheet1.Cells[17, 2, 17, 2]) {  
  99.                 Rng.Value = "Using Excel Hyperlink Function in the formula property of ExcelRange class";  
  100.                 Rng.Style.Font.Size = 13;  
  101.             }  
  102.             //HYPERLINK to a website.  
  103.             string SiteLink = "https://www.google.com";  
  104.             string DisTxt = "Go to GOOGLE";  
  105.             using(ExcelRange Rng = wsSheet1.Cells[19, 2, 19, 2]) {  
  106.                 Rng.Formula = "=HYPERLINK(\"" + SiteLink + "\", \"" + DisTxt + "\")";  
  107.                 Rng.StyleName = StyleName;  
  108.             }  
  109.             //HYPERLINK to a cell within same sheet.  
  110.             string Sht1_B10 = "#'Sheet1'!B2";  
  111.             string B10 = "Go to Cell B2";  
  112.             using(ExcelRange Rng = wsSheet1.Cells[20, 2, 20, 2]) {  
  113.                 Rng.Formula = "=HYPERLINK(\"" + Sht1_B10 + "\", \"" + B10 + "\")";  
  114.                 Rng.StyleName = StyleName;  
  115.             }  
  116.             //HYPERLINK to another sheet within same excel file.  
  117.             string Sht2_B1 = "#'Sheet2'!B1";  
  118.             string B1 = "Go to Cell B1 in Sheet2";  
  119.             using(ExcelRange Rng = wsSheet1.Cells[21, 2, 21, 2]) {  
  120.                 Rng.Formula = "=HYPERLINK(\"" + Sht2_B1 + "\", \"" + B1 + "\")";  
  121.                 Rng.StyleName = StyleName;  
  122.             }  
  123.             //HYPERLINK with any local file.  
  124.             string Local_File = @ "D:\sample.xlsx";  
  125.             string File = "D:\\sample.xlsx";  
  126.             using(ExcelRange Rng = wsSheet1.Cells[22, 2, 22, 2]) {  
  127.                 Rng.Formula = "=HYPERLINK(\"" + Local_File + "\", \"" + File + "\")";  
  128.                 Rng.StyleName = StyleName;  
  129.             }  
  130.             //------HYPERLINK with any remote server file.  
  131.             string AbsoLnk = "https://goo.gl/gOa0wm";  
  132.             using(ExcelRange Rng = wsSheet1.Cells[23, 2, 23, 2]) {  
  133.                 Rng.Formula = "=HYPERLINK(\"" + AbsoLnk + "\", \"" + AbsoLnk + "\")";  
  134.                 Rng.StyleName = StyleName;  
  135.             }  
  136.             string MailLnk = "mailto:[email protected]";  
  137.             string MailID = "[email protected]";  
  138.             using(ExcelRange Rng = wsSheet1.Cells[24, 2, 24, 2]) {  
  139.                 Rng.Formula = "=HYPERLINK(\"" + MailLnk + "\", \"" + MailID + "\")";  
  140.                 Rng.StyleName = StyleName;  
  141.             }  
  142.             wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();  
  143.             ExcelPkg.SaveAs(new FileInfo(@ "D:\Hyperlink.xlsx"));  
  144.         }  
  145.     }  
  146. }  
  • Now, build & execute the code. File is (Hyperlink.xlsx) stored on D: drive of the computer.
Thank you for reading this blog.
Next Recommended Reading How To Format Excel Table Using EPPlus Library Part Fifteen - C#