EPPlus Library Part-7

EPPLUS Library - Beginners Guide Part-7
(This is a continuation of Part-6)
How to create a Hyperlink using Excel Hyperlink() function in EPPlus?

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

First of All, If we are create a Hyperlink using function first we need to know the formula. 
What is Excel Formula ?

• A Formula is an expression which calculates the value of 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, mathematics operators (like a '+' or '-' sign for addition or subtraction) and some build-in Excel functions, which can really expend the power of a formula. 

What is Excel Function ?

• A Functions are predefined in formulas and are already available in excel or spreadsheets. Like SUM(), COUNT(), HYPERLINK() etc. These build-in functions are used for specific purpose. 

What is HYPERLINK() Function ?
  
                                   

HYPERLINK() is a predefined excel function & it takes two parameter. First is link location & second is friendly name or display name. Display or friendly name is showing on excel sheet. When we are clicking on it, it will redirect to link location. 

Hyperlink function using Formula property of ExcelRange class:      

• ExcelRange Rng = wsSheet1.Cells["B19"];
• String SiteLink = "https://www.google.com";
• String DisTxt = "Go to GOOGLE";
• Rng.Formula = "=HYPERLINK(\"" + SiteLink + "\", \"" + DisTxt + "\")";  

Above example we can see Formula  property. By using Formula property we can set cell formula in a excel sheet. It accept a string as a formula. In Part 4 of this tutorials we know that EPPlus don't have formula calculation engine. so if you typing a wrong excel formula in code, the compiler don't show any compilation error but generated excel sheet will showing error.

Hyperlink Function working on:

• 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["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 email address: 

• ExcelRange Rng = wsSheet1.Cells["B24"];
• String MailLnk = "mailto:everydaybecoding@gmail.com";
• String MailID = "everydaybecoding@gmail.com";
• Rng.Formula = "=HYPERLINK(\"" + MailLnk + "\", \"" + MailID + "\")"; 

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("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: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("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:everydaybecoding@gmail.com";
            string MailID = "everydaybecoding@gmail.com";
            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"));
        }
    }
}

• 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