export datatable to excel, value 3E2 become 300, supposed to be text 3E2

Question

I am converting datatable to excel using the following code: public static void ExportDataTabletoExcel(String fileName, DataTable thetable) { if (thetable != null) {

        //clear anything in io buffer
        HttpContext.Current.Response.Clear();
        //set to excel for to save file.
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        HttpContext.Current.Response.Charset = "";
        HttpContext.Current.Response.BufferOutput = true;
        //write columns
        string tab = "";
        foreach (DataColumn dc in thetable.Columns)
        {
            HttpContext.Current.Response.Write(tab + dc.ColumnName);
            tab = "\t";

        }
        HttpContext.Current.Response.Write("\n");

        //write rows
        int i;
        foreach (DataRow dr in thetable.Rows)
        {
            tab = "";
            for (i = 0; i < thetable.Columns.Count; i++)
            {
                //if ( i != 1 ) 
                HttpContext.Current.Response.Write(tab + dr[i].ToString()); 
                //else
                //HttpContext.Current.Response.Write(tab + "'" + dr[i].ToString());
                tab = "\t";
            }
            HttpContext.Current.Response.Write("\n");
        }
        HttpContext.Current.Response.End();
    }

}

However for value 3E2 or 0E2 or 4E3 , it is converted to number.. other value like 1D2 is correctly converted to string.

for example 3E2 become 300 etc.

Answer 1

Put double quotes around the value so that it is obvious to Excel that it is a string and not a number in scientific notation.