Too often, Microsoft Excel spreadsheets are updated through the laborious and tedious process of manually importing data from an external data source--or, worse yet, by cutting and pasting. Excel 97 implements a powerful, yet underpublicized feature called Web queries to simplify this task.
Web queries provide spreadsheets with the ability to update selected cells over an HTTP connection. By running a Web query, the user can circumvent manual data manipulation and make the spreadsheet update itself directly over the Web. By combining this feature with ASP, developers can create extremely powerful intranet and Internet Excel solutions.
To run a Web query inside Excel 97, go to the Data menu, click Get External Data, and select Run Web Query. Select the Web query from the dialog box, and press Get Data. Next, choose the cell that represents the top left corner of the Web query result. If the Web query requires any parameters, you will enter them in subsequent dialogs.
Figure 1
Press OK and watch the magic unfold. A small globe will spin in the status window at the lower right section of the screen. This indicates that Excel has initiated an HTTP connection and is retrieving the Web page for display. The results will appear in the spreadsheet.
Getting Started
A great way for developers to get familiar with Web queries is to download the Microsoft Excel Web Query Connectivity Kit from:
The magic glue that connects an Excel spreadsheet with a Web page is a text file having an iqy extension. These iqy files can be created quickly using Notepad. Excel reads this simple, four-line file to determine which Web page to request. In addition, the file instructs Excel about which data to pass to the Web page. Let’s first examine the contents of a sample iqy:
WEB
1
http://www.myserver.com/MonthlySales.asp
Month=["Month","Enter month (1-12)."]
Line 1 and 2 are optional lines because WEB and 1 are the only options, respectively. The third line is the name of the Web page that is requested by Excel. The fourth line indicates POST parameters that are sent in the HTTP request. In this case, the form variable Month is sent to the Web server. Inside the brackets are two visual cues. The first string is the title of the parameter’s dialog box. The second string is the label used to prompt the user for the parameter.
To send a query using the GET method, the fourth line would instead be concatenated at the end of the third line:
Excel will convert only a subset of HTML into Excel cells. For a complete list of supported and unsupported tags, refer to the Connectivity Kit. Web queries are geared toward HTML tables, for obvious reasons. Excel even adds some HTML table attributes that add to the interaction between Excel and the HTML table.
ASP developers can create Web queries (iqy files) that point to ASPs, either located on a company intranet or on the public Internet. Let’s continue with our Monthly Sales example. Above, we looked at the MonthlySales.iqy.
MonthlySales.iqy would be saved under Program Files\Microsoft Office\Queries so that Excel can find it.
Next, let’s look at MonthlySales.asp. This page assumes that there is a server-side COM object, Acme.Sales, that accesses company sales data from the company intranet:
<%@LANGUAGE=VBSCRIPT%>
<%
Dim lMonth
Dim objSales
Dim rsSales
lMonth = Request.Form(“Month”)
Set objSales = Server.CreateObject(“Acme.Sales”)
objSales.lMonth = lMonth
Set rsSales = objSales.Sales
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
<TABLE border=0>
<TR>
<TD> ProductID</TD>
<TD> Product Name</TD>
<TD> Sales Person</TD>
<TD> Date Closed</TD>
<TD> Sale Amount</TD>
</TR>
<% While Not rsSales.EOF %>
<TR>
<TD><%= rsSales (“ProductID”)%></TD>
<TD><%= rsSales (“ProductName”)%></TD>
<TD><%= rsSales (“SalesPerson”)%></TD>
<TD><%= rsSales (“SalesDate”)%></TD>
<TD><%= rsSales (“SalesAmount”)%></TD>
</TR>
<% rsSales.MoveNext%>
<% Wend %>
</TABLE>
</BODY>
</HTML>
By having a copy of MonthlySales.iqy, anyone in the company can now run a monthly sales report by running this Web query.
Figure 2
Web queries are part of the Excel object model, so there are a number of impressive ways to manipulate them through VBA (Visual Basic for Applications) code. Excel uses the QueryTable object for Web query automation. This object can be linked to button events and so forth to extend the power of Web queries. Another powerful feature of Web queries is their ability to automatically refresh when a workbook is opened.
Excel’s Web query technology adds some custom table data cell (TD) attributes that can enhance the spreadsheet. One of these attributes is Filter. Placing Filter inside a <TD> causes drop-down filters to appear in the column headers of the spreadsheet. Let’s place a FILTER=ALL inside the <TD> tags in the column header:
You would now be able to filter down the results by using drop downs. For instance, you could drop down the Sales Person filter and view the results for a specific sales person. The filtering is all client side (inside Excel) and the results are instantaneous. Excel simply hides the rows that don’t meet the filter criteria.
Another custom attribute that can be added to the HTML table is Formula. When Formula is used inside a <TD>, any Excel formula can be used inside the cell. An example would be for summary cells, where you want to show averages and the like:
Again, this can modified using ASP scripting. If you are tracking the number of rows you are writing to the HTML table, you can dynamically write Excel formulas:
The majority of styles used in Microsoft Internet Explorer’s (IE’s) cascading style sheet (CSS) will be converted properly by Excel. However, pay special attention to the number format. By default, Excel imports all data into the General format. If you need a specific, precise Excel number format, you need to add a style attribute on the ASP side. This style attribute takes the form of vnd.ms-excel.numberformat:xxx. To ensure that a cell uses a precision of 2, your tag would look like the following:
For more detailed information on these HTML extensions, please consult the Connectivity Kit.
Once the data is resting safely in Excel, there are an infinite amount of possibilities for further data manipulation. You can link the cells from the resulting HTML into other worksheets, workbooks, Word documents, and charts. For a good example of this, experiment with Pcquote.xls, which comes with the Connectivity Kit.
Excel even integrates with the IE security model, and will display an authentication dialog for ASPs protected by IIS. Just like IE, Excel will cache the user name and password until that instance of Excel is shut down.
Excel 2000 has added some new wrinkles to Web queries. The basic premise is the same, but 2000 makes it easier and faster to customize a Web query. Once developers learn the Excel 97 version, they will really come to appreciate these changes in Excel 2000.
Microsoft has made a strong case for simple, Web-based Excel reporting solutions. The real value comes from ASP and the use of dynamic variables. This type of solution frees up the business analyst from time-consuming data manipulation and takes away a degree of human error. And, best of all, the implementation of Web queries is most assuredly rapid development.
About the Author
Bill Jeffries is an independent Web developer located in the Washington, D.C., area. He specializes in building Microsoft Internet applications centered on ASP technology. Bill is currently working on a government ecommerce site. He holds a computer science degree from the U. S. Naval Academy. Hobbies include hockey, reading, and his beloved Orioles. He can be reached at bill@billjeffries.com.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
