MicrosoftÒ Office 2000 Web Components


White Paper

 

 

 

Published: October 1998

Table of Contents

Introduction.................................................................................................................... 1

The Microsoft Office Web Components........................................................................ 1

System Requirements................................................................................................ 3

Licensing Requirements.............................................................................................. 3

Deploying Web Components....................................................................................... 3

Creating an Interactive Web Page with Microsoft Excel............................................... 5

Creating an Interactive Web Page with Microsoft Access............................................. 5

Modifying an Interactive Web Page with Microsoft Word or Microsoft FrontPage......... 6

Component Details.......................................................................................................... 6

Office Spreadsheet Web Component.......................................................................... 6

Office PivotTable Web Component............................................................................. 10

Office Chart Web Component.................................................................................... 13

Office Data Source Web Component......................................................................... 14

Exporting a Component-based Web Page to Microsoft Excel.................................... 15

Building Solutions Based on Office Web Components................................................... 15

Server-Side Solutions with Office Web Components................................................. 16

Office Web Components and FrontPage 2000........................................................... 17

Frequently Asked Questions.......................................................................................... 17


 



Microsoft Office 2000 Web Components

White Paper

Published: October 1998

For the latest information, please see http://www.microsoft.com/office/

Introduction

Business people are increasingly turning to intranets and the Internet to share information with one another and with customers. In the early days of the Internet, only highly technical Web masters understood how to create and publish Web pages. Other users were limited to reading what Web masters published. This is changing, as products such as the MicrosoftÒ FrontPageÒ Web site creation and management tool make it possible for increasing numbers of people to create and share documents on the Web.

Microsoft Office 2000 takes this trend a step forward. Because Microsoft Word, Microsoft Excel, Microsoft Access, and the PowerPointÒ presentation graphics program support HTML as a native file format, all Office 2000 documents are Web-ready by default. And because Web server support is fully integrated into the Office 2000 File Save and File Open dialog boxes, publishing an Office 2000 document to a Web server is as easy as saving a file on your own computers hard disk.

 

But publishing a spreadsheet or database document to the Web is only half the story. The other half is enabling other people to interact with the published document and gain information specific to the viewernot just the publisher. For example, if you create a spreadsheet to analyze a products profitability given various input costs, a users ability to enter new values and recalculate the results is a key factor in the documents value. Likewise, if you create Excel PivotTableÒ dynamic views or Access forms, reports, or queries, other users must be able to sort, filter, pivot, or enter new values themselves.

 

How does this interaction translate to the Web? Web browsers cant sort, filter, or recalculate totals on Web pages. So how do Microsoft Access and Excel users share their documents on the corporate intranet and still preserve the interactivity that adds so much value to the information?

 

By using Microsoft Office Web Components.

 

The Microsoft Office Web Components

The Office Web Components are a collection of COM controls for publishing spreadsheets, charts, and databases to the Web. They take full advantage of the rich interactivity provided by MicrosoftÒ Internet Explorer. When you use Internet Explorer to browse a Web page that contains an Office Web Component, you interact with the page right in your browseryou can sort, filter, enter values for formula calculations, expand and collapse details, pivot, and so on. The COM controls provide the interactivity. The Web Components are fully programmable, enabling Office Solution Providers to build rich, interactive, Web-based solutions. The Microsoft Office Web Components include a spreadsheet, a PivotTable dynamic view, a data source, and a chart.

 

Spreadsheet

The spreadsheet component provides a recalculation engine, a full-function library, and a simple spreadsheet UI in Web pages. Calculations can refer to spreadsheet cells or to any control on the page or URL via the Microsoft Internet Explorer document object model. Office 2000 users create Web pages with spreadsheet components by saving Excel workbooks as Web pages and by selecting the option to publish the page interactively.

 

PivotTable

