Programming MS Office 2000 Web Components第二章第一节（第一部分）

译者说明：欢迎访问我的Blog: http://daidaoke.donews.net/daidaoke/

译文中的错误望不吝指出。

我的Email：[e-mail]

如需转载，请事先通知。

 

 

第二章 电子表格组件

 

       本章将深入探讨微软Office电子表格组件的功能和编程模式。因为本书关注于建立实际的解决方案，所以我将展示一些各个组件特性的有趣用法，并进行说明。在本书第二部分，您会看到许多用法的使用。

 

       本章将向您展示数据表格组件能够做什么，不能做什么，向您推荐使用自己的代码为组件添加功能的聪明方法，以及向您展示使用电子表格组件编程模式的关键所在，这可是您学习电子表格组件的起点。

 

电子表格组件基本功能

       在我们深入了解电子表格组件的细节前，让我们先来看看它的基本功能，它所支持的装载和保存数据的各种方法。

 

重算引擎

       重算引擎是电子表格控件的核心—它使得组件不再仅仅是一个网格控件。因为电子表格组件是由那些为Microsoft Excel开发重算引擎的开发者开发的，所以它几乎支持Excel2000中所有的函数，包括大部分数据分析包(ATP)中的函数。

 

注释：

       如果您对上述内容感兴趣，请注意，下列函数不被电子表格组件所支持：ASC, CALL, DATEDIF, FINDB, FREQUENCY, GETPIVOTDATA, GROWTH, INFO, ISPMT, JIS, LEFTB, LENB, LINEST, LOGEST, MDETERM, MIDB, MINVERSE, MMULT, PHONETIC, REGISTER.ID, REPLACEB, RIGHTB, SEARCHB, SQL.REQUEST, TRANSPOSE, TREND, 和YEN。还有，所有以”B”结尾的函数都会在字节级进行操作，而不是象在双字节字符集系统中那样基于字符级。

 

       请参考本章后部关于属性绑定和插件函数的部分，以了解如何使用VBA和VBScript来实现这些函数。还有，在EXCEL中INDEX和LOOKUP函数都有两种形式，一种使用数组为参数，另一种使用向量(一维队列)为参数。电子表格控件支持向量参数形式，但不支持数组参数形式。

 

       任何包括公式的电子表格组件，在使用中都会很自然的需要重算引擎的支持――例如，一个允许用户查看产品收支平衡表，改变假设值并查看重算结果的解决方案，就会需要重算引擎。重算功能是电子表格分析的主干功能，是电子表格产品的核心功能。重算引擎也能利用本章后面会讨论到的一些先进特性，例如属性绑定和插件函数。

 

       当以无用户界面的方式使用电子表格组件时(即内存对象形式)，重算引擎成为组件主要的服务。重算引擎能够执行任何电子表格模型中易于表达的复杂计算。也能在服务器上根据一个现有的模型重算并将显示结果发送给web浏览器或者发送到电子邮件信息中。许多计算工作，如果使用脚本或C来完成的话会十分繁琐，但是在电子表格模型中却会很容易。

 

       例如，银行或借贷机构可能会开发一个评估特定类型贷款风险的电子表格模型。因为借贷保险业者常常不是程序员，让他们开发服务器上运行的借贷评估函数可能会很困难――就像让不理解借贷保险业者使用的复杂计算公式的程序员来开发一样。然而，如果使用电子表格组件的重算引擎，程序员就可以加载保险业者发布的电子表格模型，改变输入值，并输出获得的新的风险评估结果。

 

       电子表格组件支持最大655,36行，702列(从A列到ZZ列)的工作表，但是每一个组件实例只支持一个工作表。这和Excel2000支持的行数相同，但几乎是Excel2000支持的列数的3倍。(Excel只支持256列)

 

       注意：

       如果您想要装载所有655,36行和702列，您会等待较长时间。因为电子表格控件是从HTML格式的数据加载它的内容，所以装载操作当然比装载二进制数据(例如Excel装载XLS文件时)慢。虽然当装载数百条记录的文件时一般相当快，但电子表格组件不能快速加载数量巨大的模型。

 

       电子表格控件同时支持公式中的绝对单元引用($A$1)和相对单元引用(A1)，就象Excel一样，当您移动，插入，或者删除行或列时，它会自动的调整这些引用。在将包含公式的单元从区域中一个地方拷贝到另一个地方时，绝对和相对引用的使用都会相当有用。例如，在不管哪行哪列包含这个公式，引用都必须保持不变时，您应该使用绝对单元引用。而如果引用必须相对于当前的行和列，则需要使用相对引用。换句话说，在拷贝之后，$A$1还会是$A$1，而A1会变成拷贝到的当前行列。

 

       注释

       电子表格组件不支持旧的R1C1引用样式。它也不支持英语公式，英语公式试图使您能够根据命名了的区域来建立公式，然而它只是一个完美的目标，实际上很少如所设想的那样工作。

 

       很遗憾，这个版本的Office中电子表格组件不支持命名的区域。当Excel发布一个具有交互能力的电子表格模型时(换句话说，发布一个包含电子表格控件的页面)，它自动将命名的区域引用转换成绝对的区域引用。当您从Excel向电子表格控件中拷贝粘贴单元时也会发生这种转换。如果您想在电子表格组件的编码中使用命名的区域时，请考虑使用Dictionary对象(Scripting.Dictionary)，因为这个对象可以方便的按名存取实际的引用。(Dictionary对象位于Microsoft Scripting Runtime 库中)

 

       例如，如果您需要在电子表格中为一组数据定义一个命名的区域，然后在脚本中使用这个区域来完成一次排序，您可以用下列的代码来完成：

   

