Software

Office Web Components deliver interactive Web pages

Office Web Components, a collection of ActiveX controls, are an Office 2000 innovation. Rick Dobson explains how to make your Web pages interactive with Office Web Components.


Office Web Components, a collection of ActiveX controls, are an Office 2000 innovation that empowers Webmasters to deliver Office component features from Web pages. This collection of controls is particularly attractive because it dramatically simplifies the task of creating interactive Web pages. Users will feel right at home on these Web pages because they can have the same look and feel as standard Office components.

In “Building database Web solutions with Access 2000 ,” I demonstrated how to create data access pages. These pages rely on the Data Source ActiveX control—one of the Office Web Components—to present database forms and reports over an intranet. In this article, I’ll give a more comprehensive overview of the Office Web Components by reviewing their four controls and describing installation issues. In addition, I’ll present three sample applications that illustrate successively more sophisticated ways to use the Office Web Components. My discussion of these samples reveals how you can accomplish the following tasks:
  • Use the Spreadsheet component as a calculator on a Web page.
  • Use a Chart component to display dynamically the contents of a Spreadsheet component.
  • Insert data into a Spreadsheet component on a Web page under programmatic control.

Overview of Office Web Components
The Office Web Component library consists of four ActiveX controls: the Spreadsheet, Chart, PivotTable List, and Data Source controls.

The Spreadsheet component includes a recalculation engine, a function library, and a single worksheet interface. Unlike Excel, the Spreadsheet component enables just a single worksheet at a time. Since this component creates a spreadsheet on a standard Web page, your applications can link with other pages to simulate the availability of multiple worksheets in a workbook. One natural way to start creating a Spreadsheet component application is within Excel. You can also insert a spreadsheet on a web page from FrontPage 2000 and Access 2000.

The Chart component is particularly interesting because of its built-in ability to change its display dynamically as users update the content on a Web page. Since Office Web Components rely on Dynamic HTML instead of Active Server Pages, they don’t require a trip back to the server in order to refresh the display inside a browser.

The PivotTable List component functions similarly to Excel PivotTable reports and Access PivotTable forms. This component draws its data from a source specified by Webmasters. Users cannot change the source, but they can update the contents of a Web page to reflect the most recent data. Users can also graphically rearrange and filter the display of the data in a PivotTable List control.

The Data Source component supplies the data for data access pages. You can create these pages with Access 2000 (see “Building database Web solutions with Access 2000”).

Users can view Web pages created with these components if they browse with the IE 5 or IE 4 browser. Office Web Components are part of a typical Office 2000 installation, and IE 5 installs automatically with Office 2000. While it isn’t necessary to have Office 2000 installed, an organization must have a site license to install the Office Web Components. Page 19 of the Office 2000 Resource Kit describes the process for installing Office Web Components on a computer that doesn’t have Office 2000.

A spreadsheet calculator on a Web page
One of the most obvious applications for the Spreadsheet component is as a calculator. You can include a spreadsheet on a Web page to help the managers and technicians perform standard computations that are essential to running an organization's business. Because of the ease of connecting to a Web server from anywhere in the world, this kind of solution can be global in reach. In addition, since the calculator is a spreadsheet, many users will feel comfortable with the solution.

Suppose that you have a simple spreadsheet calculator that computes the square, square root, cube, and cube root of any number entered by a user. Spreadsheets automatically recalculate by default, so when a user enters a new number, that number’s computed values update instantly. The computations take place locally in the browser without the need to go back to the server, which reduces the burden on the Web server as it speeds the return of the calculated values.

Two special characteristics distinguish the behavior of the calculator. First, users can enter values only in the single cell next to the one with Number. The spreadsheet's design protects all other cells. Spreadsheet calculators require this type of protection to keep users from accidentally entering values over their expressions. Second, the cursor does not move from the single unprotected cell after the user presses the [Enter] key. The cursor remains in the sole cell that permits entry. You can build both of these features from either the original workbook file or the published spreadsheet. My description of the publishing and editing processes describes how to enforce the first rule from the worksheet and the second rule from the spreadsheet on a Web page.

