第二章第二節(jié) 電子表格組件的高級功能

我們已經(jīng)討論了電子表格組件的大部分基本功能，現(xiàn)在讓我們轉(zhuǎn)向一些高級功能。大部分的這些高級功能Excel2000都不包含，因為這些是組件專門需要的特殊功能。而那些Excel2000中存在的功能，在電子表格組件中也被增強，使得可以提供一些新的功能。

屬性綁定和實時數(shù)據(jù)
“屬性綁定”是電子表格組件中最新奇的新功能之一，它是指控件能夠?qū)⑼�一個web頁面上其它對象的屬性和方法用作單元值或公式參數(shù)的能力。電子表格控件使用標準的COM機制來實現(xiàn)綁定到屬性，并在數(shù)據(jù)源對象告知屬性值已經(jīng)改變時，自動接收新值，并重新計算相關聯(lián)的單元。

例如，如果您開發(fā)了一個組件，它公開了一些屬性和方法以返回一個給定股票代號的最后銷售價格，您就可以使用電子表格控件來查看這個信息，并在價格更新時觀察到它的變化。如果電子表格的其它部分――例如當前用戶的組合信息(譯者注：金融專業(yè)的術語。)――引用了這個最后銷售價格值，那么電子表格組件也會在值改變時重新計算這些單元。

可以象下面這樣在單元中輸入一個函數(shù)來建立屬性綁定：
=document.StockTicker.Quote("msft").LastSale


只要公式以“=document.”開始，電子表格組件就認為緊接著的是一個屬性綁定。它會認為表達式的下一部分將是頁面上另一個元素的ID，而表達式的其余部分將會該元素的一個屬性，或是由一個方法返回的另一元素的一個屬性。您可以在方法調(diào)用中使用單元引用作為參數(shù)，而電子表格控件必然會將真實的值傳遞給方法。

單元綁定到的對象既可以是另一個COM對象，也可以是頁面上的任何HTML元素，例如一個文本框或一個下拉列表。這樣您就可以直接在重算模型中包含頁面上其它的數(shù)據(jù)，而不必寫腳本來將HTML元素的值輸入到電子表格單元中。

屬性綁定機制經(jīng)常在提供實時數(shù)據(jù)的環(huán)境中被提到，因為它包含了實現(xiàn)提供動態(tài)數(shù)據(jù)的兩個必須的特性。
l 當?shù)弥獙傩员恍薷臅r，電子表格組件立刻更新單元，而不會有一個固定的輪詢間隔。
l “即使用戶在編輯其它單元，或執(zhí)行命令時”，電子表格組件依然會繼續(xù)監(jiān)聽新值并更新單元。人們對Excel的DDE鏈接機制的一個普遍抱怨就是它不能完成這個功能，因此我們確信應該在電子表格控件的屬性綁定特性中避免同樣的錯誤。

屬性綁定可以指向電子表格組件自身嗎？
大膽的讀者可能已經(jīng)在思考將電子表格組件中的單元綁定到組件本身，或綁定到另一個電子表格組件上的可能性。(thinking wildly)從表面上看，似乎可以通過這個機制，輕松的支持關聯(lián)不同的電子表格上的單元。
但是，唉，這是不可能的。電子表格控件自身禁止了這種支持，因為可能造成的重入引用和循環(huán)引用是相當恐怖的。電子表格控件只知道它被綁定到另一個對象上(不是自身或另一個電子表格控件)――因此它無法確保引用沒有產(chǎn)生會掛起重算鏈的一個依賴循環(huán)。

為了實現(xiàn)將當前電子表格組件中的單元關聯(lián)到另一個電子表格組件的單元上，您必須在另一個組件中通過代碼來響應Change事件，并將新值寫入到相關聯(lián)的單元中。
當然，請記住電子表格組件處理屬性變化通知的能力完全依賴于它重算當前模型的時間。對于中小型的模型來說，通常不存在問題，因為重算只會花費一秒或者更少的時間――比起大多數(shù)人所能容忍的數(shù)據(jù)更新時間要快的多。然而，如果模型相當巨大，電子表格控件也只能盡快重算來提供新數(shù)值，這可能就比新值到達的速度慢多了。

在第十章將講述關于屬性綁定的更多細節(jié)，在該章中，您將看到如何在Visual Basic中建立一個股票行情控制系統(tǒng)，為股票組合表格提供實時的報價。


