Export GridView To Excel : Column Format

Last Month I posted that How to export to excel from web page with data formatting.

But when you are trying to export entire grid specially with Boolean datatype column,

 

               string attachment = "attachment; filename=Registration.xls";
               Response.ClearContent();
               Response.AddHeader("content-disposition", attachment);
               Response.Charset = "";
               //set the Response mime type for excel
               Response.ContentType = "application/vnd.ms-excel";
               //create a string writer
               System.IO.StringWriter stringWrite = new System.IO.StringWriter();
               //create an htmltextwriter which uses the stringwriter
               System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
               //instantiate a datagrid
               GridView dg = new GridView();
               dg.RowDataBound += new GridViewRowEventHandler(GridView1_RowDataBound);
               //set the datagrid datasource to the dataset passed in
               dg.DataSource = dt;
               //bind the datagrid
               dg.DataBind();
               //dg.Columns[1].ItemStyle.
               //tell the datagrid to render itself to our htmltextwriter
               dg.RenderControl(htmlWrite);
               //all that's left is to output the html
               Response.Write(stringWrite.ToString());
               Response.End();

 

its result:

You can see that checkbox is there, But this is not user friendly. because in excel operator are not familiar with check box in excel data.

It means there should be 1/0  or True/False in place of checkbox. lets see.

Add RowDataBound event to the grid…

 

               GridView dg = new GridView();
               dg.RowDataBound += new GridViewRowEventHandler(GridView1_RowDataBound);

 

Now create an event…

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        CheckBox chk = (CheckBox)e.Row.Cells[1].Controls[0];
        chk.Visible = false;
        if (chk.Checked)
        {
            e.Row.Cells[1].Text = "1";
        }
        else
        {
            e.Row.Cells[1].Text = "0";
        }

    }
}

You can see that now checkbox is invisible and we are writing 1 or 0 in the same cell, Cells[1] is static value for second column.

and now see the result…

You can see that value is now changed in to 1 inplace of checkbox.

We can change any kind of data by using RowDataBound event.