In Focus

Excel Export - Using "ExcelPackage" (EPPlus)

The limit of records (row items) that can be exported to Excel from client-side function varies between browsers. Every browser has its own limitations and I'm afraid that in most cases we don't have control over them. Exporting large amounts of data during export will generate strings that might be too long for the given browser.

The limit of records (row items) that can be exported to Excel from client-side functions varies between browsers. Every browser has its own limitations and I'm afraid that in most cases we don't have control over them. Exporting large amounts of data during export will generate strings that might be too long for the given browser.

Because file contents constructed entirely in client-side, it depends on each browser ability to store formatted strings (e.g. JSON) that processed by Spreadsheet.saveAsExcel() function.

If you want to export to Excel files with large amounts of records/rows , I recommend using server-side processing. 

Here is an example to perform server-side processing in controller action method.

I am using ExcelPackage (EPPlus - NuGet Package Manager).

Installation

In Visual Studio 2017, go to Tools -> NuGet Package Manager -> Package Manager Console

In the console window that appears, type the following at the prompt to install it into the current project:

PM > Install-Package EPPlus -Version 4.5.2.1

After it is finished, check the References under Solution Explorer and verify that the EPPlus assembly is listed.
View 
  1. <button id="ExportXL" name="ExportXL" type="button" value="Export to Excel" onclick="location.href='@Url.Action("ExportToExcel", "Controler Name")'" />  
Controller 
  1. public ActionResult ExportToExcel()  
  2.     {  
  3.         using (var excel = new ExcelPackage())  
  4.         {  
  5.             var workSheet = excel.Workbook.Worksheets.Add("Worksheet Name");  
  6.             workSheet.Cells[1, 1].LoadFromCollection(result, PrintHeaders: true, TableStyle: OfficeOpenXml.Table.TableStyles.Medium6);  
  7.             workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();  
  8.             return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet""Reports.xlsx");  
  9.         }  
  10.   
  11.     }  

Result is your collection of data (Which you might have from your DB).

TableStyle

OfficeOpenXml.Table.TableStyles.Medium6 - Excel row Style. The tab style gives a  good UI appearance (BackgroundColor) as well as auto filter. 

ExcelPackage

(EPPlus) Can include on Package config ----> Install-Package EPPlus -Version 4.5.1.