0

I try to write a large data about 400,000 rows per sheet and the performance is down about 10 minutes for it: My snippet Code is:

var worksheet = package.Workbook.Worksheets.FirstOrDefault(w => w.Name.Equals("name"))
foreach(IEnumerable<string> row in Data)
{
   foreach(string col in row)
   {
      dynamic data;
      //for date column
      data = DateTime.Parse(col);
      worksheet.Cells[lastRow, LastCell].Style.NumberFormat.Format = "dd/mm/yyyy";
      //for text
      data = col;      

      worksheet.Cells[lastRow, LastCell].Value = data;
   }
}
Filburt
  • 17,626
  • 12
  • 64
  • 115
Zipi
  • 9
  • 3
  • Yeah, Excel seems like a poor choice for that. What's your question? – Fildor May 05 '21 at 11:23
  • Writing to excel is the requirment from me, so I asked if there is any way to improve performance when I am writing large data, maybe by using another method or class – Zipi May 05 '21 at 11:27
  • 1
    And that code doesn't make sense, too. Where do `lastRow` and `LastCell` come from? Why aren't they incremented? The `data = DateTime.Parse(col);` is completely unneccessary ... – Fildor May 05 '21 at 11:28
  • 1
    Where does the data come from? A Database? If so, would it make more sense to have the DB export directly to excel and then only maybe do a little "spice-up" afterwards? – Fildor May 05 '21 at 11:31
  • 1
    Maybe you could try creating the raw data and then convert / import / zip into your final excel file. Other than that I'm with @Fildor that your current code is unclear on what exactly is going on here. – Filburt May 05 '21 at 11:31
  • 1
    writing one row at a time is horribly inefficient. Much better to build up a array of data in memory and pass it all in one go. Have a look here: https://stackoverflow.com/questions/536636/write-array-to-excel-range – sous2817 May 05 '21 at 11:36
  • I didnt copy all the code but ```lastRow``` and ```lastCell` equals to 1 and they onward by 1 – Zipi May 05 '21 at 11:37
  • 1
    _"and they onward by 1"_ - not in the code snippet given in the question. Here, it looks like you are writing to the very same cell over and over. So, I am assuming there is some code missing? – Fildor May 05 '21 at 11:40
  • This could potentially be useful - https://stackoverflow.com/a/32787219/3791802 – petelids May 06 '21 at 10:35

0 Answers0