Establishing a Connection
Connecting to a Workbook
You can connect to a workbook by providing authentication to Excel Online and then setting the following properties:
Workbook: Set to the name or Id of the workbook. If you want to view a list of information about the available workbooks, execute a query to the Workbooks view after you authenticate.
Drive: While optional, the Drive will further narrow down which workbook to use. A Workbook may only be retrieved by supplying both the Drive Id and the Workbook Id. If Drive is not specified, the CData ADO.NET Provider for Excel Online will automatically attempt to look up the appropriate Drive in the background.
- UseSandbox: Set to true if you are connecting to a workbook in a sandbox account. Otherwise, leave this blank to connect to a production account.
Authenticating to Excel Online
There are two authentication methods available for connecting to Excel Online data source, the OAuth 2.0 (AzureAD) and the MSI Authentication methods.
Authenticate using OAuth Authentication (AzureAD)
To authenticate using OAuth, you may leave the OAuth credentials blank to use the provider's embedded app.
Alternatively, you may create a custom app to obtain the OAuthClientId, OAuthClientSecret to use custom OAuth credentials. In addition to those properties, set CallbackURL. See Using OAuth Authentication for more information.
- AuthScheme: Set this to AzureAD.
Authenticating using Azure Service Principal
Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal and see Using Azure Service Principal Authentication for an authentication guide.
Authenticating using MSI Authentication
If you are running Excel Online on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:
- AuthScheme: Set this to AzureMSI.
The MSI credentials will then be automatically obtained for authentication.
Executing SQL to Worksheet Data
See the following to execute data manipulation SQL to worksheets and ranges:
- Selecting ExcelOnline Data
- Inserting ExcelOnline Data
- Updating ExcelOnline Data
- Deleting ExcelOnline Data
- Using Formulas
See Data Model for more information on how the provider models worksheets and cells as tables and columns.