Export To Excel Data By LINQ to SQL

Article

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 (NewsFeeds.Any())
{
}

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);
//Get all the properties
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
// Loop through all the properties
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);
}
// Finally add value to datatable
dataTable.Rows.Add(values);
}
//put a breakpoint here and check datatable of return 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.

ExportExcel(dt);

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())
{
// Excel Sheet name
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ExportReport");
// pass the cell from where we start dumping data and load the data from datatable
ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15);
//Read the Excel file in a byte array
Byte[] fileBytes = pck.GetAsByteArray();
HttpContext.Current.Response.ClearContent();
// Add the content disposition (file name to be customizable) to be exported.
HttpContext.Current.Response.AddHeader("content- disposition","attachment;filename=Exported_" + DateTime.Now.ToString("M_dd_yyyy_H_M_s") + ".xlsx");
// add the required content type
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// write the bytes to the file and end the response
HttpContext.Current.Response.BinaryWrite(fileBytes);
HttpContext.Current.Response.End();
}

Code Elaboration:

// Excel Sheet name
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ExportReport");
// pass the cell from where we start dumping data and load the data from datatable, also you can add styling by using table style and check name from excel file for required design
ws.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.Medium15);
//Read the Excel file in a byte array
Byte[] fileBytes = pck.GetAsByteArray();
// Add the content disposition (file name to be customizable) to be exported.
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=Exported_" + DateTime.Now.ToString("M_dd_yyyy_H_M_s") + ".xlsx");
// add the required content type
HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// write the bytes to the file and end the response
HttpContext.Current.Response.BinaryWrite(fileBytes);
HttpContext.Current.Response.End();

Final Output