Export DataTable to Excel with Formatting in C#

In this article  let me show you how to export a DataTable to an Excel Sheet and add format to the contents while writing the Excel file.


Create a function getDataTable() and write here the code to get data from database. I am not writing the code to get data from database. In my previous article I have explained how to get records from database using ado dot net and store procedure.

Export a DataTable to an Excel file and add format to the contents while writing the Excel file.

 
        DataTable dt = getDataTable()

    
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "RenewalMember.xls"));
        Response.ContentType = "application/ms-excel";
        

            string str = string.Empty;
            foreach (DataColumn dtcol in dt.Columns)
            {
                Response.Write(str + dtcol.ColumnName);
                str = "\t";
            }
            Response.Write("\n");
            foreach (DataRow dr in dt.Rows)
            {
                str = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    Response.Write(str + Convert.ToString(dr[j]));
                    str = "\t";
                }
                Response.Write("\n");
            }
            Response.End();