namespace table
{
public class table1
{
public DataTable select()
{
SqlCommand cmd = new SqlCommand("select123", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adap.Fill(dt);
return dt;
}
}
}

public partial class Export: System.Web.UI.Page 
{
table.table1 db = new table.table1();
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dtOriginal = new DataTable();

dtOriginal = db.select();
DataTable dtTemp = new DataTable();

dtTemp.Columns.Add("ID");
dtTemp.Columns.Add("Employee Name");

String dtName;
DataRow drAddItem;
for (int i = 0; i < dtOriginal.Rows.Count; i++)
{
drAddItem = dtTemp.NewRow();
drAddItem[0] = dtOriginal.Rows[i][1].ToString();//ID

//Join Name

dtName = dtOriginal.Rows[i][2].ToString();
drAddItem[1] = dtName;


dtTemp.Rows.Add(drAddItem);
}

//Temp Grid
DataGrid dg = new DataGrid();
dg.DataSource = dtTemp;
dg.DataBind();
ExportToExcel("BudgeReport.xls", dg);
// ExportToExcel("BudgeReport.pdf", dg);

//ExportToExcel("BudgeReport.doc", dg);//for saving in doc format
dg = null;
dg.Dispose();

}

private void ExportToExcel(string strFileName, DataGrid dg)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
// Response.ContentType = "application/pdf";
//Response.ContentType = "application/doc";//for converting in doc format
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
}