Export Data Table To Excel in ASP.Net MVC 4

This article shows how to export a Data Table to Excel in ASP.NET MVC.

The following is my Data Table in design mode.


Image 1 Design Mode

Script Of Employee Table

  1. CREATE TABLE [dbo].[Employee](  
  2. [ID] [int] IDENTITY(1,1) NOT NULL,  
  3. [Name] [varchar](50) NULL,  
  4. [Email] [varchar](500) NULL,  
  5. [Country] [varchar](50) NULL  
  6. ON [PRIMARY]  
  7. GO
Data in Employee Table


Image 2 Employee Table

Now open Visual Studio 2012 then select New -> Project:


Image 3 New Project


Image 4 New ASP.Net MVC

Now add a ClosedXML reference:


Image 5 Export Data Table

Now right-click on the Model folder then select Add New Class. ExportDataTableToExcelModel.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5.   
  6. namespace ExportDataTableToExcelInMVC4.Models {  
  7.     public class ExportDataTableToExcelModel {  
  8.         public int Id {  
  9.             get;  
  10.             set;  
  11.         }  
  12.         public string Name {  
  13.             get;  
  14.             set;  
  15.         }  
  16.         public string Email {  
  17.             get;  
  18.             set;  
  19.         }  
  20.         public string Country {  
  21.             get;  
  22.             set;  
  23.         }  
  24.     }  
  25. }
Now right-click on the Controller Folder then select Add -> Controller.


Image 6 Controller


Image 7 Add Controller

Now here in ExportDataController add the following code.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.Web.Mvc;  
  9. using ExportDataTableToExcelInMVC4.Models;  
  10. using ClosedXML;  
  11. using ClosedXML.Excel;  
  12. using System.IO;  
  13.   
  14. namespace ExportDataTableToExcelInMVC4.Controllers {  
  15.     public class ExportDataController: Controller {  
  16.         public ActionResult Index() {  
  17.             String constring = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;  
  18.             SqlConnection con = new SqlConnection(constring);  
  19.             string query = "select * From Employee";  
  20.             DataTable dt = new DataTable();  
  21.             con.Open();  
  22.             SqlDataAdapter da = new SqlDataAdapter(query, con);  
  23.             da.Fill(dt);  
  24.             con.Close();  
  25.             IList < ExportDataTableToExcelModel > model = new List < ExportDataTableToExcelModel > ();  
  26.             for (int i = 0; i < dt.Rows.Count; i++) {  
  27.                 model.Add(new ExportDataTableToExcelModel() {  
  28.                     Id = Convert.ToInt32(dt.Rows[i]["Id"]),  
  29.                     Name = dt.Rows[i]["Name"].ToString(),  
  30.                     Email = dt.Rows[i]["Email"].ToString(),  
  31.                     Country = dt.Rows[i]["Country"].ToString(),  
  32.                 });  
  33.             }  
  34.             return View(model);  
  35.         }  
  36.   
  37.         public ActionResult ExportData() {  
  38.             String constring = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;  
  39.             SqlConnection con = new SqlConnection(constring);  
  40.             string query = "select * From Employee";  
  41.             DataTable dt = new DataTable();  
  42.             dt.TableName = "Employee";  
  43.             con.Open();  
  44.             SqlDataAdapter da = new SqlDataAdapter(query, con);  
  45.             da.Fill(dt);  
  46.             con.Close();  
  47.   
  48.             using(XLWorkbook wb = new XLWorkbook()) {  
  49.                 wb.Worksheets.Add(dt);  
  50.                 wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;  
  51.                 wb.Style.Font.Bold = true;  
  52.   
  53.                 Response.Clear();  
  54.                 Response.Buffer = true;  
  55.                 Response.Charset = "";  
  56.                 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
  57.                 Response.AddHeader("content-disposition""attachment;filename= EmployeeReport.xlsx");  
  58.   
  59.                 using(MemoryStream MyMemoryStream = new MemoryStream()) {  
  60.                     wb.SaveAs(MyMemoryStream);  
  61.                     MyMemoryStream.WriteTo(Response.OutputStream);  
  62.                     Response.Flush();  
  63.                     Response.End();  
  64.                 }  
  65.             }  
  66.             return RedirectToAction("Index""ExportData");  
  67.         }  
  68.   
  69.         private void releaseObject(object obj) {  
  70.             try {  
  71.                 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);  
  72.                 obj = null;  
  73.             } catch {  
  74.                 obj = null;  
  75.             } finally {  
  76.                 GC.Collect();  
  77.             }  
  78.         }  
  79.     }  
  80. }
Now right-click on the Index Folder then select Add View.


Image 8 Add View

View.cshtml
  1. @model IEnumerable  
  2. <ExportDataTableToExcelInMVC4.Models.ExportDataTableToExcelModel>  
  3.   
  4. @{  
  5. ViewBag.Title = "Index";  
  6. }  
  7. @using (Html.BeginForm("ExportData", "ExportData", FormMethod.Post))  
  8. {   
  9.   
  10.     <p style="background-color: red; color: white; font-size: 20pt; font-weight: bold; padding: 10px; text-align: center;">  
  11. Export DataTable To Excel In MVC 4   
  12. </p>  
  13.     <table style="background-color: white; width: 100%;">  
  14.         <tr>  
  15.             <th style="border: 2px solid black; text-align: left; width: 20%; padding-left: 20px;">  
  16. @Html.DisplayNameFor(model => model.Name)  
  17. </th>  
  18.             <th style="border: 2px solid black; text-align: center; width: 20%">  
  19. @Html.DisplayNameFor(model => model.Email)  
  20. </th>  
  21.             <th style="border: 2px solid black; text-align: center; width: 20%">  
  22. @Html.DisplayNameFor(model => model.Country)  
  23. </th>  
  24.             <th></th>  
  25.         </tr>  
  26. @foreach (var item in Model)  
  27. {  
  28.   
  29.         <tr>  
  30.             <td style="padding-left: 20px;">  
  31. @Html.DisplayFor(modelItem => item.Name)  
  32. </td>  
  33.             <td style="padding-left: 20px;">  
  34. @Html.DisplayFor(modelItem => item.Email)  
  35. </td>  
  36.             <td style="padding-left: 50px;">  
  37. @Html.DisplayFor(modelItem => item.Country)  
  38. </td>  
  39.         </tr>  
  40. }  
  41.   
  42.         <tr>  
  43.             <td></td>  
  44.             <td></td>  
  45.             <td>  
  46.                 <input type="submit" name="btnExportLicensing" style="width: 140px;"  
  47. value="Export" id="exportLicensing" class="button" />  
  48.             </td>  
  49.         </tr>  
  50.     </table>  
  51. }
The following is my connection string in Web.config:
  1. <connectionStrings>   
  2. <add name="RConnection" connectionString="Server=INDIA\MSSQLServer2k8;database=TestDB;UID=sa; pwd=india;" />  
  3. </connectionStrings>
Now run your application.


Image 9 Excel In MVC4


Image 10 Export DataTable to Excel


Image 11
Employee Report

Up Next
    Ebook Download
    View all
    Learn
    View all