Using the Spreadsheet Component

The Spreadsheet Component brings the look and feel of Excel spreadsheets and some of a spreadsheet’s features to Web pages. See Figure 6-1 for an example of a Spreadsheet Component.

Figure 6-1: The Microsoft Office XP Spreadsheet Component.

Many of the buttons on the Spreadsheet Component’s toolbar, shown in Figure 6-2, perform familiar operations; for example, cutting and pasting data and sorting data in simple ascending or descending order. The following list describes the toolbar buttons that are more particular to the Spreadsheet Component:

Figure 6-2: The Microsoft Office XP Spreadsheet Component’s toolbar.

• AutoSum automatically inserts a sum for a group of cells.

• AutoFilter places a drop-down list in the top cell of each column. Clicking the arrow displays a list of values that you can use to filter the records in the spreadsheet.

• Refresh All (not available in Office 2000) retrieves the latest data from the data source.

• Commands And Options (Property Toolbox in Office 2000) displays the Commands And Options dialog box (named the Property Toolbox in Office 2000).

The tabs displayed on the Spreadsheet Component’s Commands And Options dialog box at design time, shown in Figure 6-3, provide a number of options for managing the appearance and the use of the data in the component. The tabs include the following:

Figure 6-3: The Microsoft Office XP Spreadsheet Component’s Commands And Options dialog box at design time.

• The Format tab provides options for changing the appearance of text and cells.

• The Formula tab lets you view cell formulas and define named cell groups.

• The Sheet tab provides search capabilities and settings for other display options; for example hiding grid lines, freezing panes, defining viewable cells, and so on.

• The Workbook tab sets the calculation mode. Automatic calculation is more convenient than manual calculation, but this option can potentially slow down your computer’s performance if a worksheet contains a lot of formulas. This tab also lets you display or hide scroll bars, row selectors, and the status bar, as well as rename, delete, hide, or reorder worksheets.

• The Import tab specifies the type of data to import (XML, HTML, or comma-separated value (CSV) data) from a Web address.

• The Protection tab supplies options that help you manage what users of the component can do when viewing data at run time. You can control whether a user can delete, insert, or resize columns or rows or filter and sort at run time. You can also enable the ability to insert, remove, or rename worksheets, as well as hide or display the Commands And Options dialog box at run time. These options give you greater control over what users can do with your data over the Web.

• The Advanced tab lets you set additional cell group and worksheet behaviors, such as having the spreadsheet expand or shrink on the Web page depending on how much data it holds.

• The Data Source tab defines data source connectivity information.

At run time, the tabs on the Commands And Options dialog box include the Format, Formula, Sheet, and Workbook tabs, as shown in Figure 6-4.

Figure 6-4: The Microsoft Office XP Spreadsheet Component’s Commands And Options window at run time.

Note

The Office 2000 Spreadsheet Component’s Property Toolbox tabs look different from the Office XP Spreadhseet Component’s Commands And Options dialog box, but many of the features are the same.

You can type data into a Spreadsheet Component, you can publish cell groups, worksheets, and workbooks to a Spreadsheet Component, or you can import data from another data source into a Spreadsheet Component.

To publish cell groups, worksheets, or workbooks to a Spreadsheet Component, on the File menu in Excel, click Save As Web Page. You can then select an option to save the entire workbook to the Web page or save a selection. The Add Interactivity option makes the data “live” in the Spreadsheet Component when it’s displayed on a Web page. Without this option selected, you can’t make any changes to the data at run time. The data is displayed in a static format. The Publish button provides additional control over the state of the data. This button opens a dialog box in which you can specify what portions of the workbook to publish and whether to automatically update the Web page hosting the Spreadsheet Component each time the original workbook is saved. Choosing this option means that users viewing the data on a Web site can have up-to-date information. In the Publish As Web Page dialog box, select the AutoRepublish Every Time This Workbook Is Saved option and then click Publish. When you save changes to the workbook containing the data you’ve published to the Web, Excel displays a dialog box with the option Enable The AutoRepublish Feature. Select this option and then click OK to update the data in the Spreadsheet Component as well. Keep in mind that changes that you make to the data in the Spreadsheet Component are not reflected in the original Excel workbook even if you choose the Add Interactivity option or the option to automatically republish the workbook.

Tip

The Change Title button in the Save As dialog box lets you change the text that will be displayed in the title bar of the browser or, if the Add Interactivity option is selected, to provide a title for the published data. This title will appear on the Web page at the top of the selection.

Your Turn

In this exercise, you will publish data on an existing Excel worksheet to a Spreadsheet Component on a Web page.

1. In Excel, open the InsClaim.xls file in the Chap06 folder.

2. On the File menu, click Save As Web Page.

3. Select the Add Interactivity check box to create an Office Spreadsheet Component on the Web page. (If you do not select the Add Interactivity check box, the data will be published as a list of data records that cannot be modified.)

4. Click Save.

5. Open the file (named Page.htm by default) in Internet Explorer. Compare your results to Figure 6-5.

   
   Figure 6-5: Publishing the InsClaim.xls file as an Office XP Spreadsheet Component on a Web page.

It’s important to remember when working with the Spreadsheet Component that while you can import external data into a Spreadsheet Component, any changes you make to data in a Spreadsheet Component will not be reflected in the original data source. Any changes you make to the appearance of a Spreadsheet Component at run time last only while the page with the component is open in your Web browser. If you need to save changes you’ve made to data on a Spreadsheet Component, you can export the data to a Microsoft Excel worksheet and save it there.

To import data from an external data source into an existing Spreadsheet Component on a Web page, follow these steps:

1. With a Spreadsheet Component open in FrontPage (or another Web page designer), click the Commands And Options button.

2. Click the Data Source tab.