You can use the original spreadsheet in Excel 2000 as a source for the published spreadsheet, and you can achieve its formatting with the Patterns and Fonts tabs of the Format Cells dialog box. Since the default setting locks all the cells in a worksheet, you can protect all cells from data entry with the Tools | Protection | Protect Sheet command. In order to make a spreadsheet interactive, you will normally want to unlock at least one cell before invoking the command. You can accomplish this by selecting one or more cells and clearing the Locked check box on the Protection tab of the Format Cells dialog box. After you’ve entered your expressions, formatted your cells, and protected those cells not being used for data entry, you should save the spreadsheet to an .xls file. Now, you are ready to publish interactively a spreadsheet in the workbook file or a range within a spreadsheet.

If you want to publish a range within a spreadsheet, select that range before you start to publish anything. Otherwise, Excel automatically selects the whole active sheet to publish (but you can override this later). Choose File | Save As Web Page to start the publishing process. Next, click Publish to open the Publish As A Web Page dialog box. If you selected a range, Excel automatically enters Range Of Cells into the Choose drop-down box, and it designates your selection as the range to publish. Click the Add Interactivity With check box and make sure the drop-down box next to it reads Spreadsheet Functionality. If you want a title for your spreadsheet calculator, click Change next to Title and enter a title in the pop-up dialog box. In our example, the title is Rooter Version 1. Browse to (and/or type) the storage location for your web page. Click Publish to launch the process with the settings in the dialog box. The example publishes its Web page as rooter.htm in the Spreadsheets folder of the Web server's root directory.

You can make cosmetic and functional changes to the spreadsheet component by opening the Property Toolbox. Open the toolbox's dialog box by clicking the icon next to the question mark on the spreadsheet's toolbar or by right-clicking in a cell and choosing Property Toolbox. Notice that toggle buttons control the appearance of the toolbar and gridlines on the spreadsheet. A drop-down list with settings of True and False control whether or not the cursor moves after a return.

Dynamically revise a chart by changing a spreadsheet
Excel 97 did a great job of publishing data in graphical format to a Web page. These charts were static at the time that a Webmaster published them. Excel 2000 still permits the static publication of charts, but it also enables the publishing of dynamic charts. These charts can change as you change the data in a spreadsheet source.

The Chart ActiveX control permits you to publish a chart that changes dynamically. Excel offers two routes for this kind of functionality. First, you can publish the chart from Excel. This technique also passes the coordinated spreadsheet source for the chart to a Web page. However, not all Excel chart options are available in the dynamic charts for Web pages. The Excel Save As Web Page wizard automatically modifies options so that the spreadsheet and chart work together on the Web. See the Excel 2000 Help section for "Put a chart on a Web page" to find detailed instructions for publishing the chart. Second, you can publish a spreadsheet to a Web page with Excel 2000. Then, you can add a Chart ActiveX control to the page in FrontPage 2000. This approach allows more granular control of the published spreadsheet data. It also allows you to construct a chart initially with the options that a Web page can display dynamically. This second approach reinforces the notion that you are working with two separate controls on a Web page, and it can help you understand how to edit them independently to serve your needs best.

You can set up a Web page with both Spreadsheet and Chart ActiveX controls. Suppose that the spreadsheet allows entries in all cells except Income, which the spreadsheet computes as a function of the remaining cells. When a user changes a row in the spreadsheet, the spreadsheet modifies income and makes a corresponding update to the bars in the chart on the right. Notice that the chart uses a table to help position the controls and corresponding text on the Web page.

You can add a Chart ActiveX control to a web page by choosing Insert | Component | Office Chart in FrontPage 2000. This action launches the first of three steps for the Office Chart Wizard. To construct the sample that I just described, I positioned the cursor in the lower-right cell of the table before I started to add the control to the Web page. In the Chart wizard's first dialog box, select a chart type. It includes many of the chart types available from Excel, but there are no 3-D chart types. In the second step, you can select a data source type, such as a Spreadsheet control, and then a specific instance of that type. My example contained only a single spreadsheet that was available as a data source, and the wizard selected it automatically. In the final step, you can designate spreadsheet ranges for the data series and labels in the bar chart. After the wizard positioned the chart in the table's cell, I removed the legend label because there was just one series, and I manually dragged the chart's edge to resize it.

