Title: How to transfer data from excel to a Database
Update for Delphi 2007
and maybe back to Delphi 7 (no proof)
CODE
etc.. XcelApp.activeCell.Value;
must be
CODE
etc.. XcelApp.activeCell.Value2;
.Value
must be changed to
.Value2
This program was tested in Delphi 6, running XP and office XP
Sometimes we need to do some special analysis in Excel, if there is a lot of data which has to be sorted in various ways, it is easier done in a database.
In this FAQ we will
1) Open an Excel file
2) Write the data to a table
For educational purpose, suppose we have an excel file with 4 columns.
In this file we have:
Column A -- date
Column B -- time
Column C -- tag
column D -- span
This could be a log from a computerized system, PABX, DCS, PLC or some other electronic device.
We need to prepare a database table, with the following structure (5 fields):
No -- id number, preferable a key field, this will be handy for sorting and other analysis
Date -- (column A)
Time -- (coumn B)
Tag
Span
Use your favorite database, (mine is the BDE for quick'n dirty stuff), but it also can be ADO or something else using a table component.
Ingredients:
1) form
2) 2x bitbuttons, for opening and loading
bbtOpen
+
bbtLoad
3) table
4) datasource
5) dbgrid
6) dbNavigator
7) statusbar (handy for resisizing and displaying messages)
8) editbox,
edtRangeEnd
to indicate te number of records
9) OpenDialog
10) ExcellApplication,
XcelApp
, from the Servers Palette
Step 1
Arrange all the visual components, and link table, datasource, fields etc..
Step 2
Define the variables
CODE
Var
Form1: TForm1 //automatically done by Delphi
LineNumber, LCID: integer;
Step 3
Now we will open the excell file and make it visible, the focus will be transferred to Excel. In case of multiple worksheets, just select the appropriate one in excel, before switching back to the export utility program.
We will use the onclick event of
bbtOpen
CODE
procedure TForm1.bbtOpenClick(Sender: TObject); //Open Excel
var
WBk: _WorkBook;
WS : _WorkSheet;
FileName: OleVariant;
begin
if OpenDialog1.Execute then
begin
LCID := GetUserDefaultLCID;
XcelApp.Connect;
XcelApp.visisible[LCID] := true;
FileName := OpenDialog1.FileName;
WBk := XcelApp.WorkBooks.Open( FileName, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam,
EmptyParam, LCID);
WS := WBk.Worksheets.Item['Sheet1'] as _Worksheet;
WS.Activate(LCID);
end;
//if
end;
Step 4
Now the excel file should be visible, adjust
edtRangeEnd
to the last row of data necessary and ..
CODE
procedure TForm1.bbtLoadClick(Sender: TObject); //Open Excel
var
i,j : integer;
begin
j:= StrToInt(edtRangeEnd.Text);
For i:=1 to j do
begin
Table1.Append;
Table1No.Value := i;
LineString ;= IntToStr(i);
with XcelApp do
begin
Range['A'+ LineString, 'A' + LineString].Select;
Table1Date.Value := XcelApp.activeCell.Value;
Range['B'+ LineString, 'B' + LineString].Select;
Table1Time.Value := XcelApp.activeCell.Value;
Range['C'+ LineString, 'C' + LineString].Select;
Table1Tag.Value := XcelApp.activeCell.Value;
Range['D'+ LineString, 'D' + LineString].Select;
Table1Span.Value := XcelApp.activeCell.Value;
end;
//with
Table1.Post;
end;
//For
end;