Avatar of CipherIS
CipherIS
Flag for United States of America asked on

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

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

C#ASP.NET.NET Programming

Avatar of undefined
Last Comment
CipherIS

8/22/2022 - Mon
jjester1

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

ASKER
Huh?   Where would that go?
CipherIS

ASKER
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
SAMIR BHOGAYTA

Hi, try this

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

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

.EntireColumn.NumberFormat = "@";
CipherIS

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

@samirbhogayta - why are you modifying the headertext?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
CipherIS

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
CipherIS

ASKER
Figured it out!