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.

  1. public void ExportExcel_EmployeeData() {
  2. var sb = new StringBuilder();
  3. var data = from s in odb.Employee // Odb is the object of edmx file
  4. select new {
  5. // You can choose column name according your need
  6. s.FirstName,
  7. s.LastName,
  8. s.Address,
  9. s.BirdthDate,
  10. s.City,
  11. s.Region,
  12. s.salary, s.Notes
  13. };
  14. var list = data.ToList();
  15. var grid = new System.Web.UI.WebControls.GridView();
  16. grid.DataSource = list;
  17. grid.DataBind();
  18. Response.ClearContent();
  19. Response.AddHeader("content-disposition", "attachment; filename=Emp.xls");
  20. Response.ContentType = "application/vnd.ms-excel";
  21. StringWriter sw = new StringWriter();
  22. System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw)
  23. grid.RenderControl(htw);
  24. Response.Write(sw.ToString());
  25. Response.End();
  26. }

Export Data to CSV File

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

  1. public void ExportCSV_Employee()
  2. {
  3. var sb = new StringBuilder();
  4. // You can write sql query according your need
  5. string qry = "Select * from Employee");
  6. IEnumerable < Employee > query = odbe.Database.SqlQuery < Employee > (qry);
  7. var list = query.ToList();
  8. sb.AppendFormat("{0},{1},{2},{3},{4},{5},{6}", "First Name", "Last Name", "Address ", "BirdthDate", "City", "Salry", Environment.NewLine);
  9. foreach(var item in list)
  10. {
  11. 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);
  12. }
  13. //Get Current Response
  14. var response = System.Web.HttpContext.Current.Response;
  15. response.BufferOutput = true;
  16. response.Clear();
  17. response.ClearHeaders();
  18. response.ContentEncoding = Encoding.Unicode;
  19. response.AddHeader("content-disposition", "attachment;filename=Employee.CSV ");
  20. response.ContentType = "text/plain";
  21. response.Write(sb.ToString());
  22. response.End();
  23. }

Here is the View Code to call these functions.

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