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>");

{

            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");

              //sets font

            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++)

            {

//Makes headers bold

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 5: Add a button and in the buttonclick event and in it call the above method by ing a parameter:

protected void Btn_Export_Click(object sender, EventArgs e)
{
    ExporttoExcel(dt);
}


For the complete source code, please find the attached solution.
 

Next Recommended Readings