0

So I've have an quite Big DataTable over 10.000 data and I wanted to Export it to an Excel File.

At first I tryd librarys like ClosedXML or Interop.Excel. But for this amount of Data it takes way to long.

So I decided to make it with an FileStream, thats so much faster and the creating of the excel File is working except that when I want to open the File I got an Message from Excel that the File is corrupted, that the FileFormat and File extension not fitting together.

Has someone an Idea how I can create an Excel File without Librarys or getting this Message?

My Code looks like that:

    string path = path + "someFilename.xlsx";

using (FileStream sw = File.Create(path))
                {
                    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 dt.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);
                    }

                }

dt is my DataTable

Clemens
  • 123,504
  • 12
  • 155
  • 268
NadiaBallshek
  • 29
  • 5
  • what you are trying to do looks more like a CSV file than an excel file. Usually data in CSVs are separated by ';' instead of '\t'. You can open .csv files with excel. Excel files are not just simple text files and you will need to use one of the framework you wrote about to make it work. (just try to open an working xlsx file with notepad to see that) – Kilarn123 Dec 08 '22 at 12:50
  • the problem is when I try to creat it with Interop.Excel or ClosedXML. Its loading for more then 5 minutes – NadiaBallshek Dec 08 '22 at 12:51
  • Also if formatting is not important and you will not have multiple sheets then you can create a CSV ("someFilename.Csv") and once the file is created, using Interop do a [Save As](https://stackoverflow.com/questions/16732343/converting-excel-file-from-csv-to-xlsx) and save it as Excel file. – Siddharth Rout Dec 08 '22 at 12:55
  • @SiddharthRout thats probably the best way to do it, but there is only one problem, I have in that Excel file in one split, only hyperlinks, with that methods the hyperlinks has no function – NadiaBallshek Dec 08 '22 at 15:24

0 Answers0