Set dict = CreateObject("Scripting.Dictionary")

dict.Add "MyName", "A1:F20"

Set rng = Spreadsheet1.Range(dict("MyName"))

       Dictionary对象是一个关联数组。可以将键/值对添加到其中，然后通过键，可以高效地重新得到任何值。使用这个对象，可以很容易跟踪命名区域，在任何需要向电子表格传递一个引用时，您可以使用上面最后一行代码中的方法来重新获得一个给定名称对应的真实引用。

 

电子表格组件用户界面

       电子表格组件用户界面位于重算引擎之上。当然，它和Excel的用户界面相似，但是为了支持和现有的电子表格模型交互时所需的功能，它作了特殊的设计。Excel中许多使用户可以轻易创建一个新电子表格的特性，在电子表格控件中都不存在；（然而，对于为您的解决方案创建新电子表格所必须的功能来说，它已经足够了）。还有，您可以从Excel向电子表格组件中进行拷贝和粘贴――这意味着您可以在Excel中完成大部分的创建工作。表2－1展示了一个基本的电子表格组件的例子。

 

       疯狂的电子表格引用

       我们能干的电子表格组件开发者之一，Andrew Milton，在检查本章时，向我展示了其它一些Excel支持而电子表格组件不支持的区域引用的类型。但这些引用方法中的大部分我从来没见过，甚至不知道在Excel中可以这样作。试试下面的步骤：

1、  在A1:A5的各单元中输入1,2,3,4,5。

2、  然后，在单元B1中输入值2。

3、  最后，在单元C1中输入公式：=SUM(A1:CHOOSE(B1, A1, A2, A3, A4, A5))。结果是3。

4、  现在，将单元B1的值改为3。单元C1中的公式将会重新计算，结果编程6。

 

函数CHOOSE的行为类似VBA中的Select Case语句，可以用在区域引用的中部来动态的定义区域结束的地方。也可以使用INDIRECT函数来实现动态定义区域的功能，但是电子表格组件完全不支持这种动态区域的定义。

      

       区域之间的交集是另一个有趣的创造。在最后那个例子中你所使用的电子表格里，如果在单元D1中键入公式“=A1:A5 A1:B1”，将得到1。此类引用使Excel计算两个区域的交集，结果就是指向A1的引用。电子表格组件允许您输入这样的公式，但是会将公式转化为“=A1”，而Excel会保留您最初输入的公式。

图2－1         一个基础的电子表格组件

       电子表格组件用户界面包含了许多被认为是理所当然的电子表格的特性。比起将每一个特性都罗列出来，我更愿意让您在使用控件时自己发现大部分的特性，虽然下列列表并不完全，但是能使您了解电子表格控件支持的用户界面特性的级别有多高。

 

• 改变行、列的大小

• 透明选择(译者：当用户选择单元格时，不会隐藏格式)

• 当输入公式时辅助选择

• 多级撤销

• 插入和移除行列

• 剪切，拷贝和粘贴

• 基于单元的格式化，包括设置字体，背景，对齐方式等等

• 全套的数字格式，包括对欧元格式的支持

• 自动过滤

• 自动求和

• 对区域进行排序

• 查找

• 单元边框

• 隐藏或显示工具条，行、列标头以及表格线

• 合并单元格

• 可选的标题栏

• 手动重算和自动重算的开关

• 保护单元，能够禁止插入和删除行、列。

• 可视区域和自动调整

• 在微软IE中支持按百分比缩放及最大化显示

• 内置的联机帮助

• 冻结的面板

• 完全键盘支持

• 可以控制滚动条的存在

• 可以控制回车后如何选择当前单元

• 可在全世界各国的计算机环境中运行，支持多国语言，支持从右到左的排列

 

       因为电子表格组件自身并不是应用程序，只是一个控件，所以它是通过一个名为属性工具箱的非模式工具窗口提供大部分的格式化功能的。这部分的用户界面实际上由各个组件共享，无论何时，开发者或用户需要改变控件中元素的格式时都会用到它。而如果开发者需要提供一个客户化的运行时用户界面，或禁止运行时用户改变控件中的元素，也可以通过在编程模式中改变一个属性来在运行时禁止属性工具箱。(本章稍后会讲述这个过程)

 

       非常有趣的是，属性工具箱完全是使用动态HTML(DHTML)来编写的。电子表格组件通过寄宿Internet Explorer控件的一个实例来实现属性工具箱，并在您和web页面上的元素交互时通过控制IE控件的实例来执行属性工具箱的代码。属性工具箱只是执行依赖于OWC组件的编程模型的脚本，因此，您可以在代码中完成属性工具箱的任何功能。

 

       电子表格控件，以及其它的Office Web组件，都支持Office 2000的语言设置功能，因此用户可以将电子表格控件中的用户界面语言设置成和在Office应用程序中使用的一样，而不需要设置底层的系统区域。如果这些语言设置被改变了，控件会自动的调整他们的用户界面，根据所选择的语言来进行显示。这也会影响到金额，事件和数字的格式。电子表格组件支持在单元中输入Unicode字符，在需要从右到左的排列的区域环境中，它也支持从右到左排列。

 

 

附录：英文原文

Chapter 2

The Spreadsheet Component

This chapter will delve into the functionality and the programming model of the Microsoft Office Spreadsheet component. Since this book focuses on building real solutions, I'll present a few interesting uses of each component feature along with its description. In Part II of the book, you'll see many of these ideas actually implemented.

This chapter will give you an understanding of what the Spreadsheet component can and cannot do, suggest clever ways you can use your own code to add functionality to the component, and show the key elements of the programming model that will get you started.

 

The Basics of the Spreadsheet Component

Before we get too detailed, let's examine the basic features of the Spreadsheet component and describe the various ways it can load and save data.

Recalculation Engine

The recalculation engine lies at the heart of the Spreadsheet component—making this component more than just a typical grid control. Designed by the same developers who built the recalculation engine for Microsoft Excel, the Spreadsheet component supports nearly all the functions in Excel 2000, including most of those in the Analysis ToolPak (ATP).

NOTE

---

For those who are curious, the following functions are not supported by the Spreadsheet component: ASC, CALL, DATEDIF, FINDB, FREQUENCY, GETPIVOTDATA, GROWTH, INFO, ISPMT, JIS, LEFTB, LENB, LINEST, LOGEST, MDETERM, MIDB, MINVERSE, MMULT, PHONETIC, REGISTER.ID, REPLACEB, RIGHTB, SEARCHB, SQL.REQUEST, TRANSPOSE, TREND, and YEN. All the functions ending in the letter "B" operate at the byte level instead of the character level in a double-byte character set (DBCS) system.