The PivotTable dynamic views component enables users to analyze information by sorting, grouping, filtering, outlining, and pivoting. The data can come from a spreadsheet range, from a relational database (such as Microsoft Access or Microsoft SQL ServerÔ database), or from any data source that supports multidimensional OLEDB (such as Microsoft Decision Support Server). When an Excel user saves a PivotTable or QueryTable dynamic view as an interactive Web page, the page contains a PivotTable component. Web pages with PivotTable components can also be designed directly in the Access Data Access Pages designer.

 

Data Source

The data source component is the reporting engine behind Data Access Pages and the PivotTable component. It manages communication with back-end database servers and determines which database records can be displayed on the page. For example, if a Data Access Page displays customers and orders, the data source component retrieves the order records for the customer being displayed and manages the sorting, filtering, and updating of those records in response to user actions. It relies on Microsoft Active Data Objects (ADO) for plumbing, and like all the Office Web Components, it is fully programmable.

 

Chart

The chart component graphically displays information from the spreadsheet, from the PivotTable dynamic views, or from the data source component. Because it is bound directly to other controls on the page, it updates instantly in response to user interactions with the other components. For example, you can chart a PivotTable view that displays sales by region. Then, in the browser, you can pivot to display sales by product, and the chart will update automatically without round-tripping to the Web server. When an Excel user saves a workbook containing a chart as an interactive Web page, the page contains a chart component. Office Web Component charts can also be created and edited directly in the Access Data Access Pages designer.

System Requirements

The Office Web Components require Microsoft Internet Explorer Version 4.01 or greater running on any version of the Microsoft WindowsÒ 95, Windows 98, or Windows NTÒ operating system. Hardware requirements are 16 megabytes of RAM and any Intel 486 or Pentium Processor, or any DEC Alpha Processor. To design a component-based page with Access, or browse a page created with Access, you must have Microsoft Internet Explorer 5.x or greater.

Licensing Requirements

Customers must own an Office 2000 license in order to browse a Web page interactively using the Office Web Components. Organizations that own an Enterprise Agreement for Office 2000 and that plan to deploy Office 2000 in phases can enable early adopters of Office 2000 to share component-based Web pages with users who havent yet installed Office 2000. They do this by enabling auto- downloading of the components via the Microsoft Internet Explorer built-in component installer.

Deploying Web Components

Office 2000 users create interactive Web pages with the Web Components by saving Excel workbooks as Web pages or by using the Data Access Pages designer in Access. These pages contain HTML <Object> tags that refer to the ClassIDs of the Web Components. When users browse a page with Microsoft Internet Explorerif the components are installed on their computersthe page will come alive and they will be able to interact with it. If the components are not installed, they will see a hyperlink pointing to the Web Components installation program.

The Office 2000 Setup program automatically installs the Web Components. They can also be installed when a user browses a component-based Web page using the Microsoft Internet Explorer component download mechanism. This is for corporations that have Enterprise or Select agreements and are rolling out Office 2000 in phases. Early adopters of Office 2000 can publish interactive, component-based pages using Microsoft Excel or Access. Other users who are licensed for Office 2000 through an Enterprise agreement but have not yet installed it can download the components automatically the first time they browse an interactive Web page. To make this possible, administrators check the Enable Auto-download option of the Office Web Components page of the custom setup wizard and make sure that the Web Components automatic installation files (msowcin.cab and msowc.cab) are available on an Office installation server. When a user who has not yet installed Office 2000 browses a Web page containing references to the Office Web Components, Internet Explorer will detect that the components are not installed and will download them automatically from the corporate Office installation server. The codebase attribute of the <OBJECT> tag that triggers this action looks like this:

codebase=http://MyOfficeServer/Office/msowc.cab

 

Internet Explorer fetches the CAB file from the codebase URL, checks the digital signature and, at the users agreement, unpacks it and installs it.

If the components are not available, either because they are not installed or because the user is using a browser that does not support COM components, a hyperlink points to the Web Components installation program. Administrators can also configure a message to be displayed explaining what to do to enable browsing the page with the components. Here is the Custom Installation Wizard page where administrators control these parameters:

