Accessing and managing MS Excel sheets with Delphi - Retrieve data from: MS Excel

Retrieve data from: MS Excel

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.

sheets are merely "datase" tables.recall that the GetTableNames method of the ADOConnection object populates a string list with the names of tables

AdoConnection1.GetTableNames(ComboBox1.Items,True);

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]".

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;

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;

columns

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

 it's time for a discussion on retrieved field (column) types...

Contrary to the traditional (relational database) approach, when using Excel as the datasource for you application, there is no direct way to specify the data types for columns in retrieved Excel ranges (sheets).

Excel as a data "provider", does not provide ADO with the information about the data it contains. The Jet must scan through the referenced range to guess the type of data it caries (the formatting of the cells does not count). The number of rows Jet scans is 8, by default. If you need more rows to be examined (before field types are determined), you can add a "MaxScanRows=X" to the connection string, where X specifies the value from 0 to sixteen 16. A value of zero tells the Jet to scan all existing rows.

Caution: Some problems may occur if you have mixed string values with numeric values "under" the same column. If this is the case, the Jet could return mixed null-string or null-number values.

Recall that the GetFieldInfo is called inside the button's "Re-fetch" OnClick event handler. This is how the GetFieldInfo procedure looks:

uses typinfo; //don't forget
...
procedure TForm1.GetFieldInfo;
var
  i      : integer;
  ft     : TFieldType;
  sft    : string;
  fname  : string;
begin
  ListBox1.Clear;
  for i := 0 to AdoQuery1.Fields.Count - 1 do
  begin
    ft := AdoQuery1.Fields[i].DataType;
    sft := GetEnumName(TypeInfo(TFieldType), Integer(ft));
    fname:= AdoQuery1.Fields[i].FieldName;
    
    ListBox1.Items.Add(Format('%d) NAME: %s TYPE: %s,
                              [1+i, fname, sft]));
  end;
end;

field in an Excel data source can be only one of the following:

• Numeric, ADO type adDouble, Delphi type ftFloat - precision 15,
• Text, ADO type adVarChar, Delphi type ftWideString - max length 255,
• Memo, ADO type adVarWChar, Delphi type ftMemo,
• Currency, ADO type adCurrency, Delphi type ftCurrency,
• Boolean, ADO type adBoolean, Delphi type ftBoolean,
• Date, ADO type adDate, Delphi type ftDateTime,