Add hyperlink to excel sheet closedxml

Question

I am converting a data table to excel which is working fine. I have 4 columns in data table. Column1 and Column2 are simple text. Column3 and Column4 are hyperlink url and display text. all 4 columns are exported as it is in excel with below code :

        string fileName = ConfigurationManager.AppSettings["filename"];
        string sheetName = System.IO.Path.GetFileNameWithoutExtension(fileName);
        using (XLWorkbook wb = new XLWorkbook())
        {               
            var ws = wb.Worksheets.Add(ptDataTable, sheetName);

            ws.Style.Font.FontName = "Arial";
            ws.Style.Font.FontSize = 10;
            ws.Style.Alignment.WrapText = true;
            ws.FirstRow().Style.Alignment.SetWrapText(true);

            ws.Style.Alignment.SetWrapText(true);
            ws.Style.Alignment.SetShrinkToFit(true);
            ws.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
            ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
            ws.Rows().AdjustToContents();
            ws.Columns().AdjustToContents();

            ws.Rows(2,200).Style.Fill.SetBackgroundColor((XLColor.Transparent));
            ws.Columns(9, 10).Width = 50.0;
            ws.Range("A2:J200").Style.Border.SetInsideBorder(XLBorderStyleValues.Thin);
            ws.Range("A2:J200").Style.Border.SetOutsideBorder(XLBorderStyleValues.Thin);


            using (MemoryStream stream = new MemoryStream())
            {
                wb.SaveAs(stream);
                return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
            }
        }

I want to create hyperlink with column3(url) and column4(display text) and this hyperlink will be 3 column in exported excel.

Any idea!

Francois Botha · Answer 1 · 2020-07-02T19:03:26.567

8

You will have to loop through the cells and add the hyperlinks manually:

ws.Cell(rowNumber, columnNumber).Hyperlink = new XLHyperlink(@"http://www.yahoo.com");

You can find more info about hyperlinks in ClosedXML at https://github.com/ClosedXML/ClosedXML/wiki/Using-Hyperlinks

edited Jul 02 '20 at 19:03

answered Jun 30 '20 at 09:17

Francois Botha

4,520
1
34
46

I donot want to loop through cells as it will be time consuming – Always_a_learner Jul 02 '20 at 15:48
1

If you want to do it with ClosedXML, that is your only option. – Francois Botha Jul 02 '20 at 19:04

score 3 · Answer 2 · answered Jun 26 '20 at 01:45

If I am not mistaken, your code is using the ClosedXML library, not the OpenXMLSDK.

There are two ways to add hyperlinks in OpenXMLSDK:

writing a =HYPERLINK formula into cells

Cell cell = new Cell()
{
  CellReference = "A3",
  DataType = new EnumValue<CellValues>(CellValues.Number),
  CellFormula = "=HYPERLINK(yourlinklocationcellhere, yourfriendlyhyperlinknamehere)"
};

creating Hyperlink objects in the Worksheets. https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.hyperlink?view=openxml-2.8.1

score 2 · Answer 3 · answered Jul 08 '22 at 11:49

2

Francois Botha's answer works, but it is pretty slow for a large number of hyperlinks (in the thousands, as each hyperlink generation takes up a couple of milliseconds to generate).

A good alternative is to generate the hyperlink using a formula:

cell.FormulaA1 = $"=HYPERLINK(\"{url}\", \"{text}\")";

A function using this approach that also ensures valid hyperlink values can be found on Codidact.

answered Jul 08 '22 at 11:49

Alexei - check Codidact

22,016
16
145
164

1

A downside to this approach, is that you lose the option for Tooltips – Tom Lint Jun 29 '23 at 09:33

Zeek2 · Answer 4 · 2022-10-21T08:26:41.790

Further to @Francois Botha's earlier solution, it need not take much code.

e.g. in VB.NET, I just used the following code to convert a string column containing URLs(already populated from my SQLServer database query's resultant DataTable) to clickable links:

    For Each wsrow In wc.Rows
        'Was: wsrow.Cell("G").Hyperlink = New XLHyperlink(wsrow.Cell("G"))
         wsrow.Cell("G").SetHyperlink(New XLHyperlink(url))
    Next

UPDATE: No, that appeared to work - highlighted URL - but the link content is empty :( Works a treat. FYI I am using quite an old version of OpenXML, 0.68.1.*

*I tried upgrading ClosedXML yesterday but it broke some essential existing code and the image display capability did not work for me - I was too short of time to mess about with it further.

Tried updating closed XML. Changing the middle line above as shown below allows it to build (but not fully tested yet): wsrow.Cell("H").SetHyperlink(New XLHyperlink(url2)) — Zeek2, Oct 21 '22 at 08:21

Add hyperlink to excel sheet closedxml

4 Answers4