|
September 2001Manipulate Excel Spreadsheet Data in ASP Using ADOby Jason Fisher, MCPApplications: ASP, Microsoft Excel, Microsoft ADO 2.0+
Almost all Web developers these days are very well acquainted with ActiveX Data Objects (ADO). ADO is Microsoft's preferred data access library, and you've probably used it many, many times to connect to Access and SQL Server databases. But useful data isn't limited to databases. Indeed, some of the most useful business data you may come across is often stored in flat files, like Excel spreadsheets, plain text files, and static Web pages. The usual approach to manipulating this sort of data is to open these files directly, using the Excel as a database?That's right! You can easily connect to and read data from a Microsoft Excel spreadsheet using ADO. In fact, with just a few exceptions, once connected, you can treat the spreadsheet pretty much as if it were a database. Think of the workbook as a database in which each worksheet is a separate table. Why use ADO?There are several excellent reasons to approach the manipulation of spreadsheet data as if it were part of a database. First, by now, you're already familiar with ADO. You understand the basics of creating Another good reason to use ADO is that it's an in-process component library. This means that it loads into the same process space as IIS, improving performance and control. Excel, on the other hand, is an out-of-process library, creating a performance detriment anytime you use it. Moreover, Excel isn't designed with total interfaceless automation in mind. If you attempt to close an unsaved spreadsheet, for example, a dialog box may pop up on the server asking whether you'd like to save the file. Since users of your Web site can't see this dialog box, they have no way of dismissing it. This sort of behavior has been known to bring Web servers to their knees. How to use ADO?So, now that we've seen some of the reasons to use ADO for spreadsheet data access, let's talk about how to do so. ADO can connect to an Excel data source with one of two OLE DB providers, the OLE DB provider for Jet or the OLE DB provider for ODBC. Which you use is up to you, but in our sample code, we'll be using the Jet OLE DB provider. The provider needs only two pieces of information in order to connect successfully to an Excel spreadsheet. First, you'll need to tell the provider where to find the spreadsheet; in other words, you need to provide the fully qualified path and filename. Second, you need to tell the provider which version of Excel you're using. At the present time, there are two versions you might indicate, Excel 5.0 or Excel 8.0, as summarized in Table A. Taking these two items into account, here's a code snippet that instantiates and connects to an Excel spreadsheet:
As you can see, there is nothing particularly strange about this code. Apart from the "Extended Properties" portion of the
Other things to considerBefore we dive into our sample code, there are a couple of other points to consider. First, by default, the OLE DB provider for Jet assumes that the first row of your Excel spreadsheet consists of column headings that it can use as field names. This isn't a requirement, but if this isn't the case, then you need to tell the provider. Otherwise, it will very happily make the assumption, and you'll lose the first row of your data. To properly inform the provider, add an
There are only two possible values, A second issue to bear in mind involves mixing data types. Because Excel doesn't maintain the same kind of detailed information about each field that a relational database would, the OLE DB provider has to make certain assumptions about the data format of each spreadsheet column. It does so by scanning a certain number of rows and examining the data in each column. This has the potential to create problems for you, though, so consider yourself advised to populate columns only with the same internally consistent data. For example, one column might consist of numbers only, while another might contain text data. Putting the technique into practiceOnce we've got an open connection to the Excel spreadsheet as an ADO data source, manipulate the contents of the spreadsheet is relatively straightforward. As you know, if we were dealing with a table in a traditional relational database, we'd simple instantiate an ADO
When using an Excel spreadsheet as our data source, however, there aren't any tables per se, so we need another way to pinpoint the data we're after. The OLE DB provider allows you to identify the data you want. You can refer to an entire worksheet, to a named range of cells in a worksheet, or to an unnamed range of cells. Suppose, for example, that we've got a spreadsheet with one worksheet in it, with the default name, Sheet1. To return all the data from it, we'd use a
Note that the bracket characters ([ ]) and the dollar sign ($) are required. Omit them and ADO will throw a fit. We'll use this approach in our sample code, but let's take a quick look at how easy it is to narrow down your query to a range. For a named range, simple use that name in place of a table name, as shown here:
Note the absence of the dollar sign and bracket characters. These are only required for worksheet names. For an unnamed range, use the following syntax:
There are two important caveats to be made here. If you opt for the worksheet form of the A sample applicationJust to have a little fun, we've built a simple data access page to emulate tracking the results of a popular reality television program. Launch Excel and create a new spreadsheet called Island.xls. The spreadsheet should have only one worksheet, named Island. Enter the data shown in Figure A, or something equally fanciful of your own. Figure A: We've created a simple spreadsheet to access in ASP using ADO.Now let's access this data from ASP using ADO. Enter the code from Listing A. The code is simple, but we've thrown in a trick or two just to make things interesting. Let's see what's going on here. Listing A: Code to access Excel spreadsheet data using ADO
Following the requisite variable and constant declarations (two of which, Now it's time to get the data. We'll execute a simple Next, we've got some HTML to set up the page the way we want it to look. Note the use of the Now things get fun. We're using the Now remember our two curious string constants, And that's all there is to it! Because this article is a simple introduction to a potentially much larger topic, we haven't demonstrated inserting new data or deleting old data. Both, however, are possible and, apart from a caveat or two, just as straightforward as you're used to already. Excel serves the WebNow go ahead and run your ASP page. If you've still got the Excel spreadsheet open, you'll probably notice an interesting error, shown in Figure B. One of the requirements of this technique is that the spreadsheet be closed at the time it's used. Multiple browsers can read the data, but when the spreadsheet is open in Excel, it's locked for exclusive use. Close Excel and refresh the Web page. Now, the results should be more to our liking, as illustrated in Figure C. You can see that ASP happily loaded our Excel spreadsheet data through the OLE DB interface in ADO. We've only just scratched the surface of the possible uses of this data access approach, but you can see the possibilities already. Most importantly, you can forget about learning the complicated Excel object model (at least, until you really need to!), and you can kiss a lot of those automation errors goodbye. Figure B: Our Excel spreadsheet must be accessed exclusively.Figure C: This page shows how easily we can access Excel data from ASP.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasdj01/html/asp0193.asp
|