函數(shù)插件
和在Excel中一樣，開發(fā)者可以使用函數(shù)插件，將新功能添加到電子表格組件中。與符合Excel私有的XLL模型的插件不同，電子表格組件的函數(shù)插件是以COM對象的方式被創(chuàng)建的。這種對象所公布的任何方法都以內(nèi)部函數(shù)的形式被添加(譯者注：什么是potential function？)，這樣您就可以在公式中象使用那些Excel內(nèi)部函數(shù)一樣使用它。
例如，如果您開發(fā)了一個COM對象，包含了一個叫做SumTopN的方法，該方法根據(jù)傳入的一列數(shù)值返回前N位數(shù)值的和，那么您可以在電子表格組件中通過下列代碼來使用這個函數(shù)，就像在Window_onLoad事件中一樣：
Spreadsheet1.AddIn MyObject


MyObject變量應該指向自定義函數(shù)對象的一個實例。為了保證您的對象可用，在頁面上使用<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>標簽聲明)，而不是標簽創(chuàng)建的實際COM對象。Object屬性返回一個指向?qū)嶋H的COM對象的指針。
在Visual Basic中，您還是需要調(diào)用AddIn方法，但是需要傳遞一個指向您創(chuàng)建的類的實例的變量。例如：
Dim MyAddIn As New FunctionLib
Spreadsheet1.AddIn MyAddIn



在C++中，技巧是一樣的，不過您應該使用coCreateInstance函數(shù)并向AddIn方法傳遞指向您的對象的IDispatch接口的引用。

電子表格組件實際上使用這種插件機制來裝載不常使用的函數(shù)。Owc主要的dll文件Msowc.dll并沒有實現(xiàn)電子表格控件的所有函數(shù)，那些不常使用的函數(shù)實際上是由Msowcf.dll來實現(xiàn)的(“f”代表擴展函數(shù)庫)；當?shù)谝淮问褂盟鼈儠r電子表格組件自動將他們添加到插件列表中。擴展函數(shù)通過包含一系列方法的COM對象實現(xiàn)，其中，每一個方法對應一個公布的函數(shù)。

您可能會懷疑是否函數(shù)插件與之前所描述的屬性綁定機制有所不同。答案是肯定的。屬性綁定監(jiān)聽值改變時數(shù)據(jù)源的通知信息，而函數(shù)插件僅在函數(shù)的輸入變化(或影響輸入的單元變化)時被調(diào)用。這基本上是推模式和拉模式的區(qū)別：屬性綁定類似推模式；當它認為必要時會將新值推入電子表格控件中。而另一方面，函數(shù)插件沒有到電子表格組件的通訊通道；電子表格組件決定什么時候需要調(diào)用插件函數(shù)來計算新值。

有時推和拉的界限是模糊的，尤其是當您意識到IE將頁面上的腳本函數(shù)以對象的方式暴露出來，這種能力所造成的奇怪而相當有趣的副效果時。您頁面上的所有各種<script>塊都以被名為Script的DOM對象公布出來，在<script>塊中定義的每一個函數(shù)或子方法都被當作Script對象的一個方法。這就意味這您可以將您頁面上的腳本函數(shù)用作您電子表格中的函數(shù)，不過您來完成這個功能的機制更像是屬性綁定，而不是插件函數(shù)。

例如，假設您在頁面上有這樣一個<script>塊：
<script language="VBScript">
Function VBDateAdd(interval, number, date)
On Error Resume Next
VBDateAdd = DateAdd(interval, number, date)
End Function
</script>


電子表格組件不包括復雜的日期操作函數(shù)。不過，VBScript提供了靈活的DateAdd函數(shù)，允許您在一個給定的日期上加(或減，或使用一個負數(shù)作為一個內(nèi)部的參數(shù))任何數(shù)目的時間間隔。為了使電子表格組件能夠使用這個函數(shù)。前面的<script>塊定義了一個名為VBDateAdd的函數(shù)，返回VBScript函數(shù)DataAdd的結(jié)果。為了在您的電子表格中使用VBDateAdd函數(shù)，在您需要在顯示結(jié)果的單元中輸入下列的公式：
=document.script.VBDateAdd(B1, B2, B3)


