AzamSharp

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"); 
        }
    }
}