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

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

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:

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

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