How to make Excel report fast and easy

A

Arch4ngel

Senior Contributor
Joined
Mar 22, 2004
Messages
940
Location
Montreal, QC
Report in company are most of the time made at hand. I got a case where I had to make a report where all the information was in my DB. They wanted a employee listing with all their address, title, manager, etc...

And at the most of it... they wanted a Excel report with the table and all. And you know what ?

They wanted that ASP.NET generate the report. My first though was... "Hell... how will I do that ?".

But I found a way to generate EASY report with less trouble than trying to make an Interop application.

The solution is in one line. "Excel support web content". Do you see where I'm coming ? I've seen to many people trying to make Excel work in interop. So here is what you need to make an online report and that all your user think that it's a file currently on the server.

Here is some things to do before :

  1. Make sure there's no "Interactive control" like button, checkbox, radiobutton, etc... they will be created in Excel but they'll be useless.
  2. Remove the <form ...> and the </form> tag. Excel won't use them anyway.
Here is how it'll work. Work as if you were working for Netscape 4.7. In big word... make <table> everywhere instead of <div>. All your cell will be like in Excel. The Top Left one will be A1 and so on.

The special thing about that is that the DataGrid is rendered as a table so... you can DataBind things to it and it will generate dynamic content.
Now that your data is ready... make some visualisation to make sure everything is formatted correctly (color, bold, italic) - it will be rendered correctly in Excel. Don't bother about row and columns size because in Excel it will take the largest one.

Here come the magic... I've added this code in the Page_Load. I know it's possible to put it directly in the HTML code but since I want to see result for testing, all I have to do is comment and uncomment. So here is what I added at the end of my Page_Load :

C#: 
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition","attachement; filename=\"SomeFile.xls\"");
Visual Basic: 
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition","attachement; filename=\"SomeFile.xls\"")

Those line of code will make your WebPage act like you where downloading "SomeFile.xls". And if you save it on your hard disk... it will look like your Excel Report !

N.B.: You can make anything you want in the Page_Load... in my case... I maked a SQL Connection and used a DataReader to fill my DataGrid.

This is my first tutorial so enjoy !
 
Back
Top