如何使用EPPlus在单元格中创建一个链接?

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.

我正在试图找出如何使用EPPlus在单元格中编写超链接，而不是使用包含链接文本的单元格。我需要它被识别为一个链接并且可以点击。

Any help is appreciated.

任何帮助都是感激。

6 个解决方案

#1

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!!

编码快乐! !

#2

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.

我已经测试了。它将正常工作。

#3

There's a few ways to go about it:

有几种方法可以实现:

1) To use URI, then set a human readable name

1)使用URI，然后设置一个人类可读的名称。

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

2) To use ExcelHyperlink and set a human readable name using object initializer

2)使用ExcelHyperlink并使用对象初始化器设置人类可读的名称

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

3) To use =Hyperlink() formula

3)使用=Hyperlink()公式

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

#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).

如果没有样式，超链接看起来就像普通的文本，if单元格。超链接=新的Uri(url);不使用显式样式(尽管游标将正确地指示文本实际上是一个超链接文本)。

#5

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

我不知道EPPlus，但在VBA(我猜c#也会使用同样的原则)中，您将使用以下代码:

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

希望这能有所帮助——祝你好运

#6

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:

如果您使用的是EPPlus，并希望在同一个文档中创建一个链接到另一个表，那么这是正确的方法:

  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.

这是在Excel文档中创建到另一个表的链接的正确方法。使用公式与超链接功能，如果一个文件被下载到客户端，最新的Excel版本将引起安全警告。

#1