Click here to Skip to main content
15,920,708 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

C#
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:

C#
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:

C#
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.
Posted
Updated 20-Dec-22 22:46pm

1 solution

Rather than using Interop (which will be slow, requires that you have Office installed, and won't work from an "unattended" application[^]) or generating a CSV file and "pretending" that it's a real Excel file, try generating a proper Excel file using the OpenXML SDK[^] or a wrapper library such as ClosedXML[^].

If you really need to generate an ancient pre-2007 Excel format file, then NPOI[^] will do the trick.

Either option will be significantly faster than Office Interop, and won't generate the "corrupt file" warning when you open the file.
 
Share this answer
 
Comments
EstKells 21-Dec-22 4:59am    
The problem with the corrupted File only appears if only use the CSV File way after interop the warning is gone. The other problem is why I dont want to use an library like closedXML or even interop to load from SQL directly to an Excel file is because i got over 20.000 amount of data which takes always to long. Thats why I used the CSV way because for some reason thats way faster then all the Excel librarys. With the CSV way it takes less then 20 seconds and with any library over minutes.
Richard Deeming 21-Dec-22 5:06am    
So your options are: fast and wrong; slightly less fast and correct; or horrendously slow and correct.

I know which option I'd choose...
OriginalGriff 21-Dec-22 7:56am    
4) horrendously slow and incorrect

That's the one most of QA goes for. :sigh:

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

  Print Answers RSS


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