Understanding the Spreadsheet Control

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The Spreadsheet control is a Microsoft® ActiveX® control that makes it possible for you add the functionality of a worksheet to a Web page. You can also place a hidden instance of this control on a page and use it as a powerful recalculation engine that works with other visible controls on a page. You can think of this control as a way to take the power behind an Excel worksheet and transfer it to a Web page.

The Spreadsheet control has several options for data connection. These options make the control much easier to user:

Calculation engine: A new calculation component for the Spreadsheet provides greater performance, robustness, and compatibility with Microsoft® Excel. The new calculation component was built from Excel's source code to provide the highest level of compatibility and dependability. Because the component was built from Excel's sources, it will support all of Excel's built-in formulas, perfect dependencies, array formulas, and discontinuous references.

Named ranges: An important feature for the Calculation engine that makes it possible for you define a name for a range of cells, a constant, or functions. Then, the name can be used in formulas. Names are interchangeable within Excel.

IDispatch as a new, in-cell data type: Has expanded the list of built-in data types to include IDispatch. It now supports numbers, text, Boolean expressions, dates, errors, arrays and IDispatch.

Container formulas: IDispatch is provided as a new, intrinsic data type to a spreadsheet's cells, containing formulas that make it possible for you to data-bind cells to elements from the container. This makes it possible for a Web-based application using the spreadsheet control to data-bind to HTML elements such as text boxes, list boxes, or even textual elements.

Support for XML-Spreadsheet: Both Excel and the Spreadsheet component share a common file format that makes it easy for you to construct, process, share, and extract data from your spreadsheet models. The Spreadsheet component supports Load/Save, Copy/Paste, and Range.Value with XML-Spreadsheet.

Workbooks: As with classic Excel, you can now work with a collection of sheets in a workbook. This helps you organize your information. By keeping related sheets in the same workbook, it is easier for you to make related changes and edits, to consolidate related sheets, or to do calculations involving data from multiple worksheets. This will also provide better fidelity when publishing from or exporting to Excel.

Excel-compatible object model: All of the functionality in the Spreadsheet component uses Excel's properties, methods, and events. In some cases, the full functionality of an Excel method is not supported (for example, password protection), but the object model syntax is identical to Excel. This will make it easier to develop Excel Microsoft® Visual Basic® for Applications (VBA) solutions against the Spreadsheet and ease the process of moving solutions from one version of Microsoft® Office to another.

Data-bound sheets: The Spreadsheet component now has an intrinsic way to connect to data stored in databases like SQL Server. A data-bound sheet gets its data from a table or view in a database and can be refreshed. The size of the sheet is determined by the amount of data in the database and creates automatically named ranges to help you build formulas against data-bound data.

Integrated data-binding in Data Access Pages: The data-bound worksheets will make it possible for you to drag tables and views from Data Access Page's Field List component and drop them as a Spreadsheet component. This will make it possible for you to build powerful spreadsheet models against data from SQL Server.

More rows and columns: The Spreadsheet component now supports 262,144 rows by 18,278 columns (ZZZ).

Custom row and column headers: Makes it possible for you customize the text that displays in the row and column headers through scripting. Custom text headings make it possible for the Chart component to use the Spreadsheet component as its datasheet.

New look and feel: The Spreadsheet component now looks the same as the rest of Office 10 by adding lightened row and column headings to help indicate the active selection.

See Also

Using Web Technologies with Office XP | Understanding the Chart Control | Understanding the PivotTable List Control | Understanding the Data Source Control