0

I am currently using Microsoft's Microsoft Office Interop Excel in order to insert 28k row into an Excel file.

It should look like this (with 28k rows instead of 31):

enter image description here

I am inserting the data in the following foreach loop:

foreach (var displayableProduct in DisplayableMATProductList)
        {
            Worksheet.Cells[row, column] = displayableProduct.MATProduct.MATProductNumber;
            column++;
            if (displayableProduct.MATProduct.tblFormat != null) Worksheet.Cells[row, column] = displayableProduct.MATProduct.tblFormat.FormatName;
            column++;
            if (displayableProduct.MATProduct.tblGrade != null) Worksheet.Cells[row, column] = displayableProduct.MATProduct.tblGrade.GradeName;
            column++;
            if (displayableProduct.MATProduct.tblProduct != null) Worksheet.Cells[row, column] = displayableProduct.MATProduct.tblProduct.ProductCASNumber;
            column++;
            Worksheet.Cells[row, column] = displayableProduct.ProductInflammabilityLevel;
            column++;
            Worksheet.Cells[row, column] = displayableProduct.ProductToxicityLevel;
            column++;
            Worksheet.Cells[row, column] = displayableProduct.ProductReactivityLevel;
            column++;
            Worksheet.Cells[row, column] = displayableProduct.ProductSpecialLevel;
            column++;

            row++;
            column = 1;
        }

However, the problem is that the operation is taking about 10 minutes.

Does anyone know a faster way to insert bulk data into an Excel Worksheet?

Randy Quackers
  • 665
  • 1
  • 7
  • 16
  • 1
    try this: https://stackoverflow.com/questions/2206279/exporting-the-values-in-list-to-excel – NajiMakhoul Jul 07 '20 at 16:41
  • 1
    [How to export DataTable to Excel](https://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel) –  Jul 07 '20 at 16:52
  • Ok, great. Did you guys personally try any of these methods? If so, which one is your favourite for working with a large quantity of data? – Randy Quackers Jul 07 '20 at 17:09

0 Answers0