An updateable telephone directory in a spreadsheet
A popular use of spreadsheets in Office is to store data, such as a short contact list. Suppose that you have a spreadsheet that contains a subset of the author contact data from the Pubs database that ships with SQL Server. The Web page on which the spreadsheet resides contains two buttons with click event procedures. The first button clears the spreadsheet. The second button populates the spreadsheet with data from the authors table in the Pubs database. As the event procedure for the button writes values into the spreadsheet, it colors alternating rows in the table yellow and light blue.

Notice the spreadsheet's toolbar. In prior examples, I suppressed the visibility of the toolbar for a spreadsheet on a Web page. Exposing the toolbar enables users to manipulate a spreadsheet's contents with its toolbar buttons. As in standard Office components, hovering the cursor over a button opens a short description of that button. The A-to-Z button sorts the contact records in ascending order on any column in the table on the spreadsheet. The Z-to-A button enables sorting in descending order. Clicking the button with a filter on it toggles the appearance of filter buttons on each column heading in the table. Users can operate these buttons to display a subset of the original records. Clicking the Export-to-Excel toolbar button copies the contents of the Web-based spreadsheet with formatting to a spreadsheet in Excel. Clicking the Help button on the toolbar explains how to use toolbar buttons and other features of the Spreadsheet control on a Web page.

By optionally displaying the toolbar and turning features on and off with the Property Toolbox, Webmasters can readily provide selected functionality in a spreadsheet. No programming is necessary to control the availability of these functions. On the other hand, populating a spreadsheet with data on demand will typically involve some programming. The following example shows how to accomplish this task with event procedures.

You can start to construct a sample by adding a blank Spreadsheet control to a web page in FrontPage 2000. Add the control with the Insert | Component | Office Spreadsheet command. Then, open the Property Toolbox. In the Show/Hide settings, select the Toolbar toggle button to show the toolbar. In the Advanced settings, specify a viewable range. By default, the viewable range is all 65,536 rows in a spreadsheet, but you can designate any subset of rows and columns that are appropriate for an application, such as cells A1 through D12.

By physically placing two Input button controls after the spreadsheet control on a web page, you can position the buttons below the spreadsheet. By centering both the Spreadsheet control and the buttons on the web page, you can designate the horizontal position of the spreadsheet and the buttons relative to one another. The following HTML names the buttons and centers them on a web page:
<CENTER>
<input type="button" value="Clear Records" name="cmdClear" >
<input type="button" value="Update Records" name="cmdUpdate" >
</CENTER>


The real magic in this example is the Script block in the web page. You can insert code behind web pages just as you do with code behind forms and reports in Access. While you can use either VBScript or JScript, most Office developers will find VBScript much more familiar.

Listing A presents the VBScript block with the event procedures for the Clear Records and Update Records buttons. The onclick event procedure for the Clear Records button requires just two lines. It starts by selecting the top-left cell in the spreadsheet. Next, it invokes the Clear method for the range contiguous to that cell.
<SCRIPT LANGUAGE = "VBScript">
<!—
Sub cmdClear_onclick()
Spreadsheet1.ActiveSheet.Cells(1,1).Select
Spreadsheet1.ActiveSheet.UsedRange.Clear
End Sub
 
Sub cmdUpdate_onclick()
Dim cnnConnection
Dim rstEmployees
Dim fldCurrent
Dim varData
Dim strSQL
Dim intIRow
Dim intICol
Dim bolCToggle
 
'Create Connection object
Set cnnConnection = CreateObject("ADODB.Connection")
cnnConnection.Provider = _
"sqloledb;data source=cab2200;" & _
"user id=sa; initial catalog=pubs"
cnnConnection.Open
 
