Note: There is an updated version of this article, which applies to the next generation of TimeXtender.

There are three methods to connect to excel files with CData.

Excel, Excel Services, and Excel Online.

Below I will explain all three.

Connect with the CData Excel provider

We have a setup template for Excel which means that when you select it the initial view will look like so.

It shows the necessary fields you need to use to connect to a file and some of the more used options.

To set it up you click on the button next to the Excel File field with the three ... in it, locates the file, and click OK. I would also suggest changing the Row Scan Depth to 100 or 0, so it will read more rows before deciding on the fields' data types.

If your file is not located on a drive you have access to, you need to specify more settings than what is already shown, or its location requires some sort of authentication, E.G. it is stored on an FTP server or similar, you can click on the More Options button.

It will then look like so.

There are a lot of available options. Some exclude others, this is the case for all CData providers, you do not need to use all of them. Also, check the guide by clicking on the ? icon CData ADO.NET Provider for Excel. Especially check Establishing a Connection and Connection String options.

The options to focus on are in the Authentication, Azure Authentication, Connection, Data, and Misc areas.

Authentication

Here you can set up the user you want to connect as, the sort of authentication method you would use, and if used the edition of SharePoint.

Azure Authentication

Here are the options you need to use if you connect to a file stored in a blob store and similar.

• Azure Storage account: This is the name of the Storage account where your container or file share is located.
• Azure Access Key: This is the key you use to connect to the storage account.
  
• Azure Shared Access Signature: This is used as another way to connect but is generally not used for connecting to files
• Azure Tenant: This is the id of your company. If you go to the Azure Active Directory in the Azure Portal you will find it there as the Tenant ID.
  
• Azure Environment: This Is where the storage account is located, but I would not change or use it.

Connection

This is where you can set up the connection to the file, and specify what sheet you want to connect to and the range of it.

So if your file is structured like so.

You can add a range in the Define Tables field with the following setup.

DefineTables="Table1=Sheet1!A1:N25,Table2=Sheet2!C3:M53"

This means that in the Define Tables field you need to add what is below

Data=Sheet1!B2:E7

You should always add your connection options in the URI field, the Excel File field is deprecated.

The options can be seen here in the Establishing a Connection link, the optional and required fields are shown.

For Azure Blob or file share connections, the following is necessary.

Set the URI to the name of your container and the name of the blob/file share. Additionally, set the following properties to authenticate:

• AzureStorageAccount
• AzureAccessKey

For example:

URI=azureblob://mycontainer/myblob/file.xlsx; AzureStorageAccount=myAccount; AzureAccessKey=myKey;

You can also use the OAuth authentication to connect with Azure Blob Storage. For example:

URI=azureblob://mycontainer/myblob/file.xlsx; AzureStorageAccount=myAccount; AzureAccessKey=myKey;

You can also connect to files on an FTP server like so.

Set the URI to the address of the server followed by the path to the Excel file. To authenticate, set User and Password.

For example:

URI=ftps://localhost:990/folder1/file.xlsx; User=user1; Password=password1;

It is important to note that you cannot point to just a folder, it has to be a specific file for this provider.

Data

Is an area with some options to allow or deny specific settings, such as how to handle null or empty fields.

Misc

Is an area where you can set up various settings such as what culture the file is created in and what culture it should be, along with specifying what charset the file uses.

Connect with the Excel Services CData provider

This provider is made so you can connect to a file located in SharePoint. The normal Excel provider should also be able to do it, but it requires more setup in the URI. This is an easier way to connect to a file there.

It still can only connect to one file like the other one. It can also not use OAuth to connect to the file.

As in the normal Excel provider, there are a lot of options and again some are more relevant than others.

Check the guide ADO.NET Provider for Excel Services for more info about the fields. Especially check Establishing a Connection and Connection String Options.

For this provider, the focus is on the Authentication, Connection, and Misc areas.

Authentication

There are many ways to connect and check the link about establishing a connection for more options.

Connecting to SharePoint Online

Set to SharePointEdition "SharePoint Online" and set the User and Password for an Azure Active Directory account. Important that it doesn't use 2-factor authentication.

Set the URL to a site collection to query workbooks in all nested subsites. Set the URL to a site to query workbooks on that site only.

To authenticate to SharePoint Online, set AuthScheme to the authentication type and set User and Password, if necessary.

 

URL	Example URL
Site	https://teams.contoso.com/teamA or https://teamA.contoso.com
Site Collection	https://teams.contoso.com

• Windows (NTLM): This is the most common authentication type. As such, the provider is preconfigured to use NTLM as the default; simply set the Windows User and Password to connect.
• Basic: With the Basic authentication method, the user account credentials are sent as plaintext. To use this authentication type, set AuthScheme to Basic and set the User and Password.

Connection

This area is about specifying where in the SharePoint the file is located. So you can set the filename of the folder, of the library, and define the range of specific sheets.

Misc

The only field of interest is the Include Navigation Parent Columns, which gives the navigation info on the navigation views.

Connect with the Excel Online CData provider

Excel online can connect to your company's excel files stored in SharePoint or OneDrive. OneDrive is also a sort of SharePoint anyway. What files you can see are based on the setup you use. By default, it will show the files your own Azure AD user is allowed to see.

