Did you build an intranet for your company that nobody cares about? Maybe it’s too static. Let’s face it, most of the value of a corporate intranet comes from having a central place to do corporate things, and that means interactive, modifiable Web documents. Who has time to do all that programming? I have two words for you, Microsoft Office.
You’ve probably known that you can save Office files as HTML documents for some time. Office 2000, though, comes with a new technology that can bring renewed life to your intranet: a set of components known collectively as Office Web Components (OWCs). In this Daily Drill Down, I’ll give you an overview of OWCs—what they are, what they can do, and how to use them. By the time you’ve finished reading, your creative wheels should be turning, thinking of ways you can put OWCs to work for you.
To view and interact with Office Web Components (OWCs), users must have Internet Explorer 4.01 or later installed on their machines. In the case of OWCs that are based on Microsoft Access, Internet Explorer 5.0 or later is required. No other Web browsers will work. In addition, the end user must have Microsoft Office installed, or if the company has a site license, then users will have to download the OWCs from the company intranet.
What are OWCs?
OWCs are four ActiveX controls that can be added to any Web page. (Actually, since they are standard COM controls, they can also be used in any container application including Visual Basic and VBA forms.) Each component provides a piece of functionality normally associated with either Excel or Access. Here’s the breakdown of each component and what it does:
|Spreadsheet||This is a mini Excel spreadsheet. It includes nearly the entire Excel function library. Users can enter formulas, calculate totals, and use sorting and filtering.|
|PivotTable||This functions exactly like the PivotTable in Excel. Users can filter the underlying data, add fields to rows or columns, move row and column headers, and even show or hide detail records.|
|Chart||This is a dynamic two-dimensional charting engine. If based on a Spreadsheet or PivotTable component, the user can change the underlying data and see the chart change in response.|
|DataSource||This is a nonvisual data connection control. It’s hidden when the page is viewed but provides the data connection for PivotTable and Chart components. The DataSource component is a critical part of MS Access Data Access pages.|
Each of these components can be manually added to a Web page in FrontPage or to an Office document in design mode. Once added, you can set properties by right-clicking the component and selecting Properties. You can even manipulate the component through code, since each component exposes a fairly extensive object model.
However, the handy thing about the Web Components is that you don’t have to manually design Web pages if you don’t want to. Simply fire up Excel; design your spreadsheet, pivot table, or chart the way you want it; publish your worksheet as a Web page; and voila! The Web components you need are placed on the page automatically with all the necessary properties and files included.
From Excel data to Web report in a few easy steps
First, you need to create your Excel data. For this demonstration, I made a simple dataset on an Excel worksheet (Figure A). The dataset shows response times for support calls over a three-month period. The formula in E4 is =SUBTOTAL(1,E7:E166), which allows the user to AutoFilter the data set and still see an accurate average for the rows that are showing.
|First, you must create your Excel data.|
If I were to actually publish this data, I would protect all the cells except the column headings. After all, I really don’t want users to be able to change the raw data on the Web page; I simply want them to be able to filter it interactively. For the purposes of this demonstration, though, I’ll leave the data unprotected so you can see how the spreadsheet component works.
The basic spreadsheet component
Once you have your dataset built, it’s time to publish it as a Web page. Save it first as a regular Excel file so you still have it if something goes wrong. Then, choose File | Save As Web Page. Then, you’ll see the Web Page dialog box (Figure B).
|Set the type of Web page you’re creating in the Web Page dialog box.|
If you choose to save the Entire Workbook, you’ll get a set of static Web pages without any interactive functionality. You’ll be able to switch among pages, but each page will simply be a collection of text and graphics. They will look like Excel worksheets, but they won’t work like Excel worksheets.
Once you choose the Selection radio button, though, your options open up considerably. Since I have nothing selected on my worksheet, my only option is to save the entire worksheet (the Selection: Sheet option). If I had made a selection—a range of cells, a pivot table, or a chart—then that would be noted on the Selection radio button.
When I enabled Selection, the Add Interactivity option became available as well. Since my selection is a worksheet, this means that the Spreadsheet component will be added to the Web page that Excel creates, along with all of my data.
In this dialog box, I can also give my Web page a title by clicking the Change Title button, and I can choose a save destination for my .htm file. Finally, when I click Save, the Web page is created (Figure C).
|The Web page (.htm file) looks and works just like an Excel spreadsheet.|
As you can see, Excel puts my title across the top of the page. Underneath the title, though, it looks like a miniature Excel page. If you click in one of the cells, you can edit it, or even enter a formula. You can scroll up, down, left, and right. The real power, though, comes from the toolbar at the top.
The first menu item on the left, the Office icon that resembles a puzzle, brings up an About Office Web Components dialog box with links to more information on Microsoft’s Web site. Next to it are the familiar Undo, Cut, Copy, and Paste buttons, along with the standard Excel AutoSum button.
The next two buttons are the Sort Descending and Sort Ascending buttons. They work just like they do in Excel, even to the extent of choosing the right data to sort.
Next is the AutoFilter button, which also works just like Excel’s. You can click a cell, turn on AutoFilter, and filter the data in this basic spreadsheet. Neat, huh?
The next button, the one with the Excel icon, exports the Web spreadsheet to Excel. Click it, and a copy of the spreadsheet is loaded into Excel on your local machine.
Finally, there is the button that opens the Spreadsheet Property Toolbox (Figure D) and the one that opens Help information. The Spreadsheet Property Toolbox is divided into sections, each of which can be expanded and collapsed simply by clicking on the section header. This is where you set various formatting and other options for either the entire spreadsheet or for selections.
|The Spreadsheet Web Component includes the Spreadsheet Property Toolbox.|
I’ll bet you can think of a number of online uses for the Spreadsheet component, such as setting up expense forms, time sheets, supply ordering forms, etc. The list is endless. Whatever formatting you do in Excel is preserved when you publish your Web page, so you can use fonts and colors, turn off the gridlines, add a border or two—you get the idea. Remember, you can turn protection on as well, so the user can only enter data in the cells you choose.
For our current application, though, I really don’t like giving the user the raw data to play with. Instead, I want to keep my data protected and simply give them a tool with which to manipulate the data for their own analysis purposes. Enter the PivotTable.
The PivotTable component
PivotTables have been around for a while, but I continue to be amazed at how few people know about them or use them. A crosstab on steroids, a PivotTable allows you to drag and drop data fields into place as either row or column headers. Once you do, the PivotTable carries out an aggregate calculation (Sum, Average, Count, etc.) on one or more data fields dropped into the data area of the table. You can use the drop-down headings on the PivotTable to filter the results. If you then base a chart on the PivotTable, the chart will reflect whatever filtering and rearrangement choices you make.
This sounds like the functionality I want to give my users, so my first step is to build a new PivotTable in my workbook. After walking through the PivotTable Wizard (you’ll find it in Data | PivotTable), I end up with the PivotTable you see in Figure E.
|Build a PivotTable prior to publishing it.|
Now, let’s publish the PivotTable as its own Web page. This time, instead of choosing to save the sheet, click the Publish button in the Save As Web Page dialog box. When you do, you get the Publish dialog box (Figure F).
|You can publish your PivotTable as a Web page.|
This dialog box allows you to be more specific about what you publish. The Choose drop-down menu allows you to choose a range of cells—which you will then specify in the text box under the drop-down menu—or to choose from items on each of the sheets in your workbook. (My three sheets are named BaseData, Chart1, and PivotTable.) If I select, say, Items On BaseData, every named range, chart, and PivotTable on that worksheet is listed, and I can select the one I want.
You can select the Add Interactivity check box, set or change a title for your Web page, and choose where you want to publish the page. If you click the Browse button, you can navigate to anywhere in Network Neighborhood, including your local intranet server. You can also instruct Excel to open the resulting Web page in your browser once it’s published.
Since you’re publishing a PivotTable, you are probably wondering what happens to the underlying data. If you click Publish and then go look in the folder where you saved the page, you’ll see a new subfolder having the same name as your Web page with _files tacked on the end. Inside, you’ll find two XML files: one called FileList.xml and one with a name like Book1_7768_cachedata.xml. This is the data from your spreadsheet, saved as a static XML file. If you examine the code in your Web page, you’ll see the same randomly selected name for your newly created PivotTable component. You’ll also see a new object referenced in the code, something called MSODSC. It’s a Microsoft Office Data Source Component, and it’s what links the PivotTable component to the XML file. The path to the XML file is hard-coded into the Web page as a parameter passed to the Data Source Component, which then serves as the data engine for the PivotTable.
If you publish your PivotTable and open it in your browser, you can see that the toolbar has changed slightly (Figure G). There is a new set of buttons in the middle, next to the AutoSum button. The first one turns subtotals on and off for the row or column header you have selected. The next four buttons move the selected header to another part of the PivotTable: the row header drop area, the column header drop area, the page filter area, or the data area.
|Notice the new toolbar that appears when you publish a PivotTable.|
If you have multiple fields as either rows or columns, the next two buttons let you promote and demote a selected field. The next button shows or hides detailed rows for the aggregate data. Finally, the next-to-last button activates the PivotTable Field List box (Figure H), which you can use to drag and drop new fields into your PivotTable. You’ll also notice that the PivotTable component has recognized that two of your fields are date fields, so it has already done some grouping on those fields. You could, for instance, take the DateFiled By Month field and drop it on the Page filter area at the top and view your data by month.
|Use the PivotTable Field List to drag and drop new fields into your Web PivotTable.|
Note that the original PivotTable showed the average days needed to resolve a call. This PivotTable on my Web page is showing the sum of days needed. You could try opening the Property dialog box, selecting Choose Function, and adding the Average function. But you’ll find that there isn’t any Average option in the drop-down list. You’ve just come up against one of the shortcomings of the PivotTable component. It can only show Sum, Count, Min, and Max aggregate functions in the data area. Here’s hoping that gets fixed in the future.
The Chart component
One of the coolest tools in Excel is the PivotChart. It’s a regular chart based on a PivotTable, which includes the same drop-down filtering capability right on the chart, as is available on the PivotTable itself. If you change the arrangement of the PivotTable, the chart changes to match.
For this demonstration, I made a PivotChart based on our earlier PivotTable and then published it as its own Web page. Figure I shows the chart displayed in Excel. When it’s published on the Web page, the chart is combined with a PivotTable component directly underneath, and the drop-down filtering and rearranging is done in the PivotTable itself, not the chart. The Chart component has no toolbar of its own; if you right-click it, all you get is the About dialog box that I mentioned earlier.
|On an Excel spreadsheet, a PivotChart is combined with a PivotTable.|
And one more thing…
So far, you’ve been publishing data directly from Excel to your Web pages. This gives you great functionality, but the data is static. In other words, if you add records in your Excel dataset, you have to republish the Web pages to share the latest information. Fortunately, the Save As Web Page dialog box will show a Republish button if you have already published from the worksheet, so you don’t have to re-enter all of your titles and file locations. Still, it would be nice if the data would refresh automatically.
If you have Access 2000, however, you can have your update and show it, too. In the main database window, you’ll see a new heading on the left: Data Access Pages. Click there, create a new data access page from scratch, add your PivotTable and Chart components, set the data source information for the components, and save the resulting Web page. Whenever the page is loaded in the browser, it will attempt to connect to the database and will then use the latest data in its PivotTable and/or Chart. Creating Access Data Pages is beyond the scope of this Daily Drill Down, but I’ve included a screen shot so you can see what such a page would look like in design mode (Figure J).
|Publishing a chart in Access allows you to generate automatic updates.|
Some final words, including some gotchas
As you can probably tell, I think the OWCs are very cool tools. Nevertheless, there are some limitations and gotchas that you need to keep in mind.
- No calculated fields—The PivotTable component uses the XML file as its data source, and XML stores only the actual values, so you can’t use any calculated cells in the data source for your Web PivotTable. If you try to, they will simply be dropped out of the final data set.
- Read-only to Excel—If you click the Export To Excel button, the data is blown out to your Temp directory and then loaded into Excel from the resulting .htm file, which is read-only. It’s not a big deal—simply Save As in Excel format to some other location.
- No Netscape—The OWCs are COM components, as I’ve said, and Netscape can’t run COM components. You must have at least Internet Explorer to see the components and IE 5.0 or later to use the components interactively.
- No installation across the Internet—Note that I didn’t say you couldn’t use OWCs across the Internet; it’s just that you couldn't automatically install the components from your Web server, as you can with most COM components. The code-base setting in the automatically generated Web page code always points to a file:// location and will not work with either http:// or ftp://. If a user has Office 2000 and already has the components installed, he can view and work with Web pages that contain the controls. If the components aren’t installed, whenever the Web page loads, a dialog box will ask if the user has a license to use the components. If the answer is yes, IE will download and install the components directly from the Microsoft Web site. However, MS is absolutely adamant that persons without a valid Office license cannot use the components. They are intended, in other words, only for use on an intranet.
Having listed these issues, I still insist that OWCs offer one of the most useful technologies I’ve used in a long time. Having controls that are simple enough to use through a Save As dialog box, yet powerful enough to be driven through code, is really exciting. The components are small, fast, and very flexible. They make interactive Web pages immediately accessible to the end user, and they bring marvelous new functionality to the developer.
For more information on OWCs, read these TechProGuild features:
- “Office Web Components deliver interactive Web pages”
- “Making Web pages with interactive crosstabs”
- “Building database Web solutions with Access 2000”
Here are some other links you might find useful:
- An introductory series of articles on OWC from Microsoft
- An excellent white paper on OWC that includes numerous code examples
- An excerpt from the book Programming Microsoft Office 2000 Web Components by Dave Stearns
- An article on deploying OWC along with your custom application
- An article on managing the automatic installation of OWC across the intranet
- A Knowledge Base article on printing problems with the Chart component