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:

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.

Sorting and Filtering

While you are reading this section, you might find it useful to open the Samples\ Chap02\SortFilterExample.htm file on the companion CD. The code shown in this section and the scenario described stem from that file.

The Spreadsheet component supports the basic sorting and filtering functionality found in Excel and exposes it through the programming model and the user interface. However, the sort and filter user interface in the Spreadsheet control is somewhat improved over that in Excel. Let's look at an example.

Adventures in DHTML

The Property Toolbox is an incredible piece of work created by two of OWC's talented developers, Eric Matteson and Cesar Alvarez. The Property Toolbox is also proof that attempting to emulate Office user interface conventions in DHTML is just asking for trouble. Early on, we decided that we should make the Property Toolbox look as much like the standard Office user interface as possible, and Eric and Cesar faithfully spent many months twisting and contorting HTML and Internet Explorer to make it comply . Most people can't believe that the result is actually in HTML. However, the jury's still out on whether emulating the Office user interface in HTML makes it any easier to use, considering that the average person can use web sites quite effectively but is still befuddled by many of the advanced dialog boxes in the Office applications.

I think everyone would agree that it would have been much easier to follow the user interface conventions made popular on the Web and present a new kind of command interface born and bred in HTML. For developers intending to use the Office Web Components in web pages, my advice is not to spend your time trying to wrangle HTML into a traditional Microsoft Windows application user interface. Instead, use HTML's simplicity and dynamic layout strengths to develop a more natural, easy-to-use interface for your application.

Suppose you have developed a spreadsheet that lists your current product line, showing each product's unit price, quantity in stock, and quantity on order, as well as a calculated column showing a potential worth given a sell-through rate. Now the user wants to sort the list of products by their potential worth in descending order. Through the Spreadsheet component user interface, the user simply selects the range to sort (or selects any cell in the range) and clicks the Sort Descending toolbar button. When this button is clicked, a menu appears below it that does not appear in Excel, as Figure 2-2 depicts.

Figure 2-2. The Spreadsheet component user interface in action.

One of the common problems users encounter while sorting Excel ranges is selecting the range to sort and the column to sort by. The Spreadsheet component lets users easily select the range to sort, and it lets them choose the column to sort by from a list of column names that appears when the Sort Ascending Or Sort Descending toolbar button is clicked. The sorting functionality is also available through the programming model via the Sort method of the Range object. This allows the developer to easily enable list sorting when the user clicks or double-clicks a column heading.

You might notice that the Spreadsheet component lets you sort the list only one column at a time. Excel offers a Sort dialog box that lets you sort by up to three keys at once (for example, sort by category, then by shipper, and then by potential worth). The Spreadsheet component has no user interface for doing this, but the underlying engine does support it. To emulate multicolumn sorting, you can use the following routine:

'--------------------------------------------------------------------------
' MultiColumnSort
' Purpose: Sorts the spreadsheet by many columns at once
' In:      References to the spreadsheet and range to sort,
'          an array of column numbers on which to sort,
'          and an array (same size) of direction indicators
' Out:     Nothing (performs the sort)
'
Sub MultiColumnSort(Spreadsheet, Range, Columns, Directions)
    ' Start an undo unit so that this can undo as a whole unit of work
    Spreadsheet.BeginUndo()

    ' Turn ScreenUpdating off so that the spreadsheet does not redraw
    ' while we are resetting filters, sorting, and reapplying filters
    Spreadsheet.ScreenUpdating = False

    ' Now loop over the Columns and Directions arrays backwards,
    ' which will give us the effect we want
    For ct = ubound(Columns) To lbound(Columns) Step -1
        ' 0 is a guess for column headings
        Range.Sort Columns(ct), Directions(ct), 0
    Next 'ct
    
    ' Turn ScreenUpdating back on so that the spreadsheet redraws
    Spreadsheet.ScreenUpdating = True
    
    ' End the undo unit
    Spreadsheet.EndUndo()    
    
End Sub 'MultiColumnSort()

The trick to multicolumn sorts is to actually perform the sorts in the opposite order they're defined in. If you want to sort first by category and then by shipper, the routine first sorts the list by shipper and again by category. When the spreadsheet sorts a list by a new column, the previous ordering in another column is preserved within each item in the new column. The routine we just looked at accepts a range to sort, an array of column numbers, and an array of direction values (ascending or descending). The routine walks backwards along the two arrays, giving the effect of a multicolumn sort. Note that it also uses the BeginUndo and EndUndo methods to make all the sort operations part of one undo block so that they're undone together when the user chooses the Undo command.