這個公式將單元B1,B2,B3的當前值分別用作間隔，數(shù)目和日期的參數(shù)值。這個公式和其它公式一樣，被放置在依賴鏈中，當那些輸入的單元改變時，電子表格組件會調(diào)用這個函數(shù)，傳入新的輸入值并顯示新的結(jié)果。

使用腳本函數(shù)有它的優(yōu)點和不足。腳本是以解釋方式來執(zhí)行的，這意味著他們將比編譯的代碼要慢一些。腳本也受限于腳本語言的功能和與客戶端機器的交互能力，以及網(wǎng)絡的限制(因為安全限制。)不過，編譯的對象需要下載和在客戶端機器上進行安裝，在某些機構(gòu)中可能不允許這樣，還有，如果對象編寫的不完善，測試不充分，可能會給客戶端機器帶來潛在的負面影響。Web頁面中的腳本定義上是”安全”的，并且因為他們是實時解釋執(zhí)行，因此他們不需要下載和安裝額外的文件。

當然，只有在容器是IE時才會談到腳本函數(shù)。例如，如果您在vb窗體中使用電子表格組件，您仍然可以使用函數(shù)插件，不過這里沒有頁面上的腳本塊的概念。請注意AddIn方法傳入一個COM對象的引用，因此如果您在您的應用程序中使用vb，您可以使用工程中的任何公共類作為一個函數(shù)插件的對象。只需創(chuàng)建該類的一個實例，然后將它的引用傳給電子表格控件的AddIn方法。

可視區(qū)域和自動調(diào)整
電子表格應用程序經(jīng)常只需顯示電子表格界面的一部分，而不是顯示所有的行和列。例如，時間表應用程序應該顯示足夠的行和列，以便用戶在輸入他(她)的工作時間時不必看到數(shù)據(jù)周圍大量的空白行、列。電子表格控件通過ViewableRange屬性來完成這個任務，您也可以在設計階段通過屬性工具箱來設置它。

“可視區(qū)域”定義了電子表格要顯示多少行、列。缺省是顯示所有的行、列，不過您既可以在腳本中改變它，也可以在設計階段通過屬性工具箱中改變它，可將它改變?yōu)槿魏斡行У膮^(qū)域引用。例如，將區(qū)域設置為A1:D6，使電子表格只顯示四行六列。而其它部分則顯示為一個空白的灰色區(qū)域；用戶既不能在可視區(qū)域之外選擇，也不能移動到可視區(qū)域之外。可視區(qū)域之外的單元仍然存在，并可以在腳本代碼中被引用，但是用戶看不到這些單元，也不能和它們交互。如果需要隱藏對照表(譯者注：也就是我們常說的代碼表，例如：數(shù)據(jù)庫多個表中都有單位ID列，那么單位001,002,……具體代表什么單位，就在單位代碼表，既單位對照表中定義。)或不希望用戶看到的中間計算值，這是一個極好的方法。

可以通過代碼動態(tài)調(diào)整可視區(qū)域，因此您可以調(diào)整可視區(qū)域來響應其它用戶的事件。還要注意可視區(qū)域具有和普通區(qū)域一樣的自動調(diào)整特性――如果開發(fā)者或用戶在可視區(qū)域中插入了一行或一列，可視區(qū)域就會增加一行或一列。例外，可視區(qū)域?qū)傩允荢tring類型，而不是Range對象類型，因此如果需要獲得整個可視區(qū)域的區(qū)域?qū)ο�，您需要這樣寫代碼：
Set rngViewable = Spreadsheet1.Range(Spreadsheet1.ViewableRange)


