CXO

Put your data where your Web page is with Office XP's Spreadsheet component

Continuing with his series on Office XP Web Components, Rick Dobson shows how to make your spreadsheets run with the Web. This Daily Drill Down explains everything from tapping external data sources to using Microsoft Script Editor to spruce up the page.


Office XP’s Web Components are promising new tools for those of us responsible for Web page design and development. These four components function similarly to their Office counterparts but also work on Web pages. This is the second Daily Drill Down in my series on the Web components. In "Introducing Office XP Web Components," I introduced the four Office XP Web Components and explained installation issues and general use. Here, I’ll focus on the Spreadsheet component, which enables page authors to place a spreadsheet on a Web page and lets users perform calculations on the Web page from their browser. I’ve come up with a calculator example to illustrate this process.

Introducing the net sales calculator
It's common for sales representatives to offer discounts from published prices to help close a sale. Corporate analysts may benefit from a simple calculator that lets them input the gross sales (units sold multiplied by published price) and discounts to derive net sales. This example is purposely simple so that you can focus on the Spreadsheet component rather than the logic of the calculation. Page authors using the Spreadsheet component should have access to a calculation engine based on the one in Excel 2002, which facilitates many types of advanced computations.

Figure A shows a simple calculator on a Web page created with the Spreadsheet component in FrontPage 2002. The calculator's design lets users type in gross sales and discounts to gross sales. The spreadsheet computes, in cells B5 and B7, both net sales (Gross Sales less Discounts) and the discount percent to gross sales (Discounts divided by Gross Sales).

Figure A
A spreadsheet for a Web page that computes net sales and discount percentage based on gross sales and discount sales


The calculator in Figure A has several aesthetic and functional deficiencies. First, there is no currency specified for sales dollars or percent formatting for discount percentage. The title for the calculator starts in cell A1 and extends into cell B1, but it is not centered and it doesn’t stand out above the calculations. Third, users can just type over the expressions in cells B5 and B7. While users of a Web page with a Spreadsheet component can reload the page if they corrupt a formula (by typing in a cell), it is always best to protect cells with formulas.

Figure B shows the Commands And Options dialog box during the process of formatting the Discount entry in Percent format. As you can see, I previously formatted the three sales quantities in cells B3 through B5 with a Currency format. To open the Commands And Options dialog box, click on the next-to-last control on the spreadsheet's toolbar.

Figure B
Use the Commands And Options dialog box to format a Spreadsheet component on a Web page.


The Commands And Options dialog box controls many functions. For example, you can format cells to better distinguish the calculator's title. To bold and italicize certain cells, select the cells (in this example, A1 and B1). On the Commands And Options dialog box’s Format tab, click B and I in the Text Format group. Next, click the Merge Cells control (the second control in the Cell group). Finally, click the Center control in the Text Format group. These steps will bold and italicize the title and center it over columns A and B, which contain the calculator.

You can also use the Commands And Options dialog box to protect selected cells. This feature protects all cells on the active sheet, except those that you explicitly mark to remain unlocked. Start by using the Unlock/Lock Cells control on the Protection tab to mark those cells you want to leave unprotected. In our example, we can use these cells for input. Highlight cells B3 and B4 (see Figure A) and click the Unlock Cells control. On the Protection tab, select Protect Active Sheet. This protects all of Sheet1 in Figure A, except for cells B3 and B4.

Basing a calculation on a spreadsheet range
With spreadsheet calculators, it’s common to have expressions that compute one or more values based on a range in a worksheet. Figure C shows an adaptation of this approach for our net sales calculator. The Spreadsheet component on this Web page uses two worksheets. On Sheet1, the calculator has formulas for computing gross sales and discounts based on a range of data that shows up in Sheet2. By default, the Spreadsheet component has three worksheets named Sheet1, Sheet2, and Sheet3. Page authors can rename, hide, delete, and add to these sheets. Users can perform the same actions for the copies of the Web page in their browsers; they cannot push their changes to the server without author permission for that page, though. If a user accidentally corrupts a page in his or her browser by editing its values or otherwise changing it with the Commands And Options dialog box, he or she can recover by reloading the page from the server.

Figure C
This is a spreadsheet for a Web page with a pair of sheets that work together.


The formula for gross sales on Sheet1 is =SUM(Sheet2!B2:Sheet2!B7). This expression accumulates the gross sales values in Sheet2. The expression for discounts parallels the one for gross sales: =SUM(Sheet2!C2:Sheet2!C7). These kinds of expressions are common for spreadsheet calculations, but you are not likely to see them on too many Web pages. One important advantage of the Spreadsheet component is that it lets Excel developers easily transfer their skills to creating Web pages.

