Export DataBase Data to Excel Sheet in ASP.NET C#

 

protected void Export_DatabaseData_Click(object sender, EventArgs e)
    {
        string sql = "";
        //Generating Excel Report :-

String ExportFileName = "ExportedSheet_" + DateTime.Now.Day.ToString() +   DateTime.Now.Month.ToString() + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + "_" + DateTime.Now.Millisecond.ToString() + ".xls";

//Fetching data from Database through query:-

         Sql = "SELECT * FROM <TABLENAME>";   //Query for fetching data.
        DataTable dt = new DataTable();
        objDB.GetData(sql, ref dt);   //**Executing query and get data in data table.**//
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename= " + ExportFileName + ";");      
        Response.Charset = "";
        Response.ContentType = "application/text";
        StringBuilder sb = new StringBuilder();
        for (int k = 0; k < dt.Columns.Count; k++)
        {
            //add Separator
            sb.Append(dt.Columns[k].ColumnName + ',');
        }

        //Append New Line
        sb.Append("\r\n");
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int k = 0; k < dt.Columns.Count; k++)
            {
                //Add Separator
                sb.Append(dt.Rows[i][k].ToString().Replace(",", ";") + ',');
            }
            //Append new line
            sb.Append("\r\n");
        }
        Response.Output.Write(sb.ToString());       
        Response.Flush();
        Response.End();   
    }