Creating a Dynamic Reports using ASP and Excel
For some reason, there aren't a hold load of ways to create online reports as far as I know. There's Crystal Reports Enterprise which has a good attempt, but could be a lot better. Then there's not much else (recommendations please post below!). But one easy way of generating neat reports is to generate Excel files on the fly direct from the
database, with a little ASP. It's surprisingly easy to do.
The first step is to modify the mime type in the header, so the browser knows this is an Excel file, not an HTML page:
Note this needs to be at the very top of the page, before anything else.
Once you've done that it's simply a case of reading through the fields in the database and printing them out to a standard HTML table:
It's as simple as that. You can even include simple Excel function like SUM - just put the same text into a table field as you would into the Excel field - as in :
That would show in a table field the total of rows B2 to B6 in the Excel field. To achieve this of course, you need to know the name and letter of the fields that will appear - this may require a bit of trial and error, but it's easy enough to achieve and can produce excellent results.
Thanks to a few of our members here on CodeToad for the following additional suggestions.
Preceding zeros
You can maintain preceding zeros in a box by placing a non-breaking space character ( ) in front of the number.
As in:
<%= objrs(i) %>
Currency and other formats
Currency formatting can be set on an Excel cell by preceding values with the
currency symbol. For Example, precede the value with a dollar
sign.
<TD>$<% = objrs(i) %></TD>
More currency
formatting is available with the Visual Basic FormatCurrency function.
FormatCurrency has several parameters which are optional.
FormatCurrency(Expression[,NumDigitsAfterDecimal [,IncludeLeadingDigit
[,UseParensForNegativeNumbers [,GroupDigits]]]])
The following will cause
a cell to be formatted with a preceding dollar sign, have two decimal digits,
precede values less than one dollar with a zero, place parentheses around
negative values, and use commas to group thousands.
<td><%=
FormatCurrency(objrs(i),2,vbTrue,vbTrue,vbTrue) %></td>
Other
Visual Basic functions may offer other formatting for the Excel cells.
|
Useful Links
|
|
