The connector supports retrieving Excel data from SharePoint On-Premises and SharePoint Online, through the REST API and through the OData API introduced in SharePoint 2013. Excel files must have the .xlsx format used in Excel 2007 and later.
The connector can detect the tables in your workbook based on the following:
- Excel tables
Dynamic Metadata Querying
Through dynamic metadata querying, any changes you make in Excel, such as adding an Excel table or spreadsheet or modifying columns, are reflected when you reconnect.
Configuring Table Detection
The OData API and REST API (the default) each surface different API objects. Select which API to use based on how your workbook is organized and your Excel Services version.
Connecting to Excel Tables
The OData API surfaces only the Excel tables you defined in your spreadsheets with Insert -> Table.
Connecting to Spreadsheets, Ranges, and Excel Tables
If you need to access data outside of Excel tables, set UseRESTAPI to true; the REST API enables access to spreadsheets, ranges, and Excel tables as database tables.
See REST API for more information on executing SQL to spreadsheets and ranges.
Supported APIs by SharePoint Version
The following table shows which APIs are available for each SharePoint version:
|SharePoint Version||Available APIs|
|SharePoint Online||REST or OData|
|SharePoint On-Premises 2016||REST or OData|
|SharePoint On-Premises 2013||REST or OData|
|SharePoint On-Premises 2010||REST|