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

Rajdip Sarkar
7y
5.8k
0
0

Blog

Hyperlink_inside_a_cell_in_excel_sh

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

using System;
using OfficeOpenXml;
using System.IO;
using System.Drawing;
using OfficeOpenXml.Style;
using OfficeOpenXml.Style.XmlAccess;
namespace DemoEpplus {
class Program {
static void Main(string[] args) {
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
ExcelWorksheet wsSheet2 = ExcelPkg.Workbook.Worksheets.Add("Sheet2");
using(ExcelRange Rng = wsSheet1.Cells[2, 2, 2, 2]) {
Rng.Value = "Everyday Be Coding - Excel HYPERLINK using EPPlus .Net Library";
Rng.Style.Font.Size = 16;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Italic = true;
Rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
Rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
//SET HYPERLINK STYLE-----------------------
string StyleName = "HyperStyle";
ExcelNamedStyleXml HyperStyle = wsSheet1.Workbook.Styles.CreateNamedStyle(StyleName);
HyperStyle.Style.Font.UnderLine = true;
HyperStyle.Style.Font.Size = 12;
HyperStyle.Style.Font.Color.SetColor(Color.Blue);
//------CREATE HYPERLINK USING EPPLUS HYPERLINK PROPERTY--------------
using(ExcelRange Rng = wsSheet1.Cells[4, 1, 4, 1]) {
Rng.Value = "Option-1:";
Rng.Style.Font.Size = 13;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Color.SetColor(Color.Red);
}
using(ExcelRange Rng = wsSheet1.Cells[4, 2, 4, 2]) {
Rng.Value = "Using EPPlus Hyperlink property of ExcelRange class";
Rng.Style.Font.Size = 13;
}
//------HYPERLINK to a website.
using(ExcelRange Rng = wsSheet1.Cells[6, 2, 6, 2]) {
Rng.Hyperlink = new Uri("http://www.google.com", UriKind.Absolute);
Rng.Value = "Go to GOOGLE";
Rng.StyleName = StyleName;
}
//------HYPERLINK to a cell within same sheet.
using(ExcelRange Rng = wsSheet1.Cells[7, 2, 7, 2]) {
Rng.Hyperlink = new Uri("#'Sheet1'!B2", UriKind.Relative);
Rng.Value = "Go to Cell B2";
Rng.StyleName = StyleName;
}
//------HYPERLINK to another sheet within same excel file.
using(ExcelRange Rng = wsSheet1.Cells[8, 2, 8, 2]) {
Rng.Hyperlink = new Uri("#'Sheet2'!B1", UriKind.Relative);
Rng.Value = "Go to Cell B1 in Sheet2";
Rng.StyleName = StyleName;
}
//------HYPERLINK with any local file.
using(ExcelRange Rng = wsSheet1.Cells[9, 2, 9, 2]) {
Rng.Hyperlink = new Uri(@ "D:\sample.xlsx");
Rng.Value = "D:\\sample.xlsx";
Rng.StyleName = StyleName;
}
//------HYPERLINK with any remote server file.
using(ExcelRange Rng = wsSheet1.Cells[10, 2, 10, 2]) {
Rng.Hyperlink = new Uri("https://goo.gl/gOa0wm", UriKind.Absolute);
Rng.Value = "https://goo.gl/gOa0wm";
Rng.StyleName = StyleName;
}
using(ExcelRange Rng = wsSheet1.Cells[11, 2, 11, 2]) {
Rng.Hyperlink = new Uri("mailto:[email protected]", UriKind.Absolute);
Rng.Value = "[email protected]";
Rng.StyleName = StyleName;
}
//------CREATE HYPERLINK USING EPPLUS wsSheet1.Drawings.AddPicture() Method
using(ExcelRange Rng = wsSheet1.Cells[13, 1, 13, 1]) {
Rng.Value = "Option-2:";
Rng.Style.Font.Size = 13;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Color.SetColor(Color.Red);
}
using(ExcelRange Rng = wsSheet1.Cells[13, 2, 13, 2]) {
Rng.Value = "Using Epplus AddPicture() Method of ExcelDrawings class";
Rng.Style.Font.Size = 13;
}
Image img = Image.FromFile(@ "D:\EverydayBeCoding.png");
ExcelPicture pic = wsSheet1.Drawings.AddPicture("Picture_Name", img, new Uri("http://www.google.com"));
//ExcelPicture img = wsSheet1.Drawings.AddPicture("Picture_Name", new FileInfo(@"D:\EverydayBeCoding.png"), new Uri("http://www.google.com"));
pic.SetPosition(14, 0, 1, 0);
pic.SetSize(148, 26);
//---CREATE HYPERLINK USING HYPERLINK FUNCTION
using(ExcelRange Rng = wsSheet1.Cells[17, 1, 17, 1]) {
Rng.Value = "Option-3:";
Rng.Style.Font.Bold = true;
Rng.Style.Font.Size = 13;
Rng.Style.Font.Color.SetColor(Color.Red);
}
using(ExcelRange Rng = wsSheet1.Cells[17, 2, 17, 2]) {
Rng.Value = "Using Excel Hyperlink Function in the formula property of ExcelRange class";
Rng.Style.Font.Size = 13;
}
//HYPERLINK to a website.
string SiteLink = "https://www.google.com";
string DisTxt = "Go to GOOGLE";
using(ExcelRange Rng = wsSheet1.Cells[19, 2, 19, 2]) {
Rng.Formula = "=HYPERLINK(\"" + SiteLink + "\", \"" + DisTxt + "\")";
Rng.StyleName = StyleName;
}
//HYPERLINK to a cell within same sheet.
string Sht1_B10 = "#'Sheet1'!B2";
string B10 = "Go to Cell B2";
using(ExcelRange Rng = wsSheet1.Cells[20, 2, 20, 2]) {
Rng.Formula = "=HYPERLINK(\"" + Sht1_B10 + "\", \"" + B10 + "\")";
Rng.StyleName = StyleName;
}
//HYPERLINK to another sheet within same excel file.
string Sht2_B1 = "#'Sheet2'!B1";
string B1 = "Go to Cell B1 in Sheet2";
using(ExcelRange Rng = wsSheet1.Cells[21, 2, 21, 2]) {
Rng.Formula = "=HYPERLINK(\"" + Sht2_B1 + "\", \"" + B1 + "\")";
Rng.StyleName = StyleName;
}
//HYPERLINK with any local file.
string Local_File = @ "D:\sample.xlsx";
string File = "D:\\sample.xlsx";
using(ExcelRange Rng = wsSheet1.Cells[22, 2, 22, 2]) {
Rng.Formula = "=HYPERLINK(\"" + Local_File + "\", \"" + File + "\")";
Rng.StyleName = StyleName;
}
//------HYPERLINK with any remote server file.
string AbsoLnk = "https://goo.gl/gOa0wm";
using(ExcelRange Rng = wsSheet1.Cells[23, 2, 23, 2]) {
Rng.Formula = "=HYPERLINK(\"" + AbsoLnk + "\", \"" + AbsoLnk + "\")";
Rng.StyleName = StyleName;
}
string MailLnk = "mailto:[email protected]";
string MailID = "[email protected]";
using(ExcelRange Rng = wsSheet1.Cells[24, 2, 24, 2]) {
Rng.Formula = "=HYPERLINK(\"" + MailLnk + "\", \"" + MailID + "\")";
Rng.StyleName = StyleName;
}
wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
ExcelPkg.SaveAs(new FileInfo(@ "D:\Hyperlink.xlsx"));
}
}
}