'Create Recordset object and fill it with records
'from a subset of the authors table in the
'Pubs database
Set rstEmployees = CreateObject("ADODB.Recordset")
strSQL = "SELECT au_fname, au_lname, phone, " & _
"state " & _
"FROM authors WHERE state<>'CA'ORDER BY au_lname"
rstEmployees.Open strSQL, cnnConnection, 1, 3
 
'Remove the existing values from the control
Spreadsheet1.ActiveSheet.Cells(1,1).Select
Spreadsheet1.ActiveSheet.UsedRange.Clear
 
'Add the field names as column headers
For fldCurrent = 0 to rstEmployees.Fields.Count – 1
intICol = intICol + 1
Spreadsheet1.ActiveSheet.Cells(1, intICol). _
Value = rstEmployees.Fields(fldCurrent).Name
Next
 
'Fill the control with data from the database
varData = rstEmployees.GetRows _
(rstEmployees.RecordCount)
bolCToggle=False
For intIRow = 1 To rstEmployees.RecordCount
For intICol = 0 To UBound(varData)
Spreadsheet1.Activesheet.Cells _
(intIRow + 1, intICol + 1).Value = _
varData(intICol, intIRow - 1)
if bolCToggle=False then
Spreadsheet1.Activesheet.Cells _
(intIRow + 1, intICol + 1). _
interior.color="yellow"
else
Spreadsheet1.Activesheet.Cells _
(intIRow + 1, intICol + 1). _
interior.color="lightblue"
end if
Next
if bolCToggle = False then
bolCToggle=True
else
bolCToggle=False
end if
Next
 
'Format the header row and the body of
'the control
Spreadsheet1.ActiveSheet.UsedRange. _
AutoFitColumns
Spreadsheet1.ActiveSheet.Range("A1:D1"). _
Font.Bold = True
Spreadsheet1.ActiveSheet.Range("A1:D1"). _
Interior.Color = "gray"
Spreadsheet1.ActiveSheet.Range("A1:D1"). _
Font.Italic = True
Spreadsheet1.ActiveSheet.Range("A1:D1"). _
Font.color = "Darkred"
 
'Cleanup
 
Set rstEmployees = Nothing
Set cnnConnection = Nothing
 
End Sub
—>
</SCRIPT>


The event procedure for the Update Records button is substantially longer, but it contains extensive comments. After reserving memory for variables and objects, it opens a connection to the Pubs database. When you adapt this code, you can change the data source so that it points to the name of a SQL Server on your intranet, and the initial catalog should reference an operational database for your application.

Next, the code opens a recordset on a subset of the authors table in Pubs—namely, authors not from the state of California. When modifying this code for your own needs, you can use a SQL statement as in our example, or you can reference a table, view, or stored procedure in your database as the source for the recordset.

After specifying the source for the data in the spreadsheet, the procedure's focus switches to the spreadsheet. First, it writes the field names in the first row of the spreadsheet. Second, it copies the contents of the recordset to an array with the GetRows method. Third, it writes the contents of the array across successive rows of the spreadsheet. As it performs these tasks, the procedure checks the status of a toggle variable to color the rows that contain data either yellow or light blue. The last step before cleaning up applies selected formatting codes to the first row in the spreadsheet and resizes columns to fit their contents.

Conclusion
In this article, I explained how to make your Web pages interactive through Office Web Components. After a brief overview of Office Web Components, I described the Spreadsheet ActiveX control and the Chart ActiveX control. You should have learned manual and programmatic means of making Web pages interactive with Office Web Components.

Rick Dobson, Ph.D., and his wife operate a development and training consultancy. He is the author of the best-selling book Programming Microsoft Access 2000 for Microsoft Press. Rick is a regular contributor to TechRepublic and numerous computer periodicals. In addition, he has presented training sessions and seminars on Access and Web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. You can reach Rick at either of the two Web sites that his practice maintains (www.programmingmsaccess.com and www.cabinc.net).

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks

Free Newsletters, In your Inbox