Creating a JDBC Data Source

You can create a JDBC data source to connect from your Java application. Creating a JDBC data source based on the CData JDBC Driver for Microsoft Excel Online consists of three basic steps:

• Add the driver JAR file to the classpath. The JAR file is located in the lib subfolder of the installation directory. Note that the .lic file must be located in the same folder as the JAR file.
• Provide the driver class. For example:
  

  cdata.jdbc.excelonline.ExcelOnlineDriver

• Provide the JDBC URL. For example:
  

  jdbc:excelonline:InitiateOAuth=GETANDREFRESH;
  
  or
  
  jdbc:cdata:excelonline:InitiateOAuth=GETANDREFRESH;

  The second format above can be used whenever there is a conflict in your application between drivers using the same URL format to ensure you are using the CData driver. The URL must start with either "jdbc:excelonline:" or "jdbc:cdata:excelonline:" and can include any of the connection properties in name-value pairs separated with semicolons.

Connecting to a Workbook

The driver exposes workbooks and worksheets from drives you specify in your Microsoft account. You can connect to a workbook by providing authentication to Excel Online and setting any of the following properties that control what drives are discovered:

• Drive: Set this to the ID of a specific drive. You can use the Drives and SharePointSites views to view all the sites and drives you have access to.
• SharepointURL: Set this to the browser URL of a SharePoint site. The driver will expose all drives under the site.
• OAuthClientId: If AuthScheme is set to AzureServicePrincipal or if OAuthGrantType is set to CLIENT, the drive associated with your OAuth app will be exposed.
• If none of the above are specified, the personal drive for the authenticated user will be used.

To control what workbooks and worksheets are exposed from the discovered drives, or what drives are exposed, you can use 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.
• 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.
• BrowsableSchemas: Set to a list of drive names. The drives that are exposed will be filtered by this list.
• Tables: Set to a list of table names, as exposed by the driver. The tables that are exposed will be filtered by this list.

Authenticating to Microsoft Excel Online

There are two authentication methods available for connecting to Microsoft Excel Online data source, the OAuth 2.0 (AzureAD) and the MSI Authentication methods.

OAuth (AzureAD)

To authenticate using OAuth, you may leave the OAuth credentials blank to use the driver'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.

• AuthScheme: Set this to AzureAD.

Azure Service Principal

Azure Service Principal is a connection type that goes through OAuth. Set your AuthScheme to AzureServicePrincipal. The authentication as an Azure Service Principal is handled via the OAuth Client Credentials flow, and it does not involve direct user authentication. Instead, credentials are created for just the app itself. All tasks taken by the app are done without a default user context, but based on the assigned roles. The application access to the resources is controlled through the assigned roles' permissions.

Note: You must create a custom application prior to assigning a role. See Creating a Custom AzureAD App for more information.

When authenticating using an Azure Service Principal, you must register an application with an Azure AD tenant. Follow the steps below to create a new service principal that can be used with the role-based access control.

Assign a role to the application

To access resources in your subscription, you must assign a role to the application.

1. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
2. Select the particular subscription to assign the application to.
3. Open the Access control (IAM) and select Add > Add role assignment to open the Add role assignment page.
4. Select Owner as the role to assign to your created Azure AD app.

Complete the Authentication

You are ready to connect after setting one of the below connection properties groups, depending on the configured app authentication (client secret or certificate).

In both methods

Before choosing client secret or certicate authentication, follow these steps then continue to the relevant section below:

1. AuthScheme: Set this to the AzureServicePrincipal in your app settings.
2. InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
3. AzureTenant: Set this to the tenant you wish to connect to.
4. OAuthClientId: Set this to the client Id in your app settings.

Authenticating using a Client Secret

Continue with the following:

1. OAuthClientId: Set this to the client Id in your app settings.
2. OAuthClientSecret: Set this to the client secret in your app settings.

Authenticating using a Certificate

Continue with the following:

1. OAuthJWTCert: Set this to the JWT Certificate store.
2. OAuthJWTCertType: Set this to the type of the certificate store specified by OAuthJWTCert.

MSI

If you are running Microsoft Excel Online on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:

• AuthScheme: Set this to AzureMSI.

The MSI credentials are 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 driver models worksheets and cells as tables and columns.

Retrieving data from SharePoint Excel Files

To retrieve data from Sharepoint Excel files, set the SharepointURL connection property to the URL of your Sharepoint site. For example,

 SharepointURL=https://mysite.sharepoint.com/ 

The driver automatically looks up each document library you have in SharePoint and lists it as a schema. Individual Excel workbooks and worksheets are listed as tables in the format Workbook_Worksheet under their corresponding document library. This works in the same manner as listing your own personal Excel documents when SharepointURL is not set.