Programming Microsoft Office 2000 Web Components第一章第一节

第一章第二节 Office Web Components是什么?

Office Web Components是一组的COM控件,设计的目的是为众多的控件容器提供交互的电子表格建模,数据报表和数据可视化功能。OWC库包含四个主要的组件:电子表格组件,图表组件,透视表组件和数据源组件。我们将在这一节中简要论述每个控件,然后在后续各章中讨论更多细节。

注释:

COM也被称作ActiveX。当微软发明了术语”ActiveX”来描述COM技术时,我正在Visual Basic小组,这个术语使得我们绝大部分的客户大吃一惊,因为这些客户在我们停止使用术语”OLE”后刚刚习惯于使用术语COM。我不是市场人员,所以本书中我只使用术语COM来描述组件对象模型技术。

“Office Web Components”名称中的单词”Office”表示这些控件就是由那些开发微软Excel和Access的程序员中的一部分人开发的,并且这些控件被设计成在外观,使用感受和行为表现上就像是他们的微软Office兄弟的精简版一样。这些控件确实不包含Excel和Access中的所有的功能——换句话说,您也不会为了在浏览器中查看一个报表而动态的下载完整的Excel和Access!然而,这些控件也确实包含了许多的常用功能,尤其是那些在与已有内容进行交互时所必需的功能。此外,这些控件还能够读写Excel 2000的HTML格式的文件,允许用户点击一个按钮,将当前的数据加载到Excel中,以便作更加强大的分析。本书中,我会详细描述这些被每个组件支持和不支持的Excel和Access的显著特性。也会向您展示如何利用自己的代码来添加一些这些控件所缺少的功能。

OWC名称中的”Web”部分经常使人误解。这些控件是标准的COM控件,并能够在许多的控件容器中使用,例如微软Internet Explorer,微软Visual Basic,微软Visual C++,微软Visual FoxPro和微软Office用户窗体。然而,这些控件的一些行为使它们尤其适合于在Internet Explorer的特殊环境中使用。例如,WEB浏览器为文档提供自动的滚动条,但是人们经常会为页面中自带一组滚动条的控件而烦恼。电子表格控件和数据透视控件能够通过设置,自动调整大小来适合它们当前的内容,而不需要内部滚动条。此外,所有的控件都支持在Internet Explorer中可用的颜色名称,还支持数字RGB值。这就意味着您可以使用”CornSilk”或者”PapayaWhip”(我个人的爱好)元素来设置背景颜色,控件会象Internet Explorer那样将颜色转换成适当的RGB值。

OWC名称中的”Components”部分虽然比使用单词”Controls”要准确的多(尽管在这本书中我会为了方便将OWC称为”controls”),但是也令人相当迷惑。Office Web Components的与众不同之处在于,他们能够在类似web页面,Visual Basic窗体等等控件容器中使用,也能象不可视对象那样在内存中被使用。大部分的COM控件只能在控件容器中被用作可视控件,而大部分的不可视对象,例如那些通过微软ADO接口访问的对象,只能在内存中使用而不能放在窗体或web页面中。OWC库设计的目的是可以通过任何一种方式来使用。这使得您能够通过它们暴露的用户界面来使用这些控件或者使用这些控件的各种后台服务,例如电子表格的重新计算功能。这种能够以不可见对象的方式来使用的能力,也使您能够在服务器上用这些库方便地生成可以在任何web浏览器中查看的静态内容 (在这一章的后面会更多的讲到这方面的内容)。

所有控件都支持一组丰富的编程接口,这些接口能够在微软VBScript(Visual Basic的脚本版本),微软Jscript,微软VBA(Visual Basic for Application),Java,C++以及任何其它能够调用双COM接口或调度COM接口的语言中调用。这意味这您可以将这些组件添加到定制的解决方案中,并根据您的需要设计它们的外观和行为。我将在后续章节中讨论这些组件大部分的重要属性,方法和事件,并在这些章节中通过讲述各种解决方案来讲述这些重要内容中的绝大部分,这些解决方案可以在本书附赠的CD中找到。

参见

如果您正在寻找关于COM技术的权威的指南,我建议您选读David Chappell的《Understanding ActiveX and OLE(Microsoft Press,1996)》一书。

让我们浏览一下每一个组件,并讨论我们能够使用它们来建立哪些类型的解决方案。之前已经提到过了,后面的四章将会更加深入的讨论每一个控件。

电子表格组件

电子表格组件(如图1-1所示)象一个Excel电子表格的精简版,由一个电子表格用户界面和一个重算引擎组成,支持Excel 2000中几乎所有的计算功能。使用这个控件,您可以改变和重新计算数值;排序,过滤和滚动数据;保护单元不被修改;甚至可以将数据重新装载到Excel 2000中,以进行进一步的处理。电子表格控件能够从传入的参数中或从指向HTML格式的Excel电子表格文件的任何URL处装入数据。

图1—1 电子表格组件

当您要在企业内部互联网上创建一个有效的电子表格模型,以便其他人能够修改输入值,并立刻看到重新计算的结果时,电子表格控件是很有用的。例如抵押计算器和偿还进度表模型,产品收支平衡模型,和销售预测表。

