web
第二章第二节 电子表格组件的高级功能
我们已经讨论了电子表格组件的大部分基本功能，现在让我们转向一些高级功能。大部分的这些高级功能Excel2000都不包含，因为这些是组件专门需要的特殊功能。而那些Excel2000中存在的功能，在电子表格组件中也被增强，使得可以提供一些新的功能。
属性绑定和实时数据
“属性绑定”是电子表格组件中最新奇的新功能之一，它是指控件能够将同一个web页面上其它对象的属性和方法用作单元值或公式参数的能力。电子表格控件使用标准的COM机制来实现绑定到属性，并在数据源对象告知属性值已经改变时，自动接收新值，并重新计算相关联的单元。
例如，如果您开发了一个组件，它公开了一些属性和方法以返回一个给定股票代号的最后销售价格，您就可以使用电子表格控件来查看这个信息，并在价格更新时观察到它的变化。如果电子表格的其它部分――例如当前用户的组合信息(译者注：金融专业的术语。)――引用了这个最后销售价格值，那么电子表格组件也会在值改变时重新计算这些单元。
可以象下面这样在单元中输入一个函数来建立属性绑定：
=document.StockTicker.Quote("msft").LastSale
只要公式以“=document.”开始，电子表格组件就认为紧接着的是一个属性绑定。它会认为表达式的下一部分将是页面上另一个元素的ID，而表达式的其余部分将会该元素的一个属性，或是由一个方法返回的另一元素的一个属性。您可以在方法调用中使用单元引用作为参数，而电子表格控件必然会将真实的值传递给方法。
单元绑定到的对象既可以是另一个COM对象，也可以是页面上的任何HTML元素，例如一个文本框或一个下拉列表。这样您就可以直接在重算模型中包含页面上其它的数据，而不必写脚本来将HTML元素的值输入到电子表格单元中。
属性绑定机制经常在提供实时数据的环境中被提到，因为它包含了实现提供动态数据的两个必须的特性。
l 当得知属性被修改时，电子表格组件立刻更新单元，而不会有一个固定的轮询间隔。
l “即使用户在编辑其它单元，或执行命令时”，电子表格组件依然会继续监听新值并更新单元。人们对Excel的DDE链接机制的一个普遍抱怨就是它不能完成这个功能，因此我们确信应该在电子表格控件的属性绑定特性中避免同样的错误。
属性绑定可以指向电子表格组件自身吗？
大胆的读者可能已经在思考将电子表格组件中的单元绑定到组件本身，或绑定到另一个电子表格组件上的可能性。(thinking wildly)从表面上看，似乎可以通过这个机制，轻松的支持关联不同的电子表格上的单元。
但是，唉，这是不可能的。电子表格控件自身禁止了这种支持，因为可能造成的重入引用和循环引用是相当恐怖的。电子表格控件只知道它被绑定到另一个对象上(不是自身或另一个电子表格控件)――因此它无法确保引用没有产生会挂起重算链的一个依赖循环。
为了实现将当前电子表格组件中的单元关联到另一个电子表格组件的单元上，您必须在另一个组件中通过代码来响应Change事件，并将新值写入到相关联的单元中。
当然，请记住电子表格组件处理属性变化通知的能力完全依赖于它重算当前模型的时间。对于中小型的模型来说，通常不存在问题，因为重算只会花费一秒或者更少的时间――比起大多数人所能容忍的数据更新时间要快的多。然而，如果模型相当巨大，电子表格控件也只能尽快重算来提供新数值，这可能就比新值到达的速度慢多了。
在第十章将讲述关于属性绑定的更多细节，在该章中，您将看到如何在Visual Basic中建立一个股票行情控制系统，为股票组合表格提供实时的报价。
函数插件
和在Excel中一样，开发者可以使用函数插件，将新功能添加到电子表格组件中。与符合Excel私有的XLL模型的插件不同，电子表格组件的函数插件是以COM对象的方式被创建的。这种对象所公布的任何方法都以内部函数的形式被添加(译者注：什么是potential function？)，这样您就可以在公式中象使用那些Excel内部函数一样使用它。
例如，如果您开发了一个COM对象，包含了一个叫做SumTopN的方法，该方法根据传入的一列数值返回前N位数值的和，那么您可以在电子表格组件中通过下列代码来使用这个函数，就像在Window_onLoad事件中一样：
Spreadsheet1.AddIn MyObject
MyObject变量应该指向自定义函数对象的一个实例。为了保证您的对象可用，在页面上使用<object>标签，并将对象的id传给电子表格控件的AddIn方法，如下所示：
<object classid="clsid:0002E510-0000-0000-C000-000000000046"
id=Spreadsheet1>
</object>
<object classid="clsid:ClsidOfYourObject"
codebase=PathToCABfileOfYourObject id=MyObject>
<script language=VBScript>
Spreadsheet1.AddIn MyObject.Object
</script>
<object>标签中的codebase属性告诉IE如果客户端机器上没有class ID所引用的对象，应该从哪里去安装这个对象。如果需要了解更多关于codebase属性的信息，可以查看MSDN库中的IE和DHTML主题。
只有在Internet Explorer或其它使用不同的界面来包装对象的容器中需要使用Object属性。在我们刚才讨论的HTML文件中，MyObject实际上指向了一个称作object的COM对象类型(由<object>标签声明)，而不是标签创建的实际COM对象。Object属性返回一个指向实际的COM对象的指针。
在Visual Basic中，您还是需要调用AddIn方法，但是需要传递一个指向您创建的类的实例的变量。例如：
Dim MyAddIn As New FunctionLib
Spreadsheet1.AddIn MyAddIn
在C++中，技巧是一样的，不过您应该使用coCreateInstance函数并向AddIn方法传递指向您的对象的IDispatch接口的引用。
电子表格组件实际上使用这种插件机制来装载不常使用的函数。Owc主要的dll文件Msowc.dll并没有实现电子表格控件的所有函数，那些不常使用的函数实际上是由Msowcf.dll来实现的(“f”代表扩展函数库)；当第一次使用它们时电子表格组件自动将他们添加到插件列表中。扩展函数通过包含一系列方法的COM对象实现，其中，每一个方法对应一个公布的函数。
您可能会怀疑是否函数插件与之前所描述的属性绑定机制有所不同。答案是肯定的。属性绑定监听值改变时数据源的通知信息，而函数插件仅在函数的输入变化(或影响输入的单元变化)时被调用。这基本上是推模式和拉模式的区别：属性绑定类似推模式；当它认为必要时会将新值推入电子表格控件中。而另一方面，函数插件没有到电子表格组件的通讯通道；电子表格组件决定什么时候需要调用插件函数来计算新值。
有时推和拉的界限是模糊的，尤其是当您意识到IE将页面上的脚本函数以对象的方式暴露出来，这种能力所造成的奇怪而相当有趣的副效果时。您页面上的所有各种<script>块都以被名为Script的DOM对象公布出来，在<script>块中定义的每一个函数或子方法都被当作Script对象的一个方法。这就意味这您可以将您页面上的脚本函数用作您电子表格中的函数，不过您来完成这个功能的机制更像是属性绑定，而不是插件函数。
例如，假设您在页面上有这样一个<script>块：
<script language="VBScript">
Function VBDateAdd(interval, number, date)
On Error Resume Next
VBDateAdd = DateAdd(interval, number, date)
End Function
</script>
电子表格组件不包括复杂的日期操作函数。不过，VBScript提供了灵活的DateAdd函数，允许您在一个给定的日期上加(或减，或使用一个负数作为一个内部的参数)任何数目的时间间隔。为了使电子表格组件能够使用这个函数。前面的<script>块定义了一个名为VBDateAdd的函数，返回VBScript函数DataAdd的结果。为了在您的电子表格中使用VBDateAdd函数，在您需要在显示结果的单元中输入下列的公式：
=document.script.VBDateAdd(B1, B2, B3)
这个公式将单元B1,B2,B3的当前值分别用作间隔，数目和日期的参数值。这个公式和其它公式一样，被放置在依赖链中，当那些输入的单元改变时，电子表格组件会调用这个函数，传入新的输入值并显示新的结果。
使用脚本函数有它的优点和不足。脚本是以解释方式来执行的，这意味着他们将比编译的代码要慢一些。脚本也受限于脚本语言的功能和与客户端机器的交互能力，以及网络的限制(因为安全限制。)不过，编译的对象需要下载和在客户端机器上进行安装，在某些机构中可能不允许这样，还有，如果对象编写的不完善，测试不充分，可能会给客户端机器带来潜在的负面影响。Web页面中的脚本定义上是”安全”的，并且因为他们是实时解释执行，因此他们不需要下载和安装额外的文件。
当然，只有在容器是IE时才会谈到脚本函数。例如，如果您在vb窗体中使用电子表格组件，您仍然可以使用函数插件，不过这里没有页面上的脚本块的概念。请注意AddIn方法传入一个COM对象的引用，因此如果您在您的应用程序中使用vb，您可以使用工程中的任何公共类作为一个函数插件的对象。只需创建该类的一个实例，然后将它的引用传给电子表格控件的AddIn方法。
可视区域和自动调整
电子表格应用程序经常只需显示电子表格界面的一部分，而不是显示所有的行和列。例如，时间表应用程序应该显示足够的行和列，以便用户在输入他(她)的工作时间时不必看到数据周围大量的空白行、列。电子表格控件通过ViewableRange属性来完成这个任务，您也可以在设计阶段通过属性工具箱来设置它。
“可视区域”定义了电子表格要显示多少行、列。缺省是显示所有的行、列，不过您既可以在脚本中改变它，也可以在设计阶段通过属性工具箱中改变它，可将它改变为任何有效的区域引用。例如，将区域设置为A1:D6，使电子表格只显示四行六列。而其它部分则显示为一个空白的灰色区域；用户既不能在可视区域之外选择，也不能移动到可视区域之外。可视区域之外的单元仍然存在，并可以在脚本代码中被引用，但是用户看不到这些单元，也不能和它们交互。如果需要隐藏对照表(译者注：也就是我们常说的代码表，例如：数据库多个表中都有单位ID列，那么单位001,002,……具体代表什么单位，就在单位代码表，既单位对照表中定义。)或不希望用户看到的中间计算值，这是一个极好的方法。
可以通过代码动态调整可视区域，因此您可以调整可视区域来响应其它用户的事件。还要注意可视区域具有和普通区域一样的自动调整特性――如果开发者或用户在可视区域中插入了一行或一列，可视区域就会增加一行或一列。例外，可视区域属性是String类型，而不是Range对象类型，因此如果需要获得整个可视区域的区域对象，您需要这样写代码：
Set rngViewable = Spreadsheet1.Range(Spreadsheet1.ViewableRange)
设置可视区域不会自动调整容器中的电子表格控件的尺寸。不过，只要您定义了一个可视区域，就可将AutoFit属性设置为True，这会使电子表格自动调整大小，以便不需滚动条就能显示整个可视区域。如果可视区域比控件的当前尺寸小，控件将缩短；如果比它大，它将扩大。
另外两个属性，MaxHeight和MaxWidth，管理电子表格控件增大的限度，可以使控件不会变的过大。这两个属性定义了电子表格高度和宽度的极限。如果可视区域超过了允许的最大高度和宽度，电子表格将显示内部的滚动条，使得用户可以浏览整个区域。
当控件在IE中运行时，可以将MaxHeight和MaxWidth属性设置为百分比。例如，如果MaxWidth设置为80%，电子表格将允许自身扩大到容器元素宽度80%的大小。例如，如果电子表格被放在<body>元素中，电子表格将允许扩大到页面宽度的80%。如果它被放置在一个table单元中，电子表格可以扩大到table单元宽度的80%。这种根据百分比来进行缩放的能力也应用在IE中标准的Width和Height属性中。这种能力在动态HTML布局的环境中非常有用，并且可以使电子表格能够在页面上正确的布局，而不会受窗口大小和显示器分辨率的影响。
将电子表格组件用作数据源
Excel社团中最普遍的需求之一就是希望Excel能够成为XLS文件中的数据的OLE DB提供者。当我们开发电子表格组件时，我们意识到我们必须提供一种方法，使得图表组件能够从电子表格组件中获得一系列的数据，以便使用图表来表示它们。图表组件也必须能够感知这些数据的变化，以便它能够更新图表。令人高兴的是，这些需求正是OLE DB数据绑定所能提供的，因此我们决定将电子表格控件设计成一个真正的OLE DB数据源。我会马上从技术的角度详细讨论它的意义。不过，首先我将举一个例子以便阐明这一点。
在IE中，您可以将众多的HTML元素数据绑定到任何是有效数据源的控件上。IE5能够将元素绑定到数据源中某个特定的数据成员上，以适应数据源包含一个或多个数据成员的情况。例如，如果在页面上有一个名为tdcComposers的数据源控件，您可以使用下面的HTML片断将一个html表格绑定到数据源上。
<table datasrc=#tdcComposers>
<thead>
<tr style="font-weight:bold">
<td>First</td><td>Last</td>
<td>Birth</td><td>Death</td><td>Origin</td>
</tr>
</thead>
<tbody>
<tr>
<td><div datafld="compsr_first"></div></td>
<td><div datafld="compsr_last"></div></td>
<td><div datafld="compsr_birth"></div></td>
<td><div datafld="compsr_death"></div></td>
<td><div datafld="origin"></div></td>
</tr>
</tbody>
</table>
同样，您可以将一个HTML表格绑定到电子表格组件中一个区域的内容上。下面的HTML片断是从随书光盘的SpreadsheetDS.htm文件中摘抄的：
<table datasrc=#Spreadsheet1.A2:D7 border=1>
<thead>
<tr>
<th>Salesperson</th>
<th>FY98 Sales</th>
<th>Projected Growth</th>
<th>Est. FY99 Sales</th>
</tr>
</thead>
<tbody>
<tr>
<td><div datafld="A"></div></td>
<td><div datafld="B"></div></td>
<td><div datafld="C"></div></td>
<td><div datafld="D"></div></td>
</tr>
</tbody>
</table>
电子表格控件实现了IDataSource接口，这是IE和VB6及后续版本所定义和支持的标准数据源接口。这些容器将任何实现了这个接口的控件看作页面或窗体上其它数据绑定控件的一个有效的数据源，一个数据源控件可以暴露任意数量的数据成员，每个数据成员通过一个字符串来标记，并返回一个OLE DB行集合。电子表格控件几乎暴露了无数的数据成员，因为任何有效的区域引用都是一个有效的数据成员。例如，前面的HTML片断要求一个名为A2:D7的数据成员，并得到了一个两行七列的行集合。IE5中指定数据成员的神奇的语法是<数据源控件名称>.<数据成员名称>。ID前的hash符号(#)显示数据源是当前页面上的一个控件。对于电子表格组件，任何有效的区域引用都能够作为数据成员名称。
当暴露整个区域的数据时，电子表格组件也实现了简单OLE DB提供者接口(有时被称为OSP)。相对于实现IRowset和其它OLE DB接口而言，这个接口是被用来简化OLE DB中暴露数据的方式的，简单OLE DB提供者工具包将为那些需要与IRowset接口交互的数据绑定控件，把OSP映射为一个完整的IRowset接口。实际上，IE在绑定控件请求一个IRowset接口，而数据源返回的是一个OSP接口时会自动使用这些映射关系。
电子表格组件执行简单OLE DB提供者接口的方式是read/write，当数据源区域变化时，它会发出适当的通知，以便绑定控件得知需要使用新值来刷新它们的内容。(译者注：因为对简单OLE DB提供者不了解，这两段翻译的有些似懂非懂，有没有熟悉这方面知识的朋友帮忙校正一下这两段？)
附录：英文原文
Advanced Functionality of the Spreadsheet Component
Now that we've discussed the more basic features of the Spreadsheet component, let's move on to some of the advanced ones. Most of these do not exist in Excel 2000 since they enable specific functionality that is desirable in a component. Those that do exist in Excel 2000 have been enhanced to enable some new capabilities.
Property Binding and Real-Time Data
One of the most curious new features in the Spreadsheet component is property binding, which refers to the control's ability to use properties and methods of other objects on the same web page as cell values or formula arguments. The Spreadsheet control uses a standard COM mechanism for binding to properties, and when the source object notifies the control that the property's value has changed, the control automatically retrieves the new value and recalculates any dependent cells.
For example, if you develop a component that exposes properties and methods returning the last sale price for a given stock symbol, you can use the Spreadsheet control to view this information and see it change when the value updates. If other parts of the spreadsheet—such as the current user's portfolio information—refer to that last sale value, the Spreadsheet control also will recalculate those cells when the value changes.
To set up property binding, enter a function like this into a cell:
=document.StockTicker.Quote("msft").LastSale
Whenever the first part of the formula contains =document., the Spreadsheet component knows that a property binding follows. The Spreadsheet control will expect the next part of the expression to be the ID of another element on the page, and the rest of the expression will resolve to a property of that element or of another element returned by a method. You can use a cell reference as an argument in a method call, and the Spreadsheet control will make sure to pass the real value to the method.
The object the cell is bound to can either be another COM object or any HTML element on the page, such as an edit box or a drop-down list. This allows you to include other data on the page in the recalculation model without having to write script to push the HTML element's value into a spreadsheet cell.
The property binding mechanism is often discussed in the context of real-time data feeds because it includes two necessary features for working with live data feeds:
When notified that the property has changed, the Spreadsheet component updates the cell immediately, rather than on a fixed polling interval.
The Spreadsheet component continues to listen for new values and updates cells even while the user is editing other cells or invoking commands. A common complaint about Excel's DDE links mechanism is that it doesn't do this, so we made sure to avoid the same mistake in the Spreadsheet control's property binding feature.
Can a Property Binding Refer to the Spreadsheet Component Itself?
Adventurous readers are probably thinking wildly about the possibilities of binding cells in the Spreadsheet component back to the component itself or perhaps to another Spreadsheet component. On the surface, it might seem that you can support the dependence of cells in one spreadsheet upon cells in another spreadsheet simply by using this mechanism.
But alas, this is not possible. The Spreadsheet control itself prohibits such support because the reentrancy and circular reference possibilities are absolutely frightening. The Spreadsheet control only knows it is bound to another object—not to itself or another Spreadsheet control—so it cannot check that a reference doesn't create a circular dependency that would hang the recalculation chain.
To have cells dependent on cells in another Spreadsheet component, you must write code in the other component that responds to the Change event and pushes new values into the dependent cells.
Of course, keep in mind that the Spreadsheet component's ability to process property change notifications is entirely dependent on how long it takes to recalculate the current model. For small and medium-sized models, this usually isn't a concern since recalculation takes one second or less—far faster than most people want to see new data flash before their eyes. However, if the model is quite large, the Spreadsheet control can only process new values as fast as it can recalculate, which might be slower than the rate at which new values arrive.
I'll cover property binding in greater detail in Chapter 10, where you'll see how to build a stock ticker control in Visual Basic that feeds real-time quotes to a stock portfolio spreadsheet.
Function Add-Ins
As in Excel, developers can use function add-ins to incorporate new functions into the Spreadsheet component. Unlike the add-ins that follow Excel's proprietary XLL model, function add-ins for the Spreadsheet component are created as COM objects. Any method exposed by such an object is added as a potential function that you can use in formulas, just as you would do using the intrinsic Excel functions.
For example, if you develop a COM object with a method called SumTopN that takes a range of values and returns the sum of the top N numbers, you can make that function available in the Spreadsheet component by executing the following code, most likely in the Window_onLoad event:
Spreadsheet1.AddIn MyObject
The MyObject variable should point to an instance of the custom function object. To ensure that your object is available, use an <object> tag on the page and pass the value of the id attribute to the Spreadsheet control's AddIn method, like so:
<object classid="clsid:0002E510-0000-0000-C000-000000000046"
id=Spreadsheet1>
</object>
<object classid="clsid:ClsidOfYourObject"
codebase=PathToCABfileOfYourObject id=MyObject>
<script language=VBScript>
Spreadsheet1.AddIn MyObject.Object
</script>
The codebase attribute in the <object> tag tells Internet Explorer where to install the object from if the object referenced by the class ID isn't on the client's machine. To learn more about the codebase attribute, see the Internet Explorer and DHTML topics in the Microsoft Developer Network (MSDN) Libraries.
Using the Object property is necessary only in Internet Explorer or other containers that wrap objects with a different interface. In the HTML file we just examined, MyObject actually refers to a COM object type known as object, which represents the <object> tag, not the actual COM object that the tag created. The Object property returns the pointer to the real COM object.
In Visual Basic, you would still call the AddIn method but would pass a variable that refers to an instance of a class you created. For example:
Dim MyAddIn As New FunctionLib
Spreadsheet1.AddIn MyAddIn
In C++, the technique is exactly the same, but you would of course use the coCreateInstance function and pass a reference to the IDispatch interface of your object to the AddIn method.
The Spreadsheet component actually uses this add-in mechanism for loading the functions used less often. Not all the Spreadsheet control's functions are implemented in the primary Office Web Components DLL file, named Msowc.dll. The ones used less often are actually implemented in Msowcf.dll (the "f" stands for extended function library); the Spreadsheet component automatically adds them to the add-in list the first time you use them. The extended functions are implemented as COM objects with a series of methods, one for each function exposed.
You might be wondering if function add-ins are any different than the property binding mechanism described earlier. The answer is yes. Property bindings listen for source notification that a value has changed, whereas function add-ins are called only when an input to the function (or a cell affecting the input) changes. This is essentially the difference between a push model and a pull model: Property binding is like a push model; it can push new values into the Spreadsheet control whenever it deems this necessary. Function add-ins, on the other hand, have no communication channel back to the Spreadsheet component; the Spreadsheet component determines when it needs to call the function add-in to calculate a new value.
The line between push and pull can get fuzzy sometimes, especially when you consider the strange and rather interesting side effect caused by Internet Explorer's ability to expose script functions on the page as an object. All the various <script> blocks on your page are exposed as a DOM object called Script, and each function or subroutine defined in those <script> blocks is exposed as a method of that Script object. This means you can use script functions on your page as functions in your spreadsheet, but the mechanism you use to do this is more akin to property binding than to using function add-ins.
For example, suppose you have a <script> block on the page like this:
<script language="VBScript">
Function VBDateAdd(interval, number, date)
On Error Resume Next
VBDateAdd = DateAdd(interval, number, date)
End Function
</script>
The Spreadsheet component doesn't have terrific date manipulation functions. However, VBScript offers the flexible DateAdd function that lets you add (or subtract, by using a negative number for the interval argument) any number of intervals to a given date. To enable the Spreadsheet component to use this function, the previous <script> block defines a function called VBDateAdd that returns the results of the VBScript function DateAdd. To use VBDateAdd in your spreadsheet, enter the following formula into a cell in which you want the result placed:
=document.script.VBDateAdd(B1, B2, B3)
This will use the current values in cells B1, B2, and B3 for the interval, number, and date arguments, respectively. The formula is put into the dependency chain just as any other formula, and any time one of those input cells changes, the Spreadsheet component will call this function, passing the new input values and displaying the new result.
Using script functions has its advantages and disadvantages. Scripts are executed in an interpreted manner, meaning they will usually be slower than compiled code. Scripts also are limited to the capabilities of the scripting language and are limited in their interaction with the client computer and the network because of security restrictions. However, compiled objects require downloading and installation on the client machine, which might not be allowed in certain organizations and can have a potentially negative impact on the client machine if the object wasn't implemented and tested well. By definition, scripts in web pages are "safe," and since they are interpreted on the fly, they don't require additional files to be downloaded or installed.
Of course, script functions are only relevant when the container is Internet Explorer. For example, if you are using the Spreadsheet component in a Visual Basic form, you can still use function add-ins, but there is no concept of script blocks in a page. Note that the AddIn method takes a reference to a COM object, so if you are using Visual Basic for your application, you can use any public class in the same project as a function add-in object. Just create an instance of it, and pass a reference to the Spreadsheet control's AddIn method.
Viewable Range and AutoFit
Spreadsheet applications often display just a portion of the spreadsheet surface rather than show all the columns and rows. For example, a timesheet application will show enough columns and rows so that the user can enter his or her work times without having to see numerous blank columns and rows surrounding the data. The Spreadsheet control lets you do this through its ViewableRange property, which you can also set through the Property Toolbox at design time.
The viewable range defines how many columns and rows the spreadsheet displays. The default value is to show all columns and rows, but you can change this—either in script or in the Property Toolbox at design time—to any valid range reference. For example, setting the range to A1:D6 makes the spreadsheet show only four columns and six rows. The rest of the spreadsheet appears as a blank gray area; the user cannot select or move anywhere outside the viewable range. The cells outside the viewable range still exist and can be referenced in script code, but the user cannot see or interact with them. This is an excellent way to hide lookup tables or intermediate calculated values that you don't want your users to see.
The viewable range can be adjusted dynamically through code, so you can adjust the viewable range in reaction to other user events. Also note that the viewable range has the same auto-adjustment characteristics that normal ranges do—if the developer or user inserts a column or row inside the viewable range, the viewable range will extend by one column or row. Also, remember that the ViewableRange property is a String rather than a Range object, so if you want to retrieve a Range object for the entire viewable range, you need to write code like this:
Set rngViewable = Spreadsheet1.Range(Spreadsheet1.ViewableRange)
Setting the viewable range does not automatically alter the size of the Spreadsheet control within its container. However, once you have defined a viewable range, you can set the AutoFit property to True, which will cause the spreadsheet to resize itself so that it can show the entire viewable range without scroll bars. If the viewable range is smaller than the current size, it will shrink; if it's larger, it will grow.
Two other properties govern how large the Spreadsheet control can grow so that it does not become ridiculously huge. The MaxHeight and MaxWidth properties determine the height and width thresholds for the spreadsheet. If the viewable range is larger than the maximum height and width allow for, the spreadsheet will show the internal scroll bars so that the user can navigate over the entire range.
The MaxHeight and MaxWidth properties can be set to percentages when running in Internet Explorer. For example, if MaxWidth is set to 80 percent, the spreadsheet will allow itself to grow to 80 percent of the size of the containing element. If the spreadsheet is inside the <body> element, for example, the spreadsheet can grow to 80 percent of the document width. If it is inside a table cell, the spreadsheet can grow to 80 percent of the table cell's width. This percent sizing capability also applies to the normal Width and Height properties of Internet Explorer. Such a capability can be extremely useful in the world of dynamic HTML layout and can be used to make sure the spreadsheet looks correct on the page regardless of window size or monitor resolution.
The Spreadsheet Component as a Data Source
One of the most common requests heard in the Excel group is for Excel to be an OLE DB provider for data contained in an XLS file. When we built the Spreadsheet component, we knew that we had to provide a way for the Chart component to retrieve ranges of data from the Spreadsheet component to chart them. The Chart component also had to know when those values changed so that it could update the chart. Happily, these requirements match those for OLE DB data binding, so we decided to make the Spreadsheet control a real OLE DB data source. I'll discuss exactly what this means from a technical perspective in a moment. But first, an example will help clarify this point.
In Internet Explorer, you can data-bind a number of HTML elements to any control that is a valid data source. Internet Explorer 5 has the ability to bind elements to a particular data member within a data source in cases where the data source has one or more data members. For example, if you have a data source control on the page named tdcComposers, you can bind an HTML table to it using the following HTML fragment:
<table datasrc=#tdcComposers>
<thead>
<tr style="font-weight:bold">
<td>First</td><td>Last</td>
<td>Birth</td><td>Death</td><td>Origin</td>
</tr>
</thead>
<tbody>
<tr>
<td><div datafld="compsr_first"></div></td>
<td><div datafld="compsr_last"></div></td>
<td><div datafld="compsr_birth"></div></td>
<td><div datafld="compsr_death"></div></td>
<td><div datafld="origin"></div></td>
</tr>
</tbody>
</table>
In the same manner, you can bind an HTML table to the contents of a range in the Spreadsheet component. The following HTML fragment is taken from the file SpreadsheetDS.htm on the companion CD:
<table datasrc=#Spreadsheet1.A2:D7 border=1>
<thead>
<tr>
<th>Salesperson</th>
<th>FY98 Sales</th>
<th>Projected Growth</th>
<th>Est. FY99 Sales</th>
</tr>
</thead>
<tbody>
<tr>
<td><div datafld="A"></div></td>
<td><div datafld="B"></div></td>
<td><div datafld="C"></div></td>
<td><div datafld="D"></div></td>
</tr>
</tbody>
</table>
The Spreadsheet control implements the IDataSource interface, which is the standard data source interface defined and supported by Internet Explorer and Visual Basic version 6 and later. These containers consider any control implementing this interface to be a valid source of data to other data-bound controls on the page or form. A data source control can expose any number of data members, each of which is identified by a string and returns an OLE DB Rowset. The Spreadsheet control exposes a nearly limitless number of data members because any valid range reference is a valid data member. For example, the previous HTML fragment asks for a data member named A2:D7 and gets a Rowset of two columns and seven rows. The magic syntax in Internet Explorer 5 for specifying the data member is <ID of Data Source Control>.<Data Member Name>. The ID is preceded by a hash symbol (#) to indicate that the source is a control on the same page. For the Spreadsheet component, any valid range reference can be passed for the data member name.
The Spreadsheet component also implements the OLE DB Simple Provider interface, sometimes known as OSP, when exposing ranges of data. This interface was defined to make exposing data in OLE DB a tad easier than implementing IRowset and the other interfaces of OLE DB, and the OLE DB Simple Provider toolkit provider will map OSP into a full IRowset interface for data-bound controls that want to work with the IRowset interface. In fact, Internet Explorer will automatically use these mappers when the bound control requests an IRowset interface but the source returns an OSP interface.
The Spreadsheet component's implementation of the OLE DB Simple Provider interface is read/write, and it will raise the appropriate notifications when data in the source range changes so that bound controls know to refresh their contents with new values.