Export Files In Excel Using EPPlus And LINQ

Mohsin Azam

LINQ EPPlusExport Files in Excel Using EPPlus

10 Comments

Join the conversation! Your thoughts help the community grow.

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,

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.