Export Data To Excel Or CSV Files From Your MVC Application Using Entity Framework

In this blog, you will learn about exporting data to Excel and CSV files from Entity Framework using MVC applications.

In this blog, I will show how to export data using LINQ query using Entity Framework. Here is my table.

Export data to Excle file

Here is my code to export data in .xls format.

public void ExportExcel_EmployeeData() {
    var sb = new StringBuilder();
    var data = from s in odb.Employee // Odb is the object of edmx file
    select new {
        // You can choose column name according your need
        s.FirstName,
            s.LastName,
            s.Address,
            s.BirdthDate,
            s.City,
            s.Region,
            s.salary, s.Notes
    };
    var list = data.ToList();
    var grid = new System.Web.UI.WebControls.GridView();
    grid.DataSource = list;
    grid.DataBind();
    Response.ClearContent();
    Response.AddHeader("content-disposition", "attachment; filename=Emp.xls");
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw)
    grid.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.End();
}

Export Data to CSV File

Here is my code to export data in .csv format.

public void ExportCSV_Employee()
{
    var sb = new StringBuilder();
    // You can write sql query according your need
    string qry = "Select * from Employee");
IEnumerable < Employee > query = odbe.Database.SqlQuery < Employee > (qry);
var list = query.ToList();
sb.AppendFormat("{0},{1},{2},{3},{4},{5},{6}", "First Name", "Last Name", "Address ", "BirdthDate", "City", "Salry", Environment.NewLine);
foreach(var item in list)
{
    sb.AppendFormat("{0},{1},{2},{3},{4},{5},{6}", item.FirstName, item.LastName, item.Address, item.BirthDate.Value.ToShortDateString(), item.City, Item.salary, item.ExportedTime, Environment.NewLine);
}
//Get Current Response
var response = System.Web.HttpContext.Current.Response;
response.BufferOutput = true;
response.Clear();
response.ClearHeaders();
response.ContentEncoding = Encoding.Unicode;
response.AddHeader("content-disposition", "attachment;filename=Employee.CSV ");
response.ContentType = "text/plain";
response.Write(sb.ToString());
response.End();
}

Here is the View Code to call these functions.

@Html.ActionLink("ExportCSV", "ExportCSV_Employee", "Users", null, new { @class = "btn btn-primary"})
Html.ActionLink("ExportExcel", "ExportExcel_EmployeeData", "Users", null, new { @class = "btn btn-primary"})