Advanced Functionality of the Spreadsheet Component

[Previous] [Next]

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.