Export Data to Excel with ASP.NET MVC

In creating a system, there are times that we need to export data to excel with ASPNET MVC. It could either be for reports or data analysis. In this article, we will create a Web Application that export data to excel from a table.

Below is one of those methods. To start, we need to create a fresh project of ASP.NET MVC. If this is your first time creating ASP.NET MVC, you may refer to this article “How to start with ASP.NET MVC.”

Output Preview:

Let’s start:

I assume you have already created a new project with default MVC template.

First, open HomeController. Located inside Controller folder, remove all action result except for index().

It should look like this:

Second, let’s create a view where we can display sample data into a table. To open your view, navigate Views->Home->index.cshtml or simply right click inside index action result and select Go to View.

Replace all code from your index.cshtml with the code below.

@{
ViewBag.Title = "Home Page";
}
<br />
<div class="container">
 <a href="@Url.Action("exportToExcel", "Home")" class="btn btn-default">Export to excel</a>
 <br />
 <br />
   <table class="table table-responsive table-bordered" >
     <thead>
       <tr>
          <th>Employee</th>
          <th>Age</th>
          <th>Address</th>
          <th>BirthDate</th>
          <th>BirthPlace</th>
      </tr>
     </thead>
     <tbody id="sample_data">

     </tbody>
   </table>
</div>

Third, create a model class inside Models folder. This will be used in creating our sample data and declare all properties shown below.

Code:

public class TableModel
{
  public string EmployeeName { get; set; }
  public string Age { get; set; }
  public string Address { get; set; }
  public string birthdate { get; set; }
  public string birthplace { get; set; }
}

Image preview:

Fourth, Create a sample data. Navigate back to your HomeController and create method sample_data. 

Code:

public string sample_data()
{
List<TableModel> data = new List<TableModel>();

   data.Add(new TableModel
    {
     EmployeeName = "Regie S. Baquero",
     Age = "25",
     Address = "Cebu City",
     birthdate = "01/01/1999",
     birthplace = "Philippines"
    });

string sample_data = JsonConvert.SerializeObject(data);

return sample_data;
}

Fifth, fill the HTML table that we created a while ago using Jquery. Navigate back to your controller view. Add the code below.

Code:

<script src="~/Scripts/jquery-1.10.2.js"></script>
<script>

$(document).ready(function () {
      $.ajax({
          url: '@Url.Action("sample_data", "Home")',
          method: 'GET',
          success: function (res) {
            var parse_data = JSON.parse(res);
            $("#sample_data").html(table_template(parse_data));
            }
          });
         });

function table_template(data)
{
    var temp = "";

    for (var i = 0; i < data.length; i++)
        {
            temp += " <tr>" +
                    " <th>" + data[i].EmployeeName + "</th>" +
                    " <th>"+ data[i].Age +"</th>" +
                    " <th>"+ data[i].Address+"</th>" +
                    " <th>" + data[i].birthdate + "</th>" +
                    " <th>" + data[i].birthplace + "</th>" +
                    " </tr>";
        }
      return temp;
     }
 </script>

Lastly, create exportToExcel method inside your HomeController.

Code:

public void exportToExcel()
{
   List<TableModel> data = new List<TableModel>();

   data = JsonConvert.DeserializeObject<List<TableModel>>(sample_data());

   DataTable container = new DataTable();
   container.Columns.Add("EmployeeName");
   container.Columns.Add("Age");
   container.Columns.Add("Address");
   container.Columns.Add("birthdate");
   container.Columns.Add("birthplace");
    foreach (var list in data.ToList())
     {
      container.Rows.Add(list.EmployeeName,list.Age,list.Address,list.birthdate,list.birthplace);
     }

   var grid = new System.Web.UI.WebControls.GridView();
   grid.DataSource = container;
   grid.DataBind();
   Response.ClearContent();
   Response.AddHeader("content-disposition", "attachment; filename=Branch_Pricing.xls");
   Response.ContentType = "application/excel";
   StringWriter sw = new StringWriter();
   HtmlTextWriter htw = new HtmlTextWriter(sw);

grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

}

And we’re done. This method is one of the many ways to Export Data to Excel with ASPNET MVC. To run the project, simply press the F5 key from your keyboard. Thank you for reading. Happy coding!!

To review your work please refer from the full code below… 🙂

Full Code:

TableModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Download_Excel.Models
{
 public class TableModel
 {
  public string EmployeeName { get; set; }
  public string Age { get; set; }
  public string Address { get; set; }
  public string birthdate { get; set; }
  public string birthplace { get; set; }
 }
}

HomeController.cs

using Download_Excel.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.UI;

namespace Download_Excel.Controllers
{
  public class HomeController : Controller
  {
    public ActionResult Index()
     {
       return View();
     }
    public string sample_data()
     {
       List<TableModel> data = new List<TableModel>();

       data.Add(new TableModel
       {
         EmployeeName = "Regie S. Baquero",
         Age = "25",
         Address = "Cebu City",
         birthdate = "01/01/1999",
         birthplace = "Philippines"
       });

         string sample_data = JsonConvert.SerializeObject(data);

    return sample_data;
   }

   public void exportToExcel()
    {
      List<TableModel> data = new List<TableModel>();

      data = JsonConvert.DeserializeObject<List<TableModel>>(sample_data());

      DataTable container = new DataTable();
      container.Columns.Add("EmployeeName");
      container.Columns.Add("Age");
      container.Columns.Add("Address");
      container.Columns.Add("birthdate");
      container.Columns.Add("birthplace");
      foreach (var list in data.ToList())
       {
         container.Rows.Add(list.EmployeeName,list.Age,list.Address,list.birthdate,list.birthplace);
       }

      var grid = new System.Web.UI.WebControls.GridView();
      grid.DataSource = container;
      grid.DataBind();
      Response.ClearContent();
      Response.AddHeader("content-disposition", "attachment; filename=Branch_Pricing.xls");
      Response.ContentType = "application/excel";
      StringWriter sw = new StringWriter();
      HtmlTextWriter htw = new HtmlTextWriter(sw);

      grid.RenderControl(htw);
      Response.Write(sw.ToString());
      Response.End();
    }

  }
}

Index.cshtml

@{
ViewBag.Title = "Home Page";
}
<br />
<div class="container">
 <a href="@Url.Action("exportToExcel", "Home")" class="btn btn-default">Export to excel</a>
 <br />
 <br />
   <table class="table table-responsive table-bordered" >
     <thead>
       <tr>
          <th>Employee</th>
          <th>Age</th>
          <th>Address</th>
          <th>BirthDate</th>
          <th>BirthPlace</th>
      </tr>
     </thead>
     <tbody id="sample_data">

     </tbody>
   </table>
</div>
<script src="~/Scripts/jquery-1.10.2.js"></script>
<script>

$(document).ready(function () {
   $.ajax({
       url: '@Url.Action("sample_data", "Home")',
    method: 'GET',
   success: function (res) {
     var parse_data = JSON.parse(res);
     $("#sample_data").html(table_template(parse_data));
   }
 });
});

function table_template(data)
{
  var temp = "";

  for (var i = 0; i < data.length; i++)
  {
   temp += " <tr>" +
           " <th>" + data[i].EmployeeName + "</th>" +
           " <th>"+ data[i].Age +"</th>" +
           " <th>"+ data[i].Address+"</th>" +
           " <th>" + data[i].birthdate + "</th>" +
           " <th>" + data[i].birthplace + "</th>" +
           " </tr>";
  }
return temp;
}
</script>

Final output: