codetoad.com
  ASP Shopping CartForum & BBS
  - all for $20 from CodeToad Plus!
  
  Home || ASP | ASP.Net | C++/C# | DHTML | HTML | Java | Javascript | Perl | VB | XML || CodeToad Plus! || RAM 
Search Site:

Home » ASP » Article

Creating a Dynamic Reports using ASP and Excel

Article by:  Jeff Anderson  ( 1362 ) (1/9/2003)
Bookmark us now! Add to Favourites
Email a friend! Tell a friend
Summary: A simple way to generate Excel reports from a database using Excel.
Viewed: 385451 times Rating (186 votes): 
 4.4 out of 5
  Rate this Article   Read Comments   Post Comments

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:

Select All Code


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:

Select All Code


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 :

Select All Code


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:
&nbsp;<%= 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


CodeToad Experts

Can't find the answer?
Our Site experts are answering questions for free in the CodeToad forums
Rate this article:     Poor Excellent
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








Recent Forum Threads
•  Run a program both on windows and linux
•  VERO.SurfCAM.v2014
•  Schlumberger.Petrel.V2013.2
•  Petrel.V2013.2
•  Altair.HyperWorks.v12
•  VoluMill.v6.1
•  VoluMill.NEXION.6
•  VERO.SurfCAM.v2014
•  Schlumberger.Petrel.V2013.2


Recent Articles
ASP GetTempName
Decode and Encode UTF-8
ASP GetFile
ASP FolderExists
ASP FileExists
ASP OpenTextFile
ASP FilesystemObject
ASP CreateFolder
ASP CreateTextFile
Javascript Get Selected Text


© Copyright codetoad.com 2001-2015