Programming MS Office 2000 Web Components第二章第三节

     现在总结一下我们对电子表格组件的介绍，我将讲述(电子表格)控件编程模型的各个要点，以便您了解如何运用这个控件，以及当您需要编写脚本来实现不同的功能时该如何去做。本节不是编程模型的完全指南――那将会是另外一本书。在这里会介绍常用的属性和方法，每个属性和方法会附带一段简短的描述。如果需要了解编程模型的更多信息，可以参考Msowcvba.chm文件中的联机帮助，它位于Office目录的本地化目录下(对于英语语系的人来说，一般是Program Files\Microsoft Office\Office\1033目录)。

     如果您已经非常熟悉Excel的编程模型的话，您会发现它和电子表格组件的编程模型非常相似。

 

     COM和OLE自动化允许组件定义枚举，每个枚举都是指定的一组常量，作用类似编程语言中的类型声明。可以将一个枚举用作属性或方法的参数，这使得如VB和VC这样的环境能够显示包含一个枚举中有效常量的语句完成下拉列表 (译者注：语句完成功能指开发环境会建议开发者如何完成一行代码，使开发者不需要记住对象的所有成员)。OWC和其它许多ActiveX控件一样，包含了一系列预定义的枚举。

     不过，在例如web页面或asp页面这样的脚本环境中，不存在类型的概念。因此，在现有的这些脚本语言中不能使用枚举成员名称，因为这些语言无法知道一个特定的常量(例如ssHAlignLeft)对应的是什么。这意味着您的VBScript或ECMA脚本代码会因为不能自描述的神秘数字而混乱不堪(译者注：指程序中大量的数字常量造成程序很难被读懂)。

     为了尽量解决这个问题，所有的owc组件都有一个名为Constants的顶级属性，在脚本语言中可以使用这个属性访问组件的各种枚举成员。例如，如果您需要在VBScript代码中使用ssHAlignLeft常量使一个单元的内容居左显示，您可以这样书写代码：

为了使用一个枚举常量，您可以把它当作Constants对象的一个属性来引用它，而对象将返回枚举成员的正确的值。

请注意，如果您计划在您的函数的多处使用常量对象，那么设置一个等于Constants对象的变量是很有用的，这样做既避免了不必要的打字，也能够提高性能。例如，您可以这样书写代码：

Constants对象只在没有枚举概念的脚本语言中有意义。如果您是在VB,VBA或C++中编写代码，请象平常那样直接使用枚举，不用理会Constants对象。

     之前我已经讲述了多个将数据输入组件的技巧。我现在将更详细的描述编程模型中的这些技巧。表2－1列出了与装载数据相关的属性和方法，所有这些属性和方法都通过电子表格对象的接口公布。

表 2-1. 装载数据相关的属性和方法。

 

 

 

 

 

 

     电子表格组件中最常使用的编程接口是Range对象。很多方法都返回Range对象，当需要对一个区域的各单元进行修改，格式化，排序和设置自动筛选时，都会用到Range对象。表2－2显示了在你建立电子表格组件的解决方案时应该了解的Range对象的属性和方法。

表 2-2. Range对象主要的属性和方法。

 

在将数据载入电子表格后，您可能需要通过编程来控制格式化。每个单元都有自己的字体，对齐方式，边框，颜色和数字格式属性，所有这些特征都可以使用表2－3中的属性来设置。

表 2-3. 用于格式化的属性。

 

许多属性和方法会影响整个电子表格组件。表2-4列出了定制的解决方案中最有趣的属性和方法。

表 2-4. 影响整个电子表格的主要属性。

 

表 2-5 列出了在电子表格组件中排序和过滤数据时可以使用的属性和方法。

Table 2-5. 具有排序和过滤功能的属性和方法。

 

     如果需要保护电子表格的某部分，使您的用户不能修改单元内容或改变单元格式，您需要使用控制保护功能的各属性。表2－6列出了常用的具有保护功能的属性，并给出如何使用每一个属性的简短的描述。

请注意，保护设置即通过用户界面应用到用户交互中，也应用到代码中执行的操作中。因此如果您在一个保护选项被启用的情况下需要删除一行，您必须在执行删除操作前将Protection对象的Enabled属性设置为False，并在完成操作后，将Enabled属性设回True以便返回到受保护的状态。

表 2-6. 常用的具有保护功能的各属性。

 

表2-7 列出了当您控制电子表格组件的撤消机制时会用到的有关的属性和方法。

表 2-7. 控制撤消机制相关的属性和方法。

 

电子表格的顶级对象公布了大量的事件，比OWC库中的任何其它控件都要多。表2－8列出了一些关键的事件，当围绕电子表格组件开发定制解决方案时您很可能会需要使用它们。

几乎所有电子表格控件中的事件都向事件处理函数传递一个类型为SpreadsheetEventInfo的单一参数。SpreadsheetEventInfo是一个COM对象，您可以使用它来获得事件被触发时应用程序状态的各种信息，包括什么被选择了，那个区域被影响了，鼠标的位置在哪里，那个键被按下，等等。这反映出DOM中事件信息的处理方式。

使用一个对象来作为事件参数的最重要的原因，是因为需要支持JavaScript中的撤消事件的功能。JavaScript中传递给事件的参数总是值传递的，除非参数是对象的指针。换句话说，如果OWC小组将事件设计为附加一个ReturnValue参数，脚本可以通过将这个参数设置为True来撤消事件，那么在JavaScript中因为参数的值传递方式这不会正常工作。然而，如果脚本将一个对象的ReturnValue属性设置为True，触发事件的控件则会正确的发现它。因此，如果您需要撤消一个事件(大部分以”Before”开头的事件是可以被撤消的)，请将SpreadsheetEventInfo对象的ReturnValue属性设为False。

