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
- CREATE TABLE [dbo].[Employee](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [Name] [varchar](50) NULL,
- [Email] [varchar](500) NULL,
- [Country] [varchar](50) NULL
- ) ON [PRIMARY]
- 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
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
-
- namespace ExportDataTableToExcelInMVC4.Models {
- public class ExportDataTableToExcelModel {
- public int Id {
- get;
- set;
- }
- public string Name {
- get;
- set;
- }
- public string Email {
- get;
- set;
- }
- public string Country {
- get;
- set;
- }
- }
- }
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.
- using System;
- using System.Collections.Generic;
- using System.Configuration;
- using System.Data;
- using System.Data.SqlClient;
- using System.Linq;
- using System.Web;
- using System.Web.Mvc;
- using ExportDataTableToExcelInMVC4.Models;
- using ClosedXML;
- using ClosedXML.Excel;
- using System.IO;
-
- namespace ExportDataTableToExcelInMVC4.Controllers {
- public class ExportDataController: Controller {
- public ActionResult Index() {
- String constring = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
- SqlConnection con = new SqlConnection(constring);
- string query = "select * From Employee";
- DataTable dt = new DataTable();
- con.Open();
- SqlDataAdapter da = new SqlDataAdapter(query, con);
- da.Fill(dt);
- con.Close();
- IList < ExportDataTableToExcelModel > model = new List < ExportDataTableToExcelModel > ();
- for (int i = 0; i < dt.Rows.Count; i++) {
- model.Add(new ExportDataTableToExcelModel() {
- Id = Convert.ToInt32(dt.Rows[i]["Id"]),
- Name = dt.Rows[i]["Name"].ToString(),
- Email = dt.Rows[i]["Email"].ToString(),
- Country = dt.Rows[i]["Country"].ToString(),
- });
- }
- return View(model);
- }
-
- public ActionResult ExportData() {
- String constring = ConfigurationManager.ConnectionStrings["RConnection"].ConnectionString;
- SqlConnection con = new SqlConnection(constring);
- string query = "select * From Employee";
- DataTable dt = new DataTable();
- dt.TableName = "Employee";
- con.Open();
- SqlDataAdapter da = new SqlDataAdapter(query, con);
- da.Fill(dt);
- con.Close();
-
- using(XLWorkbook wb = new XLWorkbook()) {
- wb.Worksheets.Add(dt);
- wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
- wb.Style.Font.Bold = true;
-
- Response.Clear();
- Response.Buffer = true;
- Response.Charset = "";
- Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
- Response.AddHeader("content-disposition", "attachment;filename= EmployeeReport.xlsx");
-
- using(MemoryStream MyMemoryStream = new MemoryStream()) {
- wb.SaveAs(MyMemoryStream);
- MyMemoryStream.WriteTo(Response.OutputStream);
- Response.Flush();
- Response.End();
- }
- }
- return RedirectToAction("Index", "ExportData");
- }
-
- private void releaseObject(object obj) {
- try {
- System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
- obj = null;
- } catch {
- obj = null;
- } finally {
- GC.Collect();
- }
- }
- }
- }
Now right-click on the Index Folder then select Add View.
Image 8 Add View
View.cshtml
- @model IEnumerable
- <ExportDataTableToExcelInMVC4.Models.ExportDataTableToExcelModel>
-
- @{
- ViewBag.Title = "Index";
- }
- @using (Html.BeginForm("ExportData", "ExportData", FormMethod.Post))
- {
-
- <p style="background-color: red; color: white; font-size: 20pt; font-weight: bold; padding: 10px; text-align: center;">
- Export DataTable To Excel In MVC 4
- </p>
- <table style="background-color: white; width: 100%;">
- <tr>
- <th style="border: 2px solid black; text-align: left; width: 20%; padding-left: 20px;">
- @Html.DisplayNameFor(model => model.Name)
- </th>
- <th style="border: 2px solid black; text-align: center; width: 20%">
- @Html.DisplayNameFor(model => model.Email)
- </th>
- <th style="border: 2px solid black; text-align: center; width: 20%">
- @Html.DisplayNameFor(model => model.Country)
- </th>
- <th></th>
- </tr>
- @foreach (var item in Model)
- {
-
- <tr>
- <td style="padding-left: 20px;">
- @Html.DisplayFor(modelItem => item.Name)
- </td>
- <td style="padding-left: 20px;">
- @Html.DisplayFor(modelItem => item.Email)
- </td>
- <td style="padding-left: 50px;">
- @Html.DisplayFor(modelItem => item.Country)
- </td>
- </tr>
- }
-
- <tr>
- <td></td>
- <td></td>
- <td>
- <input type="submit" name="btnExportLicensing" style="width: 140px;"
- value="Export" id="exportLicensing" class="button" />
- </td>
- </tr>
- </table>
- }
The following is my connection string in Web.config:
- <connectionStrings>
- <add name="RConnection" connectionString="Server=INDIA\MSSQLServer2k8;database=TestDB;UID=sa; pwd=india;" />
- </connectionStrings>
Now run your application.
Image 9 Excel In MVC4
Image 10 Export DataTable to Excel
![]()
Image 11 Employee Report