Establishing a Connection
You can create and manage connections with the Data Explorer and the FireDAC Connection Editor wizards. See Connection Definitions for more information on creating connections from code and creating persistent, private, or temporary connections.
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 FireDAC Components 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 component'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 component models worksheets and cells as tables and columns.
Using the FireDAC Connection Editor
Complete the following steps to use the FireDAC Connection Editor to assign a Excel Online connection to a TFDConnection object:
- Open a project and drop a TFDPhysCDataExcelOnlineDriverLink from the Tool Palette onto the Form Designer.
- Drop a TFDConnection object onto the Form Designer.
- Double-click the TFDConnection and select CDataExcelOnline in the Driver Id menu.
- Define the necessary connection properties.
- To execute ad-hoc SQL statements based on this connection, enter SQL commands on the SQL Script tab.
- In the Form Designer, select the TFDConnection object and set the Connected property to true.
Using the Data Explorer
Complete the following steps to use the Data Explorer to define persistent connections that can be shared across applications and projects:
- Click View > Tool Windows > Data Explorer in RAD Studio and expand the FireDAC node.
- Right-click the CData Excel Online Data Source node and click Add New Connection.
- Enter a name for the connection. The FireDAC Connection Editor opens.
Working with Persistent Connections
The connections you define in the Data Explorer can be assigned to a TFDConnection object by specifying the connection name in the TFDConnection object's ConnectionDefName property.
Browsing Data and Metadata
The Data Explorer also provides options for browsing Excel Online objects at design time:
- Expand the CData Excel Online Data Source node in the Data Explorer to view the defined connections.
- Expand a connection node to find the database objects available for a connection.
- Drill down to a database object to find metadata such as primary keys.
- To display data, double-click a table or view or right-click and click View.