INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Delphi with Other Programs

How to transfer data from excel to a Database by svanels
Posted: 11 Sep 06 (Edited 13 Mar 08)

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;

Back to Embarcadero: Delphi FAQ Index
Back to Embarcadero: Delphi Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close