Overview

Many spreadsheet users want to be able to access data from external sources: databases, text files, web pages, XML web services, OLAP cubes. Typically, a spreadsheet application will provide abilities for the user to locate, browse, connect to, and query external data sources. Once the data source has been located, connected to, and queried, the resulting data must be rendered in the spreadsheet application, and made available for further analysis.

Data sources such as databases are made available for browsing and consumption via data-provider technologies. Typically, the data provider provides a standard interface for accessing the data, and removes the complexity introduced due to each database application's providing non-standard data access APIs. In this way, OLEDB providers, for example, can be written for myriad database implementations, and a consumer can always use a single interface (defined by OLEDB) to access these disparate data sources.

A live connection to a data source is established by the application at runtime, and can only exist as a live connection while the application is running. There are two types of information about a particular connection:

  • The information used to establish the connection.

  • The information and properties about how the connection should be used and how the connection should behave in conjunction with the application.

Information about a connection can be supplied by the user as the connection is being established—for example, providing a password, picking a table, applying a filter, or setting behavioral properties such as whether to refresh the data when the workbook is opened and whether to store refreshed data in the worksheet when the workbook is saved.

Information about a connection can also be persisted in a connection file separately from the workbook file. In this way a directory or file share containing a variety of these connection files can be considered a library of data connections, for example.

Any time a connection is established, whether by using information from a connection file or by gathering the connection information directly from the user, a copy of the connection information is stored in the workbook.

Data providers and connection types discussed below are:

  • ODBC

  • OLEDB

  • DAO

  • Text Import

  • Web

The corresponding features in SpreadsheetML that render and analyze the data are:

  • Query Table

  • Table

  • XML Map

  • Pivot Table

  • The CUBE* Functions