54

I am trying to figure out how to write a Hyperlink inside a cell using EPPlus instead of the cell containing the link text. I need it to be recognized as a link and be clickable.

Any help is appreciated.

IEnumerator
  • 2,960
  • 5
  • 31
  • 33

6 Answers6

109

This is the other way to do:

var cell = sheet.Cells["A1"];
cell.Hyperlink = new Uri("http://www.google.com");
cell.Value = "Click me!";

I have tested. It works fine.

Han
  • 3,272
  • 3
  • 24
  • 39
  • 4
    Ahhhhhhhhhhhh much easier than `\"","\"\"??"` +1 THANKS\! – Piotr Kula Sep 27 '13 at 12:18
  • 8
    Thanks for this! I have to set Value after Hyperlink because otherwise I see address in the cell, not the value. – rpeshkov Feb 17 '15 at 08:27
  • I have got an exception like Cannot find the definition of Hyperlink for System._ComObject . I have added EPPlus reference and no compilation error is there. Even i am using .net version 4.0. Can anyone tell me the reason for this runtime exception ? – G.S Abhaypal Jun 20 '16 at 09:59
  • And... How to create a hyperlink to another cell inside the workbook? I don't want to use filename, because I sent the file over internet, the user could change it. – TNT Jan 13 '17 at 13:04
  • I have used this before and works fine while using url but when I tried to link a file from file system, this is not working and giving error like `Excel found unreadable content` while opening excel. So @bell's answer is perfect for every situation. – Jaydeep Karena Feb 06 '19 at 06:27
  • Han's version didn't work for me, the Bell's version with Formula = HYPERLINK worked like a charm, thanks Bell – Zoran Basic Nov 05 '21 at 10:39
32

The below code worked fine with me.

string FileRootPath = "http://www.google.com";
_Worksheet.Cells[intCellNumber, 1].Formula = "HYPERLINK(\"" + FileRootPath + "\",\"" + DisplayText + "\")";

I hope this would help you.

Happy coding!!

niaher
  • 9,460
  • 7
  • 67
  • 86
Bell
  • 352
  • 2
  • 2
  • Why not `_Worksheet.Cells[intCellNumber, 1].Formula = String.Format("HYPERLINK(\"{0}\", \"{1}\")", FileRootPath, DisplayText)` ? – MrOodles Dec 16 '13 at 22:08
  • 2
    Why not `String.Format(@"HYPERLINK(""{0}"", ""{1}"")", FileRootPath, DisplayText)` ;) – Pakman Sep 25 '14 at 22:23
  • 4
    To me it seems not a universal solution because HYPERLINK function may not work for non-English versions of MS Excel. From this point of view the answer from Han is better. – Victor Sharovatov Mar 07 '15 at 10:51
  • 2
    Why not `$"HYPERLINK(\"{FileRootPath}\", \"{DisplayText\")"` (keeping the apparent tradition of adding a new way to format the output every few years since the OP in 2011). – Eric J. Oct 17 '17 at 21:51
  • 1
    Why not `String.Format(CultureInfo.InvariantCulture, @"HYPERLINK(""{0}""; ""{1}"")", FileRootPath, DisplayText)` (Also: Mind the semicolon, as mine is not an english Excel) – Nils Jul 17 '19 at 09:50
  • @bell I would also suggest adding: cel.Style.Font.UnderLine = true; cel.Style.Font.Color.SetColor(Color.Blue); for formatting – Zoran Basic Nov 05 '21 at 10:44
25

There's a few ways to go about it:

  1. To use URI, then set a human readable name:

    var cell = sheet.Cells["A1"]; cell.Hyperlink = new Uri("https://www.google.com"); cell.Value = "Click me!";

  2. To use ExcelHyperLink and set a human readable name using object initializer :

    var cell = sheet.Cells["A1"]; cell.Hyperlink = new ExcelHyperLink("https://www.google.com") { Display = "Click me!" };

  3. To use =Hyperlink() formula:

    var cell = sheet.Cells["A1"]; cell.Formula = string.Format("HYPERLINK({0},{1})", "https://www.google.com", "Click me!"); cell.Calculate();

Yun
  • 3,056
  • 6
  • 9
  • 28
Cardin
  • 5,148
  • 5
  • 36
  • 37
4

Based on provided answers and documentation I was able to create an extension method that also deals with proper hyperlink formatting. It creates a named style, if needed, and use that style for all subsequent hyperlinks:

public static void WriteHyperlink(this ExcelRange cell, string text, string url, bool excelHyperlink = false, bool underline = true)
{
    if (string.IsNullOrWhiteSpace(text))
        return;

    // trying to reuse hyperlink style if defined
    var workBook = cell.Worksheet.Workbook;
    string actualStyleName = underline ? HyperLinkStyleName : HyperLinkNoUnderlineStyleName;

    var hyperlinkStyle = workBook.Styles.NamedStyles.FirstOrDefault(s => s.Name == actualStyleName);
    if (hyperlinkStyle == null)
    {
        var namedStyle = workBook.Styles.CreateNamedStyle(actualStyleName);  
        namedStyle.Style.Font.UnderLine = underline;
        namedStyle.Style.Font.Color.SetColor(Color.Blue);
    }

    if (excelHyperlink)
        cell.Hyperlink = new ExcelHyperLink(url) { Display = text };
    else
    {
        cell.Hyperlink = new Uri(url);
        cell.Value = text;
        cell.StyleName = actualStyleName;
    }
}

Without the styling, the hyperlink will look just as regular text, if cell.Hyperlink = new Uri(url); is used without explicit styling (although the cursor will properly indicate that the text is actually a hyperlink text).

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
2

I don't know EPPlus, but in VBA (and I guess C# would use the same principle) you would use the following code:

Sub Test()

    ' place value into cell
    ActiveSheet.[A1] = 13

    ' create link and set its range property
    ActiveSheet.Hyperlinks.Add ActiveSheet.[A1], "http://www.google.at"

    ' use cell in a calculation
    ActiveSheet.[A2].Formula = "=A1+2"

End Sub

Hyperlinks are objects having a range property, so while your cell value can be changed by overtyping, the link will remain. Edit the cell by a long mouse click

Hope this helps - good luck MikeD

MikeD
  • 8,861
  • 2
  • 28
  • 50
1

If you are using EPPlus and want to create a link to another sheet within the same document, then this is the proper way to do this:

  var link = "Another Excel Sheet"; //Maximum length is 31 symbols
  using (var excel = new ExcelPackage())
  {
       var ws = excel.Workbook.Worksheets.Add("Test");
       ws.Cells[row, col].Hyperlink =
                new ExcelHyperLink((char)39 + link + (char)39 + "!A1", 
                "Name of another excel sheet could be more then 31 symbols");
  }

This is the proper way to create a link to another sheet within Excel document. Having using formula with HYPERLINK function, if a file is downloaded to the client, latest Excel version will raise security warnings.

SouXin
  • 1,565
  • 11
  • 17
  • For those who wonder, `(char)39` is the single quote character, so this would also work: `new ExcelHyperLink($"'{link}'!A1")` – Métoule May 16 '19 at 07:26