This took me a while to figure this out but if you use CSS to govern the styles of a gridview in .net then you may know that the styles will not export into excel without a little help.
If you want to export a gridview into excel your function may look something like this:
private void ExportGridView(GridView gv)
{
string xlsfilename = "Name.xls";
string attachment = "attachment; filename=" + xlsfilename;
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
bool allowSorting = gv.AllowSorting; // save the current AllowSorting setting
gv.AllowSorting = false;
gv.RenderControl(htw);
gv.AllowSorting = allowSorting; // restore the current AllowSorting setting
Response.Write(sw.ToString());
Response.End();
}
You can call on this function by executing a button cmd like this:
protected void Btn_Click(object sender, ImageClickEventArgs e)
{
//Export the GridView to Excel
PrepareGridViewForExport(GridView1);
ExportGridView(GridView1);
}
The PrepareGridViewForExport(GridView1); line is a function to handle objects in the gridveiw, but I’ll leave that out of this post. Now back to the main topic. The styles of this grid will not show up in excel because the office application takes the html written from the webpage and places it into a worksheet. If that page makes calls to styles in another file then office does not have the details of those styles in the string it is passed. To add those detail we just need to pass excel the styles in a string.
Here’s how it works:
Add the stylesheet to a streamreader object:
StreamReader sr = new StreamReader(Server.MapPath("Name.css"));
string s = sr.ReadToEnd();
don’t forget to close the object:
sr.Close();
That’s it! Just add the opening and closing Head tags and Style tags to export script and Office will be able to read the details of the styles in the html string and apply them to the worksheet.
So your new function will look something like this:
private void ExportGridView(GridView gv)
{
string xlsfilename = "StatusFunds.xls";
string attachment = "attachment; filename=" + xlsfilename;
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
bool allowSorting = gv.AllowSorting; // save the current AllowSorting setting
gv.AllowSorting = false;
gv.RenderControl(htw);
gv.AllowSorting = allowSorting; // restore the current AllowSorting setting
//read the stylesheet to include the format with the export
StreamReader sr = new StreamReader(Server.MapPath("Name.css"));
string s = sr.ReadToEnd();
Response.Write("<HEAD><STYLE>");
Response.Write(s.ToString());
Response.Write("</STYLE></HEAD>");
Response.Write(sw.ToString());
Response.End();
sr.Close();
}
Good luck!
