1. Technology

Your suggestion is on its way!

An email with a link to:

http://delphi.about.com/od/database/l/aa090903c.htm

was emailed to:

Thanks for sharing About.com with others!

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.
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 2: Creating an Excel spreadsheet editor
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

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 workbook
A "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:

  • Use the sheet name followed by a dollar sign ($) and surrounded by square brackets, like "[Sheet1$]",
  • Specify a named range, like "MyRange",
  • Use an unnamed range by appending standard Excel row/column notation to the end of the sheet name, like "[Sheet1$A5:D20]".

Before we try this using Delphi, let's first create a sample workbook (or simply download it here):

  1. Start Excel, this by default creates a new workbook with 3 sheets (depending on your Excel version).
  2. Save the workbook as "AdoDelphiExcel.xls"
  3. Add some data to the work sheet "Sheet1", like in the picture below:
    Samle Excel workbook
  4. Note that we have 5 columns (of which 4 have a heading). The fist column (Birth date), holds a date value. The second column (Name) holds some string data. The third column (Points) holds some integer data. The fourth column holds some real data - the column title was left blank intentionally. Finally the last column named "Formula" holds some calculated data. The value of the cell, let's say, E2 is "=D2*100/C2"; the rest of the cells in the "Formula" column use the same calculation.

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:
procedure TForm1.FetchData;
begin
  StatusBar1.SimpleText:='';

  ConnectToExcel;

  AdoQuery1.Close;
  AdoQuery1.SQL.Text:=Edit2.Text;
  try
    AdoQuery1.Open;
  except
    ShowMessage('Unable to read data from Excel, 
                 make sure the query ' + Edit1.Text + 
                 ' is meaningful!');
    raise;
  end;
end;

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:

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  FetchData;
  
  //explained later
  GetFieldInfo;
end;

And, finally, if everything is ok, after you run your project, you should see a Delphi application displaying data from an Excel worksheet:

Delphi displaying Excel data in a DBGrid
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 columns
As 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...

Next page > A discussion on Excel field (column) types > Page 1, 2, 3, 4, 5, 6, 7

©2014 About.com. All rights reserved.