export to excel - dynamic connection sources - not truncating 0s

Question

I have searched the Internet high and low, but with no luck.  Many people have posts about this, but I could not find any real solution.  I am exporting a GridView to Excel, but I am having my leading 0s truncated.  I am aware that you need to convert the column to a 'text' style, but how do I go about doing that?

I keep my code on the backend and reference it frontend for portability.

NOTE:  GridView ID:  GridView1
DataSource = SqlDataSource1

[backend code - exportToExcel.cs]

using System;

using System.Configuration;

using System.Data;

using System.IO;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

 

/// <summary>

/// Summary description for exportToExcel

/// </summary>

public class exportToExcel

{

    public static void export(GridView gridView)

    {

        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.AddHeader("content-disposition",

            "attachment; filename=dummy.xls");

        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

 

        StringWriter sw = new StringWriter();

       

        HtmlTextWriter htmlTW = new HtmlTextWriter(sw);

 

        //create table to hold gridView

        Table tbl = new Table();

 

        //header

        if (gridView.HeaderRow != null)

        {

            exportToExcel.currentRowStatus(gridView.HeaderRow);

            tbl.Rows.Add(gridView.HeaderRow);

        }

 

        //write rows

        foreach (GridViewRow rowX in gridView.Rows)

        {

            exportToExcel.currentRowStatus(rowX);

            tbl.Rows.Add(rowX);  

        }      

       

        tbl.RenderControl(htmlTW);

 

        //the CSS that should fix the truncation thanks to Excel   >:|   geh

        ////string style = @"<style .text { mso-number-format:\@; } </style>";

       

        //writing the CSS to output

        //works - change output format to txt and can see this!

        ////HttpContext.Current.Response.Write(style);

        HttpContext.Current.Response.Write(sw.ToString());

        HttpContext.Current.Response.End();

    }

 

    //this is not being fired

    //if this would fire it would write the CSS to the third column in Excel

    ////protected void gridView_RowDataBound(object sender, GridViewRowEventArgs e)

    ////{

    ////    if (e.Row.RowType == DataControlRowType.DataRow)

    ////    {

    ////        e.Row.Cells[2].Attributes.Add("class", "text");

    ////    }

    ////}

   

 

    //get values for various gridview options

    private static void currentRowStatus(Control ctrl)

    {

        for (int i = 0; i < ctrl.Controls.Count; i++)

        {

            Control current = ctrl.Controls[i];

            if (current is LinkButton)

            {

                ctrl.Controls.Remove(current);

                ctrl.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));

            }

            else if (current is ImageButton)

            {

                ctrl.Controls.Remove(current);

                ctrl.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));

            }

            else if (current is HyperLink)

            {

                ctrl.Controls.Remove(current);

                ctrl.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));

            }

            else if (current is DropDownList)

            {

                ctrl.Controls.Remove(current);

                ctrl.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));

            }

            else if (current is CheckBox)

            {

                ctrl.Controls.Remove(current);

                ctrl.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));

            }

 

            if (current.HasControls())

            {

                exportToExcel.currentRowStatus(current);

            }

        }

    }   

}

 

 

[front end code - Default.aspx.cs]

 

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

 

public partial class _Default : System.Web.UI.Page

{

    protected void Button1_Click(object sender, EventArgs e)

    {

       exportToExcel.export(GridView1);

    }

}