How To Script the Office XP Spreadsheet Component from a Web Page (286209)


The information in this article applies to:

  • Microsoft Office XP Web Components
This article was previously published under Q286209

SUMMARY

This article provides sample code that illustrates how you can script the Office XP Spreadsheet component from a Web page.

MORE INFORMATION

Working with the Office XP Spreadsheet component is very similar to working with a Microsoft Excel workbook object. The methods and properties you use to manipulate the spreadsheet are similar to those you use when you automate Excel. In fact, one of the new features of the Office XP Spreadsheet component is that it allows for more than one worksheet. There are also additional ways to populate the Spreadsheet component, such as with an XML data source or with a connection string to a database.

Populating the Spreadsheet Component

You can add data to the Spreadsheet component in one of the following ways:
  • Enter data directly into a worksheet.
  • Copy and paste data from an Excel worksheet, text file, or Microsoft Word document.
  • Import data from a text file, Web page, or XML file. These various types of data may be imported by using the Spreadsheet object's CSVURL, HTMLURL, XMLURL, CSVData, HTMLData, and XMLData properties and the LoadText method of the Range object.
  • Use the Value property of the Range object to assign values to specific cells in the spreadsheet.
  • Bind to a data source by using the DataSource and DataMember properties, or by using the ConnectionString and CommandText properties.
The following sample illustrates how you can populate the Spreadsheet component with data and apply various formatting to the cells and the worksheet. The sample populates a worksheet by using the ConnectionString and CommandText properties to bind to data in the Northwind sample database (Northwind.mdb).

Sample

  1. Paste the following code into Notepad, and then save the code on your local computer as Spreaddemo.htm.
    <html>
<body>
<button id="btnDemo">Spread Demo</button>
<br/>
<br/>
<object classid="clsid:0002E551-0000-0000-C000-000000000046" id="oSS" width=400 height=250>
</object>
</body>

<SCRIPT LANGUAGE="VBScript">

Sub btnDemo_OnClick()    
    Dim oSheet
    Dim sCnn
    Dim sSQL
    Dim iNumCols
    Dim iNumRows
    Dim oTbarButton

    Dim c
    Set c = oSS.Constants
       
    ' Add a new worksheet.
    Set oSheet = oSS.Worksheets.Add(1)

    ' Delete all other worksheets.
    Do While oSS.Worksheets.Count > 1
        oSS.Worksheets(2).Delete
    Loop

    ' Name the new worksheet.
    oSheet.Name = "Employees"
        
    ' Fill the Employees sheet with data from Northwind sample database.
    sCnn = "provider=microsoft.jet.oledb.4.0; data source=" & _
        "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
    sSQL = "SELECT Employees.EmployeeID, First(Employees.FirstName) AS FirstOfFirstName, " & _
        "First(Employees.LastName) AS FirstOfLastName, " & _
        "Count(Orders.OrderID) AS CountOfOrderID, " & _
        "Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Total " & _
        "FROM (Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) " & _
        "INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID " & _
        "GROUP BY Employees.EmployeeID;"
    oSheet.ConnectionString = sCnn
    oSheet.CommandText = sSQL
        
    ' Determine the number of rows and columns.
    iNumCols = oSheet.UsedRange.Columns.Count
    iNumRows = oSheet.UsedRange.Rows.Count
    
    ' Change field names as desired.
    oSheet.Cells(1, 2).Value = "First Name"
    oSheet.Cells(1, 3).Value = "Last Name"
    oSheet.Cells(1, 4).Value = "Orders"
    oSheet.Cells(1, 5).Value = "Total"
    
    ' Format field names.
    With oSheet.Range(oSheet.Cells(1, 1), oSheet.Cells(1, iNumCols))
        .Font.Bold = True
        .Font.Size = 11
        .Interior.Color = "Silver"
        .Borders(c.xlEdgeBottom).Weight = c.xlThick
    End With
        
    ' Add subtotals for Orders and Total fields.
    oSheet.Cells(iNumRows + 2, 4).Formula = "=SUBTOTAL(9,D2:D" & iNumRows & ")"
    oSheet.Cells(iNumRows + 2, 5).Formula = "=SUBTOTAL(9,E2:E" & iNumRows & ")"
        
    ' Format columns.
    oSheet.Cells(1, 1).EntireColumn.HorizontalAlignment = c.xlHAlignLeft
    oSheet.Cells(1, 4).EntireColumn.NumberFormat = "#,##0"
    oSheet.Cells(1, 5).EntireColumn.NumberFormat = "Currency"
    oSheet.UsedRange.EntireColumn.AutoFit
    
    ' Sort by Total (column 5).
    With oSheet
        .Range(.Cells(1, 1), .Cells(iNumRows, iNumCols)).Sort _
            5, c.xlDescending, c.xlYes
    End With

    ' Hide the title bar.
    oSS.DisplayTitleBar = False

    ' Hide the Microsoft Office logo on the toolbar.
    oSS.DisplayOfficeLogo = False
    
    ' Display the Toolbar.
    oSS.DisplayToolbar = True
    
    ' Remove the "Refresh All" and "Help" button from the toolbar.
    On Error Resume Next
    Set oTbarButton = oSS.Toolbar.Buttons("owc10061") 'Refresh All button
    If Not Err.Number Then
        oSS.Toolbar.Buttons.Remove oTbarButton.Index - 1
        oSS.Toolbar.Buttons.Remove oTbarButton.Index
    End If
    Err.Clear
    Set oTbarButton = oSS.Toolbar.Buttons("owc1006") 'Help button
    If Not Err.Number Then
        oSS.Toolbar.Buttons.Remove oTbarButton.Index - 1
        oSS.Toolbar.Buttons.Remove oTbarButton.Index
    End If
    On Error GoTo 0
    
    ' Apply window settings.
    oSS.Windows(1).ViewableRange = oSheet.UsedRange.Address
    oSS.Windows(1).DisplayRowHeadings = False
    oSS.Windows(1).DisplayColumnHeadings = False
    oSS.Windows(1).DisplayVerticalScrollBar = False
    oSS.Windows(1).DisplayHorizontalScrollBar = False

    'Autofit the component on the Web page.
    oSS.AutoFit = True
End Sub
</script>
</html>
  2. Make sure the path to Northwind.mdb is correctly specified in the sCnn variable.
  3. Start Microsoft Internet Explorer and browse to Spreaddemo.htm.
  4. Click the button to fill the Spreadsheet component with data and to apply formatting.

Additional Notes

When you bind the Spreadsheet component to a data source, the spreadsheet's ViewableRange is automatically set to the size of the returned data. ViewableRange updates as the data source is refreshed. Note that at the end of the above code, the ViewableRange property was set to the UsedRange to ensure that the subtotals below the bound data are viewable.

Conceptually, the contents of a data-bound worksheet are owned by the Spreadsheet component. This means that any user edits to the worksheet are lost during the next data refresh without warning. Similarly, when binding data to a worksheet that already contains data, the existing data may be replaced when the data from the bound data source is retrieved.

To programmatically cause a data refresh, call the Refresh method of the Worksheet object, or of the Application object to refresh all worksheets.

REFERENCES

For documentation on the Office XP Web Components Object Models, see the Owcvba10.chm Help file.

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

288130 How To Use ASP to Build Spreadsheet XML for Client-Side Display

286316 PRB: Client-Side Spreadsheet Fails to Import Data From URL

235883 How To Use the Spreadsheet Web Component with Visual Basic

288907 INFO: Binding the Office XP Chart Component to a Data Source

For more information on scripting and working with the Microsoft Office Web components, see the following Microsoft web sites:

Using Office Web Components
http://support.microsoft.com/ofd

Microsoft Office Developer Center
http://msdn.microsoft.com/office/

Modification Type:MinorLast Reviewed:8/23/2005
Keywords:kbhowto kbOfficeWebSpread KB286209 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.