Click here to Skip to main content
15,906,766 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Im reading Data from an SQL Query to an DataTable. In this DataTable is one column in which are only Hyperlinks.

In the Excel File the Hyperlinks not getting read. So I use this Code to take the current Cell Value of this Column and formatting it to an working Hyperlink with FormulaA1.

This is my Code:

using (XLWorkbook workbook = new XLWorkbook())
        {
            IXLWorksheet worksheet = workbook.Worksheets.Add("Sheet1");

            // Load the data from the DataTable into the worksheet
            worksheet.Cell("A1").InsertTable(newsletter);

            // Iterate through the rows in the DataTable
            for (int rowIndex = 1; rowIndex <= newsletter.Rows.Count; rowIndex++)
            {
                // Get the cell for the "Link" column in the current row
                IXLCell cell = worksheet.Cell(rowIndex + 1, newsletter.Columns.IndexOf("Bezeichnung") + 1);

                // Set the value of the cell to the hyperlink text
                cell.FormulaA1 = newsletter.Rows[rowIndex - 1]["Bezeichnung"].ToString();
            }

            // Save the Excel file
            workbook.SaveAs("SomePath");
        }


Note my datatable is called newsletter

The Problem with this is code that

newsletter.Rows[rowIndex - 1]["Bezeichnung"].ToString();
contains the correct Hyperlink which is Converted like that

=HYPERLINK("the working link")
but at the moment I load the value into the FormulaA1 the new Hyperlink is formatted like that:

"HYPERLINK(\"somelink\")"
and because of the adding of this "", Excel cant read it gaves me an error message and the whole Column is empty because Excel is removing the Formulas that is not readable for Excel.

I thought I can just remove every ** but that isnt usefule because he always adding these symbols to the HyperlinkString.

Does anyone know a way to workaround this problem?

What I have tried:

I tried thing like cell.FormulaR1C1 or cell.sethyperlink but nothing worked
Posted
Updated 6-Jan-23 8:24am
Comments
adriancs 1-Jan-23 8:00am    
Try appending the character of single quote in front of the link, for example: cell.FormulaA1 = "'" + newsletter.Rows[rowIndex - 1]["Bezeichnung"].ToString();.

1 solution

To add hyperlink, uset this:

ws.Cell(1, 1).Value = "Link to a web page, no tooltip - Yahoo!";
ws.Cell(1, 1).Hyperlink = new XLHyperlink(@"http://www.yahoo.com");


More: Using Hyperlinks · ClosedXML/ClosedXML Wiki · GitHub[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900