foo.bmp (131670 bytes)

 

If you are browsing your corporate intranet with Internet Explorer and you have not yet installed Office 2000, here is what you will see if you browse an interactive, component-based Web page created by an Office 2000 user: 

 

download.bmp (233198 bytes)

 

Answering Yes installs the Office Web Components and the Microsoft Data Access Components on your machine. Answering No" leaves the components uninstalled. The page will still load, but in the regions of the page where you would see the components, you will see a hyperlink to the components installation program.  

Creating an Interactive Web Page with Microsoft Excel

Users create interactive Web pages from Excel by selecting the Save As Web Page command from the Excel File menu. Expanding the Interactive Options buttons reveals the following options:

 

 

Users can publish static HTML or can publish items on their spreadsheetscell ranges, charts, or PivotTable or Query Table viewsinteractively. When published interactively, the .htm page that is generated contains OBJECT tags that refer to the Office Web Components. When a user browses the page with Microsoft Internet Explorer 4.0 or 5.0, the controls are instantiated in place, providing interactive regions inside the page. If the Web Components are not installed, the user sees a hyperlink to the installation program. Users browsing with Netscape Navigator or another browser that does not support COM components will not see the controls.

 

Creating an Interactive Web Page with Microsoft Access

Interactive Web pages can also be designed directly in the Data Access Pages designer in Microsoft Access. Users can add Office Web Components to a Web page by dragging and dropping from the Toolbox. In addition, the Data Access Pages designer contains the Office 2000 Field List, which allows users to build up complex data pages from databases without first building complicated SQL statements.

Modifying an Interactive Web Page with Microsoft Word or Microsoft FrontPage

In addition to the Data Access Pages designer, pages with the Web Components can be edited by any HTML editor that supports COM controls, such as Microsoft FrontPage 98, FrontPage 2000, the Office Visual Script editor, or Microsoft Word 2000.

Component Details

Office Spreadsheet Web Component

There is a list of 4800 Web calculators on http://www-sci.lib.uci.edu/HSG/RefCalculators.html. These range from a Lye Calculator (for making soap) to a Capital Gains Calculator (for making money). Many such calculators are typically created in Microsoft Excel. The Office spreadsheet component makes it possible to use those Excel calculations on a Web page by presenting a grid that the user can type into.

 

More advanced users can use the Office spreadsheet component with the Data Access Pages to create "Recalc behind HTML" documents without any programming or HTML editing. (Recalc behind HTML documents look like any other Web page but do calculations using the spreadsheet components calculation engine.)

 

Developers can use the Office spreadsheet component in Web-based applications such as Expense-report systems or with Microsoft Internet Information Services running Active Server Pages.

Web Calculations Using the Office Spreadsheet Web Component Grid

Any user will be able to go into the Excel Save to the Web dialog box and create a Web page that uses Office spreadsheet components to deliver the same model on the Web. This is an important scenario because it moves one of the key pieces of Excel functionalitythe ability to perform calculationsto the Web, where people can find it.

 

Lets say youre an Excel user building a model that analyzes direct-mail response rates. You want to make your work available to other users throughout the company, so you open up your model, which might look something like this:

 

 

You might have even gone to the trouble of protecting all cells except those the user is supposed to type in. To create the Web page, use the Save to the Web command and choose the Interactive option. The resulting Web page will look like this:



Spreadsheet Recalc Behind HTML

In this case, the Office spreadsheet component is used to provide recalc for an HTML page. Lets say the creator of the Response Rate Calculator wants to give the page a totally different look. He/she wants background images, drop-downs to fill in values, and lots of pictures. Maybe something like this:

 


The spreadsheet component supports the OLE Simple Provider databinding interface (OLEDB). This makes it possible to tie other controls on the page to Office spreadsheet component cells using Microsoft Internet Explorer data binding. Heres what the user will do:

 

1.    Publish the page as usual.