3. Complete the information in the Commands And Options dialog box to import the external data.

Creating a connection to an external data source is covered in more detail later in the chapter.

Note

In the Office 2000 Spreadsheet Component in design time, click the Property Toolbox button, click the Import tab, and then complete the information in the Property Toolbox to import the external data.

Analyzing Data in the Spreadsheet Component

With the Spreadsheet Component, you insert worksheet formulas into cells, automatically sum data, sort data, and filter data. To insert a worksheet formula, you can type the formula in the applicable cell and then press Enter. To view a formula that’s included in a cell, click the Commands And Options button and then click the Formula tab.

Your Turn

In this exercise, you will determine the average insurance claim amount.

1. Open the file Sales.htm (or Sales2000.htm if you are using Office 2000) in the Chap06 folder.

2. In the Sales.htm file’s Spreadsheet Component, click cell G274.

3. Type =AVERAGE(G2:G273), and then press Enter. Compare your results with Figure 6-6. The average insurance claim amount is $10,216.84.

   
   Figure 6-6: Average insurance claim amount.

To automatically sum data in the Spreadsheet Component, click the cell in which you want the summary formula, click the AutoSum button on the Spreadsheet Component’s toolbar, and then press Enter.

our Turn

In this exercise, you will determine the total amount for all insurance claims.

1. In the Sales.htm file’s Spreadsheet Component, click cell G274.

2. Clear the AVERAGE worksheet function in cell G274 from the previous exercise.

3. On the Spreadsheet Component’s toolbar, click the AutoSum button and then press Enter. Compare your results with Figure 6-7. The total amount for all of the insurance claims is $2,778,981.81.

   
   Figure 6-7: Total amount for all insurance claims.

To sort data in the Spreadsheet Component, click the arrow next to the Sort Ascending or Sort Descending button and then select the field by which you want to sort the data. Repeat these steps to further sort the records using other fields.

Note

In the Office 2000 Spreadsheet Component, you must click inside the field and then click the Sort Ascending or Sort Descending button. The arrows are not available.

To filter data in the Spreadsheet Component, on the Spreadsheet Component’s toolbar, click the AutoFilter button. Click the arrow next to each field name by which you want to filter. Select or clear the check boxes to display or hide matching records by the specified field items. Then click OK.

Tip

Select or clear the Show All and Blanks check boxes to display or hide all items or blank field values, respectively.

Putting It Together

In this exercise, you want to find the highest October claim. To do so, you will filter the records to show just the October claims and then sort the claims in descending order.

1. In the Sales.htm file’s Spreadsheet Component’s toolbar, click the AutoFilter button.

2. Click the arrow in cell A1.

3. Clear the Show All check box, select the 10 check box, and then click OK.

4. Click cell G1, and then on the Spreadsheet Component’s toolbar, click Sort Descending. The highest claim for October was $19,297.08, as you can see in Figure 6-8.

   
   Figure 6-8: Data can be sorted and filtered in a Spreadsheet Component at run time.

Office XP Spreadsheet Component Features

The Office XP Spreadsheet Component has some features that the Office 2000 Spreadsheet Component lacks, such as support for XML Spreadsheet formatted data, displaying multiple worksheets in a single Spreadsheet Component, and data-bound sheets. This section briefly describes these features.

XML Spreadsheet Data Support

Excel can save a workbook’s data and layout characteristics in the form of an XML file. This allows a workbook’s data and layout to be saved as plain text using the XML Spreadsheet schema, enabling greater use of the data in other software applications. (For more information about the XML Spreadsheet schema and using XML data in Excel, see Chapter 10, “Working with XML Data in Excel and Access.”) Additionally, other software applications that save XML data in the XML Spreadsheet schema format can provide instructions for presenting the data using different colors, cell borders, and so on.

To import data formatted in the XML Spreadsheet schema into a Spreadsheet Component, first open the Web page containing the Spreadsheet Component in FrontPage. Open the Commands And Options dialog box, and then click the Import tab. Select XML in the Data Type list, type the location of the XML file, and click Import Now.

Multiple Worksheets

Just like its Excel counterpart, the Office XP Spreadsheet Component supports multiple worksheets. To switch between worksheets, click the worksheet tab near the bottom of the component and then click the worksheet you want to display.

To rename a sheet, display the Commands And Options dialog box, click the Workbook tab, click a sheet in the Sheet Name list, and then rename the sheet in the box above the list of worksheets.

Note

You can rename worksheets at run time only if the Insert, Remove, Or Rename Sheets option on the Protection tab of the Commands And Options dialog box was selected at design time.

Data-Bound Sheets

The Spreadsheet Component can connect to data stored in databases such as those maintained in Microsoft SQL Server 2000. A data-bound sheet gets its data from a database object such as a table or view. The data displayed in the component can easily be refreshed by the user.

To bind a Spreadsheet Component in this manner, you use the Data Source tab on the Commands And Options dialog box at design time. On the Data Source tab, click the Sheet Data Source option. In the Connection box, type the connection string for the data source or click Edit and follow the directions in the dialog box to connect to the data source you want to use. If necessary, type the command text or SQL string you want to use to retrieve specific data from the data source in the Command Text Or SQL box.

Here’s an example of a connection string, using a computer running SQL Server 2000 named Server_Name and a database named Database_Name:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=Server_Name;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=409 ;Workstation ID=Worksta tion_Name;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Database_Name

Most of the information in this string is provided by default as you select connections in the various data source dialog boxes. An example of a command text or SQL string that selects the Product ID and Product Name fields from a table named Alphabetical List of Products would be:

SELECT ’Alphabetical List of Products’.ProductID, ’Alphabetical List of Products’.ProductName FROM ’Alphabetical List of Products’