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");
worksheet.Cell("A1").InsertTable(newsletter);
for (int rowIndex = 1; rowIndex <= newsletter.Rows.Count; rowIndex++)
{
IXLCell cell = worksheet.Cell(rowIndex + 1, newsletter.Columns.IndexOf("Bezeichnung") + 1);
cell.FormulaA1 = newsletter.Rows[rowIndex - 1]["Bezeichnung"].ToString();
}
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