Connecting and Read/Write to Microsoft Excel becomes a common need among many Applications. How to do with ADO connection in your C++ Builder Application? This Post guide will you to do that and its very simple steps.
Assume you need to create a application, Read data from excel and populate into a Grid, and need to write data to the excel. To do this four components required,
TADOConnection: TADOConnection encapsulates the ADO connection object. Use TADOConnection for connecting to ADO data stores(e.g Excel). The connection provided by a single TADOConnection component can be shared by multiple ADO command and dataset components through their Connection properties. TADOConnection allows you to control the attributes and conditions of a connection to a data store.
TADOQuery: To access one or more tables( In Excel, it is sheet) in a data store using SQL statements. Retrieve data from tables in an ADO data store using SELECT statements. Perform actions on tables and other metadata objects in an ADO data store with statements like INSERT, DELETE, UPDATE, ALTER TABLE, and CREATE TABLE. Execute stored procedures.
TDataSource: To provide a conduit between a dataset and data-aware controls on a form that enable display, navigation, and editing of the data underlying the dataset.
TDBGrid: Displays and manipulates records from a dataset in a tabular grid. Applications can use the data grid to insert, delete, or edit data in the database, or simply to display it.
Steps to connect to Excel using ADO Connection:
- Create a new Windows VCL C++ Application.
- Add these components TADOconnection, TADOQuery, TDataSource, TDBGrid, TCombox, and TButton. Set the ADOquery and DBGrid Datasource property with TDatasource instance.
- Assign the TADOConnection.Connectionstring with “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:YourExcel.xls;Extended Properties=”Excel 8.0”;
- Populate the tables(sheet list) into the combobox from TADOconnection.GetTableNames(StringList).
- On Button click set TADOQuery SQL string with “select * from <excel sheet name selected in the combobox>” and open the TADOQuery.
Check the video for demonstration.
In the next article, know what is ADO and how it can provide data from different sources in a format designed around data, rather than functions and logic.
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today
Free Delphi Community Edition Free C++Builder Community Edition
Hi, is using ADO have the same limitation like using Firedac, i have tried to use firedac and connecting to xls or xlsx files, it can connect but can only read up to 255 columns, still don’t try the row limit yet