Dave Stearns
Microsoft Corporation
July 1999
Excerpted from Programming Microsoft Office 2000 Web Components.
Copyright 1999 by Microsoft Corp. This is a preliminary version, reprinted with permission of Microsoft Press.
Microsoft Press® books are available worldwide wherever quality books are sold. To order direct, call 1-800-MSPRESS (United States) or 1-800-667-1115 (Canada). For more information, visit our site on the World Wide Web at http://mspress.microsoft.com/.
Summary: Chapter 1 discusses the Office Web Components library as a whole, explaining why it was created and for what uses it was intended. It provides a brief introduction to each of the components in the library and lists the supported containers in which you can use them. (11 printed pages)
Introduction
Why Were the Office Web Components Created?
What Are the Office Web Components?
Where Can You Use the Components?
Microsoft® Office 2000 includes a new set of controls called the Office Web Components (OWC). Using these components, you can build many useful data analysis and reporting solutions, both in the Web browser and in traditional programming environments. In this chapter, I will explain why the Office Web Components were created, what they are, and where you can use them. I will also give you a few initial ideas for their use in business solutions. Part II of the book will provide some common, practical uses of the components and will take you on a step-by-step tour of the techniques used in those solutions.
If you don't know anything about these controls, read on, as this chapter will serve as the foundation for the rest of the book. If you are already familiar with the components and just want to see how to use them in business solutions, skip ahead to Chapter 6.
The technologies developed for the World Wide Web are taking both large and small businesses by storm. Companies now see the Internet and the Web as key mechanisms for reaching their customers and for distributing information among customers, suppliers, and vendors. Companies are also realizing that the same technologies used internally on their LANs or WANs (known as intranets) provide scalable, flexible, easy-to-use mechanisms for sharing information and for developing and deploying tools to run their businesses.
Not so long ago, setting up and managing content on an intranet was a black art relegated to "Webmasters." With the advent of site management and content creation tools such as Microsoft FrontPage®, ordinary mortals could create Web-ready documents and manage them much like they managed files on their local computers. Tools such as these made it possible for semitechnical individuals to set up intranets and share information with their coworkers.
Documents full of text lend themselves well to HTML and Web browser technologies, but not all documents are useful when viewed as static text only. Authors who create spreadsheets, databases, and database reports encounter special problems and opportunities when sharing these on their corporate intranets. Much of the value of sharing a spreadsheet or database report lies in letting other users interact with it and tailor the model to their own needs. For example, if you create a spreadsheet to analyze a product's profitability given various input costs, an important aspect of sharing that spreadsheet is enabling other users to change or enter new assumptions and view the recalculated results. Likewise, if you create a Microsoft Excel PivotTable® report (more commonly known as a crosstab report), allowing other people to sort, filter, group, reorganize, or drill down to more detail is an essential part of sharing these documents. In other words, publishing a spreadsheet or database document on a web is only half the story. The other half is enabling others to interact with the published document and garner information that's meaningful to them, not just to the publisher.
Corporate information technology (IT) groups are also realizing the benefits of Web and Internet technologies. Many information systems are much easier to develop, deploy, use, and support when created with Web technologies on the corporation's intranet. Two such classes of systems exist: the decision support system (DSS), also known as the executive information system (EIS), or by the more recent name online analytical processing (OLAP); and the transactional system, which is used infrequently or by large audiences. The Sales Analysis and Reporting solution in Chapter 7 gives an example of OLAP in a Web browser, and the Timesheet solution described in Chapter 8 shows an example of a transactional tool commonly used by a large audience.
(To be precise, the terms decision support system, executive information system, and OLAP are not quite synonymous—each has a slant that makes it a little different from the others. The term executive information system is hardly used today because information systems aren't used only by executives anymore; however, once upon a time the phrase described an information system that delivered critical, high-level business information to executives who were monitoring the health of the company. The term decision support system is more inclusive and applies to a system that aids in decision making, often focusing on delivering ad hoc data analysis. OLAP is a buzzword today, and it's more often used to describe a class of technologies than the solutions built around them. Alas, our friends in the trade press often use all these terms interchangeably.)
Decision support systems lend themselves incredibly well to the technologies and user metaphors of the Web. Want to know how many units of your product were sold last month? Open your Web browser and click a particular hyperlink. Need to see a list of customers in your district? Again, it's just a click away from your team's intranet home page. Technologies such as Common Gateway Interface (CGI) and Microsoft Active Server Pages (ASP) have made it possible for IT groups to deliver live reports on demand in a format that can be viewed, printed, or imported into a variety of analysis tools.
However, often the person viewing the report wants to see it in a slightly different way, sort it by a different value, group the data in a different order, drill down and see more detail about a number, or see the data organized into a chart. When any of these scenarios occurs, IT groups return to the same problem they have always had to deal with: how do they build a flexible, robust, and easy-to-use reporting system that satisfies everyone's needs?
The second class of systems—transactional systems—also benefits from the technologies of the Web. Need to change your 401(k) contribution? Just follow a hyperlink on the human resources home page, enter the new value, and click the Submit button. Need help fixing your computer? Navigate to the Helpdesk site, fill out the form with a description of your problem, and click Submit. There's no installation program to run, no complex application to execute, and little or no client-side disk space needed.
Sometimes these applications demand a richer client interface, one that will provide gridlike data entry, recalculation, updates to charts showing the impact of the current value, and so on. To keep the deployment benefits, an IT group would need to use an active component in the page; however, they often lack the resources to develop such components themselves.
So how do you deliver an interactive experience on the corporate intranet? How do you make a spreadsheet or database report come to life in the Web browser? How can you develop and deploy solutions that provide rich data analysis and data visualization capabilities? How can you build transactional solutions with richer client interfaces? The answer is, with the Office Web Components.
The Office Web Components are a set of Component Object Model (COM) controls designed to bring interactive spreadsheet modeling, database reporting, and data visualization to a number of control containers. The OWC library contains four principal components: Spreadsheet, Chart, PivotTable, and Data Source. We'll discuss each of these controls briefly in this section and in much more detail in the following chapters.
Note COM is also known as ActiveX®. I was on the Visual Basic team when Microsoft invented the term ActiveX to describe the COM technologies, throwing most of our customers for a loop since they had just gotten used to saying COM after we stopped using the term OLE. Since I'm not a marketing person, I'll just use the term COM in this book to describe the Component Object Model technologies.
The word Office in the name Office Web Components indicates that the controls were developed by some of the same programmers who created Microsoft Excel and Microsoft Access and that the controls were made to look, feel, and behave like small versions of their Microsoft Office siblings. These controls definitely don't have all the features found in Excel and Access—because of course you wouldn't want to dynamically download all of Excel and Access to view a report in your browser! However, the controls do contain many of the commonly used features, especially those needed when interacting with content that's already been created. Plus, they can read and write the HTML file format of Excel 2000, allowing the user to click a button and load the current data into Excel for more powerful analysis. In this book, I'll detail the noteworthy Excel or Access features that are and aren't supported by each component. I'll also show you how to add some of these missing features with your own code.
The Web part of OWC's name is often misleading. The controls are standard COM controls and can be used in many control containers such as Microsoft Internet Explorer, Microsoft Visual Basic®, Microsoft Visual C++®, Microsoft Visual FoxPro®, or Microsoft Office UserForms. However, the controls have a few behaviors that make them especially suited to the unique environment of Internet Explorer. For example, Web browsers automatically support scrolling along a document, and it can be annoying for a control in the page to have its own set of scroll bars. The Spreadsheet and PivotTable controls can be set to automatically adjust themselves to fit their current content without requiring internal scroll bars. Also, all the controls support the color names available in Internet Explorer, in addition to supporting numeric RGB values. That means you can set the background color of an element to "CornSilk" or "PapayaWhip" (my personal favorite), and the control will convert the color to the appropriate RGB value just as Internet Explorer would.
The Components part of OWC's name is a touch confusing, although it's more accurate than using the word Controls (though I will often refer to OWC as "controls" for convenience throughout this book). The Office Web Components are unusual in that they can be used in control containers such as Web pages, Visual Basic forms, and so on, as well as in memory as invisible objects. Most COM controls can be used only as visible controls in control containers, and most invisible objects, such as those accessed via the Microsoft ActiveX® Data Objects (ADO) interface, can be used only in memory and cannot be put on a form or Web page. The OWC library was built so that its components could be used either way, which enables you to use the controls with the user interfaces they expose or for their base services, such as spreadsheet recalculation. The ability to use the components as invisible objects also enables you to use the library on a server to easily generate static content that users can view in any Web browser (more on that later in the chapter).
All the controls support a rich set of programming interfaces that you can call from Microsoft Visual Basic Scripting Edition (VBScript), Microsoft JScript®, Microsoft Visual Basic for Applications (VBA), Java, C++, and any other language capable of calling a dual or dispatch COM interface. That means you can weave the components into a custom solution and make them look and act the way you want. I will discuss most of the important properties, methods, and events in the subsequent chapters and will cover many more of these in the chapters describing the various solutions found on the companion CD.
See also If you are looking for a definitive reference on COM, I'd recommend picking up a copy of David Chappell's Understanding ActiveX and OLE (Microsoft Press, 1996).
Let's take a brief look at each of the components and discuss what kinds of solutions you can build with them. As already mentioned, the next four chapters will cover each component in more depth.
The Spreadsheet component (shown in Figure 1-1) is like a small version of an Excel spreadsheet, complete with a spreadsheet user interface and a recalculation engine that supports nearly all the calculation functions in Excel 2000. With this control, you can change or recalculate values; sort, filter, and scroll data; protect cells; and even reload the data into Excel 2000 for further manipulation. The Spreadsheet control can load its data from an embedded parameter or from any URL that points to an Excel spreadsheet saved in HTML file format.
Figure 1-1. The Spreadsheet component
The Spreadsheet control is useful anytime you want to make a spreadsheet model available on your intranet so that others can change the input and instantly view the recalculated results. Examples include a mortgage calculator and payment schedule model, a product break-even model, and a sales forecasting model.
This control is also useful for any kind of cross-tabulated or grid-based data entry, especially when you need to use formulas with automatic recalculation. Examples include expense reports, timesheets, and budgets.
The Spreadsheet control has the ability to bind cells to properties of other objects on the page and then automatically update the cell and its dependents when the source indicates that the property value has changed. This makes it possible to feed real-time data into the spreadsheet for scenarios such as stock portfolios. The Spreadsheet control is specifically designed to keep listening for new values and recalculating even when you are editing other formulas or formatting other cells in the spreadsheet you're working on.
The Chart component (shown in Figure 1-2) is comparable to a small version of Excel charting, supporting most of the two-dimensional chart types in Excel 2000 as well as a Polar chart type. Another big feature is that the Chart control can display many plots at once, allowing you to create a small-multiple design—in other words, a collection of plots that vary by one property and can be compared at a glance. A chart can be data-bound to the Spreadsheet control, the PivotTable control, or an ADO Recordset object, or it can be filled with literal data values. When bound to a data source, a Chart control will be updated whenever the source data changes.
See also For more information on the power of small-multiple designs, see Edward Tufte's book Envisioning Information (1990; ISBN 0-961-39211-8; Graphics Press, Cheshire, CT; phone: 800-822-2454).
Figure 1-2. The Chart component
The Chart control is primarily useful any time you need to chart live data or monitor a specific metric critical to your business. Because it supports a rich programming model, you can also add many effects to a chart with this control, such as zooming and panning on large axes, dynamically changing other content in the application based on the mouse's location, or letting users double-click to link to a new page displaying more information about the selected data point.
Designed to deliver interactive data reporting and analysis, the PivotTable component (shown in Figure 1-3) provides all the functionality found in Excel PivotTable reports and external data ranges. It can retrieve data from tabular, relational databases through OLE DB, as well as from OLAP server cubes and cube files through OLE DB for OLAP. By using this control, you can view data grouped, sliced, and sorted in a variety of ways, creating polished reports and interactive analysis on live data.
You can use this control for many tasks, although it's best suited for database reporting and data analysis solutions. When bound to an OLAP cube, the PivotTable control can provide the user with a flexible, high-performing analysis surface. IT groups can concentrate on collecting and cleaning data and loading it into cubes that reflect the way their company thinks about the data, while users working with this control can create slices of the data to fit their own needs.
Figure 1-3. The PivotTable component
The PivotTable control can also perform the same operations directly on a relational database, so you can use it even if you don't have an investment in an OLAP system. However, the performance when using an OLAP data source is always much faster because of the nature of the technology. OLAP has other logical benefits that we'll discuss further when we explore the Sales Analysis and Reporting solution in Chapter 7.
The Data Source component (DSC) is the backbone for controls that require data from external sources. Although this control is invisible, it is widely used to retrieve data, manipulate data into hierarchies or temporary OLAP cubes (more on this in Chapter 4), and establish data bindings between the various controls. Because the DSC supports the same standard interfaces as other data source controls found in Internet Explorer and Visual Basic, it will interoperate in those environments. The DSC is used heavily in the Access 2000 Data Access Pages feature and encapsulates much of the functionality found in the Access reporting engine.
The DSC is involved almost anytime the other components retrieve data from an external database. However, it also supports a programming model of its own, and you can use it to build or manipulate hierarchical Recordset objects. In general, you don't need to think much about the DSC because the other components and the Access 2000 Data Access Page Designer will set it up and implement it for you.
Because the Office Web Components are COM controls, you'd naturally expect them to work in any environment that calls itself a COM control container. However, theory and reality don't always match up, especially in the world of software. Microsoft makes many environments that can contain controls (16 that our team could recall off the top of our heads), and various other companies create many others.
To keep the Office test team from going mad, we tested the Office Web Components at different levels in different containers. First, we picked the containers we thought people would use most and performed full test passes on them. We then performed basic, ad hoc testing on the containers used less often. And finally, we grouped a few containers into the "not formally tested but doesn't cause a nuclear meltdown when tried" category. Table 1-1 shows which containers fell into what category.
Table 1-1. Tested containers
| Container | Test Coverage |
| Microsoft Internet Explorer 5.0 | Full |
| Microsoft Internet Explorer 4.01 | Full |
| Microsoft Internet Explorer 3.x | Not supported |
| Microsoft Visual Basic 6.0 | Full |
| Microsoft Visual Basic 5.0 | Basic |
| Microsoft Visual InterDev® | Basic |
| Microsoft Access Forms | Basic |
| Microsoft Office UserForms | Basic |
| Microsoft FrontPage 99 | Full |
| Microsoft FrontPage 98 | Basic |
| Microsoft Access 2000 Data Access Page Designer | Full |
| Microsoft Script Editor | Full |
| Microsoft Word 2000 | Basic |
| Microsoft Excel 2000 | Basic |
| Microsoft PowerPoint® 2000 | Basic |
| Microsoft Outlook® 2000 | Basic |
| Microsoft Visual C++/MFC Projects | None |
| Microsoft Visual J++ | None |
Also note that the design-time activation of the controls varies quite a bit from container to container. Since the Office Web Components are fairly complex controls, it is often necessary to select elements within a control (such as a cell in a spreadsheet, a pivot field in a PivotTable report, or a series of data points in a chart) and format or perform operations on them. To enable this functionality, the controls will become UI active in containers that allow this, meaning they will respond to mouse and keyboard events. Any changes made to the controls in design mode will persist with the document or form when it is reloaded at run time, resetting the controls to look exactly as they did when you saved them. Some containers make the controls UI active as soon as you click them. Others require that you double-click the control before it becomes UI active. Still other containers require you to single-click them twice to activate them. This behavior is entirely determined by the container, so refer to the container's documentation to see how it deals with COM controls at design time.
Note that this does not affect a control's run-time behavior. At run time, all containers make the controls UI active after loading them. But some environments, such as Office documents, never get into a run-time state because you are always editing the document that's currently loaded. Internet Explorer, Visual Basic, Visual C++, Access Forms, and Office UserForms all have a run-time mode in which the controls are immediately active.
If you read any of the Access 2000 documentation, you will likely see statements that say you can view data access pages only in Internet Explorer version 5 and later. Although true, that statement does not apply to the Office Web Components in general. The components will function in Internet Explorer 4.01 and later, although they won't function in any earlier versions (4.0, 3.x, 2.x, and so on). This applies to any interactive content created with Excel 2000 (which uses the Office Web Components) and to custom solutions developed by using the components.
Data access pages rely on a few specific features in Internet Explorer 5, which is why they only run in that environment. However, the Office Web Components themselves can run quite happily in the tested containers listed earlier (Table 1-1).
As mentioned before, the Office Web Components were designed to run both as controls "sited" within a form, document, or HTML page, and as objects in memory with no user interface. Each of the controls exposes a property or method that returns a static representation of its current content. The Chart and PivotTable controls can create GIF images of their content, and the Spreadsheet control can return an HTML table fragment or full page that can be rendered by any browser that's compatible with HTML 3.2. All of this means you can use the components on a Web server to perform server-side generation of chart images and PivotTable reports or server-side recalculation of a spreadsheet model. I'll demonstrate a few of these techniques in Part II of this book.
Besides discussing the how-tos of using the components on the server, we'll examine a number of more subtle issues related to performance, scalability, and reliability later in the book.
One of the more attractive aspects of Web-based solutions is that they don't require explicit setup of an application. Anything needed is either included with the Web page in the form of scripts or is automatically downloaded as applets or COM controls. The Office Web Components enable a no-installation deployment and automatic upgrade mechanism by using the codebase feature of Internet Explorer. The components also include a sophisticated web-installing control that makes the download experience much more attractive. Furthermore, OWC has no technological dependency on Office 2000, so it can coexist in an Office 97 environment or be used on a machine that doesn't have any other part of Office installed.
I'll cover all the aspects of deployment and how you can build your solutions to automatically deploy OWC in Chapter 12.
When I talk about the Office Web Components at conferences or other customer gatherings, invariably someone asks the question, "Do these run in Netscape Navigator?"
The answer is essentially no, with a few exceptions.
Netscape Navigator 4.5 does not natively support COM controls, so without any plug-ins, the Office Web Components won't even be loaded by Netscape Navigator. However, a company called NCompass Labs, Inc., makes a plug-in for Netscape Navigator that can host COM controls in a Web page. So if you installed this plug-in, could you use the components in Netscape Navigator? The answer is, it depends, and there are a few caveats.
Many solutions you might build with OWC involve communication between the components—for instance, a chart bound to a PivotTable report or spreadsheet, or a spreadsheet cell bound to the property of another element on the page. These solutions depend on functionality provided in Internet Explorer that isn't present in Netscape Navigator even with the NCompass plug-in, so they simply won't work in Netscape Navigator.
Using a single component or multiple components that do not communicate with one another is possible in Netscape Navigator. However, this is one of the containers that our test team did not research, so I can't guarantee that it will work.
You should always remember, however, that static content generated by the controls on the server can be rendered in either Netscape Navigator or Internet Explorer. So if your run-time environment requires that you support both browsers, you probably will be more interested in using the components on your Web server and in the solutions that show the techniques that do so.