2.    Open the page in the Data Access Pages editor.

3.    Right-click on the spreadsheet and select Properties to view the Property Page.

4.    Set the Visible property of the spreadsheet control to False.

5.    Set the BoundCells property of the spreadsheet to the list of cells the user would like to bind to.

6.    Place the controls on the Data Access Page and set their DataSource properties to Spreadsheet 1 and their DataField properties to the correct cell name (D5 etc). This will automatically connect the controls up to the values in the spreadsheet.

 

Use Office Spreadsheet Web Component in Custom Solutions

Corporate application developers will use the Office spreadsheet component to enable a variety of solutions. A classic example is the Expense Report application. A typical entry screen includes several different calculations (for example, mileage) as well as calculations that happen in two directions. The Microsoft expense report is a typical example that the Office spreadsheet component can handle. Here's how it might look in a Web page.



 

Note that expense reports need a simple recalc engine, data entry, layout, and formatting. But they also require business logic and scripting to post the information from the Expense Report entry screen.

A developer would build the expense report in Excel and then save it as an interactive Web page. He/she would then open the page in the Data Access Pages designer, Visual Script Editor, or Microsoft Visual InterDevÔ Web development system to add scripting and business logic.

 

Office PivotTable Web Component

Office PivotTable Web Components offer Office users the best way to analyze data in Microsoft Internet Explorer. The PivotTable component combines the list features of Excel (sort, autofilter, and outline) with the autosummarizing features of PivotTable views into a single COM component that runs in Internet Explorer. Simple mouse or keyboard gestures allow users to seamlessly transition between an ordinary list view of their data and a pivot view. Heres a PivotTable dynamic view showing sales by month. (Some sales have been expanded to show the detail records behind the sales figure.)



PivotTable Author and User

In most cases, the author of a PivotTable dynamic view is not the user. The author is responsible for the initial appearance of the PivotTable view, and the user can use the dynamic runtime features to investigate the information.

 

How Is a PivotTable Web Component Created?

A PivotTable component can be created two ways:

         By creating a list or PivotTable dynamic view in Excel and saving it as an interactive Web page. The PivotTable Web Component will be used to display the information.

         By building from scratch in the Data Access Pages designer. The easiest way is to drag a table from the Access Field List onto a Data Access Page. A PivotTable component bound to the table will be automatically created for you.

 

What Can I Do With a PivotTable Component in the Browser?

 

         Pivot

         Browse report data

         Dynamically filter and sort

         Group by row or by column

         Create totals

         Work efficiently with large or small amounts of data

         Drill into the details behind totals

         Chart the results and see the chart update automatically in response to my actions in the browser

 

PivotTable Web Component Examples: A Classic List

The following simple PivotTable dynamic view shows a list of sales records and drop zones for pivoting. This can be created in two ways:

         By publishing a list from an Excel spreadsheet

         By dragging a row source from the Access Field List

 


PivotTable Web Component Examples: A Classic Pivot

Heres a simple PivotTable dynamic view showing a pivot of orders. This can be created in three ways:

         By publishing a PivotTable dynamic view from an Excel spreadsheet

         By dragging a row source from the Office Field List and using pivoting commands

         By dragging a cube from the Office Field List. A cube is a multi-dimensional datasource, such as the Microsoft Decision Support Server, which supports the Microsoft Multi-Dimensional OLEDB API (aka Tensor).


 

PivotTable Web Component Example: The Pivot and List Integrated

Double-clicking a cell displays a grid of the detail records behind the cell total(s). The data for this detail comes from a relational database instead of a decision support server.

 


 

Office Chart Web Component

The Office Chart Web Component enables viewing and interacting with pre-authored, data-bound charts in Microsoft Internet Explorer 4.0 and 5.0. Charts can be bound to the spreadsheet component, to the PivotTable component, to the Data Source component, to ADO recordsets, or to any COM control or HTML intrinsic that supports the OLESimpleProvider databinding interface. The data driving the chart can therefore be stored in the HTML of the page itself or can be supplied remotely from a database server such as Microsoft SQL Server or Oracle, or from a Decision Support Server. As the user updates numbers in a spreadsheet component, or as data is updated in the back-end server, the chart automatically updates, scales, and sizes appropriately.

 

