Monday, 13 May 2013

Exporting data to Excel and CSV in ASP.NET MVC application





Consolidate your mvc knowledge with this book : http://amzn.to/2uC6axk
If you are looking for old school asp.net webforms : http://amzn.to/2uyLaI0

We want to be able to export some information to Excel file or CSV file. We are going to create a simple class that will hold basic Client information (First name, last name, Date of birth and Email) and we are going to populate it with some dummy data to display it on a View (table) and then export it to Excel/CSV.
Within your new ASP.Net MVC (lets name it ExportToExcel_PDF_CSV) application in Model folder create a new class and call it ClientsList.cs.


You can download project from here : ExportToExcelCSVASPMVC
MODEL
  public class ClientsList
    {
        public ClientsList(string firstname, string lastname, DateTime dob, string email)
        {
            this.FirstName = firstname;
            this.LastName = lastname;
            this.Dob = dob;
            this.Email = email;
        }

        public string FirstName { set; get; }
        public string LastName { set; get; }
        public string Email { set; get; }
        public DateTime Dob { set; get; }

    }

Now in Home controller we are going to populate this class with some data. Make sure you instantiate this List within class so it will be available in every method in this controller.

CONTROLLER
public class HomeController : Controller
    {

List<ClientsList> ClientsList = new List<ClientsList>
            {
                 new ClientsList ( "Adam",  "Bielecki",  DateTime.Parse("22/05/1986"),       "adamb@example.com" ), 
                 new ClientsList (  "George", "Smith",  DateTime.Parse("10/10/1990"),  "george@example.com" )
            };

public ActionResult Index()
        {
            return View(ClientsList);
        }

}
VIEW
We are going to display data that came from controller and model we are using is List<ClientsList>.
@{
    ViewBag.Title = "Export";
}
@model List<ExportToExcel_PDF_CSV.Models.ClientsList>
<h2>
    Export to PDF, Excel CSV Example</h2>

    <table>
        <thead>
            <tr>
                <th>
                    First Name
                </th>
                <th>
                    Last Name
                </th>
                <th>
                    Date Of Birth Name
                </th>
                <th>
                    Email
                </th>
            </tr>
        </thead>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @item.FirstName
                </td>
                <td>@item.LastName
                </td>
                <td>@item.Dob
                </td>
                <td>
                    @item.Email
                </td>
            </tr>
        }
    </table>
    <div class="ExportSection">
        @Html.ActionLink("Export to Csv", "ExportClientsListToCSV")
        @Html.ActionLink("Export to Excel", "ExportClientsListToExcel")
       
    </div>


Add some styling to your Site.css style sheet.

/* Custom added

----------------------------------------------------------*/

.ExportSection
{
    margin:10px 0;
    float:left;
   
}
.ExportSection a
{
    text-decoration:none;
    padding:0 5px 0 0;
   
}





Now in controller we want to add 2 methods:
ExportClientsListToExcel
ExportClientsListToCSV

   public void ExportClientsListToCSV()
        {
         
            StringWriter sw = new StringWriter();

            sw.WriteLine("\"First Name\",\"Last Name\",\"DOB\",\"Email\"");

            Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment;filename=Exported_Users.csv");
            Response.ContentType = "text/csv";

            foreach (var line in ClientsList)
            {
                sw.WriteLine(string.Format("\"{0}\",\"{1}\",\"{2}\",\"{3}\"",
                                           line.FirstName,
                                           line.LastName,
                                           line.Dob,
                                           line.Email));
            }

            Response.Write(sw.ToString());

            Response.End();

        }


public void ExportClientsListToExcel()
        {
            var grid = new System.Web.UI.WebControls.GridView();

            grid.DataSource = /*from d in dbContext.diners
                              where d.user_diners.All(m => m.user_id == userID) && d.active == true */
                              from d in ClientsList
                              select new
                              {
                                  FirstName = d.FirstName,
                                  LastName = d.LastName,
                                  DOB = d.Dob,
                                  Email = d.Email

                              };

            grid.DataBind();

            Response.ClearContent();
       Response.AddHeader("content-disposition", "attachment; filename=Exported_Diners.xls");
            Response.ContentType = "application/excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);

            grid.RenderControl(htw);

            Response.Write(sw.ToString());

            Response.End();

        }

In both cases we are using HttpResponseBase (Response) and stream writer.
In excel export we are using grid, so we can bind data to it.

You can download project from here : ExportToExcelCSVASPMVC