表 2-8. 有用的事件

有时您需要查看正在使用的控件的版本，以便能够利用新特性的优势，或者使用变通的代码来解决一个较早版本中的问题。大部分的软件程序都会在主要的版本之间发布SR版本，因此您常常需要检查您的代码正在交互的版本是否就是您所期望的版本。

为了帮助您完成这些工作，我们为owc库中的每一个控件添加了表2－9中的属性。您可以使用这些属性来判断您的代码正在操作的控件的版本，并执行适当的动作。

表 2-9. 所有Office Web 组件都具有的版本信息属性。

 

 

 

 

To conclude our introduction to the Spreadsheet component, I'll cover the key elements of the control's programming model so that you'll know how to get the control working and where to go when you want to write script for different functionality. This section is not a full reference to the programming model—that would be a book unto itself. Instead, the properties and methods you'll commonly use are presented here, along with a brief description of each. For more information on any part of the programming model, refer to the online help in the Msowcvba.chm file, which you can find in the local folder under the Office folder (generally Program Files\Microsoft Office\Office\1033 for English-speaking people).

If you are at all familiar with the Excel programming model, you will notice that the Spreadsheet component's programming model is quite similar.

COM and OLE Automation enable components to define enumerations, each of which is a named set of constant values that acts like a type declaration in a programming language. A property or method argument can be typed as an enumeration, which causes environments such as Visual Basic and Microsoft Visual C++ to display the statement completion drop-down list containing the valid constants for that enumeration. The Office Web Components, like many other ActiveX controls, have a predefined set of enumerations.

However, in scripting environments such as a web page or an ASP page, no notion of types exists. Therefore, no ability to use enumeration member names in script languages exists since these languages have no way of knowing what a particular constant, such as ssHAlignLeft, evaluates to. This means your VBScript or ECMA Script code can get littered with magical numbers that are not self-describing.

To alleviate this problem, all the Office Web Components have a top-level property called Constants that can be used in scripting languages to access their various enumeration members. For example, if you want to use ssHAlignLeft in VBScript code to align a cell's contents to the left, you can write code like this:

The most commonly used programming interface in the Spreadsheet component is that of the Range object. Range objects are returned from many methods and are used whenever you want to modify the contents, formatting, sort order, or filter settings of a range of cells. Table 2-2 shows the properties and methods of the Range object that you should know about when building solutions with the Spreadsheet component.

Table 2-2. Principal Range object properties and methods.

Once you have loaded data into the spreadsheet, you might want to apply formatting programmatically. Each cell can have its own font, alignment, border, color, and number formatting, and all these aspects are set using the properties shown in Table 2-3.

Table 2-3. Formatting properties.

A number of properties and methods affect the entire Spreadsheet component. Table 2-4 shows the most interesting ones for custom solutions.

Table 2-4. Principal properties that affect the spreadsheet as a whole.

Table 2-5 lists the properties and methods you will use when sorting and filtering data in the Spreadsheet component.

Table 2-5. Properties and methods for sorting and filtering.

If you want to protect part of the spreadsheet so that your users cannot modify cell contents or change cell formatting, you need to work with the properties that control protection. Table 2-6 lists the common protection properties and gives a brief description of how each is used.

Note that the protection settings apply to user interactions through the user interface and to operations performed in code. If you want to delete rows while a protection option is enabled, you must set the Enabled property of the Protection object to False before performing the operation, and then set it back to True to return to the protected state.

Table 2-6. Common protection properties.

Table 2-7 lists the relevant properties and methods you will use when controlling the Undo mechanism of the Spreadsheet component.

Table 2-7. Properties and methods for controlling undo.

A number of events are exposed from the top-level Spreadsheet object, more than from any other control in the OWC library. Table 2-8 lists several of the key events you likely will want to use when developing custom solutions around the Spreadsheet component.

Nearly all the events in the Spreadsheet control pass a single parameter of type SpreadsheetEventInfo to the event handler. SpreadsheetEventInfo is a COM object that you can use to retrieve all kinds of information about the state of the application when the event was fired, including what was selected, what range was affected, where the mouse was, what keys were pressed, and so on. This mirrors the treatment of event information in the DOM.

The biggest reason to use an object as the parameter is to support cancelable events in JavaScript. Parameters passed to an event in JavaScript are always passed by value unless they are object pointers. In other words, if the OWC team had designed the events with a ReturnValue parameter that the script set to True to cancel the event, it wouldn't work in JavaScript because of the parameter being passed by value. However, if the script sets an object's ReturnValue property to True, the control raising the event will see it. So if you want to cancel an event (most of the events whose names begin with "Before" can be canceled), set the ReturnValue property of the SpreadsheetEventInfo object to False.

Table 2-8. Useful events.

Sometimes you need to find out the version of the control you're working with so that you can either take advantage of new features or use workaround code to solve problems in an older version. Most software programs have service releases between their major version releases, so you often need to verify that the version your code is talking to is indeed the version you expect.

To help you do so, we added the properties listed in Table 2-9 to every control in the Office Web Components library. You can use them to determine the version of the control you're coding against and take the appropriate action.

Table 2-9. Version information properties for all Office Web Components.