CODEWITHSUNDEEP
c#excelasp.net-mvc-3epplus
JosephLYapcengcoJr
JosephLYapcengcoJr

Reputation: 23

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

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;
    }

Upvotes: 1

Views: 1999

Answers (2)

L. Guthardt
L. Guthardt

Reputation: 2056

Updated Answer:

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

This should definitely help you out.

Upvotes: 1

user1080381
user1080381

Reputation: 1786

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();

    }

}

Upvotes: 0

Related Questions