Establishing a ConnectionCData Excel Add-In for OData 2019 - Build 19.0.7354
Configure a Connection Profile
Click From OData to launch the connection dialog. Here, you can set the connection settings, test the connection, and save the connection profile. The profile enables you to control the following for a connection:
Select the Store in Workbook option to create easy-to-share spreadsheets. By default, the add-in saves the connection to an .rdc file in the CData subfolder in the %APPDATA% folder.
Enable or restrict updates, deletes, and inserts for the connection.
Connecting to OData
To connect, you need to set the Url to a valid OData service root URI in addition to the authentication values.
Also, you can specify a CacheLocation to store the metadata of your OData organization. This keeps the CData Excel Add-In for OData 2019 from having to send requests for metadata on each connection.
Fine-Tuning Data Access
Set the following properties to control how the add-in models OData APIs as a database:
- NavigationPropertiesAsViews: By default, the add-in models navigation properties as views. This enables access to related entities, even though these entities may not be linked by a foreign key in your OData service.
- SupportsExpand: If your API does not support the $expand parameter, set this property to avoid an error when NavigationPropertiesAsViews is set. If this is the case for your API, specify the base entity's primary key in the WHERE clause to access navigation properties.
- DataFormat: Set this property to JSON or XML. Otherwise, the add-in uses the default format defined by the service.
- ODataVersion: Use this to override the version detected by the add-in. This is useful if your application supports an older OData version.
- UseIdUrl: By default the add-in returns the direct URL to an entity as the primary key. By setting this to false, the entity key is returned.
- UseSimpleNames: Set this to true to return only alphanumeric characters in column names. This can help you to avoid SQL escapes and errors in SQL-based tools.
Authenticating to OData
The add-in supports the major authentication schemes, including HTTP and Windows.
Set AuthScheme to use the following authentication types.
- HTTP Authentication: The add-in supports authentication with HTTP Basic, Digest, and custom headers. To use Basic or Digest, set the User and Password. You can specify other authentication values in CustomHeaders.
- Windows (NTLM): Set the Windows User and Password to connect and set AuthScheme to "NTLM".
- Kerberos and Kerberos Delegation: To authenticate with Kerberos, set AuthScheme to NEGOTIATE. To use Kerberos Delegation, set AuthScheme to KERBEROSDELEGATION. If needed, provide the User, Password, and KerberosSPN. By default, the add-in attempts to communicate with the SPN at the specified Url.
Securing OData Connections
By default, the add-in attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.
- Querying Data: Use the data selection wizard to pull data into a spreadsheet. You can also configure scheduled data refresh here.
- Using the Excel Add-In: Find other ways to interact with OData data, such as using the available CData Excel Formulas.
- Managing Connections: To access an existing connection, click Edit in the Connection Wizard or click Existing Connections in the CData ribbon. Additionally, see this section for more information on workbook sharing and user access.