Generating HTML Tables

,

Generating HTML tables for displaying data is a pretty common task these

days. Easy to do, easy to tweak, no special tools needed (though they do come in

handy), all the end user needs is a browser. How you do it can make all the

difference in how easy it is to maintain. They key to separate your display code

from the data. XML and style sheets offer some interesting ways of doing this

which I'll discuss in a later article, today we're just looking at old fashioned

HTML. Combined with our friend the ADO recordset, we can do some wonderful

things. Well, useful anyway. This may be old news to many of you, but I still

see a lot of this table code being written. If nothing else when you see it

you'll have a resource to point the offender to!

Let's start with the some what contrived example of needing to generate a web

page that displays all the files in a specific folder. We know that the folder

is updated once a day. Here is some code that shows one way of doing it that

could easily be run from a SQL job:

Sub LoadFilesIntoTable(PathToLoad As String, OutputFileName As String)

Dim oFSO As Scripting.FileSystemObject

Dim oFolder As Scripting.Folder

Dim oFile As Scripting.File

Dim oStream As Scripting.TextStream

Dim sTemp As String

Set oFSO = New Scripting.FileSystemObject

If oFSO.FolderExists(PathToLoad) = True And OutputFileName <> "" Then

Set oFolder = oFSO.GetFolder(PathToLoad)

'added tab and cr/lfs to make it more readable when viewing source

sTemp = "<TABLE WIDTH=100% BORDER=1>" & vbCrLf

For Each oFile In oFolder.Files

    sTemp = sTemp & vbTab & "<TR><TD>" & oFile.Name & "</TD><TD>" & oFile.Size & "</TD></TR>"    

& vbCrLf

Next

sTemp = sTemp & "</TABLE>"

Set oFolder = Nothing

'create the file, overwrite any previous version of the file

Set oStream = oFSO.CreateTextFile(OutputFileName, True, False)

oStream.Write sTemp

Set oStream = Nothing

End If

Set oFSO = Nothing

End Sub

Nothing very fancy, just using the filesystem object to both get the list of

files and to write it back to disk. See how the table formatting tags are mixed

in? Now suppose the user asks for the date created to be added. We only need to

change one line, like this:

sTemp = sTemp & vbTab & "<TR><TD>" & oFile.Name & "</TD><TD>" & oFile.Size & "</TD><TD>" & oFile.DateCreated & "</TD></TR>" & vbCrLf

Fairly trivial right? What if the report needed to be sorted by file size? Or

able to sort on all columns? Sorting on all columns requires more effort, we'll

cover that next time too - but sorting on one column we should be able to do.

But how? Just thinking while I write, I came up with these ideas:

1) Load the info into an array, write or find some sorting code, sort it,

then do the file generation.

2) Maybe some kind of hack, use the OS to do something like this "dir

*.* >temp.txt" which will create temp.txt containing all the files in

the folder, then load & parse it back into columns, then do the file

generation.

3) Load the data into a table, sort it using an order by, create the file,

drop the table.

All will work, but being a SQL site and all, I think we'll try #3! Here is

what I came up with:

Sub LoadFilesIntoTable2(PathToLoad As String, OutputFileName As String)

Dim oFSO As Scripting.FileSystemObject

Dim oFolder As Scripting.Folder

Dim oFile As Scripting.File

Dim oStream As Scripting.TextStream

Dim sTemp As String

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Set oFSO = New Scripting.FileSystemObject

If oFSO.FolderExists(PathToLoad) = True And OutputFileName <> "" Then

Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=EG\ONE"

cn.Execute "Create Table #FileList(FileName varchar(100), FileSize int, FileCreated datetime)"

Set oFolder = oFSO.GetFolder(PathToLoad)

For Each oFile In oFolder.Files

cn.Execute "Insert into #FileList (FileName, FileSize, FileCreated) values ('" & oFile.Name & "'," & oFile.Size & ",'" & oFile.DateCreated & "')"

Next

Set oFolder = Nothing

Set rs = cn.Execute("Select * from #FileList order by Filesize")

'added tab and cr/lfs to make it more readable when viewing source

sTemp = "<TABLE WIDTH=100% BORDER=1>" & vbCrLf

Do Until rs.EOF

sTemp = sTemp & vbTab & "<TR><TD>" & rs.Fields("FileName") & "</TD><TD>" & rs.Fields("FileSize") & "</TD><TD>" & rs.Fields("FileCreated") & "</TD></TR>" & vbCrLf

rs.MoveNext

Loop

sTemp = sTemp & "</TABLE>"

rs.Close

Set rs = Nothing

cn.Execute "drop table #FileList"

cn.Close

Set cn = Nothing

'create the file, overwrite any previous version of the file

Set oStream = oFSO.CreateTextFile(OutputFileName, True, False)

oStream.Write sTemp

Set oStream = Nothing

End If

Set oFSO = Nothing

End Sub

Now we can sort on any column if we need to, we could even use a where clause

to restrict the output. We've still got our display code mixed with the data and

