Export Database Table to Excel File

There are several methods to export Database Table to Excel file.
1. Export to Gridview and then to excel file

Code:
string style = @"<style> .text { mso-number-format:\@; } </style>";
GridView GridView1 = new GridView();
GridView1.RowDataBound += new GridViewRowEventHandler(gvUsers_RowDataBound);
GridView1.DataSource = [YOUR DATASOURCE];
GridView1.DataBind();
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.ContentType = "application/excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(style);
Response.Write(stringWrite.ToString());
Response.End();


protected void gvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[6].Attributes.Add("class", "text");
e.Row.Cells[7].Attributes.Add("class", "text");
}
}

2. DataTable to Excel File:

a. Perfect solution:
//Create the DataTable first

string physicPath = HttpContext.Current.Server.MapPath("./Files");
string fileName = Guid.NewGuid() + ".Xls"

String strExcelConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'", physicPath+fileName);
DataTableToExcel(dt, strExcelConn);
Response.Clear();
Response.WriteFile(physicPath + fileName);
string httpHeader = "attachment;filename=output.xls";
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", httpHeader);
Response.Flush();


public void DataTableToExcel(DataTable dt, string connString)
{
int rows = dt.Rows.Count;
int cols = dt.Columns.Count;
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE ");
if (string.IsNullOrEmpty(dt.TableName)) dt.TableName = "Table1";
sb.Append(dt.TableName + " ( ");
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
sb.Append(string.Format("[{0}] varchar,", dt.Columns[i].ColumnName));
else
sb.Append(string.Format("[{0}] varchar)", dt.Columns[i].ColumnName));
}
using (OleDbConnection objConn = new OleDbConnection(connString))
{
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objCmd.CommandText = sb.ToString();
objConn.Open();
objCmd.ExecuteNonQuery();
sb.Remove(0, sb.Length);
sb.Append("INSERT INTO ");
sb.Append(dt.TableName + " ( ");
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
sb.Append("[" + dt.Columns[i].ColumnName + "],");
else
sb.Append("[" + dt.Columns[i].ColumnName + "]) values (");
}
for (int i = 0; i < cols; i++)
{
if (i < cols - 1)
sb.Append("@" + dt.Columns[i].ColumnName + ",");
else
sb.Append("@" + dt.Columns[i].ColumnName + ")");
}
objCmd.CommandText = sb.ToString();
OleDbParameterCollection param = objCmd.Parameters;
for (int i = 0; i < cols; i++)
{
param.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, OleDbType.VarChar));
}
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < param.Count; i++)
{
param[i].Value = row[i];
}
objCmd.ExecuteNonQuery();
}
}
}



b. Non-perfect solution(File created does not comprise with the excel standard):
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
string sep = "";
foreach (DataColumn dc in dt.Columns)
{
Response.Write(sep + dc.ColumnName);
sep = "\t";
}
Response.Write("\n");
int i;
foreach (DataRow dr in dt.Rows)
{
sep = "";
for (i = 0; i < dt.Columns.Count; i++)
{
Response.Write(sep + dr[i].ToString());
sep = "\t";
}
Response.Write("\n");
}