Solved

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

Posted on 2012-12-21

1 solution

4,775 Views

Last Modified: 2012-12-31

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

Question by:CipherIS

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

8 Comments

LVL 2

Expert Comment

by:jjester1

ID: 387142102012-12-21

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

LVL 1

Author Comment

by:CipherIS

ID: 387142252012-12-21

Huh? Where would that go?

LVL 1

Author Comment

by:CipherIS

ID: 387144462012-12-21

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?

LVL 11

Expert Comment

by:SAMIR BHOGAYTA

ID: 387154782012-12-22

Hi, try this

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

LVL 2

Expert Comment

by:jjester1

ID: 387155992012-12-22

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

.EntireColumn.NumberFormat = "@";

LVL 1

Author Comment

by:CipherIS

ID: 387212112012-12-26

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

@samirbhogayta - why are you modifying the headertext?

LVL 1

Accepted Solution

by:CipherIS

CipherIS earned 0 total points

ID: 387213962012-12-26

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

LVL 1

Author Closing Comment

by:CipherIS

ID: 387321872012-12-31

Figured it out!

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL CLR .Net Integration in 2015 (year not product version)

Article by: Éric

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…

.NET Programming

Add Theme to ASP.NET MVC

Article by: Shaun

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project

How to access multiple mailboxes from one account in MS Exchange Server 2010 - video tutorial

Video by: Adam

There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

Network monitoring: why having a policy is the best policy?

Video by: AdRem

Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…