You do not need to add a location to look for, the files you can access are entirely based on what sort of authentication you use.

There are three ways to generate access. You can read about them here Establishing a Connection

Authenticate using OAuth Authentication (AzureAD)

To authenticate using OAuth, you may create a custom app to obtain the OAuthClientId and 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.

Generating the App.

Steps to Create a Custom OAuth App for OAuth Authentication

Follow the steps below to obtain the OAuth values for your app, the OAuthClientId and OAuthClientSecret.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory then App Registrations and click the New registration button.
3. Enter an app name and set the radio button for the desired tenant setup. When creating a custom OAuth application in Azure Active Directory, you can define if the application is single- or multi-tenant. If you select the default option of "Accounts in this organizational directory only", you will need to set the AzureTenant connection property to the Id of the Azure AD Tenant when establishing a connection with the Provider for Excel Online. Otherwise, the authentication attempt will fail with an error. If your app is for private use only, "Accounts in this organization directory only" should be sufficient. Otherwise, if you want to distribute your app, choose one of the multi-tenant options.
4. Then set the redirect URL to something such as http://localhost:33333, the provider's default. Or, set a different port of your choice and set CallBackURL to the exact reply URL you defined.
5. Define the app authentication type by going to the Certificates & Secrets section. 
   
   • Create a new application secret: In the Certificates & Secrets section, select New Client Secret for the app and select its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once, and it is used as the OAuthClientSecret.
6. Select API Permissions and then click Add. If you plan for your app to connect without a user context, select the Application Permissions (OAuthGrantType = CLIENT). Otherwise, when selecting permissions, use the Delegated permissions. Application permissions require an admin to consent for it to work.
   
7. Add the following application permissions: Sites.Read.All, Files.Read, Files.Read.All, Files.Read.Selected, and offline_access.
8. Save your changes.
9. If you have selected to use permissions that require admin consent (such as the Application Permissions), you may grant them from the current tenant on the API Permissions page. Otherwise, follow the steps under Admin Consent.
   Running the stored procedures mentioned in the above link will be difficult, it would be easier to get an admin to authorize it.

Steps to Create a Custom OAuth App for Azure Service Principal Authentication

Follow the steps below to obtain the OAuth values for your app.

1. Log in to https://portal.azure.com.
2. In the left-hand navigation pane, select Azure Active Directory then App Registrations and click on New registration button.
3. Enter an app name and set the radio button for "Any Azure AD Directory - Multi Tenant". Then set the redirect url to something such as http://localhost:33333, the provider's default.
4. Copy the Application (client) Id value displayed on the Overview section after creating the app, since this value is used as the OAuthClientId
5. Define the app authentication type by going to the Certificates & Secrets section.
   • Create a new application secret: In the Certificates & Secrets section, select New Client Secret for the app and select its duration. After saving the client secret, the key value is displayed. Copy this value as it is displayed only once, and it is used as the OAuthClientSecret.
6. In the Authentication tab, make sure to check the option: Access tokens (used for implicit flows).
7. Open the Subscriptions page by searching and selecting the Subscriptions service from the search bar.
8. Select the particular subscription to assign the application to, then open the Access control (IAM) section, and click on the Add role assignment button.
9. Select Owner as the role to assign to your created OAuth app.

Set up the OAuth connection to the source

Once you have a working app you can connect to your file source.

Authentication with your OAuth App

Set up the following fields:

• OAuthClientId: Set this to the Client Id in your app settings.
• OAuthClientSecret: Set this to the Client Secret in your app settings.
• OAuth Settings Location: Set this to a specific folder that is visible for all developers and service accounts that uses the project.
• AuthScheme: Set this to the "AzureAD" in your app settings if you use OAuth Authentication, or set it to "AzureServicePrincipal" if you use the Azure Service Principal.
• InitiateOAuth: Set this to GETANDREFRESH initially.

Then click on the Authorize OAuth button and see that it goes through the flow successfully.

Once you get this message, change the Initiate OAuth field to REFRESH.

Connection

When you synchronize the data source, you will get some default tables that will show you what files you are allowed to see. This can be specified with the fields in the connection area.

• Define Tables: This is the same as in the other two and can be used to specify what files you want to connect to.
• Workbook: Essentially refers to this table called Workbooks and you can add the ids or names of files in a comma-separated list.
• Drive: Refers to the drive/s you look at. Same as for Workbook there also is a table where you can specify the id to see a specific drive.
• Include SharePoint Sites: Whether to retrieve drives for all SharePoint sites when querying Drives view. If 'true' the provider will retrieve all Site IDs recursively and for each of them issue a separate call to get their drives. Therefore, be aware that setting this property to 'true' may decrease performance for Drives view.

Misc

Here you can set extra settings that will increase performance or allow a specific appearance to be allowed.

• Show Shared Documents: If set to true, shared documents will be listed alongside user-owned workbooks as database tables.
• Use Pagination: decides whether or not Excel Online should use client-side paging. Set this to true in case of a large dataset.
• Pagesize: set this to a value different than -1 if you also use Pagination.
• Use Simple Names: set this to true if the names of fields are too long. The limit is 128 characters.