This article provides a brief overview of the Microsoft Excel data access provider for UniDAC used to establish a connection to Excel workbooks from Delphi and Lazarus. You will find the description of some useful features and how to get started quickly.
Excel provider is based on the ODBC provider. It uses Microsoft Excel ODBC driver to work with a workbook. Main features of the Excel data access provider are as follows:
The full list of the Excel provider features can be found on the UniDAC features page.
Both Professional and Standard Editions of UniDAC include the Excel provider. Express Edition of UniDAC does not include the Excel provider.
To learn the supported versions of Microsoft Excel, refer to the Compatibility section.
Applications that use the Excel provider require Microsoft Data Access Components (MDAC) to be installed on the client computer. In the current versions of Microsoft Windows, since Windows 2000, MDAC is already included as a standard package.
When an application was built without runtime packages (Link with runtime packages is set to False in Project Options), you do not need to deploy any BPL files with it. For more information, see Deployment.
Note, that UniDAC Trial requires deployment of additional BPL files regardless of the Link with the runtime packages.
Option name | Description |
---|---|
Database | The full path to the workbook to connect to. |
Default Cell Type |
Specifies what string data types are used by the provider for literal columns.
cdString .
|
Describe Amount | Specifies the number of sample worksheet rows used to create a list of columns in the corresponding table. The default value is 25. |
Exclusive Lock | If set to True, this parameter enforces a single-user access mode for the workbook. Even with ReadOnly set to True, only one user is able to access the workbook at a time. The default value is False. |
Header Row | Indicates whether the data in the very first worksheet row should be treated as column names. If set to True, then each column value represents the corresponding table column name. The default value is False. |
Hide RowId | Specifies whether the driver should hide the ROWID pseudo-column. The default value is True. |
Include Empty Rows | Indicates, whether to include empty lines in the worksheet or skip them while reading data. This option is enabled by default. |
Read Only | By default, Microsoft Excel is a single-user system. The driver implements a read-only multi-user mode to enable several users to read data from the workbook simultaneously. The possible values are True and False. The default value is False. By setting ReadOnly to True, two or more users are able to access the workbook simultaneously, however only in the read-only mode. |
Option name | Description |
---|---|
CommandTimeout | The time to wait for a statement to be executed. |
Option name | Description |
---|---|
CommandTimeout | The time to wait for a statement to be executed. |
ExtendedFieldsInfo | If True, an additional query is performed to get information about returned fields and tables they belong to. The default value is True. |
FetchAll |
If True, all records of a query are requested from database server when the dataset is being opened.
If False, records are retrieved when a data-aware component or a program requests it. The default value is False. |
The TUniDump component has no Excel-specific options.
The TUniLoader component has no Excel-specific options.
The TUniDump component has no Excel-specific options.
Direct connection enables users to connect to Microsoft Excel workbooks directly, without the need for Microsoft Excel to be installed on their machine.
The following table lists the constants for mapping Excel data types to Delphi data types. See Data Type Mapping for more information.
Constant | Description |
---|---|
excelBoolean | Maps boolean to Delphi data types. |
excelInteger | Maps integer to Delphi data types. |
excelDouble | Maps float to Delphi data types. |
excelDate | Maps date to Delphi data types. |
excelTime | Maps time to Delphi data types. |
excelDateTime | Maps datetime to Delphi data types. |
string | SQL_VARCHAR |