'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
– mrogal.ski, Commented Jul 11, 2017 at 10:34
How? by creating first an temp excel file, save it, then open to write there? — JosephLYapcengcoJr
– JosephLYapcengcoJr, Commented Jul 11, 2017 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 — mrogal.ski
– mrogal.ski, Commented Jul 11, 2017 at 10:57

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 11, 2017 at 10:33

L. Guthardt

2,0766 gold badges26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JosephLYapcengcoJr Over a year ago

yes I can get my resources but the package will be closed once it executes the package.GetAsByteArray();

JosephLYapcengcoJr Over a year ago

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! :)

user1080381 · Accepted Answer · 2017-07-12 07:10:29Z

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, 2017 at 7:10

answered Jul 11, 2017 at 12:19

user1080381

1,8062 gold badges21 silver badges25 bronze badges

5 Comments

user1080381 Over a year ago

@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?

JosephLYapcengcoJr Over a year ago

convert it to excel then download it on client.

JosephLYapcengcoJr Over a year ago

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!..

user1080381 Over a year ago

@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

JosephLYapcengcoJr Over a year ago

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 -the ExportToOXML method only and it works!, Thanks for helping! :)

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related