Link to home
Start Free TrialLog in
Avatar of CipherIS
CipherISFlag 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

Avatar of jjester1
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
Avatar of CipherIS

ASKER

Huh?   Where would that go?
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?
Avatar of SAMIR BHOGAYTA
Hi, try this

protected void Button1_Click(object sender, EventArgs e)
{
    this.gvw1.Columns[0].HeaderText = "The new header";
}
You should be able to format the column in question to be text using:

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

@samirbhogayta - why are you modifying the headertext?
ASKER CERTIFIED SOLUTION
Avatar of CipherIS
CipherIS
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Figured it out!