Like all Web Components, Office chart is fully programmable and supports a rich object model enabling developers to build highly customized Office-based solutions that run in the browser and take full advantage of Internet Explorer.

 

 

Creating a Web Page with an Office Chart Web Component

With Office 2000, you can create Web pages with the chart component in one of two ways:

 

         By selecting a chart in Excel and then saving the selection as an interactive Web page

         By designing a Data Access Page and dragging a chart from the toolbox. This launches a chart Wizard that helps you hook up your chart to any data sources on the page.

Office Data Source Web Component

The Office Data Source Web Component is the reporting engine behind Data Access Pages, PivotTable dynamic views, and data-bound charts. It manages database connections and serves up records for the other components to display. It relies on Microsoft Active Data Objects (ADO) for connecting with relational databases such as Microsoft Access, Microsoft SQL Server, or Oracle, and on the Microsoft Decision Support Server (DCUBE) for connecting to multi-dimensional data.

 

The Data Source component provides programmatic interfaces to perform the following operations:

 

         Create a new Office Data Source object and associate it with a database connection

         Add rowsources (tables, views, stored procedures, or SQL statements) to an Office Data Source

         Build flat SQL and hierarchical SHAPE commands to retrieve the data represented by the rowsources from the back-end database and the ADO Shape provider

         Return standard ADO Recordsets, sometimes linked together in hierarchies, to data-consuming objects on the page, including the Internet Explorer 4.0 built-in data binding agent, the PivotTable component, or the chart

         Modify the SHAPE definition in response to user requests to group and aggregate base data

         Use the services of the Microsoft decision support engine to support a pivot UI in the PivotTable component

         Persist an Office Data Source definition to a file and load from a file

Exporting a Component-based Web Page to Microsoft Excel

The spreadsheet, chart, and PivotTable components support exporting to Excel. If you browse a component-based Web page in Microsoft Internet Explorer and manipulate the data (perhaps by entering new values in a spreadsheet or filtering a PivotTable dynamic view), you can extend your analysis using the full power of Excel. Simply click the Export to Excel command button on the component toolbar. This command exports the data from the current component--as it appears in your browser--into Excel, allowing you to save and modify your own copy of the spreadsheet, chart, or PivotTable dynamic view. This is particularly useful for extending a model for later republishing or for accessing the superior printing capability of Excel.

Building Solutions Based on Office Web Components

All Office Web Components are fully programmable via COM automation. Any container that supports COM can reference the type library of the components by setting a reference from their project to the Office Web Components. For example, here is how a Visual BasicÒ development system programmer would refer to the Office Web Components COM library:

 

 

 

The Object Models for the Office Web Components are fully documented in Office 2000 Help and are available in any object browser that supports COM type library inspection, such as the Visual Basic object browser. The object model help file is msowcvba.chm.

 

Server-Side Solutions with Office Web Components

Office Web Components can be used in server-side ASP solutions with Microsoft Internet Information Services, allowing developers to hide the formulas on which their calculations were based or to build a solution that runs on any browser. In server-based solutions (because the controls are not active at the client), the user interface that the controls provide cannot collect input from the user: The command bars, field lists, and input grids are not present. It is the developers responsibility to collect input from the user with standard HTML input controls and to thenusing these inputs as parametersmanipulate the components on the server via their object models and return results to the user via the response.write method of the active server.

The ASP scripting (pseudocode) for a server-side spreadsheet-based solution looks like this:

Set ss = CreateObject(owc.spreadsheet) 'Instantiate the spreadsheet component

ss.DataAsHTML = "file://c:\mysheet.htm" 'Load up the file that contains the model