The Spreadsheet component also sports a new AutoFilter user interface. The filtering functionality is similar to that found in Excel, but the AutoFilter drop-down lists in the user interface are a little different. Suppose that in the list of products we examined in Figure 2-2 you want to filter out some product categories to see how it would affect the products with high potential worth. The developer or user could turn on AutoFilter, click the AutoFilter arrow for the Category column, and see the screen shown in Figure 2-3.

Figure 2-3. The Spreadsheet component's AutoFilter user interface.

In Excel, you can choose a single item quite easily; however, selecting more than one item requires using the Custom AutoFilter dialog box, which can be quite arduous when you simply want to exclude four or five items. In the Spreadsheet component, the AutoFilter drop-down list has check boxes next to each item, as well as a Show All item at the top that lets you quickly toggle all items between the checked and unchecked states.

The astute reader will notice that the AutoFilter drop-down lists do not include two of the useful settings found in Excel. For instance, you won't find a Top 10 item, which allows you to quickly filter for the top 10 (or any other number of) items. Nor will you find a Custom item, which you can use to perform more complex filtering than simple include and exclude filters allow. Unfortunately, these higher-level functions aren't built in to the Spreadsheet component yet. However, you can easily emulate them by making a few calls to the Spreadsheet control's programming model.

To emulate top N filtering, you can use the following routine:

'--------------------------------------------------------------------------
' TopNFilter
' Purpose: Filters for the top N items in the list given a column number
' In:      References to the spreadsheet and range, column number, 
'          number of rows, and direction value that indicates
'          top N or bottom N filtering
' Out:     Nothing (performs the top N filter)
'
Sub TopNFilter(Spreadsheet, Range, ColumnNum, N, Direction)
    Set c = Spreadsheet.Constants
    Set rngData = Range
    Set af = Spreadsheet.ActiveSheet.AutoFilter
    
    ' Start an undo unit so that this can undo as a whole unit of work
    Spreadsheet.BeginUndo()

    ' Turn ScreenUpdating off so that the spreadsheet does not redraw
    ' while we are resetting filters, sorting, and reapplying filters    
    Spreadsheet.ScreenUpdating = False
    
    ' Clear any existing filters
    ClearFilters Spreadsheet

    ' Sort the list in the data range by the column number
    If LCase(Direction) = "bottom" Then
        rngData.Sort ColumnNum, c.ssAscending, c.ssNo
    Else
        rngData.Sort ColumnNum, c.ssDescending, c.ssNo
    End If
    
    ' Top N can actually include more than N rows if the N+1, N+2,
    ' and so on rows have the same value as the Nth row.
    ' Go to the N+1 row and see if it's the same as the Nth.
    ' Loop until there is a different value.
    vNValue = rngData.Cells(N,ColumnNum).Value
    
    While rngData.Cells(N+1,ColumnNum).Value = vNValue
        N = N + 1
    Wend
    
    ' N is now set to the number of rows we want to include in the filter
    Set fltr = af.Filters(ColumnNum)
    fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude
    
    For ct = 1 To N
        fltr.Criteria.Add(rngData.Cells(ct,ColumnNum).Text)
    Next
    
    ' Finally apply the AutoFilter
    af.Apply

    ' Turn ScreenUpdating back on so that the spreadsheet redraws
    Spreadsheet.ScreenUpdating = True
    
    ' End the undo unit
    Spreadsheet.EndUndo()

End Sub 'TopNFilter()

Top N filtering might seem as easy as sorting and then viewing the first N rows. But true top N filtering can return more than N rows because it really means "include the top N values." If the tenth and eleventh values are identical after the sort, a top 10 filter will return both products since they are among the top 10 values. The previous code can perform both top and bottom N filters by merely changing the sort direction from descending to ascending.

Similarly, you can emulate expression-based filtering using a routine like this:

'--------------------------------------------------------------------------
' ExpressionFilter
' Purpose: Filters a list on a given column using an arbitrary expression
'          that can be evaluated by VBScript
' In:      References to the spreadsheet and range, column number to
'          filter upon, and expression to use for evaluation
' Out:     None (list is filtered)
'
Sub ExpressionFilter(Spreadsheet, Range, ColumnNum, Expression)
    Dim sExp         ' Temporary expression variable
    Dim vValue       ' Temporary value holder
    
    Set c = Spreadsheet.Constants
    Set rngData = Range
    Set af = Spreadsheet.ActiveSheet.AutoFilter

    ' Start an undo unit so that this can undo as a whole unit of work
    Spreadsheet.BeginUndo()

    ' Turn ScreenUpdating off so that the spreadsheet does not redraw
    ' while we are resetting filters, sorting, and reapplying filters
    Spreadsheet.ScreenUpdating = False
    
    ' Clear any existing filters
    ClearFilters Spreadsheet

    ' Get the filter object for the specified column,
    ' and set the filter function to "include"
    Set fltr = af.Filters(ColumnNum)
    fltr.Criteria.FilterFunction = c.ssFilterFunctionInclude

    ' Check whether the expression contains the column value token,
    ' and set a flag if it does
    fValueToken = cbool( _
        instr(1, Expression, g_sValueToken, vbTextCompare) > 0)

    ' Loop over the column values in all the rows
    For Each cell In rngData.Columns(ColumnNum).Cells
        ' Get the current row's value
        vValue = cell.Value
        
        ' If vValue is a string, we need to wrap quotes around it in
        ' case it contains spaces
        If vartype(vValue) = vbString Then    
            vValue = """" & vValue & """"
        End If
        
        ' Build the expression we need to execute by inserting the
        ' current row's value in the right place
        If fValueToken Then
            sExp = "g_fEval = cbool(" & Replace(Expression, _
                g_sValueToken, vValue, 1, -1, vbTextCompare) & ")"
        Else
            sExp = "g_fEval = cbool(" & vValue & " " & Expression & ")"
        End If
        
        ' Execute the expression
        window.execScript sExp, "VBScript"
        
        ' The global g_fEval will now be set to True or False.
        ' If True, the row should be included in the filter.
        If g_fEval Then
            fltr.Criteria.Add cell.Text
        End If
    Next 'ct

    ' Finally apply the AutoFilter
    af.Apply

    ' Turn ScreenUpdating back on so that the spreadsheet redraws
    Spreadsheet.ScreenUpdating = True
    
    ' End the undo unit
    Spreadsheet.EndUndo()

End Sub 'ExpressionFilter()

This routine uses a Document Object Model (DOM) method named execScript to evaluate expressions. (DOM is the programming model exposed to scripting in Internet Explorer.) This method passes the script code in string form to the Active Scripting Engine (in this case, VBScript) for evaluation. The script code stores the result of the expression in a global variable that is then used to determine whether the expression is True or False. If the expression is True, the row is included in the filtered set; if False, the row is not included.

Alternatively, you can evaluate expressions by using the Eval method of the Spreadsheet component's Worksheet object. Eval uses the Spreadsheet component's function libraries and expression evaluator instead of the active scripting engine, meaning it's useful in containers other than Internet Explorer or when you want to let users include spreadsheet functions or range references in the expression. However, the active scripting engine can give you a powerful expression evaluator . Plus, it allows you to use other scripting languages, such as ECMA Script (also known as JavaScript).

Loading Data

Since the Spreadsheet component is not an application, the questions "Where does it get data from?" and "How can I save its data?" are crucial. Yet the answers to these questions are much more complicated than they would be for an application. The good news is that the Spreadsheet component can load and save data in a variety of ways that you can use creatively in your solutions.

Unlike an application, a component does not "own" the storage mechanism used by the container. It is the container's responsibility to save the entire form or document into a persistence mechanism and to reload it. A container usually asks the component to save its current state into a stream or property bag; the container then inserts the data into the middle of the form or document it is saving. Because of this, any component is somewhat at the mercy of its container to make loading and saving data easy. Needless to say, not all containers are created equal; some do a better job than others. Recognizing this, we designed the Spreadsheet component to be flexible in how it loads and saves data. In fact, you've got four ways to get data into the Spreadsheet control:

• Publish a spreadsheet or range from Excel 2000 with interactivity.
• Copy a range from Excel 2000, and paste it into the Spreadsheet control.
• Type data or a new set of formulas directly into the Spreadsheet control while it's in a designer such as Microsoft FrontPage 2000, Microsoft Script Editor, Microsoft Visual InterDev, or Microsoft Visual Basic.
• Specify a URL to load data from, one that returns an HTML document with at least one HTML table in it. Alternatively, you can load Comma Separated Values (CSV) data from a URL.

Publishing from Excel

Publishing a spreadsheet or range from Excel 2000 with interactivity will prompt Excel to create an HTML file with an <object> tag for the Spreadsheet component. Excel copies the content of the selected spreadsheet or range into the HTML page as a parameter to the <object> tag, so once the data is published, it no longer refers to the source spreadsheet. However, you can easily republish content from Excel, and Excel will replace the previous content with the new content, preserving any other changes you made to the surrounding page.

To try publishing from Excel 2000, open your favorite workbook and choose the Save As Web Page command from the File menu. You will then see the dialog box depicted in Figure 2-4.

Figure 2-4. Choose Save As Web Page to display this dialog box.

Choose the Selection option, and check the Add Interactivity check box. When you save, instead of saving the content as static HTML, Excel will write a page containing the Spreadsheet control and a copy of the selected content. For more advanced control over what is published, click the Publish button to display the Publish As Web Page dialog box.

You might encounter an error message when attempting to publish certain spreadsheets to HTML. If the source spreadsheet is protected with a password (using the ToolsProtectionProtect Sheet command), Excel won't let you publish the spreadsheet or any range on it to a web page. Doing so is a breach of security since a web page is only plain text that anyone can open, view, and modify in any text editor. Spreadsheets are often protected with passwords when authors want to keep users from modifying certain parts . For example, a company expense report created in Excel is typically password protected so that employees can't adjust the formulas or validation rules.

Note that you can still use protection to lock most cells and let users change only the cells you've designated as updateable. As long as you don't use a password when protecting the spreadsheet, you can publish or copy the spreadsheet to the Spreadsheet component and all the protection settings will be preserved.

Copying and Pasting

Excel 2000 and the Spreadsheet component are both capable of reading and writing ranges in HTML table format, augmented with extra attributes and XML (Extensible Markup Language) code that conveys information specific to Excel. This means you can copy ranges from Excel 2000 and paste them into the Spreadsheet control and vice versa, which is useful for both authoring spreadsheets and copying data seen in a Spreadsheet control to Excel for further analysis.

You should note a few things when copying and pasting ranges. First, if a cell in the range has a formula referring to a cell that's not in the range, that's on another worksheet, or that's in another workbook, Excel will simply copy the current value for that cell but not the formula. Consider the problem for a moment: If you paste a formula referring to a cell that is outside the range of the copied cell into the Spreadsheet control, the Spreadsheet control has no way to resolve that reference and can't show any data. So any reference to a cell outside the copied range turns into a literal value that equals the value of the reference when it was copied.

Second, a more advanced structure in an Excel spreadsheet, such as a PivotTable, will paste only as literal data cells and not as a PivotTable structure. (In other words, you won't be able to pivot or drill to more detail.) The Office Web Components do, however, include the PivotTable component for performing PivotTable functionality. Charts will not paste at all since the Spreadsheet component isn't capable of hosting other controls or floating images.

Third, spreadsheet protection settings affect not only how the content gets published but also how it gets pasted into the Spreadsheet control. If the source spreadsheet is password protected, the range will still copy and paste, but only literal values will be pasted into the Spreadsheet component. If the spreadsheet is protected, but not with a password, the range will paste normally.

It's also interesting to note that the format of data pasted into the Spreadsheet component is HTML, so any application that can copy an HTML table to the clipboard can be used to get data into the Spreadsheet control. Excel 2000 does embed other information in the clipboard data, such as the formula and full-precision value for a given cell. However, if another application copies an HTML table to the clipboard without this extra information, the table will still be pasted into the Spreadsheet control as literal data with formatting.

Typing Directly into the Spreadsheet Component

There isn't much to say about this approach, except that you will find many of the features that make authoring spreadsheets easy in Excel missing in the Spreadsheet component. Nevertheless, you can still select cells while entering formulas to quickly enter cell references, set formatting information through the Property Toolbox, and hide or show various elements such as the title bar, toolbar, column and row headings, and gridlines. It's much more practical to author your spreadsheets in Excel 2000 and then publish or copy the content into the Spreadsheet component when done. But for simple spreadsheets, you might find it easier to type the model directly into the Spreadsheet control.

As noted earlier, not all containers make it easy to activate controls at design time and interact with them. Typing directly into the Spreadsheet component will work only in containers that allow controls to activate and that persist the control's content when saving.

Using the Spreadsheet Component in Visual Basic and FrontPage

Both Visual Basic and FrontPage make excellent containers for the Spreadsheet component; however, a few tips can help make the editing experience much easier.

In Visual Basic, the Spreadsheet control will be UI active as soon as you click it. This makes it hard to move the control around the form since clicking and dragging will simply select ranges of cells. However, you can move the entire control by clicking the title bar and then dragging the control. If the title bar isn't visible, set the DisplayTitleBar property to True to temporarily show it, and then set it to False to hide it.

When editing the Spreadsheet control in Visual Basic or FrontPage, you should also avoid using the AutoFit property (leave it set to False). Using AutoFit in Visual Basic is dangerous because the Spreadsheet control will resize to fit whatever content is loaded into it without scroll bars. If that content is larger than your Visual Basic form, the control will resize right off the edge of the form. However, if you know that the content will be smaller than the form and that it will never receive larger content, it's safe to use AutoFit.

In FrontPage 2000, the AutoFit behavior will not work in the Normal view; however, it will work correctly in the Preview view or in the web browser.

Loading from a URL

A URL is the most curious yet powerful mechanism for loading data into the Spreadsheet component. Using the Property Toolbox or the programming model, you can tell the Spreadsheet control to load its content by opening a specific URL and loading the first HTML table encountered . Alternatively, you can load CSV data returned from a URL. However, CSV lacks any formatting or formula information, so you will load only raw data values from a CSV stream. The data remains stored in the file the URL points to, and the Spreadsheet control loads the data every time it is initialized . Of course, this URL could easily point to a Microsoft Active Server Pages (ASP) page or a CGI program that generates HTML tables from an enterprise database or another storage system on the fly, enabling you to load the Spreadsheet control with dynamic, up-to-the-minute data.

Note that the Spreadsheet component uses the security mechanism in Internet Explorer to make sure that it doesn't load data from a domain other than the one where the first page originated (depending on your Internet Explorer security settings). This feature prevents malicious developers from sending you a web page containing the Spreadsheet control and the script that ran as the page loaded. If the security feature is not in place, a hacker can use your credentials to load sensitive data and then send that data to another location for his or her examination.

This feature uses the security settings the client specifies in Internet Explorer, so users can turn off cross-domain access warnings if they think the site the page originated from is "trusted." If the site is not in the trusted sites zone or has anything but the lowest security setting, the Spreadsheet control will warn the user before accessing a URL from any domain other than the one the original page came from. If the original page and the URL from which to load come from the same domain, the Spreadsheet component won't show any warnings because the situation is considered safe.

I'll discuss security a bit more in the chapters ahead. The Spreadsheet component's security mechanism is slightly different from security used when accessing databases (more on this in Chapter 5): it will never give the user the opportunity to permit cross-domain access when the URL refers to a site not in the trusted sites zone. The Spreadsheet control merely returns an error and says that it's not allowed, whereas in the database access scenario, the user can approve the cross-domain access if he or she chooses.

Unfortunately, the Spreadsheet control cannot load data directly from a binary XLS file, but it can load from an Excel file saved in HTML format by loading from a URL as described above. This allows developers building a spreadsheet model to use Excel 2000 for creation and maintenance. Plus, it lets developers load that model directly into the Spreadsheet control at runtime.

Is a Different Share on the Same File Server a Different Domain?

Near the end of the OWC product cycle, a tester posted a bug against the Spreadsheet component that said it was showing the security warning message even though the URL it was getting data from was on the same file server as the original HTML page. We were perplexed since it did seem that this should be a trusted scenario, but because we used Internet Explorer's security code to determine whether two URLs were from the same domain, we pushed the bug over to the Internet Explorer team.

It turns out that the page's URL and the data's URL were indeed pointing to the same file server but were pointing to different shares on that file server. The Internet Explorer team explained that technically these are two different domains since it's common to use mammoth file servers for many different groups in an organization, and those that have permissions to one share might not have the same permissions on the other share.

So if you are using file share access instead of a web server, keep in mind that different shares on the same file server are different domains as far as security is concerned . However, this applies only to file share access, not HTTP access to a web server.