There are many times we require certain reports to be exported to certain require Files i.e Export to Excel, PDF and CSV but for today we can see how we can export to excel with querying to database through
LINQ to SQL query.
So let's start to build interface buttons which will do the rest of work to query and export to excel.
- <asp:Button ID="btnGetData" runat="server" Text="Export to Excel" OnClick="btnGetData_Click" />
This button has OnClick (btnGetData_Click) Event which will fire when we click on it, before starting we will add the following references to the project. on the upper section where reference are defined and also by adding in the
Reference folder of solution.
- using System.Web;
- using System.Drawing;
- using System.Reflection;
- using System.Data;
- using System.Linq;
- using OfficeOpenXml;
- using OfficeOpenXml.Style;
- using OfficeOpenXml.Table;
Now we will write the backend logic of button.
- protected void btnGetData_Click(object sender, EventArgs e)
- {
-
- DataTable dt = new DataTable();
- List<NewsFeed> NewsFeeds = (from emp in db.NewsFeed select emp).ToList<NewsFeed>();
- if (NewsFeeds.Any())
- {
- ListtoDataTableConverter converter = new ListtoDataTableConverter();
- dt = converter.ToDataTable(NewsFeeds);
- ExportExcel(dt);
- }
-
- }
Now I will explain the line of code as you can see we have a button event which will fire on click.
FIrst of all we create DataTable and initialize it on the start which will hold the converted data.
- DataTable dt = new DataTable();
Now we have write the linq to sql query and all the result which will fetch through query is moved to the list.
- List<NewsFeed> NewsFeeds = (from emp in db.NewsFeed select emp).ToList<NewsFeed>();
After that we will check as a best practice, is there any data returned by the query by writing line of code like this
If list variable contains any thing we have use .Any() as a best practice to check the content length otherwise you can use .Count() method too to check for any count of returned row to do the rest of processing.
- if (NewsFeeds.Count())
- {
-
- }
Now we will create new class and initialize it to convert the list structure to datatable to ease our work as per our requirement.
- ListtoDataTableConverter converter = new ListtoDataTableConverter();
Complete class is elaborated below.
- public class ListtoDataTableConverter
- {
- public DataTable ToDataTable<T>(List<T> items)
- {
- DataTable dataTable = new DataTable(typeof(T).Name);
-
- PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
-
- foreach (PropertyInfo prop in Props)
- {
-
- dataTable.Columns.Add(prop.Name);
- }
-
- foreach (T item in items)
- {
- var values = new object[Props.Length];
- for (int i = 0; i < Props.Length; i++)
- {
-
- values[i] = Props[i].GetValue(item, null);
- }
-
- dataTable.Rows.Add(values);
-
- }
-
- return dataTable;
- }
- }
I have elaborate every piece of code with comments what is going on while converting list to dataTable. We are using the DataTable method to convert it to the datatable from list inside class ListtoDataTableConverter.
- dt = converter.ToDataTable(NewsFeeds);
Finally, we will use the Export to Excel function by allocating the content type to convert it to the appropriate format. We will call export method and pass converted dataTable from list.
For conversion to excel purpose I am using microsoft built in library called open office XML Extension.
Right click on References folder of project and select Add Reference.
Add below highlighted
Extensions in to the project.
Add This Method to File.
- private void ExportExcel(DataTable dt)
- using (ExcelPackage pck = new ExcelPackage())
- {
-
- ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ExportReport");
-
- ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15);
-
-
- Byte[] fileBytes = pck.GetAsByteArray();
- HttpContext.Current.Response.ClearContent();
-
- HttpContext.Current.Response.AddHeader("content- disposition","attachment;filename=Exported_" + 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();
-
- }
-
Code Elaboration:
-
- ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ExportReport");
-
-
- ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15);
-
- Byte[] fileBytes = pck.GetAsByteArray();
-
-
- HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Exported_" + 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();
-
Final Output