Hello, so I get data from an SQL Query and load the data into an Excel File. At first the amount of data is to big to use Interop because the loading would take 5 minutes or something. So I load the data from the SQL Query in an new Excel file like I would with an CSV File.
using (FileStream sw = File.Create(tempPath))
{
var data = Encoding.Unicode.GetBytes("Artikelnummer" + "\t" + "Hersteller" + "\t" + "Beschreibung" + "\t" + "Nettopreis" + "\t" + "Bruttopreis" + "\t" + "Zustand" + "\t" + "P/N" + "\t" + "Kategorie I" + "\t" + "Kategorie II" + "\t" + "Kategorie III" + "\t" + "Shop-Link" + "\n");
sw.Write(data, 0, data.Length);
foreach (DataRow r in newsletter.Rows)
{
data = Encoding.Unicode.GetBytes(r["Artikelnummer"].ToString() + "\t" + r["Hersteller"].ToString() + "\t" + r["Bezeichnung"].ToString() + "\t" + r["Nettopreis"].ToString() + "\t" + r["Bruttopreis"].ToString() + "\t" + r["Zustand"].ToString() + "\t" + r["PN"].ToString() + "\t" + r["Kategorie I"].ToString() + "\t" + r["Kategorie II"].ToString() + "\t" + r["Kategorie III"].ToString() + "\t" + r["Link"].ToString() + "\n");
sw.Write(data, 0, data.Length);
}
}
In the data I get from an SQL Query there is one Column which has Hyperlinks in it. Which looks like that :
=HYPERLINK("www.example")
with the way I load the data into an new Excel file the Hyperlinks now looks correctly in the File with the correct Display text.
The problem with this is that if you start the program ther appears an Message that the file is corrupted. Thats because I created an xls File in the way of an CSV.
What I have tried:
Because I dont want to have this error Message and want to have working Hyperlinks and because it would take way to long time to load the full SQL Query directly with Interop excel I choose to create the file like before but then save the file with Interop as an real excel file with code like that:
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = app.Workbooks.Open(tempPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet ws2 = (Worksheet)wb.Worksheets[1];
ws2.Columns.AutoFit();
wb.SaveAs(newsView.path + "newsletter.xlsx", XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.Close();
app.Quit();
and now its creating a real Excel file without the error Message but now when I look into the Excel file the Hyperlinks are not working, in the cells there is just the full hyperlink string, only when I enter the cell like I would edit it and then leave directly the cell, the Hyperlink is working.
How can I achieve that the whole column automaticly make it to an working Hyperlinks without just putting the Hyperlink string into the cell.
What I already tried was something like that:
Microsoft.Office.Interop.Excel.Range range = ws2.Columns[3];
foreach (Microsoft.Office.Interop.Excel.Range cell in range)
{
object cellValue = cell.Value;
string cellValueString = cellValue as string;
cell.Hyperlinks.Add(cell, cellValueString);
}
I tried to run through every cell of this one specific column and take the value which is the full hyperlink and add it like this as a Hyperlink specific but that didnt work either.