Export DataTable to Excel File

use this code...

    dt = city.GetAllCity();//your datatable    string attachment = "attachment; filename=city.xls";    Response.ClearContent();    Response.AddHeader("content-disposition", attachment);    Response.ContentType = "application/vnd.ms-excel";    string tab = "";    foreach (DataColumn dc in dt.Columns)    {        Response.Write(tab + dc.ColumnName);        tab = "\t";    }    Response.Write("\n");    int i;    foreach (DataRow dr in dt.Rows)    {        tab = "";        for (i = 0; i < dt.Columns.Count; i++)        {            Response.Write(tab + dr[i].ToString());            tab = "\t";        }        Response.Write("\n");    }    Response.End();

This snippet could be faster to implement:

// Example dataDataTable table = new DataTable();table.Columns.AddRange(new[]{ new DataColumn("Key"), new DataColumn("Value") });foreach (string name in Request.ServerVariables)    table.Rows.Add(name, Request.ServerVariables[name]);// This actually makes your HTML output to be downloaded as .xls fileResponse.Clear();Response.ClearContent();Response.ContentType = "application/octet-stream";Response.AddHeader("Content-Disposition", "attachment; filename=ExcelFile.xls");// Create a dynamic control, populate and render itGridView excel = new GridView();excel.DataSource = table;excel.DataBind();excel.RenderControl(new HtmlTextWriter(Response.Output));Response.Flush();Response.End();

Below link is used to export datatable to excel in C# Code.

http://royalarun.blogspot.in/2012/01/export-datatable-to-excel-in-c-windows.html

  using System;         using System.Data;     using System.IO;     using System.Windows.Forms;      namespace ExportExcel      {              public partial class ExportDatatabletoExcel : Form          {              public ExportDatatabletoExcel()              {                  InitializeComponent();              }              private void Form1_Load(object sender, EventArgs e)            {                DataTable dt = new DataTable();                //Add Datacolumn                DataColumn workCol = dt.Columns.Add("FirstName", typeof(String));                dt.Columns.Add("LastName", typeof(String));                dt.Columns.Add("Blog", typeof(String));                dt.Columns.Add("City", typeof(String));                dt.Columns.Add("Country", typeof(String));                //Add in the datarow                DataRow newRow = dt.NewRow();                newRow["firstname"] = "Arun";                newRow["lastname"] = "Prakash";                newRow["Blog"] = "http://royalarun.blogspot.com/";                newRow["city"] = "Coimbatore";                newRow["country"] = "India";                dt.Rows.Add(newRow);                //open file                StreamWriter wr = new StreamWriter(@"D:\\Book1.xls");                try                {                    for (int i = 0; i < dt.Columns.Count; i++)                    {                        wr.Write(dt.Columns[i].ToString().ToUpper() + "\t");                    }                    wr.WriteLine();                    //write rows to excel file                    for (int i = 0; i < (dt.Rows.Count); i++)                    {                        for (int j = 0; j < dt.Columns.Count; j++)                        {                            if (dt.Rows[i][j] != null)                            {                                wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t");                            }                            else                            {                                wr.Write("\t");                            }                        }                        //go to next line                        wr.WriteLine();                    }                    //close file                    wr.Close();                }                catch (Exception ex)                {                    throw ex;                }            }        }    }