Home���ASP���Article
Creating a Dynamic Reports using ASP and Excel
|
| Article by: |
 �Jeff Anderson �( 1362 ) (1/9/2003) |
|
| Summary:
|
A simple way to generate Excel reports from a database using Excel. |
|
| Viewed: 385451 times |
Rating (186 votes):� |
|
�4.4 out of 5 |
|
|
|
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
|
|
View highlighted Comments
 User Comments on 'Creating a Dynamic Reports using ASP and Excel'
|
| RELATED ARTICLES |
ASP FilesystemObject
by Jeff Anderson
An introduction to the Filesystemobject |
|
ASP GetTempName
by Jeff Anderson
Use the GetTempName method to create a randomly generated temporary file on the server. |
|
ASP Format Date and Time Script
by Jeff Anderson
An ASP script showing the variety of date and time formats possible using the FormatDateTime Function. |
|
ASP OpenTextFile
by Jeff Anderson
An introduction to the OpenTextFile Method of the FileSystemObject |
|
Email validation using Regular Expression
by Jeff Anderson
Using regular expression syntax is an exellent way to thoroughly validate an email. It's possible in ASP. |
|
Add or Subtract Hours in SQL or ASP using DateAdd
by Jeff Anderson
A beginners guide to using the SQL DATEADD function to add or subtract hours. Particularly useful when setting the time displayed on the ASP page to a different time zone (eg when the server is in the US, and the site is for a UK audience). |
|
The asp:radiobutton and asp:radiobuttonlist control
by David Sussman, et al
In HTML, radio buttons are used when we need to make multiple sets of choices available, but we want the user to select only one of them. |
|
The asp:checkbox and asp:checkboxlist control
by David Sussman, et al
Checkboxes are similar to radio buttons, and in HTML, they were used to allow multiple choices from a group of buttons. |
|
Concatenate strings in sql
by Jeff Anderson
A brief introduction to concatenating strings in an sql query (using SQL server or access databases). |
|
ASP FileExists
by Jeff Anderson
An introduction to the FileExistsMethod of the FileSystemObject |
|
|
|
�
�
