How can I format a column from a GridView before export to show all numbers in excel?

Question

I'm trying to export a GridView to Excel and I have a column with a series of numbers like 1245333325364. When I run the query for the GridView I can see the complete number but when I export to excel all I see is 1.00133E+12 on that column. I know I can have the user change this in excel but not all files are being open after export they just save it straight into a directory. I will really like to change the column's format in the export process rather than having the user do it before they save the file. I'm performing the export in C# any help will be really appreciate.

The code I'm using to export the GridView is like so:

    protected void exporttoexcel_Click(object sender, EventArgs e)
    {
        string date = DateTime.Now.ToString("MM-dd-yyyy");

        PrepareGridViewForExport(GridView1);
        Response.Clear();
        Response.Buffer = true;

        Response.AddHeader("content-disposition", "attachment;filename=" + date + "_" + CHROUT.Text + "_Trailer_" + TRAILER.Text);
        Response.Charset = "''";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        GridView1.AllowPaging = false;
        GridView1.DataBind();

        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
        GridView1.HeaderRow.Cells[0].Style.Add("width", "105px");
        GridView1.HeaderRow.Cells[0].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[1].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[2].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[4].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[5].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[6].Style.Add("background-color", "#CCCCCC");
        GridView1.HeaderRow.Cells[7].Style.Add("background-color", "#CCCCCC");

        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            GridViewRow row = GridView1.Rows[i];

            row.BackColor = System.Drawing.Color.White;

            row.Attributes.Add("class", "texmode");

            if (i % 2 != 0)
            {
                row.Cells[0].Style.Add("background-color", "#f0f0f0");
                row.Cells[1].Style.Add("background-color", "#f0f0f0");
                row.Cells[2].Style.Add("background-color", "#f0f0f0");
                row.Cells[3].Style.Add("background-color", "#f0f0f0");
                row.Cells[4].Style.Add("background-color", "#f0f0f0");
                row.Cells[5].Style.Add("background-color", "#f0f0f0");
                row.Cells[6].Style.Add("background-color", "#f0f0f0");
                row.Cells[7].Style.Add("background-color", "#f0f0f0");
            }
        }
        GridView1.RenderControl(hw);
        //style to format numbers to string
        string style = @"<style> .text { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }

Answer 1

try like this

   protected void btnExportToExcel_Click(object s, EventArgs e)
    {

    GridView gvExportExcel = new GridView();
                        gvExportExcel.ID = "ExportExcel";
                        gvExportExcel.AllowPaging = false;
                        gvExportExcel.DataSource = listOfData(Generic list);
                        gvExportExcel.DataBind();
                        for (int i = 0; i < gvExportExcel.Rows.Count; i++)
     gvExportExcel.Rows[i].Cells[0].Attributes.Add("style", "mso-number-format:\\@");

                        Export("Test.xls", gvExportExcel);
    }


private void Export(string fileName, GridView dgvExport)
        {
            try
            {
                HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
                HttpContext.Current.Response.Buffer = true;
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                Response.Charset = string.Empty;
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
                dgvExport.RenderControl(htw);

                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.Flush();
                HttpContext.Current.ApplicationInstance.CompleteRequest();
                HttpContext.Current.Response.Close();
                //HttpContext.Current.Response.End();

            }
            catch (System.Threading.ThreadAbortException ex)
            {
                //write your exception
            }
}

Answer 2

You can set its CellFormat as Number or Currency replace its original format.

Answer 3

I finally got the answer for this question. I basically just need to add the format to the GridView before export and to be more specific on the DataBound. Take a look at the code below:

First create an event for the OnRowDataBound

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[1].Attributes.Add("class", "text");
        e.Row.Cells[2].Attributes.Add("class", "text");
    }
}

Then reference this on the GridView like this:

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound">

Then just add this little line of code right before your export the GridView.

Response.Write(style);

And that's all.

Answer 4

Try putting a Single Quote (') symbol just before the big number... Excel recognises Single Quote as Text.

Alternatively you can try enclosing the value in Double Quotes like this "12345678901234"

Hope this works.

Answer 5

Isn't the issue one of the cell's width rather than it's format? Change the width of the cell and the number should show up fine.