Export Files In Excel Using EPPlus And LINQ

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.

<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,

using OfficeOpenXml;
using OfficeOpenXml.Style;
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:

DataTable dt = new DataTable();
//here you have to add your class like student etc.
List<NewsFeed> NewsFeeds = (from news in db.NewsFeed select news).ToList<NewsFeed>();
ListtoDataTableConverter converter = new ListtoDataTableConverter();
dt = converter.ToDataTable(NewsFeeds);
ExportExcel(dt);

Step 5: In this step convert our list of data into DataTable:

public class ListtoDataTableConverter
{
    public DataTable ToDataTable < T > (List < T > items)
    {
        DataTable dataTable = new DataTable(typeof(T).Name);
        //Get all the properties
        PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach(PropertyInfo prop in Props)
        {
            //Setting column names as Property names
            dataTable.Columns.Add(prop.Name);
        }

        foreach(T item in items)
        {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    //inserting property values to datatable rows
                    values[i] = Props[i].GetValue(item, null);
                }

                dataTable.Rows.Add(values);

            }
            //put a breakpoint here and check datatable
        return dataTable;
    }
}

Step 6: In this step give out data in data table form to export it.

private void ExportExcel(DataTable dt)
{
    using(ExcelPackage pck = new ExcelPackage())
    {
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("SearchReport");
        ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15); //You can Use TableStyles property of your desire.
        //Read the Excel file in a byte array
        Byte[] fileBytes = pck.GetAsByteArray();
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Mohsin Azam_" + DateTime.Now.ToString("M_dd_yyyy_H_M_s") + ".xlsx");
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        HttpContext.Current.Response.BinaryWrite(fileBytes);
        HttpContext.Current.Response.End();

    }
}

Step 7: Save all your changes and rebuild.