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
 

Comments

  1. In the "ExportClientsListToCSV" method, you should add Birthday to the headers as well:
    sw.WriteLine("\"First Name\",\"Last Name\",\"Birthday\",\"Email\"");

    ReplyDelete
  2. Great thanks Adam, great knowledge!!!

    ReplyDelete
    Replies
    1. why you use query for data source because table always display and i want to use session

      Delete
  3. excellent article.
    exactly what I was looking for

    Thank you

    ReplyDelete
  4. Melhor tutorial que encontrei até agora. Funcionou perfeitamente. Obrigada.

    Best tutorial I've found so far. It worked perfectly. Thank you.

    ReplyDelete
  5. How to export a excel with multiple sheets?

    In this example we can export a excel which has only one worksheet.

    I want to export an excel which has multiple sheets.

    ReplyDelete
  6. the code is very good and clear but how we need to connect to the DB.

    ReplyDelete

Post a Comment