I'll bet at least one person is thinking that using SQL and a table for sorting

this list is overkill?

Let's do something about making this code more generic and separating the

UI/data. For that all we need is a function that creates a table from a

recordset. Here is a very simple version:

Public Function CreateTableFromRS(rs As ADODB.Recordset) As String

Dim J As Integer

Dim sTemp As String

sTemp = "<TABLE WIDTH=100% BORDER=1>"

Do Until rs.EOF

sTemp = sTemp & "<TR>"

For J = 0 To rs.Fields.Count - 1

sTemp = sTemp & "<TD>" & rs.Fields(J) & "</TD>"

Next

sTemp = sTemp & "</TR>"

rs.MoveNext

Loop

sTemp = sTemp & "</TABLE>"

CreateTableFromRS = sTemp

End Function

Once we have that, we can change our earlier code to look like this:

Set rs = cn.Execute("Select * from #FileList order by Filesize")

sTemp = CreateTableFromRS(rs)

Now if (when!) we change the columns included, the output portion is handled

automatically. Now let me show you another advantage of this technique. Suppose

you'd like to add a header row to your table. Going back to our earlier example,

we would do something like this:

sTemp = sTemp & "<TR><TH>FileName</TH><TH>File Size</TH><TH>Date Created<TH></TR>"

This works, but now we are back to having to change our code in multiple

places each time we change the columns being displayed. If we use the table

technique and our new function, we can leverage some meta data to do this work

for us, like this:

Public Function CreateTableFromRS2(rs As ADODB.Recordset) As String

Dim J As Integer

Dim sTemp As String

sTemp = "<TABLE WIDTH=100% BORDER=1>"

sTemp = sTemp & "<TR>"

For J = 0 To rs.Fields.Count - 1

    sTemp = sTemp & "<TH>" & rs.Fields(J).Name & "</TH>"

Next

sTemp = sTemp & "</TR>" & vbCrLf

Do Until rs.EOF

sTemp = sTemp & "<TR>"

For J = 0 To rs.Fields.Count - 1

    sTemp = sTemp & vbTab & "<TD>" & rs.Fields(J) & "</TD>" & vbCrLf

Next

sTemp = sTemp & "</TR>"

rs.MoveNext

Loop

sTemp = sTemp & "</TABLE>"

CreateTableFromRS2 = sTemp

End Function

As you can see I'm looping through the fields collections to get the column

names. This code be easily extended to format the column sizes based on the size

of the column, do special formatting when the data type is a number or date,

etc. If you use this function in all your pages, you leverage every little

improvement you make across all of those pages! That is code reuse at it's best.

Now let's return to whether we really need SQL to do the sorting. Or even to

create a recordset. The answer is a conditional no. We can do the sorting other

ways, but having the recordset is what makes the separation between data and UI

work. We don't have to have SQL running to use a recordset though. In the next

example I'm creating a disconnected recordset, loading the data into it, then

using it's ability to filter and sort to customize the output.

Sub LoadFilesIntoTable3(PathToLoad As String, OutputFileName As String)

Dim oFSO As Scripting.FileSystemObject

Dim oFolder As Scripting.Folder

Dim oFile As Scripting.File

Dim oStream As Scripting.TextStream

Dim sTemp As String

Dim rs As ADODB.Recordset

Set oFSO = New Scripting.FileSystemObject

If oFSO.FolderExists(PathToLoad) = True And OutputFileName <> "" Then

Set rs = New ADODB.Recordset

rs.Fields.Append "FileName", adVarChar, 100

rs.Fields.Append "FileSize", adInteger

rs.Fields.Append "FileCreated", adDBTimeStamp

rs.Open

Set oFolder = oFSO.GetFolder(PathToLoad)

For Each oFile In oFolder.Files

rs.AddNew

rs.Fields("FileName") = oFile.Name

rs.Fields("FileSize") = oFile.Size

rs.Fields("FileCreated") = oFile.DateCreated

rs.Update

Next

Set oFolder = Nothing

'sort it

rs.Filter = "Filename like 's%'"

rs.Sort = "FileSize desc"

rs.MoveFirst 'probably should go in the createtable function

sTemp = CreateTableFromRS2(rs)

rs.Close

Set rs = Nothing

'create the file, overwrite any previous version of the file

Set oStream = oFSO.CreateTextFile(OutputFileName, True, False)

oStream.Write sTemp

Set oStream = Nothing

End If

Set oFSO = Nothing

End Sub

Whether you create a table, do a select from an existing table, or create a

disconnected recordset depends on the circumstances of course. The key is that

by always pushing our data into a recordset we can leverage our display code

heavily and that is worth something.

In a follow up article we'll look at how we can use XML to accomplish the

same thing and add the ability to do client side sorting. As always I look

forward to your comments. Whether you agree or disagree, take a min to add your

thoughts to the discussion forum attached to the article, other readers will

appreciate the additional insight only you can provide.

Rate

Share

Share

Rate