Generating and Exporting an Excel File in ASP.NET-Using HTMLTextWriter Object ASP.NET


I am going to explain how to Generate and Export an Excel File in ASP.NET Using HTMLTextWriter Object ASP.NET

Description


The below code demonstrate how to generate and export an Excel file in ASP.net.


// Pass Grid View or DataGrid Object to this method

public void Export(string fileName, GridView gv)

{

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.ClearHeaders();

HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);

HttpContext.Current.Response.Charset = "";

HttpContext.Current.Response.AddHeader(

"content-disposition", string.Format("attachment; filename={0}", fileName));

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

String style = @"";

using (StringWriter sw = new StringWriter())

{

using (HtmlTextWriter htw = new HtmlTextWriter(sw))

{

Table table = new Table();

table.GridLines = GridLines.Both;

if (gv.HeaderRow != null)

{

PrepareControlForExport(gv.HeaderRow);

table.Rows.Add(gv.HeaderRow);

}



foreach (GridViewRow row in gv.Rows)

{

PrepareControlForExport(row);

table.Rows.Add(row);

}

if (gv.FooterRow != null)

{

PrepareControlForExport(gv.FooterRow);

table.Rows.Add(gv.FooterRow);

}

table.RenderControl(htw);

HttpContext.Current.Response.Write(style + sw.ToString());

HttpContext.Current.Response.End();

}

}

}

///

/// Replace any of the contained controls with literals

///


///

private void PrepareControlForExport(Control control)

{

for (int i = 0; i < control.Controls.Count; i++)

{

Control current = control.Controls[i];

if (current is LinkButton)

{

control.Controls.Remove(current);

control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));

}

else if (current is ImageButton)

{

control.Controls.Remove(current);

control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));

}

else if (current is HyperLink)

{

control.Controls.Remove(current);

control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));

}

else if (current is DropDownList)

{

control.Controls.Remove(current);

control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));

}

else if (current is CheckBox)

{

control.Controls.Remove(current);

control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));

}

if (current.HasControls())

{

PrepareControlForExport(current);

}



}

}





Pros:

1. Gives very good performance.
2. Supports simple formatting and formulas.

Cons:

1. One worksheet per workbook.
2. Works fine with Excel 2000, XP, and 2003, but gives security warning in Excel 2007 while opening the file.
3. While saving the file in Excel 2007, it gives this message.
4. Importing the existing exported file using Excel COM object, throws an error.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: