Accessing and managing MS Excel sheets with Delphi [page 1/7]

Computing

{
Article: 
Accessing and managing MS Excel sheets with Delphi

http://delphi.about.com/library/weekly/aa090903a.htm

How to retrieve, display and edit Microsoft Excel spreadsheets
with ADO (dbGO) and Delphi. This step-by-step article describes
how to connect to Excel, retrieve sheet data, and enable editing
of data (using the DBGrid). You'll also find a list of most common
errors (and how to deal with them) that might pop up in the process.
}


Download zipped project

Unit1.pas
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids, DB, ADODB, Buttons,
  ComCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    Edit1: TEdit;
    Panel1: TPanel;
    Label1: TLabel;
    ADOQuery1: TADOQuery;
    Edit2: TEdit;
    Label2: TLabel;
    BitBtn1: TBitBtn;
    StatusBar1: TStatusBar;
    ComboBox1: TComboBox;
    Label3: TLabel;
    ListBox1: TListBox;
    ADOConnection2: TADOConnection;
    ADOQuery2: TADOQuery;
    Panel2: TPanel;
    Button1: TButton;
    Button2: TButton;
    procedure BitBtn1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    procedure ConnectToExcel;
    procedure FetchData;
    procedure GetFieldInfo;

    procedure DisplayException(Sender:TObject; E: Exception);
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation
{$R *.dfm}
uses typinfo;


{ TForm1 }

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*)

procedure TForm1.FetchData;
begin
  StatusBar1.SimpleText:='';

  if not AdoConnection1.Connected then 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;

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  FetchData;

  GetFieldInfo;
end;

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

  Application.OnException:= DisplayException;


  //connect to an Access database to send the data to Excel
  AdoConnection2.LoginPrompt:=False;
  AdoConnection2.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\!Gajba\About\QuickiesContest.mdb;Persist Security Info=False';
  AdoQuery2.Connection:=AdoConnection2;
  AdoConnection2.Open;
end;

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

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;

procedure TForm1.Button2Click(Sender: TObject);
var sAppend : string;
begin
  sAppend:='INSERT INTO [Sheet2$] IN "' + Edit1.Text + '" "Excel 8.0;" SELECT AuthorEmail, Title, Description FROM Articles';

  AdoQuery2.SQL.Text:=sAppend;
  AdoQuery2.ExecSQL;
end;

procedure TForm1.Button1Click(Sender: TObject);
var sCopy : string;
begin
  sCopy := 'SELECT * INTO ["Excel 8.0;Database=' + Edit1.Text + '"].[SheetAuthors] FROM Authors';

  AdoQuery2.SQL.Text:=sCopy;
  AdoQuery2.ExecSQL;
end;
end.



Form1.dfm

Select Form1 (empty project),
Right Click to get context popup menu, 
Select View As Text, 
Paste the text into Editor,
Select View As Form.

object Form1: TForm1
  Left = 330
  Top = 239
  Width = 377
  Height = 349
  Caption = 'Delphi + ADO: Excel '
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 0
    Top = 125
    Width = 369
    Height = 66
    Align = alClient
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DBNavigator1: TDBNavigator
    Left = 0
    Top = 93
    Width = 369
    Height = 32
    DataSource = DataSource1
    Align = alTop
    TabOrder = 1
  end
  object Panel1: TPanel
    Left = 0
    Top = 0
    Width = 369
    Height = 93
    Align = alTop
    TabOrder = 2
    object Label1: TLabel
      Left = 4
      Top = 8
      Width = 53
      Height = 13
      Caption = 'Workbook:'
    end
    object Label2: TLabel
      Left = 4
      Top = 48
      Width = 31
      Height = 13
      Caption = 'Query:'
    end
    object Label3: TLabel
      Left = 180
      Top = 48
      Width = 36
      Height = 13
      Caption = 'Sheets:'
    end
    object Edit1: TEdit
      Left = 4
      Top = 24
      Width = 357
      Height = 21
      TabOrder = 0
      Text = 
        'C:\Program Files\Borland\Delphi7\Projects\AdoExcel\AdoDelphiExce' +
        'l.xls'
    end
    object Edit2: TEdit
      Left = 4
      Top = 64
      Width = 169
      Height = 21
      TabOrder = 1
      Text = 'SELECT * FROM [Sheet1$]'
    end
    object BitBtn1: TBitBtn
      Left = 284
      Top = 60
      Width = 77
      Height = 25
      Caption = '&Re-fetch'
      TabOrder = 2
      OnClick = BitBtn1Click
      Kind = bkRetry
    end
    object ComboBox1: TComboBox
      Left = 180
      Top = 64
      Width = 97
      Height = 21
      Style = csDropDownList
      ItemHeight = 13
      TabOrder = 3
    end
  end
  object StatusBar1: TStatusBar
    Left = 0
    Top = 303
    Width = 369
    Height = 19
    Panels = <>
  end
  object ListBox1: TListBox
    Left = 0
    Top = 191
    Width = 369
    Height = 71
    Align = alBottom
    ItemHeight = 13
    TabOrder = 4
    Visible = False
  end
  object Panel2: TPanel
    Left = 0
    Top = 262
    Width = 369
    Height = 41
    Align = alBottom
    BevelOuter = bvLowered
    TabOrder = 5
    object Button1: TButton
      Left = 20
      Top = 8
      Width = 149
      Height = 25
      Caption = 'Copy data from Access'
      TabOrder = 0
      OnClick = Button1Click
    end
    object Button2: TButton
      Left = 192
      Top = 8
      Width = 145
      Height = 25
      Caption = 'Insert data from Access'
      TabOrder = 1
      OnClick = Button2Click
    end
  end
  object ADOConnection1: TADOConnection
    CursorLocation = clUseServer
    LoginPrompt = False
    Mode = cmShareDenyNone
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    Left = 120
    Top = 148
  end
  object DataSource1: TDataSource
    DataSet = ADOQuery1
    Left = 44
    Top = 148
  end
  object ADOQuery1: TADOQuery
    Parameters = <>
    Left = 196
    Top = 148
  end
  object ADOConnection2: TADOConnection
    ConnectionString = 
      'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\!Gajba\About\Qui' +
      'ckiesContest.mdb;Persist Security Info=False'
    LoginPrompt = False
    Provider = 'Microsoft.Jet.OLEDB.4.0'
    Left = 140
    Top = 212
  end
  object ADOQuery2: TADOQuery
    Connection = ADOConnection2
    Parameters = <>
    Left = 220
    Top = 212
  end
end


{
********************************************
Zarko Gajic
About.com Guide to Delphi Programming
http://delphi.about.com
email: delphi@aboutguide.com
free newsletter: http://delphi.about.com/library/blnewsletter.htm
forum: http://forums.about.com/ab-delphi/start/
********************************************
}
Delphi