'System.OutOfMemoryException' occurred in mscorlib.dll when exporting huge Excel file

Question

I am trying to export an Excel file with 1,800,000+ rows of data with 9 columns using EPPlus. I used a DataSet and separate it into batch (100,000 each DataTable). Currently, it can export at max of 1,000,000 rows.

Here's my code for export below:

    public FileContentResult ExportInventory()
    {
         DataSet dsInventory = Session["Live_Inventory"] as DataSet;

         var filename = "Excel Inventory.xlsx";
         var excelContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
         byte[] excelContent = ExportExcel(dsInventory);

         return File(excelContent, excelContentType, filename);
    }

    public byte[] ExportExcel(DataSet dsInventory)
    {
         byte[] result = null;
         ExcelWorksheet workSheet = null;

         var package = new ExcelPackage();
         for (int tbl = 1; tbl <= dsInventory.Table.Count; tbl++)
         {
              workSheet = package.Workbook.Worksheets.Add(String.Format("Sheet " + tbl));
              workSheet.Cells["A1"].LoadFromDataTable(dsInventory.Tables[tbl], true);                  
         }

         result = package.GetAsByteArray();//Exception occurs here when table count > 10 
         return result;
    }

Why dont you just write this directly to the file? `.xlsx` is just a pack of `.xml` files .. — mrogal.ski, Jul 11 '17 at 10:34
How? by creating first an temp excel file, save it, then open to write there? — JosephLYapcengcoJr, Jul 11 '17 at 10:51
Create a zip file and then inside of this zip file you can just throw your data in. [https://stackoverflow.com/questions/11082278/how-to-properly-assemble-a-valid-xlsx-file-from-its-internal-sub-components](https://stackoverflow.com/questions/11082278/how-to-properly-assemble-a-valid-xlsx-file-from-its-internal-sub-components) — mrogal.ski, Jul 11 '17 at 10:57

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

Updated Answer:

You should have a look at this: Export large data to an exel file

This should definitely help you out.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 11 '17 at 10:33

L. Guthardt

1,990
6
22
44

yes I can get my resources but the package will be closed once it executes the package.GetAsByteArray(); – JosephLYapcengcoJr Jul 11 '17 at 11:03
I tried to use your code but it still returns 'System.OutOfMemoryException' on that same line. I wasn't able to try adding `await Task.Delay(5000);` because it is only available in Framework 4.5 (my project is using .net Framework is 4). Thanks for helping! :) – JosephLYapcengcoJr Jul 13 '17 at 06:20

user1080381 · Accepted Answer · 2017-07-12T07:10:29.963

0

public void DataToExcel(DataTable dataToConvert, HttpResponse response)
{
    FileInfo myFile = new FileInfo("~/MyProject/Inventory.xlsx");
    using (ExcelPackage package = new ExcelPackage(myFile))
    {
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

        worksheet.Cells["A1"].LoadFromDataTable(dataToConvert, true, OfficeOpenXml.Table.TableStyles.Light1);

        package.Save();


        response.Clear();
        response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
        response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", "Inventory.xlsx"));
        response.WriteFile("~/MyProject/Inventory.xlsx");
        response.Flush();
        response.End();

    }

}

edited Jul 12 '17 at 07:10

answered Jul 11 '17 at 12:19

user1080381

1,597
1
16
22

@JosephLYapcengcoJr So what is your final goal? You want to convert your data to excel and save it to the server or download it on client side? or anything else? – user1080381 Jul 11 '17 at 15:21
convert it to excel then download it on client. – JosephLYapcengcoJr Jul 11 '17 at 23:22
I don't think your code will solve my problem because you just added methods in downloading a file and my problem is when I'm converting "package = new ExcelPackage();" to byte array when the datatable's rows reach 1M+, the System.OutOfMemoryException occurs. Anyway, thanks for your help!.. – JosephLYapcengcoJr Jul 12 '17 at 00:01
@JosephLYapcengcoJr Try the following: create excel file in your project manually, say inventory.xlsx . Then write data in that file using LoadFromDataTable method and finally download the file via HttpResponse. This way you will bypass the convertion of package to the byte array which overloads your memory. I have edited my code above. Hope that helps – user1080381 Jul 12 '17 at 07:10
1

I tried to use your code but it still returns 'System.OutOfMemoryException' on `package.Save();` So I replaced the part that using EPPlus library with an OpenXML here's the [code](https://www.codeproject.com/Tips/659666/Export-very-large-data-to-Excel-file) -the ExportToOXML method only and it works!, Thanks for helping! :) – JosephLYapcengcoJr Jul 13 '17 at 06:18

'System.OutOfMemoryException' occurred in mscorlib.dll when exporting huge Excel file

2 Answers2