The spreadsheet design in Figure C lends itself to two operational scenarios. First, you could run it with both Sheet1 and Sheet2 fully protected. This approach lets a page author control the update of the sales and discounts by products, while at the same time prohibiting users from alternating values for what-if analyses. Second, you could save the Web page with one or more of the cells on Sheet2 unprotected. This would let users perform what-if analyses by changing cell values from the initial level set by a page author the last time he or she saved the page. In either case, page authors can update the Web page on the server whenever necessary.

Some related issues depend on whether a browser workstation also has Excel 2002 installed. Recall from my previous Daily Drill Down that you must have the Office XP Web Components installed on a workstation to open a Web page with the Spreadsheet component on it. However, there is no requirement to have Office XP or Excel 2002. If a user has a version of Excel 2002 installed on his or her workstation, he or she can use the Export To Excel tool on the Spreadsheet component's toolbar. This captures the formulas and values from the component on the Web page and opens an Excel workbook with the same formulas and values. If users don’t have Excel 2002 (but do have the Office XP Web Components), they can open the Web page without having to copy it. In this case, the Export To Excel tool is grayed out on the Spreadsheet component's toolbar. In addition, they cannot use the Windows Clipboard to capture formulas or values from the Spreadsheet component on a Web page.

Basing a calculation on an external data source
One way to base calculations on an external source is to bind one of the worksheets in a spreadsheet to a set of records in the source and then base a calculation on the worksheet range holding the records from the external data source. This approach relieves the page author or user of having to maintain the integrity of input data to the expressions in a Spreadsheet component on a Web page. Whenever a user loads a Web page with the Spreadsheet component, the Web page's spreadsheet automatically refreshes its contents relative to the external data source. In addition, the toolbar for the Spreadsheet component offers a control for refreshing a spreadsheet against an external data source on demand. You can even automatically program the refreshing of a spreadsheet with an external data source.

To show the flexibility of the Spreadsheet component, I will use it with a custom view in the sample SQL Server Northwind database. Although SQL Server is not a part of Office, it’s still a perfectly acceptable source for a Spreadsheet component. As you can see, the From clause specifies the Invoices view as its source. The prefix for the view name (dbo) merely designates the type of owner for the database object and has significance for SQL Server security. The custom view also references "as is" two columns from the sample Northwind view. These are OrderID and ProductName. The SELECT statement includes expressions for computing gross sales and discounts to gross sales.

After identifying an appropriate external data source for your Spreadsheet component on a Web page, you can use the Commands And Options dialog box to bind a worksheet within the spreadsheet to the source. First, move to the worksheet that will hold the data in the Spreadsheet component. Next, open the Commands And Options dialog box and click the Data Source tab. Select Sheet data source (if the Spreadsheet component does not automatically select it). Click Edit to open the Select Data Source dialog box. If you are going to use a previously referenced data source, select its name and click Open. Otherwise, click New Source to launch the Data Connection Wizard. This allows you to specify the external data source you wish to use. After finishing your replies to the Data Connection Wizard, choose Open on the Select Data Source dialog box.

Figure D shows an excerpt of the worksheet with the sample data from the Northwind SQL Server database. As you can see, I attached it to Sheet3. The worksheet contains a separate row for each line item within every order. The first column uniquely identifies the order (column A). Column B shows the product name for a line item. The third column (column C) designates gross sales for an individual line item, and the fourth column (column D) shows any discount for the line item. For example, the order with an OrderID of 10248 contains a line item for Queso Cabrales with a gross sale value of $168 and no discount. Other orders for this product could have discounts.

Figure D
A worksheet bound to an external data source


Figure E shows two more worksheets from the same Spreadsheet component. Sheet1 shows the net sales computations for two products: Queso Cabrales and Tofu. The worksheet computes Gross Sales and Discounts based on the values in Sheet3 (see Figure D). The expressions to compute Gross Sales and Discounts are the same for both products, except for a criterion. The criteria for these expressions appear in Sheet2.

Figure E
A spreadsheet for a Web page with two sheets working together


The expressions in Sheet1 for cells B4 and B5 contain the expressions for Gross Sales and Discounts for Queso Cabrales. Notice that these expressions use the DSUM function to accumulate the values in a column of a database range. The function's first argument designates the range of cells over which to sum. It specifies the maximum number of rows in a worksheet (65,536). The second argument designates the name of a specific column in the database range. The last argument references a criterion range that specifies the product over which to accumulate values. The expressions for Tofu are nearly identical. The Tofu expressions differ in the third argument that specifies the criterion range. For cells B9 and B10 on Sheet1, the third argument is Sheet2!A4:A5.

