How can I export very large amount of data to excel

EPPlus to export data to excel. It works admirably for small amount of data. But it consume a lots of memory for large amount of data to export.

A few years ago, I wrote a C# library to export data to Excel using the OpenXML library, and I faced the same situation.

It worked fine until you started to have about 30k+ rows, at which point, the libraries would be trying to cache all of your data... and it'd run out of memory.

However, I fixed the problem by using the OpenXmlWriter class. This writes the data directly into the Excel file (without caching it first) and is much more memory efficient.

And, as you'll see, the library is incredibly easy to use, just call one CreateExcelDocument function, and pass it a DataSet, DataTable or List<>:

// Step 1: Create a DataSet, and put some sample data in it
DataSet ds = CreateSampleData();

// Step 2: Create the Excel .xlsx file
try
{
    string excelFilename = "C:\\Sample.xlsx";
    CreateExcelFile.CreateExcelDocument(ds, excelFilename);
}
catch (Exception ex)
{ 
    MessageBox.Show("Couldn't create Excel file.\r\nException: " + ex.Message);
    return;
}

You can download the full source code for C# and VB.Net from here:

Mike's Export to Excel

Good luck !