译者说明：欢迎大家访问我的Blog: http://daidaoke.donews.net/daidaoke/
我的Email：tangtaike@163.com
如需转载，请事先通知。
第二章第三节 编程模型要点
现在总结一下我们对电子表格组件的介绍，我将讲述(电子表格)控件编程模型的各个要点，以便您了解如何运用这个控件，以及当您需要编写脚本来实现不同的功能时该如何去做。本节不是编程模型的完全指南――那将会是另外一本书。在这里会介绍常用的属性和方法，每个属性和方法会附带一段简短的描述。如果需要了解编程模型的更多信息，可以参考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常量使一个单元的内容居左显示，您可以这样书写代码：
MyRange.HAlignment = Spreadsheet1.Constants.ssHAlignLeft
为了使用一个枚举常量，您可以把它当作Constants对象的一个属性来引用它，而对象将返回枚举成员的正确的值。
请注意，如果您计划在您的函数的多处使用常量对象，那么设置一个等于Constants对象的变量是很有用的，这样做既避免了不必要的打字，也能够提高性能。例如，您可以这样书写代码：
Set c = Spreadsheet1.Constants
MyRange.HAlignment = c.ssHAlignRight
MyRange.VAlignment = c.ssVAlignBottom
Constants对象只在没有枚举概念的脚本语言中有意义。如果您是在VB,VBA或C++中编写代码，请象平常那样直接使用枚举，不用理会Constants对象。
将数据输入组件
之前我已经讲述了多个将数据输入组件的技巧。我现在将更详细的描述编程模型中的这些技巧。表2－1列出了与装载数据相关的属性和方法，所有这些属性和方法都通过电子表格对象的接口公布。
表 2-1. 装载数据相关的属性和方法。
属性或方法
描述
Spreadsheet.DataType
字符串类型的属性，告诉电子表格组件在多个用于装载数据的属性被设置时，应该使用哪个属性来装载数据，您赋给这个属性的值就是您应该用来装载数据的那个属性的名称――例如，将这个属性设置位HTMLURL，会使组件装载从属性HTMLURL中的URL处 获得的数据。
Spreadsheet.HTMLData
字符串类型的属性，可用来获得或设置HTML表格格式的电子表格内容。这种格式也包含了额外的属性和XML标签，这些属性和标签是用来保留重建电子表格模型所必须的信息(例如一个单元公式)的，不过它们不属于HTML3.2的表格格式。您可以将这个属性设置为包含了一个HTML表格的一个字符串，或者在需要保存时通过读取该属性值来获得电子表格完整的内容。
Spreadsheet.HTMLURL
字符串类型的属性，包含了一个URL，可以从这个URL处装载电子表格的数据。这个URL必须返回一个包含了表格的HTML文档。使用Excel 2000中来保存为HTML格式的电子表格可以使用这个属性来装载，这个属性也可以是一个从数据库中实时生成表格的ASP页面或CGI程序。
Spreadsheet.CSVData
字符串类型的属性，和属性HTMLData类似，不过它接受和返回的数据的格式是CSV。当需要从不能发布HTML格式的老系统中装载数据时这个属性很有用。
Spreadsheet.CSVURL
字符串类型的属性，和属性HTMLURL类似，不过它希望从URL处获得的数据格式是CSV格式的。就象HTMLURL属性一样，这个URL可以是一个从数据库中实时生成表格的ASP页面或CGI程序。
Spreadsheet.LoadText
可以将一个被分隔的文本文件载入电子表格的方法。与CSV格式不同的是，这个文本文件可以使用任何字段分隔符。Range对象也可使用LoadText方法和它的兄弟方法ParseText，来将文本载入电子表格的一个指定的区域中。LoadText方法可以指向一个您本地文件系统中的文件或指向一个URL。
使用区域
电子表格组件中最常使用的编程接口是Range对象。很多方法都返回Range对象，当需要对一个区域的各单元进行修改，格式化，排序和设置自动筛选时，都会用到Range对象。表2－2显示了在你建立电子表格组件的解决方案时应该了解的Range对象的属性和方法。
表 2-2. Range对象主要的属性和方法。
属性或方法
介绍
Spreadsheet.Range
该方法接受一个区域引用(例如A1:B2或A:B)，返回一个Range对象。因为一个区域可以只有一个单元，因此您也可以传入单个单元的引用(例如A1)。该方法也可以接受两个不同的单元引用，而返回一个含盖这两个单元的区域。
Range.Address
该属性返回一个区域的地址(例如，A1:B2)。
Range.Cells
当我首次看到这个函数时，我非常迷惑，因为它被定义为返回另一个Range对象。不过，您可以将该属性当作多个单元的一个集合，也就是说您可以使用For Each循环访问其中的每一个单元。该属性也可以以二维数组的形式来访问。例如，MyRange.Cells(1,3).Value会返回区域中行1，列3的值。电子表格和工作表对象也有Cells属性，因此您可以使用它代替前面提到的Range方法来访问指定单元。
Range.Column, Range.Row
这些属性指出区域中首行和首列的编号。当您在一个区域的行和列中进行迭代时，如果需要知道当前位于哪行哪列，这个属性非常有用。
Range.Columns, Range.Rows
虽然在名称上和前两个属性非常相似，但是这对属性返回的是Range对象包含的行或者列的集合。Range.Columns.Count和Range.Rows.Count可以告诉你当前区域中行和列的数目。
Range.HTMLData
该属性和Spreadsheet.HTMLData属性相似，不过Range对象的这个属性是只读的。使用它可以快速获得一个给定区域中数据的HTML表格的表现形式。
Range.Value
该属性获得或设置区域的值。虽然帮助文件说在区域由多个单元构成时Range.Value会返回一个二维变量数组，但实际上当前版本中这是没有实现的。不过，Range.Value可以接受一个二维数组的变量，来将数据输入区域中，当你需要使用文字值设置一个或多个单元值，或者是需要获得一个单元的无格式的值时，可以使用这个属性。(译者注：无格式的值指没有经过格式化的值。)
Range.Formula
该属性读写一个单元的公式字符串。当您需要获得或设置区域中一个或多个单元的公式时可以使用这个属性，记住在公式的开始使用等号(=)。
Range.Text
Range.Text属性返回Range.Value属性的格式化版本。当您需要在消息框或另一个用户界面元素中显示格式化的值时这个属性很有用，该属性值就是您在添加AutoFilter对象的过滤条件时传入的值。(译者注：也就是说，过滤是根据一个单元格的表面值进行过滤的)
格式化
在将数据载入电子表格后，您可能需要通过编程来控制格式化。每个单元都有自己的字体，对齐方式，边框，颜色和数字格式属性，所有这些特征都可以使用表2－3中的属性来设置。
表 2-3. 用于格式化的属性。
属性
描述
Range.NumberFormat
字符串类型的属性，控制单元中数字的格式。您可以引用现有的格式名称来使用众多的内置格式（例如Currency）。也可以创建自定义格式（例如，#,###,使1000显示为1,000）。
Range.Font
返回一个许多组件都能使用的常用的Font对象。Range.Font允许设置Font对象的的多个属性，例如Name,Size,Bold,Italic,Color和Underline。请注意，如果需要，可以在Font.Color属性中使用IE的颜色名。
Range.Halignment, Range.Valignment
这两个属性控制区域的各单元水平和垂直方向上的文本对齐方式。每个属性中定义了各自可以使用的对齐方式的枚举值。
Range.Borders
该属性返回一个Borders对象，可以通过它来设置每个单元边框的各个特性，如边框线的线粗，样式及颜色。
组件级的外观和行为
许多属性和方法会影响整个电子表格组件。表2-4列出了定制的解决方案中最有趣的属性和方法。
表 2-4. 影响整个电子表格的主要属性。
Property
Description
Spreadsheet.AllowPropertyToolbox
控制是否显示属性工具箱(译者注：指“命令和选项”窗口)。如果该属性设置为False，属性工具箱在工具条上的图标和在右键菜单上的菜单项会被禁止。可以随时使用这个属性来禁止缺省的格式化用户界面，并提供您自己的格式化界面。
Spreadsheet.AutoFit
决定控件是否处于”自动适应”模式。请查看前面章节中关于AutoFit的细节来了解这个特性是如何工作的。
Spreadsheet.Dirty
当控件中有任何修改，任何单元被改变后，这个属性会被置为true以通知您这个改变。通常使用Dirty属性来判断是否需要保存内容。请注意这是一个read/write属性，因此可以通过重置它来使电子表格又变”干净”。You commonly use the Dirty property to determine whether you need to save the contents in some way.
Spreadsheet.DisplayColHeaders, Spreadsheet.DisplayRowHeaders
控制是否显示行头和列头。这两个属性的缺省值是True。通常，您可以在需要使用您自己的代码来对电子表格界面进行完全控制时将这两个属性设置为False。
Spreadsheet.DisplayGridlines
控制是否显示网格线。缺省是要显示的，如果在定制解决方案中，要在需要单元分隔线的地方使用边框，则常关闭这个属性。
Spreadsheet.DisplayPropertyToolbox
控制是否显示属性工具箱。将属性置为true则显示，置为false就会隐藏。
Spreadsheet.DisplayTitleBar
控制是否显示标题栏。缺省是显示标题栏的。可以使用下面介绍的TitleBar属性来修改标题栏上的内容和格式。
Spreadsheet.DisplayToolbar
控制是否显示工具条。缺省是显示。
Spreadsheet.EnableAutoCalculate
控制电子表格模型如何重算。如果这个属性被置为false，电子表格模型不会自动重算；必须调用工作表对象的Calculate方法，才能看到改变输入后的新结果。在计划修改一系列的输入，并且希望只有在完成所有的修改后，才重算模型时，这个属性非常有用。这个属性缺省为True――模型会在电子表格被修改时自动进行重算。
Spreadsheet.ScreenUpdating
缺省情况下，电子表格在屏幕上的显示总是反映最新的数据，不过如果计划执行大量的操作，而不希望电子表格在每一个操作之后都会因为更新而闪烁。可以将这个属性置为Fasle。将这个属性重新设回True会触发一个彻底的重绘动作。
Spreadsheet.Selection
返回当前选择的对象。可以使用VBA或VBScript中的TypeName函数来判断这个对象的类型。
Spreadsheet.TitleBar
使您可以访问电子表格的标题栏，可以改变标题栏的文本和格式。
Spreadsheet.ViewableRange
控制电子表格实际可见的部分。请查看前面关于ViewableRange和AutoFit的介绍，以了解这个属性是如何工作的。
排序和过滤
表 2-5 列出了在电子表格组件中排序和过滤数据时可以使用的属性和方法。
Table 2-5. 具有排序和过滤功能的属性和方法。
属性或方法
介绍
Range.Sort
根据给定的列和排序方法对区域进行排序。
Worksheet.AutoFilter
该属性返回一个AutoFilter对象，该对象可用来设置当前过滤器的细节。
AutoFilter.Filters
该属性返回当前自动过滤区域的过滤器集合。一个过滤器对象应用于自动过滤区域中的一列，过滤器对象的下标和对应区域中列的下标相对应。
AutoFilter.Apply
该方法应用一个新的自动过滤。在设置完过滤条件后，必须调用这个方法来实际应用过滤器。
Criteria.FilterFunction
该属性控制符合过滤条件的项是包括在过滤器中，还是被排除在过滤器之外。如果是包含，则过滤器仅包含符合过滤条件集合的项，而如果是排除，则过滤器包含除了哪些符合过滤条件集合的项之外的所有项。
Criteria.ShowAll
该属性决定是否显示所有的数据。当设为True时，该属性重置过滤器来显示所有的数据。当设为False时，假如没有任何过滤条件，将不显示任何数据。
Criteria.Add
该方法将一个新的过滤条件添加到过滤器中。
Range.AutoFilter
该方法针针对一个给定的区域打开AutoFilter开关。首先调用这个方法，然后使用Worksheet.AutoFilter属性(如上所述)访问过滤器并设置过滤条件。
保 护
如果需要保护电子表格的某部分，使您的用户不能修改单元内容或改变单元格式，您需要使用控制保护功能的各属性。表2－6列出了常用的具有保护功能的属性，并给出如何使用每一个属性的简短的描述。
请注意，保护设置即通过用户界面应用到用户交互中，也应用到代码中执行的操作中。因此如果您在一个保护选项被启用的情况下需要删除一行，您必须在执行删除操作前将Protection对象的Enabled属性设置为False，并在完成操作后，将Enabled属性设回True以便返回到受保护的状态。
表 2-6. 常用的具有保护功能的各属性。
属性
描述
Worksheet.Protection
返回一个Protection对象，可以使用它设置各种保护选项，从而启用各种全局操作(例如：插入和删除行)。
Protection.Enabled
控制一般情况下是否启用保护。在需要使用保护选项或锁定单元格时，首先设置选项或者锁定单元格，然后将这个属性设为True。当需要在代码中执行操作时，可以将该属性设为False来暂时禁止保护功能。
Protection.AllowInsertingColumns, Protection.AllowInsertingRows, Protection.AllowDeletingColumns, Protection.AllowDeletingRows
允许或禁止在电子表格中插入、删除列或行。例如，如果AllowInsertingRows被设为False，则电子表格会禁止所有用来插入一行的命令，包括哪些编程模型中的命令。
Protection.AllowSizingAllColumns, Protection.AllowSizingAllRows
允许或禁止调整列或行的大小。例如，如果AllowSizingAllRows被设为False，电子表格就不会允许用户调整行的大小，也不会允许您通过代码来这样做。
Protection.AllowSorting
允许或禁止在电子表格中排序数据。将AllowSorting设为False会禁止用户对任何区域中的内容进行排序。
Protection.AllowFiltering
允许或禁止使用自动筛选功能。将这个属性设为False会禁止用户启用自动过滤功能。
撤 消
表2-7 列出了当您控制电子表格组件的撤消机制时会用到的有关的属性和方法。
表 2-7. 控制撤消机制相关的属性和方法。
属性和方法
描述
Spreadsheet.BeginUndo
一个使您可以将一系列的操作看作一个撤消操作的方法。例如，如果您调用BeginUndo方法，然后执行了三个不同的排序动作，或改变了许多单元，您可以一下撤消所有这些操作。
Spreadsheet.EndUndo
一个标记您的逻辑撤消单元的结束的方法。在调用BeginUndo方法和调用EndUndo方法之间执行的所有操作会被当前一个单一的单元来撤消。
Spreadsheet.EnableUndo
该属性控制撤消功能是否可用。缺省是可用的。您可能需要暂时禁止这个功能，以便节约内存或在代码中执行大量的操作。
有用的事件
电子表格的顶级对象公布了大量的事件，比OWC库中的任何其它控件都要多。表2－8列出了一些关键的事件，当围绕电子表格组件开发定制解决方案时您很可能会需要使用它们。
几乎所有电子表格控件中的事件都向事件处理函数传递一个类型为SpreadsheetEventInfo的单一参数。SpreadsheetEventInfo是一个COM对象，您可以使用它来获得事件被触发时应用程序状态的各种信息，包括什么被选择了，那个区域被影响了，鼠标的位置在哪里，那个键被按下，等等。这反映出DOM中事件信息的处理方式。
使用一个对象来作为事件参数的最重要的原因，是因为需要支持JavaScript中的撤消事件的功能。JavaScript中传递给事件的参数总是值传递的，除非参数是对象的指针。换句话说，如果OWC小组将事件设计为附加一个ReturnValue参数，脚本可以通过将这个参数设置为True来撤消事件，那么在JavaScript中因为参数的值传递方式这不会正常工作。然而，如果脚本将一个对象的ReturnValue属性设置为True，触发事件的控件则会正确的发现它。因此，如果您需要撤消一个事件(大部分以”Before”开头的事件是可以被撤消的)，请将SpreadsheetEventInfo对象的ReturnValue属性设为False。
表 2-8. 有用的事件
事件
描述
Spreadsheet.Change
只要电子表格中的一个或多个单元被改变，该事件就会被触发。可以使用SpreadsheetEventInfo对象的Range属性来判断被改变的区域。
StartEdit, EndEdit, CancelEdit
当一个单元将要被编辑，正要结束编辑，或正要撤消编辑时触发。您可以在EndEdit事件处理函数中执行数据校验，并通过将SpreadsheetEventInfo对象的ReturnValue属性设为True来拒绝输入的新值。使用SpreadsheetEventInfo对象的EditData属性可获得单元的新值。为了编辑的需要，可以在StartEdit事件中使用另一个元素来代替显示的值。例如使用一个TrueType字体来显示一个特殊的符号，而不是使用文字的描述。
BeforeCommand, Command
在正要执行一个命令之前和刚执行完一个命令后触发――例如排序；过滤；插入或删除行或列；显示帮助；和剪切，拷贝或粘贴这样的动作――被执行时。在Msowcvba.chm文件中或在您的对象浏览器中查看SheetCommandEnum常量的列表，可以知道在这些事件中可以捕捉的所有命令。同样的，如果需要撤销一个事件的缺省行为，您可以将BeforeCommand事件中的ReturnValue属性设置为False。例如，您可能希望当用户点击电子表格工具条上的帮助按钮时显示您自己的帮助页面。
获得版本信息
有时您需要查看正在使用的控件的版本，以便能够利用新特性的优势，或者使用变通的代码来解决一个较早版本中的问题。大部分的软件程序都会在主要的版本之间发布SR版本，因此您常常需要检查您的代码正在交互的版本是否就是您所期望的版本。
为了帮助您完成这些工作，我们为owc库中的每一个控件添加了表2－9中的属性。您可以使用这些属性来判断您的代码正在操作的控件的版本，并执行适当的动作。
表 2-9. 所有Office Web 组件都具有的版本信息属性。
属性
描述
MajorVersion
一个long型的整数值，指示组件的主版本号。对于Office 2000版本来说，这个数字是9。
MinorVersion
一个字符型的值，指示组件的次版本号。对于Office2000版本来说，这个数字是0，如果在下一个主版本发布之前，发布了任何小版本，这个数字会增加。请注意，为了处理出现例如”a”这样的版本号的情况，MinorVersion是一个字符串值。最好对这个值进行相等的比较操作，而不要进行大于或小于的比较操作。
BuildNumber
一个字符型的值，指示组件的编译数目。编译数目会随着组件DLL的每一次编译而增加。Office 2000版本的这个数值在写作这本书时还不可用，这也是一个字符型的数值，因此，它可以处理在发布一个次版本时版本号中被加入了一个字母的情况。
Version
一个字符型的数值，返回整个版本数值。当需要显示版本号时可以使用这个属性，但是在需要判断一个版本是否就是您需要的那个版本时请使用其它的属性。
附录(英文原文)：
Key Elements of the Programming Model
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.
Working with Enumerations in Script
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:
MyRange.HAlignment = Spreadsheet1.Constants.ssHAlignLeft
Working with Ranges
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.
Property or Method
Description
Spreadsheet.Range
This method returns a Range object given a range reference (such as A1:B2 or A:B). Because a range can be just one cell, you can also pass a single cell reference (such as A1). The Range method can also take two different cell references and return a bounding range.
Range.Address
This property returns the address of the range (for example, A1:B2).
Range.Cells
I was highly confused by this property when I first saw it because it's typed to return another Range object. However, you can use this property as a collection of cells, meaning that you can access the individual cells using a For Each loop. The property can also be accessed as a two-dimensional array. For example, MyRange.Cells(1,3).Value will return the value from row 1, column 3 in the range. There is also a Cells property for the Spreadsheet and Worksheet objects, so you can use it instead of the Range method (described earlier) to access specific cells.
Range.Column, Range.Row
These properties indicate the number of the first column and first row in the range. They are useful when you're iterating over a range of columns or rows and want to know what column or row you're at.
Range.Columns, Range.Rows
Although strikingly similar in name to the previous two properties, this duo returns a collection of columns or rows contained in the Range object. Range.Columns.Count and Range.Rows.Count tell you the number of columns and rows in the current range.
Range.HTMLData
This property is similar to Spreadsheet.HTMLData, except that it is read-only for the Range object. Use it to quickly get an HTML table representation of the data in a given range.
Range.Value
This property gets or sets a variant value for the range. Although the help file states that Range.Value returns a two-dimensional array of variants if the range constitutes more than one cell, it actually isn't implemented in this version. However, Range.Value can accept a two-dimensional array of variants for putting data into the range. Use this property when you want to set the cell or cells to a literal value or when you want to get a cell's current unformatted value.
Range.Formula
This property is used to read or write the formula string for a cell. Use it when you want to get or set the formula for a cell or cells in a range, and remember to use the equals sign (=) at the beginning of the formula.
Range.Text
The Range.Text property returns the formatted version of the Range.Value property. It is useful when you need to present the formatted value in a message box or another user interface element, and it's the value you pass when adding AutoFilter criteria.
Formatting
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.
Property
Description
Range.NumberFormat
A string-based property that controls the formatting used for a cell's numeric value. A number of built-in formats that you can use by name exist (such as Currency). You can also construct your own format definitions (for example, #,###, which makes 1000 appear as 1,000).
Range.Font
A property that returns the common Font object used by many of the components. Range.Font lets you set various properties of the Font object such as Name, Size, Bold, Italic, Color, and Underline. Note that you can use the Internet Explorer color names with the Font.Color property if you want.
Range.Halignment, Range.Valignment
Two properties that control the horizontal and vertical text alignment within the range's cells. Enumerations that contain the possible alignment values are defined for each of these properties.
Range.Borders
A property returning the Borders object that lets you set the various aspects of each cell border, such as line weight, line style, and line color.
Component-Level Appearance and Behavior
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.
Property
Description
Spreadsheet.AllowPropertyToolbox
Controls whether the Property Toolbox can be shown. If AllowPropertyToolbox is set to False, the Property Toolbox toolbar icon and context menu command are disabled. You use this property any time you want to disable the default formatting user interface and supply your own.
Spreadsheet.AutoFit
Determines whether the control is in AutoFit mode. See the section earlier on AutoFit for more details on how this feature works.
Spreadsheet.Dirty
Tells you if anything has changed in the control. If any cell has been modified, this property returns True. You commonly use the Dirty property to determine whether you need to save the contents in some way. Note that this is a read/write property, so you can also reset it to make the spreadsheet "clean" again.
Spreadsheet.DisplayColHeaders, Spreadsheet.DisplayRowHeaders
Control whether the column and row headers are displayed. These two properties are True by default. Normally, you set them to False if you want to have total control over the spreadsheet surface from your code.
Spreadsheet.DisplayGridlines
Controls whether gridlines are displayed. By default they are, and it's common to turn them off for custom solutions that use borders in specific places where you want cell separator lines.
Spreadsheet.DisplayPropertyToolbox
Controls whether the Property Toolbox is displayed. Set this to True to display it or False to hide it.
Spreadsheet.DisplayTitleBar
Controls whether the title bar is displayed. The title bar is displayed by default. Use the TitleBar property described below to modify the contents and formatting of the title bar.
Spreadsheet.DisplayToolbar
Controls whether the toolbar is displayed. The toolbar is displayed by default.
Spreadsheet.EnableAutoCalculate
Controls how the spreadsheet model recalculates. If this property is set to False, the spreadsheet model will not automatically recalculate; you must call the Calculate method of the Worksheet object to see new results for changed inputs. This property can be useful if you plan to make many changes to a set of inputs and want to recalculate the model only when you're done with all the changes. By default, this property is True—models will automatically recalculate when changed.
Spreadsheet.ScreenUpdating
By default, the screen display of the spreadsheet always reflects the most current data, but you can set this property to False if you plan to perform a number of operations and don't want the spreadsheet to flicker after each one. Setting this property back to True causes a full repaint.
Spreadsheet.Selection
Returns the currently selected object. You can use the TypeName function in VBA or VBScript to determine what type of object it is.
Spreadsheet.TitleBar
Gives you access to the Spreadsheet control's title bar, which you can change the text and formatting of.
Spreadsheet.ViewableRange
Controls what part of the spreadsheet is actually visible. See the earlier discussions of ViewableRange and AutoFit for more details on how this works.
Sorting and Filtering
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.
Property or Method
Description
Range.Sort
This method sorts the range given a column and sort direction.
Worksheet.AutoFilter
This property returns the AutoFilter object that can be used to set up the details of a current filter.
AutoFilter.Filters
This property returns the Filters collection for the current AutoFilter range. One Filter object applies to each column in the AutoFilter range, and the index of the Filter object matches the column index in the range.
AutoFilter.Apply
This method applies a new AutoFilter. After you've set up the criteria, you must call this method to actually apply the filter.
Criteria.FilterFunction
This property controls whether the criteria is included in the filter or excluded from it. Include filters include exactly the items in the criteria set, while exclude filters exclude the items in the criteria set but include everything else.
Criteria.ShowAll
This property determines whether all data will be shown. When set to True, the property resets a filter to show all data. When set to False, assuming there are no filter criteria, it shows no data.
Criteria.Add
This method is used to add new criteria to a filter.
Range.AutoFilter
This method is used to turn AutoFilter on for a given range. Call this method first, and then use the Worksheet.AutoFilter property (described above) to access the filters and set up the criteria.
Protection
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.
Property
Description
Worksheet.Protection
Returns the Protection object for which you set the various protection options that enable global actions such as inserting or deleting rows.
Protection.Enabled
Controls whether protection in general is enabled. To use the protection options or lock cells, first set the option or lock the cells and then set this property to True. You can set this property to False to temporarily disable protection while you perform operations in code.
Protection.AllowInsertingColumns, Protection.AllowInsertingRows, Protection.AllowDeletingColumns, Protection.AllowDeletingRows
Enable or disable the ability to insert or delete columns or rows in the spreadsheet. For example, if AllowInsertingRows is set to False, the spreadsheet will disable all commands that can be used to insert a row, including those in the programming model.
Protection.AllowSizingAllColumns, Protection.AllowSizingAllRows
Enable or disable the ability to resize columns or rows. For example, if AllowSizingAllRows is set to False, the spreadsheet won't allow the user to resize the rows, nor will it let you do so through code.
Protection.AllowSorting
Allows or prohibits the sorting of data in the spreadsheet. Set AllowSorting to False to prohibit users from sorting the contents of any range.
Protection.AllowFiltering
Allows or prohibits the use of the AutoFilter feature. Set this property to False to prohibit users from enabling the AutoFilter feature.
Undo
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.
Property or Method
Description
Spreadsheet.BeginUndo
A method that enables you to treat a number of operations as one undo operation. For example, if you call BeginUndo and then perform three different sorts or change many cells, you can undo all these operations at once.
Spreadsheet.EndUndo
A method that marks the end of your logical undo unit. All operations performed between the BeginUndo call and the EndUndo call will be undone as a single unit.
Spreadsheet.EnableUndo
A property that controls whether the undo feature is available. By default, it is. You might want to temporarily disable this feature to save memory or perform a number of operations in code.
Useful Events
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.
Event
Description
Spreadsheet.Change
Fires any time a change is made to a cell or cells in the spreadsheet. Use the Range property of the SpreadsheetEventInfo object to determine the range affected.
StartEdit, EndEdit, CancelEdit
Raised whenever a cell is about to be edited, was just edited, or just had its edit canceled. You can perform data validation in the EndEdit event and set the ReturnValue property of the SpreadsheetEventInfo object to True to deny the new value. Use the EditData property of the SpreadsheetEventInfo object to get the new value for the cell. Use the StartEdit event to replace a displayed value with another element for editing purposes, such as using a TrueType font for displaying a special symbol instead of a text description.
BeforeCommand, Command
Raised just before and after a command—an action such as sorting; filtering; inserting or deleting rows or columns; showing help; and cutting, copying, or pasting—is processed. See the list of SheetCommandEnum constants in the Msowcvba.chm file or in your object browser for all the possible commands you can catch using these events. Again, set the ReturnValue property to False in the BeforeCommand event if you want to cancel the default behavior for an event. For example, you might want to show your own help page when the user clicks the Help button on the spreadsheet toolbar.
Retrieving Version Information
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.
Property
Description
MajorVersion
A long integer value that indicates the major version number of the component. For the Office 2000 release, this number is 9.
MinorVersion
A string-based value that indicates the minor version number of the component. For the Office 2000 release, this number is 0 and will be incremented if any minor releases occur before the next major version release. Note that MinorVersion is a string value in case an "a" release occurs. It's best to perform an equality comparison on this value rather than a greater-than or less-than comparison.
BuildNumber
A string-based value that indicates the build number of the component. The build number is incremented with every build of the component DLL, and the value for the Office 2000 release wasn't yet available at the time of this writing. Again, this is a string value, so it can handle cases in which a letter is added to a version number in the event of a minor release.
Version
A string-based value that returns the entire version number. Use the Version property when displaying the version, but use the other properties for determining whether the version is the one you want.
