Using Excel Spreadsheets As Web Data Sources
Posted on 23 Nov 1999
by Steven Raines (raines)
Rated 3.83 (Ratings: 4)
- More articles in Backend
ProblemYou want to create a way to easily manage a small set of data which can be easily maintained by the average office worker.
- Microsoft Internet Information Server 4.0
- Microsoft Excel ODBC Driver installed on Web Server.
- Microsoft ActiveX Data Objects version 2.0 or greater installed on Web Server.
- Microsoft Excel 95, 97 or 2000
SolutionCreate an Excel Spreadsheet that contains the data. Allow the user to update this via FTP, or have a scheduled process that does this for them. Then create an Active Server Pages script which uses ActiveX Data Objects to gather the information from the spreadsheet and display it.
ExampleTo illustrate this solution, we'll create an online event listing. We want this to be updated by the events coordinator, who is not very web saavy.
Create the Excel SpreadsheetTo begin, create a new Worksheet in Microsoft Excel. In the first row, place the names you would like for your fields. In this example, we need to know the name of the event, the location of the event, who is coordinating it and the date and time of the event.Now, we need to fill in some data.Now, highlight the the columns that contain your data.From the menu, Select Insert, Name, Define. In the dialog box that appears, enter the name for the highlighted data. In this example, we'll call it "Events".Hit OK. You should see the name of your data group in the "Name Box" at the upper left hand corner of the screen.Now your data should be all set. If your worksheet is protected or is placed in a secure directory, make sure that the user that the web server is running as (usually IUSR_MachineName) has the appropriate permissions. Save your data file and place it on your web server.
Create the ASP codeThe first thing that we have to do is create the Connection and Recordset that we will be using.
'## Create Connection and Recordsets Dim Conn, RS Set Conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset")Next, lets create a variable for the file name. In this example, let's say we named the file "EventData.xls".
'## The Excel File Name XLFile = "F:\webdata\EventData.xls"Now, we have to define what data we want to get. Most of the time, we'll want all the data. This case is no exception, so we create a SQL query to get everything from the data we defined as "Events".
'## The Data to extract szSQL = "select * from Events"To get the data, we need to open the connection to the data source. Here is where we stick in the name of the file defined above. You could just as easily put the file in this line, as well.
'## Create and open the connection Conn.ConnectionString = "DBQ=" & XLFile & ";DRIVER=Microsoft Excel Driver (*.xls);UID=admin;" Conn.OpenOnce this is done, we set some properties on the recordset and use the Recordset.Open method to get the specific data from our spreadsheet. By default, ADO creates Forward Only recordsets. We'll make ours static. After we get the data, we can disconnect the Active connection, since we don't need it anymore.
'## set the cursor to be static. rs.cursortype = 3 ' adStatic.Now we can operate on the data just like any other recorddata. Here, we will iterate through the data, displaying the events on the web. Naturally, we could generate any formatting we want.
'## open the recordsetrs.open szSQL, conn
'## Disconnect recordset, eliminate connectionrs.activeconnection = nothing Conn.Close Set Conn=nothing
'## iterate through the recordset.while not rs.eof response.write RS("name") & " -- " response.write RS("location") & " -- " response.write RS("Coordinator") & " -- " response.write RS("Date") & " -- " response.write RS("Time") & "<BR>" rs.movenextwendFinally, we close out the recordset and release it. Of couse, you could skip this set and move the data into the user's session, if they were going to be querying it repeatedly. For now, we'll assume that isn't the case.
RS.closeSet RS=nothingThis should get you started. Of course there are all kinds of neat things you can do once your data is imported into an ADO Recordset. Excellent information is available from the Able Consulting's Technology Page.