Tuesday, August 18, 2009
saAction
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.
