Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Create an Employee Photo Directory with Excel and ASP

Learn how to use Excel 2000 and Active Server Pages to create a photo directory.




Application Security Testing: An Integral Part of DevOps

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
First, here's a quick tour of the browser-based interface and navigation as shown in Figure 1. On the left pane, there's a treeview page built with client-side JavaScript and server-side VBScript. When you click a letter node, the right-hand pane shows thumbnail images of all the employees whose last names start with the letter that you selected. When you expand a letter node and click the name of an employee, the right-hand pane shows a passport-sized photo of the employee. There's also a search function that returns a list of thumbnail results. You can click a link to zoom to the passport view.

Figure 1. Photo Directory: The Photo Directory project displays thumbnail and, as shown here, passport-sized images.
Prepare the Spreadsheet to Use as a Database
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 data—a 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/.

Figure 3. Defining Name Range: Add a table name to your spreadsheet by defining a named range.
Querying the Spreadsheet
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.

srch=request("let") 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:

Ray Stevens Ritchie Valens Steve Lawrence Walter Brennan Brook Benton Chris Kenner Clarence Henry Connie Stevens Dodie Stevens James Darren

Add Treeview Navigation
The treeview pane shown in Figure 1 is my ASP-oriented modification of some sophisticated JavaScript menu code written by Dieter Bungers (dieter.bungers@gmd.de). His article and freeware code are available at http://www.siteexperts.com/tips/techniques/ts01/index.htm.

Rather than using the static JavaScript file and hard-coded menu text, I use ASP to read from the Excel spreadsheet and build the JavaScript array that Dieter's code wants to see. This way, whenever you update the spreadsheet, the ASP code creates new letter nodes and employee names instantly. Look inside tocjs.asp to see how you can create the letter nodes as well as the employee names for TOC.

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.

In this article, you have seen how to prepare an Excel spreadsheet as a data source for an intranet site. Using ADO and SQL commands you can read, sort, and filter recordsets based on the spreadsheet's rows and columns. By passing query string values in ASP, you displayed a set of thumbnail records that matched your criteria or showed users a single passport-size image. Finally, you have seen that you can generate client-side JavaScript using server-side ASP to make your Table of Contents dynamic. It is important to download and analyze the complete source code for a fuller understanding of the techniques shown in this article.

Ken Cox is a Senior Technical Designer at Nortel Networks in Toronto, Canada where he is a Web application programmer and technical writer. In addition to writing for computer magazines, Ken is a contributing author to Visual InterDev 6 Unleashed by Sams. He has been involved with IIS, ASP and Visual InterDev since their earliest beta releases. Microsoft has recognized Ken as a Most Valuable Professional (MVP) for his contribution to the online community in the Visual InterDev and Active Server Pages areas.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date