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.