Export Excel File In MVC.NET

In this article, we are learning how to export an Excel file in MVC.NET.

Introduction

 
In this article, we will learn how to export an Excel file using the EPPlus plugin in MVC.NET. The EPPlus plugin is open source and easy to use.
 
EPPlus allows the users to set cell ranges, border, charts, pictures, shapes, comments, tables, protection, data validation, conditional formatting, formula calculation and cell style such as alignment, cell color, font color, font-style, font-size etc.
 
We have to follow some simple steps for exporting an Excel in MVC.NET.
 
Step 1 - Install the EPPlus Plugin
 
First of all, we need to install the EPPlus plugin from NuGet Package Manager, by just following the below points.
 
Excel Export In MVC.NET
 
Step 2 - Create a method for Excel export
 
Add these three namespaces at the top of the controller.
  1. using OfficeOpenXml;  
  2. using System.IO;  
  3. using RotativaPDF.Models;  
  4. using System.Data;  
  5. using OfficeOpenXml.Table;  
Then, we need to write the logic for Excel export method.
  1.         public ActionResult ExcelExport()  
  2.         {  
  3.             empEntities context = new empEntities();  
  4.   
  5.             List<emp_table> FileData = context.emp_table.ToList();  
  6.   
  7.   
  8.             try  
  9.             {  
  10.   
  11.                 DataTable Dt = new DataTable();  
  12.                 Dt.Columns.Add("ID"typeof(string));  
  13.                 Dt.Columns.Add("Name"typeof(string));  
  14.                 Dt.Columns.Add("Age"typeof(string));  
  15.                 Dt.Columns.Add("Contat"typeof(string));  
  16.   
  17.                 foreach (var data in FileData)  
  18.                 {  
  19.                     DataRow row = Dt.NewRow();  
  20.                     row[0] = data.id;  
  21.                     row[1] = data.name;  
  22.                     row[2] = data.age;  
  23.                     row[3] = data.contact;  
  24.                     Dt.Rows.Add(row);  
  25.   
  26.                 }  
  27.   
  28.                 var memoryStream = new MemoryStream();  
  29.                 using (var excelPackage = new ExcelPackage(memoryStream))  
  30.                 {  
  31.                     var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");  
  32.                     worksheet.Cells["A1"].LoadFromDataTable(Dt, true, TableStyles.None);  
  33.                     worksheet.Cells["A1:AN1"].Style.Font.Bold = true;  
  34.                     worksheet.DefaultRowHeight = 18;  
  35.   
  36.   
  37.                     worksheet.Column(2).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;  
  38.                     worksheet.Column(6).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;  
  39.                     worksheet.Column(7).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;  
  40.                     worksheet.DefaultColWidth = 20;  
  41.                     worksheet.Column(2).AutoFit();  
  42.                       
  43.                     Session["DownloadExcel_FileManager"] = excelPackage.GetAsByteArray();  
  44.                     return Json("", JsonRequestBehavior.AllowGet);  
  45.                 }  
  46.             }  
  47.             catch (Exception ex)  
  48.             {  
  49.                 throw;  
  50.             }  
  51.   
  52.               
  53.         }  
  54.           
  55.         public ActionResult Download()  
  56.         {  
  57.           
  58.             if (Session["DownloadExcel_FileManager"] != null)  
  59.             {  
  60.                 byte[] data = Session["DownloadExcel_FileManager"as byte[];  
  61.                 return File(data, "application/octet-stream""FileManager.xlsx");  
  62.             }  
  63.             else  
  64.             {  
  65.                 return new EmptyResult();  
  66.             }  
  67.         }  
Call the ExcelExport method for creating an Excel file and call the "Download" method for downloading the file.
 
In the lines given below, we are creating an instance of ExcelPackage.
  1. using (var excelPackage = new ExcelPackage(memoryStream))  
We converted the list into the data table and loaded this data table into a worksheet and gave some properties, like font style, alignment, row height, column width etc.
  1.                   var worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");  
  2.                    worksheet.Cells["A1"].LoadFromDataTable(Dt, true, TableStyles.None);  
  3.                    worksheet.Cells["A1:AN1"].Style.Font.Bold = true;  
  4.                    worksheet.DefaultRowHeight = 18;  
  5.   
  6.   
  7.                    worksheet.Column(2).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;  
  8.                    worksheet.Column(6).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;  
  9.                    worksheet.Column(7).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;  
  10.                    worksheet.DefaultColWidth = 20;  
  11.                    worksheet.Column(2).AutoFit();  
Using this line, we stored the worksheet bytes into sessions for downloading from other methods.
  1. Session["DownloadExcel_FileManager"] = excelPackage.GetAsByteArray();  
Then, we called the Download() method for downloading the Excel file.
 
Step 3 - Call from View  
 
We are writing this code from the View side so as to display a button for exporting the Excel sheet. On the click of it, it calls the ExcelExport method. If this method gets called without an error, then we can call the Download method in the success part of the AJAX call, for downloading the Excel File. 
  1. <button onclick="DownloadExcel()" class="btn  btn-success" style="margin-top:20px;">Excel Export</button>  
  2.   
  3. <script>  
  4.     function DownloadExcel() {  
  5.         $.ajax({  
  6.             type: "POST",  
  7.             url: "/Excel/ExcelExport",  
  8.             cache: false,  
  9.             success: function (data) {  
  10.                 window.location = '/Excel/Download';  
  11.             },  
  12.             error: function (data) {  
  13.                 Materialize.toast("Something went wrong. ", 3000, 'rounded');  
  14.             }  
  15.         });  
  16.     }  
  17. </script>  

Output

 
Excel Export In MVC.NET