对于任何类型的交叉表或基于表格的数据输入,尤其是当您需要使用有自动重算功能的公式时,这个控件也是很有用的。例如成本报表,事件表和预算报表。

电子表格控件能够将单元绑定到在页面上的其它对象的属性上,然后当数据源表明属性值改变时自动地更新单元以及和单元相关联的其它单元。这使得可以在电子表格中为一些特定的环境提供实时数据,例如股票组合。

为了实现即使当您在正在工作的电子表格中编辑其它的公式或者正在为其它的单元设置格式时,电子表格也能够继续监视新的数据并且重新计算,电子表格控件做了专门的设计。

图表组件

图表组件(如图1-2所示)可以说是Excel图表的一个精简版,它支持Excel2000中的大部分二维图表类型,例如Polar图表类型。图表组件的另一个重要的特征是能够立刻显示多个图形,从而允许您创建一个小型多重设计——换句话说,根据某一属性变化的一系列图形可以立刻进行比较。一个图表可以和电子表格控件,数据透视控件或者一个ADO的Recordset对象进行数据绑定,或者使用原始的数据来填充。当绑定到一个数据源上时,无论何时数据源发生变化图表控件都会更新。

参见

如果需要了解小型多重设计的威力的更多信息,请看Edward Tufte的书《Envisioning Information (Graphics Press, 1990)》。

图1-2 图表组件

当需要用图表来表示动态的数据或监视对你的业务而言非常重要的元素时。图表控件会相当的有用。因为它提供丰富的编程模型,所以也能利用这个控件向图表中添加许多种效果,例如在坐标轴的最大范围上缩放和平移,根据鼠标的位置在应用程序中动态地改变图表的内容,或者让用户双击一个链接,转到显示所选择的数据点的更多信息的新页面。

数据透视组件

数据透视组件(如图1-3所示),是为了提供交互的数据报表和分析功能而设计的,它能提供Excel数据透视表的所有功能和各种外部数据类型。它能通过OLE DB从表格,和关系数据库中提取数据,也能通过OLE DB从OLAP服务器的cube和cube文件中为OLAP提取数据。使用这个控件,您能够对数据进行各种方式的分组,切片和排序并查看,在动态数据上创建优美的报表和交互分析功能。

虽然它最适用于数据报表和数据分析解决方案,但您可以在很多任务中使用这个控件,当被绑定到一个OLAP cube上时,数据透视控件能够给用户提供一个灵活,高效的分析界面。IT机构能够集中精力根据他们公司对数据的理解来收集和整理数据,并将数据装入cube中,而操作这个控件的用户则能够根据自己需求来对数据进行切片。

图1-3 数据透视表

数据透视报表也能直接在关系数据库上完成同样的工作,因此即使您在OLAP系统上没有投资,您也能够使用这个控件。然而,使用OLAP数据源的性能总是要快的多,原因在于这项技术的本质。OLAP还有其它逻辑上的好处,我们将在第七章中探讨销售分析和报表解决方式时深入的讨论它。

数据源组件

数据源组件(DSC)是需要从外部数据源获得数据的控件的骨干框架。虽然这个控件是不可见的,但是它被广泛的使用于从层次(OLAP中的术语)或临时OLAP cube中获取和处理数据(第四章中将详细介绍),以及在各种各样的控件之间建立数据关联。既然数据源组件和那些Internet Explorer和Visual Basic中的其它数据源控件一样,支持相同的标准接口,那么在这些环境中它就是可以互操作的。数据源组件在Access2000的数据访问页特性中得到大量的使用,并且集成了许多Access报表引擎中的功能。

当其它的组件要从外部数据库获得数据时,几乎都要用到数据源组件。然而,它也支持它自己的一个编程模型,您能使用它来建立或者操作层次的Recordset对象。一般来说,您不需要过多的考虑数据源控件,因为其它组件和Access2000数据访问页设计器会为您设置和调用它。

疑问:1、The "Components" part of OWC’s name is a touch confusing这句如何翻译?

2、the PivotTable component (shown in Figure 1-3) provides all the functionality found in Excel PivotTables and external data ranges.中的external data ranges如何翻译?

附录:英文原文

What Are 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. (throw…for a loop:大吃一惊)

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—in other words, 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’s often 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 like 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 VBScript (Visual Basic Scripting Edition), Microsoft JScript, Microsoft VBA (Visual Basic for Applications), 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

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 a stock portfolio.

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

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 update whenever the source data changes.

SEE ALSO

——————————————————————————–

For more information on the power of small-multiple designs, see Edward Tufte’s book Envisioning Information (Graphics Press, 1990).

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.

The PivotTable Component

Designed to deliver interactive data reporting and analysis, the PivotTable component (shown in Figure 1-3) provides all the functionality found in Excel PivotTables 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. 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

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. Since 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 Access 2000’s data access pages feature and encapsulates much of the functionality found in the Access reporting engine.

The DSC is involved almost any time 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 since the other components and the Access 2000 Data Access Page Designer will set it up and implement it for you.