|Accessing and managing MS Excel sheets with Delphi|
|Page 3: Retrieving the data from Excel. How to reference a table (or range) in an Excel workbook.|
Since now you know how to connect to an Excel workbook using Delphi and ADO, it's time to discuss how to retrieve sheet data.
Once connected, we are just one step away from retrieving the worksheet's data. First, we need to specify exactly what data we are trying to retrieve. In the most basic case this will be the data in the worksheet.
"Tables" in an Excel workbookA "database table" in Excel is simply a range (with a defined name). There are numerus ways you can reference a range in an Excel workbook. First, recall that the GetTableNames method of the ADOConnection object populates a string list with the names of tables in the database. In our case, the database is an Excel workbook - surpassingly the GetTableNames should return the sheet names of an Excel workbook - thus the sheets are merely "datase" tables.
The ConnectToExcel procedure in our simple Excel spreadsheet editor has a line of code that populates the ComboBox1 component with sheet (table) names: AdoConnection1.GetTableNames(ComboBox1.Items,True);
So, how do you specify the range, in the SQL statement's FROM part, you want to grab from Excel? Here's how:
Before we try this using Delphi, let's first create a sample workbook (or simply download it here):
Ok. It's time to retrieve this Excel workbook's data into a Delphi DBGrid.
"Select * From Excel"In our sample Excel spreadsheet editor Delphi project, the procedure that fetches the data from an Excel workbook is defined as:
Again, 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.
Note the the SQL.Text property of the AdoQuery1 component - we use the query specified in the Edit2 edit box component. First we call the ConnectToExcel procedure, to take care of the situation when the file name of the Excel workbook, we are interested in, has changed. The dataset (AdoQuery1) is closed, and the SQL SELECT statement is built to retrieve the data. Finally, the Open method is called, so the DB aware component linked to ADOQuery1 (through a DataSource1) can be populated with data.
To actually fetch the data, we add a call to the FetchData procedure in BitBtn1's OnClick event handler:
And, finally, if everything is ok, after you run your project, you should see a Delphi application displaying data from an Excel worksheet:
Caution: A query string like this one: "Select * From [Sheet1]", is not valid. In other words, if you are using a workbook sheet's name for the table name in the Select statement, and you omit the dollar sign and the brackets, or just the dollar sign, you could receive the "... engine could not find the specified object", the "Syntax error in FROM clause." or the "Syntax error in query. Incomplete query clause." error message.
A "story" about columnsAs discussed before, when connecting to Excel workbooks using ADO, the first row in a specified range is, by default, considered to hold the field (column) names - all subsequent rows are supposed to contain the records. When the first row does not contain headers, ADO automatically names the fields for you (where "F1" is the name of the first field, F2 represents the second field, and so on).
This is why, in our example, the unnamed column's header cell, was assigned the name "F4".
Now, that you know how to retrieve data from an Excel workbook, and how ADO names fields, it's time for a discussion on retrieved field (column) types...