ASP.NET - C# - set gridview cell as text and export to excel as text

CipherIS
CipherIS used Ask the Experts™
on
I am populating a grid with data from a database.  I am then exporting that data to excel.  The problem I am having is that a column will have a value "001" but it exports as "1".  How can I define it so it is text when it exports?

protected void ExportToExcel(DataTable dt)
    {
        string sFileName = "Data_" + DateTime.Now.Year + DateTime.Now.Month + DateTime.Now.Day + ".xls";

        GridView gv = new GridView();
        gv.AllowPaging = false;
        gv.DataSource = dt;
        gv.DataBind();

        Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", sFileName));
        Response.ContentType = "application/vnd.ms-excel";
        //Response.ContentType = "application/vnd.openxmlformats";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        //Change the Header Row Foreground to white color
        gv.HeaderRow.Style.Add("color", "#FFFFFF");
        //Applying stlye to gridview header cells
        for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
        {
            //Change to Blue
            gv.HeaderRow.Cells[i].Style.Add("background-color", "#507CD1");
        }
        int j = 1;
        //This loop is used to apply stlye to cells based on particular row
        foreach (GridViewRow gvrow in gv.Rows)
        {
            gvrow.BackColor = Color.White;
            if (j <= gv.Rows.Count)
            {
                if (j % 2 != 0)
                {
                    for (int k = 0; k < gvrow.Cells.Count; k++)
                    {
                        gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
                    }
                }
            }
            j++;
        }
        gv.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
jjester1

Commented:
string myString = myInt.ToString();

also, if you need to pad leading zeros or format, check this info:

http://msdn.microsoft.com/en-us/library/dd260048.aspx
CipherIS

Author

Commented:
Huh?   Where would that go?
CipherIS

Author

Commented:
I walked through the values in the DataTable and GridView and both contain 001.  The problem is when it opens in excel that it formats it to a number.

Any ideas?
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Start Today!
SAMIR BHOGAYTATeam Lead

Commented:
Hi, try this

protected void Button1_Click(object sender, EventArgs e)
{
    this.gvw1.Columns[0].HeaderText = "The new header";
}
jjester1

Commented:
You should be able to format the column in question to be text using:

.EntireColumn.NumberFormat = "@";
CipherIS

Author

Commented:
@jjester1 - where would i put that code ".EntireColumn.NumberFormat = "@"; "

@samirbhogayta - why are you modifying the headertext?
Commented:
I figured it out.

Original Code
        foreach (GridViewRow gvrow in gv.Rows)
        {
            gvrow.BackColor = Color.White;
            
            if (j <= gv.Rows.Count)
            {
                if (j % 2 != 0)
                {
                    for (int k = 0; k < gvrow.Cells.Count; k++)
                    {
                        gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
                    }
                }
            }
            j++;
        }

Open in new window


Code Fix 
        foreach (GridViewRow gvrow in gv.Rows)
        {
            gvrow.BackColor = Color.White;

            //HERE IS THE FIX - Format Specific Cell as Text
            gvrow.Cells[21].Style.Add("mso-number-format", "\\@");

            if (j <= gv.Rows.Count)
            {
                if (j % 2 != 0)
                {
                    for (int k = 0; k < gvrow.Cells.Count; k++)
                    {
                        gvrow.Cells[k].Style.Add("background-color", "#EFF3FB");
                    }
                }
            }
            j++;
        }

Open in new window

CipherIS

Author

Commented:
Figured it out!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial