1. 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/ ******************************************** }

©2014 About.com. All rights reserved.