Introduction
In this tip, let us see how to export a DataTable to an Excel file and add format to the contents while writing the
Excel file.
Step 1: Create a web application and add a class Student with 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
the 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;
}
}
The above method will set the property name as a column name for the DataTable and for each object in the list; it will create a new row in the DataTable and insert values.
Step 4: I have written the below method which will convert a DataTable to
an Excel file. In this method, I added font, made headers bold, and added a border. You can
customize the method as per your needs.
private void ExporttoExcel(DataTable table)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
int columnscount = GridView_Result.Columns.Count;
for (int j = 0; j < columnscount; j++)
{ HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write("<B>");
HttpContext.Current.Response.Write(GridView_Result.Columns[j].HeaderText.ToString());
HttpContext.Current.Response.Write("</B>");
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{ HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}
HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}
Step 4: Add a button and in the button click event, call the above method by passing a parameter.
protected void Btn_Export_Click(object sender, EventArgs e)
{
ExporttoExcel(dt);
}
For the complete source code, please find the attached solution.
Started my career with Infosys and currently working with Education First. I have great passion towards Microsoft technologies. I have experience in Microsoft technologies like WPF, WCF, ASPNET, WinForms,Silverlight, VB.NET, C-Sharp Entity framework,SSRS, LINQ, Extension methods and SQL server.
Submit an article or tip
Post your Blog
News 
Bug 
Joke 
Rant 
Admin 
