In this article we will learn how to export data in excel file using EPPlus dll and LINQ. Here are the steps to start:

Step 1: Add new WebForm e.g: Default.aspx in your Application, and then add the following code snippet in your code after div tag.
  1. <asp:Button ID="btnGetData" runat="server" Text="GetData" OnClick="btnGetData_Click" />
Step 2: Now add EPPlus dll in Reference, the dll can be downloaded from the above link.

Step 3: Add this using,
  1. using OfficeOpenXml;
  2. using OfficeOpenXml.Style;
  3. using OfficeOpenXml.Table;
Step 4: Now in Design View, double click on button and it opens code behind of button. After that add the following code:
  1. DataTable dt = new DataTable();
  2. //here you have to add your class like student etc.
  3. List<NewsFeed> NewsFeeds = (from news in db.NewsFeed select news).ToList<NewsFeed>();
  4. ListtoDataTableConverter converter = new ListtoDataTableConverter();
  5. dt = converter.ToDataTable(NewsFeeds);
  6. ExportExcel(dt);
Step 5: In this step convert our list of data into DataTable:
  1. public class ListtoDataTableConverter
  2. {
  3. public DataTable ToDataTable < T > (List < T > items)
  4. {
  5. DataTable dataTable = new DataTable(typeof(T).Name);
  6. //Get all the properties
  7. PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
  8. foreach(PropertyInfo prop in Props)
  9. {
  10. //Setting column names as Property names
  11. dataTable.Columns.Add(prop.Name);
  12. }
  14. foreach(T item in items)
  15. {
  16. var values = new object[Props.Length];
  17. for (int i = 0; i < Props.Length; i++)
  18. {
  19. //inserting property values to datatable rows
  20. values[i] = Props[i].GetValue(item, null);
  21. }
  23. dataTable.Rows.Add(values);
  25. }
  26. //put a breakpoint here and check datatable
  27. return dataTable;
  28. }
  29. }
Step 6: In this step give out data in data table form to export it.
  1. private void ExportExcel(DataTable dt)
  2. {
  3. using(ExcelPackage pck = new ExcelPackage())
  4. {
  5. ExcelWorksheet ws = pck.Workbook.Worksheets.Add("SearchReport");
  6. ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15); //You can Use TableStyles property of your desire.
  7. //Read the Excel file in a byte array
  8. Byte[] fileBytes = pck.GetAsByteArray();
  9. HttpContext.Current.Response.ClearContent();
  10. HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Mohsin Azam_" + DateTime.Now.ToString("M_dd_yyyy_H_M_s") + ".xlsx");
  11. HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
  12. HttpContext.Current.Response.BinaryWrite(fileBytes);
  13. HttpContext.Current.Response.End();
  15. }
  16. }
Step 7: Save all your changes and rebuild.