Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.

Those of you who have exported the GridView to Excel might have noticed that if you try to export a GridView with a column which displays number like 0034, 0012, 0045 etc. In other words the first digit is a '0' and the column is a varchar column then the exported excel file contains the data as 34, 12, 45. So, it removes all the zero's from the column data. You can easily keep the zero's by making small modification in the code. You can achieve this by adding style to the column. Check out the complete code below:

Now, when you export the GridView you will see that the leading '0' are not truncated.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;

public partial class _Default : System.Web.UI.Page 
{
    
protected void Page_Load(object sender, EventArgs e)
    {
        
if (!Page.IsPostBack)
        {
            BindData();
        }
    }

    
private void BindData()
    {
        SqlConnection myConnection = 
new SqlConnection
        ("Server=localhost;Database=School;Trusted_Connection=true");
        SqlDataAdapter ad = 
new SqlDataAdapter("SELECT
        * FROM Users", myConnection);
        DataSet ds = 
new DataSet();
        ad.Fill(ds);

        gvUsers.DataSource = ds;
        gvUsers.DataBind(); 
    }

    
protected void Btn_ExportClick(object sender, EventArgs e)
    {
        
string style = @"<style> .text { mso-number-format:\@; } </script> "; 

        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment;
        filename=MyExcelFile.xls");
        Response.ContentType = "application/excel";

        StringWriter sw = 
new StringWriter();
        HtmlTextWriter htw = 
new HtmlTextWriter(sw);

        gvUsers.RenderControl(htw);
        
// Style is added dynamically
        
Response.Write(style); 
        Response.Write(sw.ToString());
        Response.End();
    }

    
public override void VerifyRenderingInServerForm(Control control)
    {
        
    }

    
protected void gvUsers_RowDataBound(object sender, GridViewRowEventArgs e)
    {      

        
if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[1].Attributes.Add("class", "text"); 
        }
    }
}

powered by IMHO 1.3

Posted on Saturday, August 12, 2006 11:41 PM | Back to top


Comments on this post: GridView Export to Excel Formatting Issue Solution

# re: GridView Export to Excel Formatting Issue Solution
Requesting Gravatar...
Thanks so much for the tip. It helped me a lot.
Left by Susan on Aug 15, 2006 6:22 AM

# re: GridView Export to Excel Formatting Issue Solution
Requesting Gravatar...
I have to change some columns from text format to number format.
Please let me know the solution for this
Left by Vishwas on Sep 11, 2007 1:08 AM

# re: GridView Export to Excel Formatting Issue Solution
Requesting Gravatar...
i need the same thing in datagrid... cud u plz tell me.... i've been trying to do same as u did .. but i cudnt do that...
Left by anant on Feb 08, 2008 1:33 AM

# re: GridView Export to Excel Formatting Issue Solution
Requesting Gravatar...
Data Grid Code (C#):
DataGrid dg = new DataGrid();
DataSet ds = GetDS();
dg.HeaderStyle.Font.Bold = true;
dg.DataSource = ds;
dg.DataBind();

//format cells for percentages
string style = @"<style> .percent { mso-number-format:'0%'; } </script> ";
//string style = @"<style> .percent { mso-number-format:Percent; } </script> ";
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int x = (monthsInBudget * 3) + 7;
dg.Items[i].Cells[x].Attributes.Add("class", "percent");
dg.Items[i].Cells[x + 1].Attributes.Add("class", "percent");
dg.Items[i].Cells[x + 2].Attributes.Add("class", "percent");
}

dg.RenderControl(htmlWrite);
Left by Allan on Aug 12, 2010 7:24 AM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net