1. Computing
About.com
Delphi
Accessing and managing MS Excel sheets with Delphi
Page 2: Creating an Excel spreadsheet editor using Delphi and ADO
Win prizes by sharing code!
Do you have some Delphi code you want to share? Are you interested in winning a prize for your work?
Delphi Programming Quickies Contest
More of this Feature
• Page 1: Connect to: MS Excel
• Page 3: Retrieving the data from Excel
• Page 4: Excel field (column) types
• Page 5: Modify Excel sheets
• Page 6: Transfering data to Excel

• Page 7: Full source code
Join the Discussion
"Post your views, comments, questions and doubts to this article."
Discuss!
Related Resources
• A Beginners Guide to ADO programming
• Automation with Excel and Delphi

As discussed on the first page of this article, one way to read and edit Excel sheets using Delphi is through ADO.

Setting up a project
Start Delphi, this by default creates a new Windows application hosting one Delphi form (Form1:TForm). For the sake of simplicity, we'll start from here by adding new components to the form. This is what you will need for the visual part of the "Excel spreadsheet editor"...
A button (BitBtn1:TBitBtn), a combo box (ComboBox1:TComboBox), three labels (Label1:TLable, Label2:TLabel, Label3:TLabel), two edits (Edit1:TEdit, Edit2:Tedit), a list box (ListBox1:TListBox) and a status bar (StatusBar1:TStatusBar).
Note the "(Name:ClassName)" annotation, all the components are left with their default names when dropped on the form.
We'll need several more components, to be precise several DB and ADO related components. Go on and add a dbgrid (DBGrid1:TDBGrid), a database navigator (DBNavigator1:TDBnavigator), and the three "standard" components we need when working with ADO: the connection object (ADOConnection1:TADOConnection1), a datasource (DataSource1:TDataSource) and a dataset descendant object (ADOQuery1:TAdoQuery).

When you have finished, this is how your form should look like at design time (I've placed some default text into edits using the Object Inspector):

Delhpi Excel spreadsheet editor at design time

Note the control names in red. This is the idea: Edit1 (where the location to the Excel file is specified) is used to set the data source (Excel workbook). Edit2 is used to specify a query to run against the workbook. ComboBox1 will display the workbook sheet names. ListBox1 will give details about Field objects. StatusBar1 will show any error that pops up in the process. DBGrid and DBNavigator are in control of editing and navigating through the Excel data. If you need more-user friendly DBNavigator, check the "Customizing the DBNavigator" article; for DBGrid related articles, go see "Using the DBGrid - tips and tricks"

Connectivity
First, we need to connect all the DB related components together. You can "connect" the components using the Object Inspector, but I've decided to do all the work from code. This is done in the OnCreate event handler for the Form1:

procedure TForm1.FormCreate(Sender: TObject);
begin
  AdoConnection1.LoginPrompt := False;
  AdoQuery1.Connection       := AdoConnection1;
  DataSource1.DataSet        := AdoQuery1;
  DBGrid1.DataSource         := DataSource1;
  DBNavigator1.DataSource    := DataSource1;

  Application.OnException    := DisplayException;
end;

Since there might be some errors popping up the screen while examining various Delphi+Excel+ADO techniques I decided to handle any possible error in one common place. The Application.OnException event is the right candidate for the job, on any exception the DisplayException gets called. This is how the DisplayException procedure looks like (nothing special, just displays the error message in the StatusBar1 control):

procedure TForm1.DisplayException(Sender: TObject; E: Exception);
begin
  StatusBar1.SimpleText := E.Message;
end;
The connection
Now, we go for the connection part. Since we have Edit1 holding the file name of the Excel workbook we are using as the data source, we'll place all the code related to ADOConnection1 in a separate procedure. 
procedure TForm1.ConnectToExcel;
var strConn :  widestring;
begin
  strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
           'Data Source=' + Edit1.Text + ';' +
           'Extended Properties=Excel 8.0;';

  AdoConnection1.Connected:=False;
  AdoConnection1.ConnectionString:=strConn;
  try
    AdoConnection1.Open;
    AdoConnection1.GetTableNames(ComboBox1.Items,True);
  except
    ShowMessage('Unable to connect to Excel, make sure
                 the workbook ' + Edit1.Text + ' exist!');
    raise;
  end;
end;(*ConnectToExcel*)

Note: The easiest way to create this procedure is to write its header in the private part of the form declaration and hit CTRL+SHIFT+C - Delphi adds skeleton code (as a result of the Code Completion feature) in the implementation section, for you to edit.

Here's what the ConnectToExcel procedure does: first we build the valid connection string using Edit1 edit box since it holds the full file name to the Excel workbook. We close the connection, set the new connection string, and try to connect. In case of an error a message is displayed and the error is re-raised - enabling Application.OnException to be called and the error message displayed in the status bar.

Caution: if you are trying to connect to a password-protected workbook, the connection will fail. Even if you know the password, and specify it in the ConnectionString property, and unless the workbook is already open in Excel, you'll receive the "Could not decrypt file." error - and won't be able to retrieve data.

Ok, suppose that we have connected to Excel. What now? How do you know the "table" names? Or better yet what are tables (database table objects) in an Excel sheet?!

As you will see on the next page, there are several ways you can specify a table (or range) in your select query statement...

Next page > Retrieving the data from Excel > Page 1, 2, 3, 4, 5, 6, 7

©2013 About.com. All rights reserved.