I've been outputting excel-readable data for years in one form or another and always avoided it because none of them never worked the way I wanted them to. I think finally things are at a place where outputting richly formatted data files to native Excel are easily doable. This is what I did today and was pleased with the results.
There are some very good examples out there posted recently on creating formulas, using stylesheets, and approaches with streams. Good stuff. I personally like the following approach.
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = " ";
Response.AddHeader("Content-Disposition", "attachment; filename=whatever.xls;");
Response.Write("<table border=0><tr><td style='font-family:Arial; font-size:14pt; font-weight: lighter'>table goes here.");
Response.Write("notice the inline-style use.</td></tr></table>");
Response.End();
That about does it. Not a thing has to be in the .ASPX/.ASCX file. Works great! No ActiveX, Server-side automation, .CVS files, etc.. Finally a straightforward method to export data to native Excel with rich formatting, thanks to Excel's smart handling of HTML and .NET.
One weird thing, though. The File Open/Save Dialog box appears twice, requiring two clicks before the file is displayed in Excel. A minor annoyance and I told my users it was an IE thing until I resolve it. They'll love the reports so they won't care about the extra mouseclick. But if anyone knows why this may be happening, I'd appreciate your insights! Thx!