Generating Spreadsheet-Like HTML TablesBy Richard Chisholm
Outputting database information to an HTML table is pretty easy: everyone who reads 4guys has probably done this dozens of times. And thats part of the problem. Every time you need to display a table that contains information from a database, you probably just write out the code necessary to do this each time. But if you are like me you are probably a little lazy, and thats why I created a handy script to easily write out HTML tables.
Here was my situation: I was writing an application to analyze our webserver data (the tools that come with IIS arent very good in my opinion). All of my data is updated from another ASP page into an Access database, and then I needed to output the final data in a way that my boss (the MIS Manager) could view at any time. So I decided to create an ASP file and stick in on our Intranet instead of simply exporting the database to Excel.
However, I wanted to be able to add more fields in my Access database without changing the ASP page, and to easily total the data in each column. So I solved both problems by writing a little function to easily generate a simple HTML table with stats from a database, with the bottom row containing the totals for each column. This will accommodate any number of columns.
Because I wanted to use this in multiple pages, I encapsulated the code in a SUB in a separate file called "data-html.asp". The code and concept for this is very simple, so Ill just explain it by jumping in to the code for it. (The script below assumes you have opened a connection to the DB and created a recordset named rsData).
This is the basic initializing of the variables. Each position of
contain the current value of the corresponding column. I set the array to 50 arbitrarily,
because I know there won't be more than 50 columns in any of my databases, and I don't
want to waste the resources to
ReDim the array. You can set this at a lower value if you
don't need that many columns. Note that the variable
count is set to one less
than the number of fields. Later when I reference the recordset fields using integers, and
since recordset indexes begin at zero we need to subtract one from the total.
The first thing to do is write out the table HTML and populate the header. I use a simple for loop to create the header, with the database column name as the header name.
for r = 0 to count 'populate the header Response.Write("" & rsData(r).Name & " " & vbCrLf) Next
Now for the heart of the script. Here I use a
Do ... Loop to run through the
Recordset, with a
For loop to write out the cells. Notice that the first cell is written
For loop. In my database the first column is a date, and doesnt need to
be added (in fact, cant be added). The
For loop runs through each column of data and
writes the value, while the array tallies the data by adding the current value to array.
Now to write the final row of the table, and output the totals for each column using
For loop, and close the table.
Finally, heres how you can put all together with another asp page. In my app, the
user chooses which type of data they want to see (browser stats, page hits, etc), and then
all there is to do is open the recordset and output the data (because other pages have
already updated the database with the current stats). This is one limitation to this
script. You cant really do anything with the data before you output it (unless you
update the database before calling the
The data will look something like this:
Now, if you really wanted to, you could incorporate these two pages into one, and use that as a generic HTML Table generator, but chances are you'll have reasons to keep them seperate. But either way, the data-html.asp script will save you time.
A few things to note. If you have a large number of fields the table will probably go beyond the width of your screen, so you will end up having to scroll horizontally to see all of it. Also, when using this script your database should probably have meaningful column names to make the column headers more understandable. Additionally, if you actually want ASP to export the data into Excel to do more than just total the columns, I recommend you check out the ASP Today article on creating Excel files in ASP. I found it very easy to change my code to do this.
data-html.aspin text format