Export to Excel string column converted to date

Question

I wish to export my datatable to excel.

I have a column which has values like 2/5 1/5 ... After export this data to excel it looks as 02.May 01.May (Date Format)

How can i export this column so that excel treats it like a string instead of converting it to a date.?

this is my ExportToExcel method

protected void ExportToExcel()
{

    DataTable dt = new DataTable();

    dt = Session["aaa"] as DataTable;

    if (dt.Rows.Count > 0)
    {
        string filename = "DownloadMobileNoExcel.xls";
        System.IO.StringWriter tw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
        DataGrid dgGrid = new DataGrid();
        dgGrid.DataSource = dt;
        dgGrid.DataBind();

        //Get the HTML for the control.
        dgGrid.RenderControl(hw);
        //Write the HTML back to the browser.
        //Response.ContentType = application/vnd.ms-excel;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
        this.EnableViewState = false;
        Response.Write(tw.ToString());
        Response.End();
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Answer 1

One solution might be to loop through al the cells in your DataTable for the column that is causing the issue and prepend a single quote ' character before the current value.

In other words 2/5 would become '2/5. This should force Excel to treat the data as text.