Accessing and managing MS Excel sheets with Delphi - Connect to: MS Excel

Connect to: MS Excel

Since Excel is JET OLE DB compliant, you can connect to it with Delphi using ADO (dbGO or AdoExpress) then retrieve the worksheet's data into an ADO dataset by issuing a SQL query - just like you would open a dataset against any database table.

using the ADO components, you can build an application that can use an Excel workbook as the database.

Excel is an out-of-process ActiveX server. ADO runs in-process, and saves the overhead of costly out-of-process calls.

 ADO connection cannot be used for sheet formatting or implementing formulas to cells unles pre-formated.you can carry out any conditional formatting using a (pre-recorded) macro in the worksheet.

You can connect to Excel using ADO with the two OLE DB Providers that are a part of MDAC: Microsoft Jet OLE DB Provider or Microsoft OLE DB Provider for ODBC Drivers.

The ConnectionString

The ConnectionString property tells ADO how to connect to the datasource.

TADOConnection component encapsulates the ADO connection object; it can be shared by multiple ADO dataset (TADOTable, TADOQuery, ...) components

a valid connection string involves only two (additional) pieces of information: the full path to the workbook, and the Excel file version.

ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyWorkBooks\myDataBook.xls;Extended Properties=Excel 8.0;';

Caution: you must use the Jet 4.0 Provider, since Jet 3.5 does not support the ISAM drivers. If you set the Jet Provider to version 3.5 you'll receive the "Couldn't find installable ISAM." error message.

Another Jet extended property is "HDR=". "HDR=Yes" means that there is a header row in the range, so the Jet will not include the first row of the selection into the dataset. If "HDR=No" is specified, then the provider will include the first row of the range (or named range) into the dataset. The first row in a range is considered to be the header row by default ("HDR=Yes"), therefore if you have column heading you do not need to specify this value. If you do not have column headings, you need to specify "HDR=No";

First, we need to connect all the DB related components together.

This is done in the OnCreate event handler for the Form1:

procedure TForm1.FormCreate(Sender: TObject);
begin
  AdoConnection1.LoginPrompt := False;
  AdoQuery1.Connection       := AdoConnection1;
  DataSource1.DataSet        := AdoQuery1;
  DBGrid1.DataSource         := DataSource1;
  DBNavigator1.DataSource    := DataSource1;

  Application.OnException    := DisplayException;
end;

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.

procedure TForm1.DisplayException(Sender: TObject; E: Exception);
begin
  StatusBar1.SimpleText := E.Message;
end;

the connection part.

procedure TForm1.ConnectToExcel;
var strConn :  widestring;
begin
  strConn:='Provider=Microsoft.Jet.OLEDB.4.0;' +
           'Data Source=' + Edit1.Text + ';' +
           'Extended Properties=Excel 8.0;';

  AdoConnection1.Connected:=False;
  AdoConnection1.ConnectionString:=strConn;
  try
    AdoConnection1.Open;
    AdoConnection1.GetTableNames(ComboBox1.Items,True);
  except
    ShowMessage('Unable to connect to Excel, make sure
                 the workbook ' + Edit1.Text + ' exist!');
    raise;
  end;
end;(*ConnectToExcel*)