Refer to the sections on property binding and function add-ins later in this chapter to see how to use the VBA or VBScript equivalents of these functions. Also, the INDEX and LOOKUP functions each have two forms in Excel, one using arrays and one using vectors (single-dimension ranges). The Spreadsheet control supports the vector forms but not the array forms.

Any use of the Spreadsheet component that involves formulas will naturally require the recalculation engine—for example, a solution allowing users to view a product break-even model, change assumptions, and see the recalculated results. Recalculation is the backbone of spreadsheet "what if" analysis and the core feature of any spreadsheet product. The recalculation engine can also take advantage of some of the advanced features discussed later in the chapter, such as property binding and function add-ins.

When the Spreadsheet component is used without a user interface (that is, as an in-memory object), the recalculation engine becomes its primary service. The recalculation engine can perform any complex calculation that's easily expressed in a spreadsheet model. It can also recalculate an existing model on the server and then send the output to a web browser or into an e-mail message. Many calculations can be cumbersome to construct in script or C code but can be expressed rather easily in a spreadsheet model.

For example, a bank or lending institution might develop a spreadsheet model to assess the risk of a particular type of loan. Since loan underwriters typically are not programmers, developing a loan assessment function to run on the server probably would be difficult—just as it would be for the programmer who doesn't understand the complicated calculations the loan underwriter uses. However, using the Spreadsheet component's recalculation engine, the programmer can load the underwriter's published spreadsheet model, change the inputs, and grab the new risk assessment as the output.

The Spreadsheet component supports a worksheet with a maximum of 65,536 rows by 702 columns (A through ZZ) but supports only one worksheet per instance of the component. This is the same number of rows that Excel 2000 offers but nearly three times the number of columns. (Excel supports only 256 columns.)

WARNING

---

If you try to load all 65,536 rows and 702 columns, you'll be waiting quite a while. Since the Spreadsheet control loads its content from HTML, the load operation is naturally slower than that of loading binary data, such as when Excel loads an XLS file. Although you can generally load files with hundreds of rows fairly quickly, large models will not load quickly in the Spreadsheet component.

The Spreadsheet control supports both absolute ($A$1) and relative (A1) cell references in formulas and, just like Excel, it automatically adjusts these references when you move, insert, or delete rows or columns. Using both absolute and relative references can be especially useful when copying cells containing formulas from one part of a range to another. For example, you would use an absolute cell reference if that reference must remain the same regardless of which row and column contains the formula. A reference that must refer to the current row and column, however, needs to be relative. In other words, $A$1 will remain $A$1 after the copy operation, while A1 will be converted to the current row and column into which you have copied.

NOTE

---

The Spreadsheet component does not support the old R1C1 reference style. It also doesn't support English language formulas, which attempt to let you build formulas based on named ranges. While this was a wonderful goal, it seldom works as expected.

Unfortunately, the Spreadsheet component doesn't support named ranges in this version of Office. When Excel publishes a spreadsheet model with interactivity (in other words, publishes a page containing the Spreadsheet control), it automatically converts named range references to absolute range references. This also occurs when you copy and paste cells from Excel to the Spreadsheet control. If you want to use a named range in the code around the Spreadsheet component, consider using the Dictionary object (Scripting.Dictionary) as an easy way to store and retrieve the actual references for a given name. (The Dictionary object is implemented in the Microsoft Scripting Runtime library.)

For example, if you want to define a named range that refers to a list of data in the spreadsheet and want to use that range in script to perform a sort, you can write code like this:

Set dict = CreateObject("Scripting.Dictionary")

dict.Add "MyName", "A1:F20"

Set rng = Spreadsheet1.Range(dict("MyName"))

The Dictionary object is an associative array. You add key/value pairs to it and, given the key, you can efficiently retrieve any value. You can easily keep track of named ranges with this object, and any time you need to pass a reference to the spreadsheet, you can use the method shown in the last line of code above to retrieve the real reference for a given name.