ss.Range("B6").value = response("InputValue") 'The value that came from the user

document.write "The answer is " & ss.Range("A5")

All Web Components support server-side results sets. The spreadsheet will typically be used to perform a calculation, storing the result in a range. The value of that range is then shipped down to the client as HTML text. Both the chart and the PivotTable components support an Export Picture method. In server-side solutions, developers can manipulate their object models based on user inputs, perhaps using an input box to select a value on which to filter results, for example, and then send results down to the client browser as a GIF. Even though the components do not need to be installed on the client in this scenario, an Office 2000 license is still required at the client in order to view pages that are generated using the Web Components on the server.

 

Office Web Components and FrontPage 2000

Together, Microsoft Office 2000 and FrontPage 2000 provide organizations with a comprehensive solution for creating and managing workgroup webs or intranets. Office Web Components can be easily incorporated into FrontPage 2000 based intranet or Internet sites. Users can then edit Office Web Components directly inside FrontPage 2000 thanks to tighter integration between the two products.

Frequently Asked Questions

Q: Why would I use the Web Components when I can save my regular Office documents to the Web in Office 2000?
A: Use the Web Components when you want to create Web pages from spreadsheets and databases that contain interactive regions to allow people viewing the pages to manipulate and explore the information, tailoring it to their own needs. Save Web pages using static HTML when you want people to see published information but not be able to interact with it.

 

Q: What happens when I view an interactive Web page with Netscape Navigator?
A: You will see a static HTML representation of your page as the author last saved it. You can still see the information, but you cannot interact with it.

 

Q: Are the Office Web Components written in Java?
A: No. They were created using Microsoft Visual C++
Ò development system.

 

Q: How do I further analyze an interactive Web page?
A: The Spreadsheet, Chart, and PivotTable components support exporting from a Web page to Microsoft Excel.

 

Q: If I dont want to use the Web Components, can I turn them off?
A: Yes. You can configure your network installation server to not install the Web Components. This also disables interactive Web publishing from Excel and the Data Access Pages designer from Access.

 

Q: Can I build solutions with the Office Web Components using Visual Basic or Visual C++ development systems?
A: Yes. Because the Office Web Components are COM controls, they can be used in any development environment that supports COM, including all the programming tools in Microsoft Visual Studio
Ò development system. They can be embedded in Visual Basic Forms or in native Windows programs created with Visual C++ development system. Developers can freely redistribute the Office Web Components as part of custom solutions, provided that the client computer on which the solution is to be run is licensed for Office 2000 as described in the licensing section above.

 

Q: How do the Office Web Components stack up against Lotus eSuite?
A: The Microsoft Office Web Components are tightly integrated with the Microsoft Office applications to enhance Web publishing and browsing. They augment the capabilities of Excel and Access, enabling Office 2000 users to create new, interactive Web-based scenarios while preserving and enhancing their investment in Office training and expertise, and their existing Office documents. Lotus eSuite, on the other hand, is a Java-based alternative to Lotus SmartSuite that emulates the functionality of the standard Lotus suite of applications, but has fewer features and poorer performance. eSuite has more demanding hardware requirements than the Office Web Components (32 meg of RAM vs. 16 meg for the Office components), and is not available, even in beta form, on any operating system platforms that are not supported by the Office Web Components.

 

For more information: http://www.microsoft.com/office/

uuuu

 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This is a preliminary document and may be changed substantially prior to final commercial release. This document is provided for informational purposes only and Microsoft makes no warranties, either express or implied, in this document. Information in this document is subject to change without notice. The entire risk of the use or the results of the use of this document remains with the user. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Unpublished work. Ó 1998 Microsoft Corporation. All rights reserved.

Microsoft, FrontPage, the Office logo, PivotTable, PowerPoint, Visual Basic, Visual C++, Visual Interdev, Visual Studio, Windows are either registered trademarks or trademarks of Microsoft Corporation in the U.S.A. and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.