|Accessing and managing MS Excel sheets with Delphi|
|Page 2: Creating an Excel spreadsheet editor using Delphi and ADO|
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 projectStart 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):
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"
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):
The connectionNow, 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.
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...