in

ASP.NET Weblogs

Dave Burke - A freelance .NET Developer specializing in Online Communities

A freelance .NET Developer

Outputting to native Excel

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!


 

Published Apr 06 2004, 11:44 PM by daveburke
Filed under:

Comments

 

Mark Struck said:

Dave,

If you don't want the file open/save dialog to popup but want the excel document to open directly in the browser change the second parameter in the AddHeader method to "inline;filename=whatever.xls"
April 7, 2004 12:27 AM
 

Dave Burke said:

Mark, Thanks. I tried that and it worked great, but its important for our users that the report opens in Excel, not in IE. I would agree with them on that score, cause if I am looking at an Excel spreadsheet I want to have the full resources of Excel to work with it. So I guess we'll live with the double dailog box display for now.
April 7, 2004 2:08 PM
 

Mark Struck said:

Dave,

After a little searching I came across this at MSDN.

http://support.microsoft.com/?id=238588

It describes the behavior you were referring to. Hope this helps.

April 8, 2004 4:36 AM
 

Dave Burke said:

Thanks, Mark. That KB helped! I'm posting the solution now...
April 8, 2004 9:27 AM
 

TrackBack said:

April 8, 2004 9:52 AM
 

Jerry Pisk said:

Dave, this is not outputing native Excel file, it's just using the fact that Excel can import HTML.
April 8, 2004 1:09 PM
 

Dave Burke said:

Jerry, You're right. Thanks for the clarification. Native Excel as I defined it is launching Excel rather than viewing in a browser. Once it gets there (thanks to Office's HTML smarts), its a bonafide Excel doc in my book. Speaking of HTML smarts, Jason Haley has a great post on exporting excel data with formulas: http://dotnetjunkies.com/WebLog/jhaley/archive/2004/03/20/9583.aspx
April 8, 2004 5:45 PM
 

Jerry Pisk said:

Dave, thanks for that link, that article is definitely more like it since it actually uses Excel's "HTML" format.

<rant>Of course it would be nice if Microsoft actually posted the format so we don't have to reverse engineer it (which is illegal, at least in the US it is), I'm wondering if Jason has a permission from Microsoft to post and explain that piece ;)</rant>
April 8, 2004 10:58 PM
 

Jason Haley said:

Dave, nope I don't have Microsoft's permission to post their HTML format. If you don't want to use the reverse engineering method, check out http://www.carlosag.net/Tools/ExcelWriter/Default.aspx, it makes my method look like a hack. Plus I think Carlos works for Microsoft.
April 9, 2004 9:10 AM
 

TrackBack said:

MSDN reference for Excel 2002 xml spreadsheet reference
April 9, 2004 9:32 AM
 

Ardo said:

Thanks a lot!
I was going nuts trying to find a way to export data so that it could be imported into Excel. Using CVS was not a solution, because Excel consideres every field as of "general" type. So 00011 becomes 11 and 1.01 becomes 1st of January aso. Using html as output helped and now everything works fine. BTW, I use PHP, not ASP :)
May 13, 2004 8:16 AM
 

Dave Burke said:

Ardo, Thanks for your comments! The idea certainly wasn't mine, but yeah, Office 2003's support for HTML makes the task a little easier!
May 13, 2004 8:30 AM
 

Eamonn said:

Hi Dave,
here is a possible workaround.

In the File Explorer under Tools menu, Folder Options menu option, File Types tab, Advanced button (Windows 2000) change (uncheck) the "Confirm open after download" option for the "open" action
August 3, 2004 4:45 AM
 

Satyadeep said:

Hi All,

    But using this code how can we genrate multiple sheets in the same Excel Workbook.

With Regards

Satya

July 19, 2007 12:47 AM
 

Wasim Kazi said:

Hi Satya

That will not be possible. Because, in this approach it is excel which is doing the major work. The code is only a trick.

After all trick will be tricks, they cant be magic.

But would love to know if that can be done.

September 11, 2007 4:47 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
Terms of Use