jorame jorame - 3 years ago 232
C# Question

How can I format a column from a GridView before export to show all numbers in excel?

I'm trying to export a GridView to Excel and I have a column with a series of numbers like 1245333325364. When I run the query for the GridView I can see the complete number but when I export to excel all I see is 1.00133E+12 on that column. I know I can have the user change this in excel but not all files are being open after export they just save it straight into a directory. I will really like to change the column's format in the export process rather than having the user do it before they save the file. I'm performing the export in C# any help will be really appreciate.

The code I'm using to export the GridView is like so:

protected void exporttoexcel_Click(object sender, EventArgs e)
{
string date = DateTime.Now.ToString("MM-dd-yyyy");

PrepareGridViewForExport(GridView1);
Response.Clear();
Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=" + date + "_" + CHROUT.Text + "_Trailer_" + TRAILER.Text);
Response.Charset = "''";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);

GridView1.AllowPaging = false;
GridView1.DataBind();

GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
GridView1.HeaderRow.Cells[0].Style.Add("width", "105px");
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "#CCCCCC");
GridView1.HeaderRow.Cells[1].Style.Add("background-color", "#CCCCCC");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "#CCCCCC");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "#CCCCCC");
GridView1.HeaderRow.Cells[4].Style.Add("background-color", "#CCCCCC");
GridView1.HeaderRow.Cells[5].Style.Add("background-color", "#CCCCCC");
GridView1.HeaderRow.Cells[6].Style.Add("background-color", "#CCCCCC");
GridView1.HeaderRow.Cells[7].Style.Add("background-color", "#CCCCCC");

for (int i = 0; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];

row.BackColor = System.Drawing.Color.White;

row.Attributes.Add("class", "texmode");

if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#f0f0f0");
row.Cells[1].Style.Add("background-color", "#f0f0f0");
row.Cells[2].Style.Add("background-color", "#f0f0f0");
row.Cells[3].Style.Add("background-color", "#f0f0f0");
row.Cells[4].Style.Add("background-color", "#f0f0f0");
row.Cells[5].Style.Add("background-color", "#f0f0f0");
row.Cells[6].Style.Add("background-color", "#f0f0f0");
row.Cells[7].Style.Add("background-color", "#f0f0f0");
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .text { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}

Answer Source

I finally got the answer for this question. I basically just need to add the format to the GridView before export and to be more specific on the DataBound. Take a look at the code below:

First create an event for the OnRowDataBound

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        e.Row.Cells[1].Attributes.Add("class", "text");
        e.Row.Cells[2].Attributes.Add("class", "text");
    }
}

Then reference this on the GridView like this:

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound">

Then just add this little line of code right before your export the GridView.

Response.Write(style);

And that's all.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download