EPPlus Library Part-6

EPPLUS Library - Beginners Guide Part-6
How to create a Hyperlink inside a cell or image in excel sheet using EPPlus?

Hindi Video :  Click here
Previous Video :  Click here [English]
Previous Video :  Click here [Hindi]
Source Code :  Click to download [675 KB]

What is Hyperlink:

• A Hyperlink is a link inside a document that open a another destination object when users are 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 are 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: first 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 'Uniform Resource 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 are set value of "B6" cell by using  Rng.Value property.

Hyperlink working 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 files has multiple sheets, then you can use 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:everydaybecoding@gmail.com", UriKind.Absolute);
• Rng.Value = "everydaybecoding@gmail.com"; 

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:everydaybecoding@gmail.com", UriKind.Absolute);
                Rng.Value = "everydaybecoding@gmail.com";
                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"));
        }
    }
}

• Now build & execute this code. File is (Hyperlink.xlsx) store on D: drive of computer.

Thank you for reading this article. 
Please subscribe my YouTube Channel & don't forget to like and share. 

YouTube :	https://goo.gl/rt4tHH
Facebook :	https://goo.gl/m2skDb
Twitter :	https://goo.gl/nUwGnf