Spreadsheet Component User Interface

On top of the recalculation engine sits the Spreadsheet component user interface. The user interface is, of course, similar to that of Excel but is specifically designed to favor activities you'd need to perform when interacting with an existing spreadsheet model. Many of the features found in Excel that make authoring a new spreadsheet easy do not yet exist in the Spreadsheet control; however, there are enough so that you can create new spreadsheets for your solutions as needed. Plus, you can copy and paste from Excel to the Spreadsheet component—meaning you can do most of your authoring in Excel. Figure 2-1 shows an example of a basic Spreadsheet component.

Spreadsheet Reference Madness

While reviewing this chapter, one of our capable Spreadsheet component developers, Andrew Milton, showed me many other types of range references supported by Excel but not supported by the Spreadsheet component. However, I had never seen most of these references and didn't even know they were possible in Excel. Try this:

1. Type 1, 2, 3, 4, 5 into the cells A1:A5.
2. Next, type the value 2 in cell B1.
3. Finally, enter the formula =SUM(A1:CHOOSE(B1, A1, A2, A3, A4, A5)) into cell C1. You should get 3 as the result.
4. Now change cell B1 to 3. The formula will recalculate to 6.

The CHOOSE function acts like a Select Case statement in VBA and can be used in the middle of a range reference to define the range's end point dynamically. Defining a range dynamically also works with the INDIRECT function, but the Spreadsheet component simply doesn't support such dynamic range definition.

Range intersection is another interesting construct. On the same spreadsheet you used for the last example, type the formula =A1:A5 A1:B1 into cell D1. You should get 1. This type of reference makes Excel perform an intersection of the two ranges, resolving to a reference of A1. The Spreadsheet component lets you enter a formula like this, but it resolves the formula to =A1, while Excel leaves the formula as you originally entered it.

Figure 2-1. A basic Spreadsheet component.

The Spreadsheet component user interface contains a great number of spreadsheet features that people now take for granted. Rather than list every last one here, I'll leave you to discover most of them as you play with the control. Although the following list is not exhaustive, it should give you an idea of the level of user interface features supported by the Spreadsheet control:

• Column and row resizing

• See-through selection

• Semiselect when entering formulas

• Multilevel undo

• Insert and remove columns and rows

• Cut, copy, and paste

• Per-cell formatting, including font, background, alignment, and so on

• Full set of number formats, including euro support

• AutoFilter

• AutoSum

• Sort ranges

• Find

• Cell borders

• Hide or Show toolbar, row and column headers, and gridlines

• Merged cells

• Optional title bar

• Manual or automatic recalculation switch

• Protected cells and ability to disable insertion and deletion of rows and columns

• Viewable range and AutoFit

• Percent sizing and maximum size in Microsoft Internet Explorer

• Built-in online help

• Frozen panes

• Full keyboard support

• Control over the existence of scroll bars

• Control over current cell selection after return

• Worldwide, multilanguage, and right-to-left support

Since the Spreadsheet component is just a control and not an application unto itself, most of the formatting capabilities are exposed through a modeless tool window called the Property Toolbox. This part of the user interface is actually shared among all the components and used whenever the developer or user wants to change the formatting of an element in the control. A developer that wants to supply a custom runtime user interface or disable runtime changes altogether can disable the Property Toolbox at runtime by changing a property in the programming model. (I'll describe this process later in the chapter.)

Interestingly enough, the Property Toolbox was written entirely in Dynamic HTML (DHTML). The Office Web Components host an instance of the Internet Explorer control to render the Property Toolbox and execute its code when you interact with the elements on the web page. The Property Toolbox merely executes script against the component's programming model so that anything the Property Toolbox does, you can do in code too.

The Spreadsheet control, along with the other Office Web Components, supports the Office 2000 language settings that enable users to set the user interface language used in the Office applications without having to reset the underlying system regional settings. If these language settings have been changed from their defaults, the controls will automatically adjust their user interfaces to show the selected language. This also affects currency, date, and number formatting. The Spreadsheet component supports Unicode characters in cells, as well as right-to-left layout for those locales that require it.