Refreshing a spreadsheet automatically
The previous example demonstrates how to update a spreadsheet based on the values in an external data source, such as the Northwind database. Clicking the Refresh All control on the Spreadsheet component's toolbar updates Sheet3 with the most recent Northwind data. Despite the ease of constructing the Web page, this sample has a couple of weaknesses for some applications. First, anyone can view all the data in Sheet3. One solution is to just hide Sheet3. Second, the Web page exposes all the controls on the toolbar, but the sample requires just one control: Refresh All. Again, some environments may call for the removal of unnecessary functions.

It’s easy to accomplish these modifications automatically. You first need the Microsoft Script Editor. This is an Integrated Design Environment (IDE) for code on Web pages, just like the VBA window you use to create and edit macros in normal Office applications. To use it, you must first open FrontPage and select the Normal tab for the Web page depicted in Figures D and E. Then, choose Tools | Macro | Microsoft Script Editor to open the Microsoft Script Editor for the Web page.

Make sure the Microsoft Script Editor's Code window is open. It shows the code for the Web page (the top tag will be a beginning HTML tag). You can open the Code window by choosing View | Code. If the Script Editor's Document Outline window is not open, choose View | Other Windows | Document Outline. Next, select the Script Outline control at the top of the Document Outline window. (The control next to it is for an HTML Outline view.) Open the window object within the Client Objects & Events folder and double-click Onload to add a script block to the Code window for the Web page.

An onload event procedure shell will appear within the script block added to the Code window. Within the event procedure, type:
spreadsheet1.DisplayToolbar = False
spreadsheet1.Worksheets("Sheet3").Visible = False


The first line suppresses the display of the toolbar. The second line hides Sheet3. Notice the syntax is straightforward object-oriented programming. In the case of these two lines of code, we are simply assigning property values. As with any new object model, you need to learn the objects, properties, methods, and events.

The second modification requires the addition of a button to the Web page and an event procedure for when it is clicked. A click of the button should invoke the spreadsheet's Refresh method. Adding a button is facilitated by the availability of the Toolbox. If you do not see the Toolbox, open it by choosing View | Toolbox. Next, drag the Button control from the Toolbox to a place in the Code window after the Spreadsheet component and before the closing body tag. This adds a new client object named Button1 to the page. It has default property settings, such as Button for Value and Button1 for ID. The Value property is the caption that appears on the button. The ID property is an automatic program identifier for the button control. Click into the INPUT tag for the new button and then replace the Value setting, Button, with Refresh Data. Likewise, enter cmdRefreshData as the new ID setting. When you change the ID setting, the name of the button changes to cmdRefreshData in the Client Objects & Events folder.

Now that the button is on the page, we can create its event procedure. Click the expand control, indicated by a plus sign (+), for the button in the Client Objects & Events folder of the Document Outline window. Next, double-click Onclick (beneath cmdRefreshData) to add an event procedure shell to the previously created script block. Type spreadsheet1.Refresh within the event procedure shell for cmdRefreshData to complete the coding of the sample Web page. Figure F shows the completed Code window for the Web page. You can use this view of the Code window to verify that you entered code in the proper places. As soon as you start to add your own custom procedures, FrontPage collapses the built-in code for the Spreadsheet component. When you reopen the page, it may expand the code to allow you to control its content. However, you can use the Document Outline window to navigate easily to the custom objects and scripts that you add to a Web page.

Figure F
The Code window for the Web page shown in Figure G


Figure G shows the revised version of the Web page in the top panel of Figure E. If you clicked the tab control showing Sheet1, it would display just Sheet1 and Sheet2. Although Sheet1 contains values based on the entries in Sheet3, users cannot directly view or edit the values within Sheet3.

Figure G
Notice the new Refresh Data button below the spreadsheet. Also, there is no toolbar.


Conclusion
The Spreadsheet component in the Office XP Web component library is a powerful and easy-to-use tool for creating interactive Web pages. Without any programming at all, you can readily build interactive Web pages with custom calculators in many different business contexts. Using the Commands And Options dialog box, you can easily customize spreadsheets on Web pages by formatting cells, protecting worksheet ranges, and binding worksheets to external record sources such as a view in a SQL Server database. In addition, along with the Microsoft Script Editor, it makes it easier to perform simple programming chores that customize spreadsheets on Web pages. Used this way, the Microsoft Script Editor works for code in Web pages similarly to the way the VBE window does for VBA code in Office documents and Access database files. The Microsoft Script Editor can automatically expose objects and their events as well as create script blocks and shells for event procedures.

Editor's Picks

Free Newsletters, In your Inbox