How To Create A Hyperlink Inside A Cell Or Image In Excel Sheet Using EPPlus .Net Application (C#) - Part Six

Rajdip Sarkar
7y
4.7k
0
0

Blog

Hyperlink_inside_a_cell_in_excel_sh

First EPPlus library can be downloaded from : http://epplus.codeplex.com/ & add this library to your Visual Studio solution or you can download my source code.

What is Hyperlink

A Hyperlink is a link inside a document that open a another destination object when users click on it. Here destination object means any web page, any files, emails address or any program. The Hyperlink itself can be text or a picture.

How to create Hyperlink in excel sheet using EPPlus Library. There are four ways to create Hyperlink in excel sheet.

Option 1 - Using EPPlus Hyperlink property of ExcelRange class.
Option 2 - Using AddPicture() method of ExcelDrawings class.
Option 3 - Using Excel Hyperlink Function in the formula property of ExcelRange class. (We will be discuss on this topic in Part-7 of this video series)
Option 4 - Using VBA (Visual Basic for Application) Code. It is a macro enable excel sheet. (We will discuss on this topic in my upcoming video series "Excel VBA code development using EPPlus Library")

Note

fFrst two options of Hyperlink accept relative and absolute URI .

What is Relative & Absolute URI (Uniform Resource Identifier)?

A URI is a compact sequence of characters that identifies an abstract or physical resource. A URI can be further classified as a locator, a name, or both. The term 'UniformResource Locator(URL) refers to the subset of URI.

The absolute URL contains all the information necessary to locate a resource.

An absolute URL format: scheme://server/path/resource
Example : https://everyday-be-coding.blogspot.in/p/epplus-library-part-6.html

A relative URL typically consists only of the path, and optionally, the resource, but no scheme or server.

Using EPPlus Hyperlink Property

ExcelRange Rng = wsSheet1.Cells["B6"];
Rng.Hyperlink = new Uri("https://www.google.com", UriKind.Absolute);
Rng.Value = "Go to GOOGLE";

Here wsSheet1 is the object of ExcelWorkSheet class. It hold the "B6" cell. Uri() is the constructor of class Uri & accept the string value URL as a parameter. It assign to the Uri class object, here object is Hyperlink property of ExcelRange class. That means the type of Hyperlink property is Uri class. After that we set value of "B6" cell by using Rng.Value property.

Hyperlink works with:

To another cell of existing sheet.
To different sheet within same excel file.
To any local file.
To any remote server file.
To link with email address.

To another cell of existing sheet:

ExcelRange Rng = wsSheet1.Cells["B7"];
Rng.Hyperlink = new Uri("#'Sheet1'!B2", UriKind.Relative);
Rng.Value = "Go to Cell B2";

In this above example #'<Sheet Name>'!<Cell address> is the link location & here# keyword specify the sheet name & ! sign used for relation operator with cell B2. If your excel file has multiple sheets, then you can use a specific sheet name. UriKind is a enum for specific addressing.

To different sheet within same excel file,

ExcelRange Rng = wsSheet1.Cells["B8"];
Rng.Hyperlink = new Uri("#'Sheet2'!B1", UriKind.Relative);
Rng.Value = "Go to B1 in Sheet2";

To any local file,

ExcelRange Rng = wsSheet1.Cells["B9"];
Rng.Hyperlink = new Uri(@"D:\sample.xlsx");
Rng.Value = "D:\\sample.xlsx";

*In this above example @"D:\sample.xlsx" is the local file path.

To any remote server file,

ExcelRange Rng = wsSheet1.Cells["B10"];
Rng.Hyperlink = new Uri("https://goo.gl/gOa0wm", UriKind.Absolute);
Rng.Value = "https://goo.gl/gOa0wm";

To link with email address,

ExcelRange Rng = wsSheet1.Cells["B11"];
Rng.Hyperlink = new Uri("mailto:[email protected]", UriKind.Absolute);
Rng.Value = "[email protected]";

Using AddPicture() method of ExcelDrawings class,

Image img = Image.FromFile(@"D:\sample.png");
ExcelPicture pic = wsSheet1.Drawings.AddPicture("EDBC", img, new Uri("https://everyday-be-coding.blogspot.in/", UriKind.Absolute));
Pic.SetPosition(14,0,1,0);
Pic.SetSize(148,26);

Above example was already discussed in Part-5 (EPPlus library) of this video series. In this example 3rd parameter's of AddPicture("Pic_Name", img, new Uri("https://everyday-be-coding.blogspot.in/")) method is URI constructor. By using this method we can attach a hyperlink on image file.

Output in Excel Sheet

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("https://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("https://everyday-be-coding.blogspot.in/", UriKind.Absolute));
//ExcelPicture img = wsSheet1.Drawings.AddPicture("Picture_Name", new FileInfo(@"D:\EverydayBeCoding.png"), new Uri("https://everyday-be-coding.blogspot.in/", UriKind.Absolute ));
pic.SetPosition(14, 0, 1, 0);
pic.SetSize(148, 26);
wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
ExcelPkg.SaveAs(new FileInfo(@ "D:\Hyperlink.xlsx"));
}
}
}