設置可視區(qū)域不會自動調(diào)整容器中的電子表格控件的尺寸。不過，只要您定義了一個可視區(qū)域，就可將AutoFit屬性設置為True，這會使電子表格自動調(diào)整大小，以便不需滾動條就能顯示整個可視區(qū)域。如果可視區(qū)域比控件的當前尺寸小，控件將縮短；如果比它大，它將擴大。
另外兩個屬性，MaxHeight和MaxWidth，管理電子表格控件增大的限度，可以使控件不會變的過大。這兩個屬性定義了電子表格高度和寬度的極限。如果可視區(qū)域超過了允許的最大高度和寬度，電子表格將顯示內(nèi)部的滾動條，使得用戶可以瀏覽整個區(qū)域。
當控件在IE中運行時，可以將MaxHeight和MaxWidth屬性設置為百分比。例如，如果MaxWidth設置為80%，電子表格將允許自身擴大到容器元素寬度80%的大小。例如，如果電子表格被放在<body>元素中，電子表格將允許擴大到頁面寬度的80%。如果它被放置在一個table單元中，電子表格可以擴大到table單元寬度的80%。這種根據(jù)百分比來進行縮放的能力也應用在IE中標準的Width和Height屬性中。這種能力在動態(tài)HTML布局的環(huán)境中非常有用，并且可以使電子表格能夠在頁面上正確的布局，而不會受窗口大小和顯示器分辨率的影響。
將電子表格組件用作數(shù)據(jù)源
Excel社團中最普遍的需求之一就是希望Excel能夠成為XLS文件中的數(shù)據(jù)的OLE DB提供者。當我們開發(fā)電子表格組件時，我們意識到我們必須提供一種方法，使得圖表組件能夠從電子表格組件中獲得一系列的數(shù)據(jù)，以便使用圖表來表示它們。圖表組件也必須能夠感知這些數(shù)據(jù)的變化，以便它能夠更新圖表。令人高興的是，這些需求正是OLE DB數(shù)據(jù)綁定所能提供的，因此我們決定將電子表格控件設計成一個真正的OLE DB數(shù)據(jù)源。我會馬上從技術的角度詳細討論它的意義。不過，首先我將舉一個例子以便闡明這一點。
在IE中，您可以將眾多的HTML元素數(shù)據(jù)綁定到任何是有效數(shù)據(jù)源的控件上。IE5能夠?qū)⒃�素綁定到�?shù)據(jù)源中某個特定的數(shù)據(jù)成員上，以適應數(shù)據(jù)源包含一個或多個數(shù)據(jù)成員的情況。例如，如果在頁面上有一個名為tdcComposers的數(shù)據(jù)源控件，您可以使用下面的HTML片斷將一個html表格綁定到數(shù)據(jù)源上。
<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>



同樣，您可以將一個HTML表格綁定到電子表格組件中一個區(qū)域的內(nèi)容上。下面的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>



電子表格控件實現(xiàn)了IDataSource接口，這是IE和VB6及后續(xù)版本所定義和支持的標準數(shù)據(jù)源接口。這些容器將任何實現(xiàn)了這個接口的控件看作頁面或窗體上其它數(shù)據(jù)綁定控件的一個有效的數(shù)據(jù)源，一個數(shù)據(jù)源控件可以暴露任意數(shù)量的數(shù)據(jù)成員，每個數(shù)據(jù)成員通過一個字符串來標記，并返回一個OLE DB行集合。電子表格控件幾乎暴露了無數(shù)的數(shù)據(jù)成員，因為任何有效的區(qū)域引用都是一個有效的數(shù)據(jù)成員。例如，前面的HTML片斷要求一個名為A2:D7的數(shù)據(jù)成員，并得到了一個兩行七列的行集合。IE5中指定數(shù)據(jù)成員的神奇的語法是<數(shù)據(jù)源控件名稱>.<數(shù)據(jù)成員名稱>。ID前的hash符號(#)顯示數(shù)據(jù)源是當前頁面上的一個控件。對于電子表格組件，任何有效的區(qū)域引用都能夠作為數(shù)據(jù)成員名稱。
當暴露整個區(qū)域的數(shù)據(jù)時，電子表格組件也實現(xiàn)了簡單OLE DB提供者接口(有時被稱為OSP)。相對于實現(xiàn)IRowset和其它OLE DB接口而言，這個接口是被用來簡化OLE DB中暴露數(shù)據(jù)的方式的，簡單OLE DB提供者工具包將為那些需要與IRowset接口交互的數(shù)據(jù)綁定控件，把OSP映射為一個完整的IRowset接口。實際上，IE在綁定控件請求一個IRowset接口，而數(shù)據(jù)源返回的是一個OSP接口時會自動使用這些映射關系。
電子表格組件執(zhí)行簡單OLE DB提供者接口的方式是read/write，當數(shù)據(jù)源區(qū)域變化時，它會發(fā)出適當?shù)耐ㄖ�，以便綁定控件得知需要使用新值來刷新它們的�?nèi)容。(譯者注：因為對簡單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>



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>



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.