Export a DataTable to an Excel spreadsheet

 If you need to export a DataTable to an Excel spreadsheet

Thankfully there is a another way to write a datatable to excel sheet is as follows--

We kanow that Excel can open HTML Tables so use this trick for this purpose.

Foirst take a webcontrol Datagrid and bind that to the DataTable which you want to write to Excel Sheet,So your datatable is now converted to HTML Tables Now create a streamwriter object and pass it to a HTMLWriter object which writes your Html Data To that Stream Writer and than StreamWriter write that HTMl datat to excel sheet.And now youe excel is ready to USE

// create the DataGrid and perform the databinding

System.Web.UI.WebControls.DataGrid grid =
            new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = data;
grid.DataMember = data.Stats.TableName;

grid.DataBind();

// render the DataGrid control to a file

using(StreamWriter sw = new StreamWriter("c:\\test.xls"))
{
    using(HtmlTextWriter hw = new HtmlTextWriter(sw))
    {
        grid.RenderControl(hw);
    }
}

Hope this will work for you :).