Export DataTable to Excel With Formatting in C#

In this document, let us see how to export a DataTable to Excel file and add formatting to the contents while writing the Excel files.

Step 1: Create a web application and add a class named Student with the properties as below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Reflection;

namespace ExportToExcelFromDataTable
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)

{

}

}
public class Student
{
public string Name { get; set; }
public int StudentId { get; set; }
public int Age { get; set; }
}
}

Step 2: I have added Gridview_Result. Create a list for students in page_load event. Add a property dt of type DataTable. Bind the DataTable to the GridView after converting the list to a DataTable. The conversion class is described in the next step.

protected void Page_Load(object sender, EventArgs e)
{
List<Student> Students = new List<Student>(){
new Student() { Name = "Jack", Age = 15, StudentId = 100 },
new Student() { Name = "Smith", Age = 15, StudentId = 101 },
new Student() { Name = "Smit", Age = 15, StudentId = 102 }
};
ListtoDataTableConverter converter = new ListtoDataTableConverter();
dt = converter.ToDataTable(Students);
GridView_Result.DataSource = Students;
GridView_Result.DataBind();
}


Step 3: Now we are going to convert this list object to a DataTable. For that we need to create a new class and a conversion method as 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;
}
}

Please look into the following article for more detail. List to DataTable Converter

Step 4: I have written the following method which will convert a DataTable to an Excel file. In this method, I added a font, made headers bold and added a border. You can customize the method as you need.

private void ExporttoExcel(DataTable table)
HttpContext.Current.Response.Write("<Td>");