onsider this scenario: an administrative assistant maintains a staff list in Excel 2000. You want to publish the information on the corporate intranet. Your first thought might be to convert the spreadsheet into an Access or SQL Server database to make it searchable.
But let's analyze this for a moment. Isn't an Excel spreadsheet just another database? Essentially, a spreadsheet is a table with rows and columns of data. Given that the admin assistant is already comfortable with Excel, and the intranet data is read-only, you don't need to go the trouble of converting to another format.
This article shows how to use an Excel *.xls file as the database for an employee photo directory. As part of this Active Server Pages application you'll see how to configure the spreadsheet for online use, create recordsets from the rows of data, and perform queries and navigate using a browser.
What you need:|
Internet Information Server or
Personal Web Server
Active Server Pages
ActiveX Data Objects
MS Excel 2000
A Tour of the Interface
Prepare the Spreadsheet to Use as a Database
|Figure 1. Photo Directory: The Photo Directory project displays thumbnail and, as shown here, passport-sized images.|
To use an Excel spreadsheet with ASP code, you need to make sure that the spreadsheet data is properly formatted. ADO picks up the column name from the first row of the spreadsheet. Therefore, each column needs a unique name.
The heading names in photodir.xls are "Last Name," "First Name," "Employee," "Extension," and "File Name." The data starts in row 2, as shown in Figure 2. The "File Name" is the photo of the employee, perhaps from the worker's ID badge. An easy way to ensure unique image names is to use employee numbers as the base names. When we review the HTML code later in the article, you'll see the creation of the full path and image name.
|Figure 2. Data Row: The first row of the spreadsheet contains the column names.|
You have fields (columns) and records (rows) in your spreadsheet database, but you need a table name. In a spreadsheet, the table name is the title of a defined area of cells called a range. To create a named range, select (highlight) all of the cells that contain dataa nice shortcut for selecting adjacent cells is CTRL+SHIFT+* (asterisk). With the area selected, go to Insert > Name > Define. Figure 3
shows the Define name dialogue box. In the Names in workbook box, type the table name, such as "photorange" and then click Add. Click OK when you are done.
To make sure that you have the correct type of data for your Web application, format the cells as text, even the cells that contain numbers. To do this, with the range of cells still selected, go to Format > Cells > Number and in the Category box, click Text. Click OK to confirm the formatting. That's all that you need to do in Excel 2000, so you can save your changes and exit the program. If you're likely to perform this step often, you might want to use the VBA macro described in the sidebar, One-Click Spreadsheet Formatting."
Create an Excel Database Connection in ASP
The next step is to put together an Active Server Pages script that displays the contents of the spreadsheet. As you can see, ActiveX Data Objects (ADO) accepts an Excel spreadsheet as a database.
First, you need to instantiate an ADO Connection object, using server.CreateObject("ADODB.Connection"). Next, you need to tell ADO what provider and which driver to use, as well as where to find the database/spreadsheet file. As you can see in Listing 1, from centre.asp, you can store the connection string in a variable such as 'connstr' for easier handling. If you aren't sure that you have the correct driver for Excel, you should install the Microsoft Data Access Components (MDAC 2.5 or higher) on your Web server. You can get MDAC from http://www.microsoft.com/data/.
Querying the Spreadsheet
|Figure 3. Defining Name Range: Add a table name to your spreadsheet by defining a named range.|
Having created a connection to the database, you need to open the connection and build a SQL query to fetch a record. As you'll see in the complete code
, an HTML query string passes the employee number to the ASP page that stores the value in the variable 'idnum'. In Listing 2
you retrieve all of the columns (SELECT *) from the defined range (photorange) that match the given employee number (for example, WHERE Employee='10'). By executing the SQL query, you create a recordset that is stored in the variable 'rs'.
Writing to the HTML Stream
|Figure 4. Display Field Values: Thumbnail views include a hyperlink to the passport view.|
Once you have a recordset, you can insert the values of each field into the HTML stream by using a construction such as <%=rs("First Name")%>. That's the shortcut version of ASP's Response.Write method wrapped in the ASP delimiters.
In Listing 3, I've removed all of the style formatting to simplify the code. You can download the complete code that includes all of the HTML. Notice the way to display the image in HTML. You can build the value of the src parameter using the name of the subdirectory (passport), the employee number, and the image extension (.jpg).
If the query string has passed a letter in from a node of the treeview (such as centre.asp?let=C), the script creates a complete recordset of all employees whose last names start with the selected letter and then displays thumbnail views. In code, you concatenate a string that resolves to something like "[Last Name] LIKE 'C%'" and pass the string to ADO's Find method. The following snippet shows the basic technique, but you'll want to refer to the complete source for working code.
fndtext= "[Last Name] LIKE '"
rs.Find fndtext & srch & "%'"
To display the field values, loop through the recordset with a Do While rs.EOF <> True...Loop structure. The following code creates HTML hyperlinks to the corresponding passport view. Figure 4 shows how the links appear in right-hand pane of the browser.
Response.Write "<a href=" & chr(34) & _
"centre.asp?id=" & _
rs("Employee") & chr(34) & ">" & _
rs("First Name") & " " & _
rs("Last Name") & "</a>"
Searching Spreadsheet Rows in ASP
The search code in the Photo Directory application is similar to the routine that finds employees whose last names begin with a letter selected in the treeview. However, the code in search.asp lets the user choose whether to search on the first name or last name. For added flexibility, users can prefix the search string with an asterisk (*) as a wildcard character. That way a search of last names with "*en" as the search text returns these hits:
Add Treeview Navigation
A Thumbnail Utility
Creation of a photo directory site assumes that you have photos to display and that they are the right dimensions. To speed the generation of the thumbnails and passport images, I purchased a handy utility called Au2Thumbs Pro from http://www.filehouse.com/au2thumbspro/index.html. The Pro version offers a command line batch mode that generates hundreds of thumbnails in seconds.