?
Solved

Connecting to EXEL file

Posted on 2002-03-14
30
Medium Priority
?
444 Views
Last Modified: 2010-04-04
Hi,
 
Is it possible to connect to an EXEL worksheet so that it could be used the same way as a database file? If it is , how? How can an xls file be created and populated with data from Delphi?

Regards, Laci
0
Comment
Question by:Laci030999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 7
  • 3
  • +3
30 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6864487
yes, via ole or via odbc-driver
0
 

Author Comment

by:Laci030999
ID: 6864502
Hi Kretzschmar ,


Thanks for the quick reaction.
Could you be a bit more specific?

0
 

Author Comment

by:Laci030999
ID: 6864524
Hi Kretzschmar ,


Thanks for the quick reaction.
Could you be a bit more specific?

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:revdmv
ID: 6864535
Here's a web site with some info

http://www.djpate.freeserve.co.uk/Automation.htm
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6864538
0
 

Author Comment

by:Laci030999
ID: 6864544
Hi Kretzschmar ,


Thanks for the quick reaction.
Could you be a bit more specific?

0
 
LVL 2

Accepted Solution

by:
gallaghe earned 800 total points
ID: 6864727
You could open a Excel file using ADO also

Example;
1) Open the Excel file, create a NamedRange i.e.
   A1 to B11
   Give it a name like "MyTest"
2) Populate the cells (exclude the first row) in rows
   2 thru 11.

3) Save the worksheet.

4) Fire up Delphi, on a form add a ADOConnection object
   and a ADOTable object.

For the connection object do you connection string like so
const MyConnectStr:WideString =
  'Provider=Microsoft.Jet.OLEDB.4.0;' +
  'User ID=Admin;' +
  'Data Source=%s;' +
  'Mode=Share Deny None;' +
  'Extended Properties=Excel 8.0;' +  { IMPORTANT TO MAKE THIS WORK }
  'Jet OLEDB:System database="";' +
  'Jet OLEDB:Registry Path="";' +
  'Jet OLEDB:Database Password="";' +
  'Jet OLEDB:Engine Type=35;' +
  'Jet OLEDB:Database Locking Mode=0;' +
  'Jet OLEDB:Global Partial Bulk Ops=2;' +
  'Jet OLEDB:Global Bulk Transactions=1;' +
  'Jet OLEDB:New Database Password="";' +
  'Jet OLEDB:Create System Database=False;' +
  'Jet OLEDB:Encrypt Database=False;' +
  'Jet OLEDB:Don''t Copy Locale on Compact=False;' +
  'Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False' ;

Then to connect

var
  S:String ;
...
  { Using some method set "S" to the Excel spreadsheet
    perhaps using an OpenDialog component }

MyConnectObj.ConnectionString := Format(MyConnectStr,[S]) ;
{ Set the TableName to the NamedRange }
ADOTable1.TableName := 'MyNamedRange' ;

Next open the connection followed by the table. You can now work with the data in the range like anyother database.

One note; If memory serves me correct anything in an Excel file that ends with a dollar sign does not work well with the above method.

Hope this is of use.
Kevin S. Gallagher
Project JEDI http://www.delphi-jedi.org/
0
 

Author Comment

by:Laci030999
ID: 6864777
Revdmv,  Kretzschmar

thanks for the links, helps me to learn about ole. Still reading ...


Gallaghe

Your code seems to be the closest to what I whished for. Is there a way to make a NamedRange from Delphi?  
0
 
LVL 2

Expert Comment

by:gallaghe
ID: 6864973
I have not tried to add a NamedRange to an Excel Spreadsheet in Delphi but there appears to be code for doing it at http://www.djpate.freeserve.co.uk/AutoExcl.htm#AddName

An alternative would be to create an empty NamedRange in a "Template" spreadsheet. When needed copy the template spreadsheet to the production (inuse) path and use it there. Since the empty NameRange is not really all that different from an empty table found in say MS-Access there is no real reason for creating NamedRanges on the fly in most instances.


Caveat: I have created fancy looking WorkSheets were dynamic data found in cells are actually NamedRanges (one cell per range) and I populate the cells when display the spreadsheet to the user. Got this idea from a Visual Basic example found on the web (same as the templates). At least VB is good for something ;-)

Kevin S. Gallagher
Project JEDI http://www.delphi-jedi.org/ 
0
 
LVL 1

Expert Comment

by:shyampaliyath
ID: 6867438
here is an code transfering the data from oracle to excel worksheet.



unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, OleCtrls, vcf1, DBTables, Db, OleCtnrs, ComCtrls, ExtCtrls,
  Buttons, FileCtrl;

type
  TForm1 = class(TForm)
    F1Book1: TF1Book;
    Query1: TQuery;
    HeaderControl1: THeaderControl;
    btnretrive: TBitBtn;
    btnsave: TBitBtn;
    edttime: TEdit;
    Timer1: TTimer;
    edtdate: TEdit;
    Label1: TLabel;
    EdtTableName: TEdit;
    FileListBox1: TFileListBox;
    DirectoryListBox1: TDirectoryListBox;
    DriveComboBox1: TDriveComboBox;
    FilterComboBox1: TFilterComboBox;
    edtexceltable: TEdit;
    Label2: TLabel;
    Label3: TLabel;
    BitBtn1: TBitBtn;
    procedure btnretriveClick(Sender: TObject);
    procedure btnsaveClick(Sender: TObject);
    procedure Timer1Timer(Sender: TObject);
    procedure FilterComboBox1Change(Sender: TObject);
    procedure edtexceltableKeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
  public
    { Public declarations }
    filename: string;
    lsdirectory,lspath,lsfiletype: string;
    end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.btnretriveClick(Sender: TObject);
VAR
I,J: INTEGER;
begin
     if edttablename.Text='' then
     begin
        showmessage('Enter The Oracle Table Name');
        exit;
     end;
     with query1 do
     begin
          close;
          sql.clear;
          sql.add('SELECT * FROM '+ TRIM(EdtTableName.Text));
          open;
     end;
     FOR  I:=0 TO QUERY1.FieldCount-1 DO
     BEGIN
          F1BOOK1.TextRC[1,I+1]:=QUERY1.Fields[I].FieldName;
     END;
     J:=2;
     QUERY1.First;
     WHILE NOT QUERY1.EOF DO
     BEGIN
          FOR I:=0 TO QUERY1.FieldCount-1 DO
          BEGIN
               F1BOOK1.TextRC[J,I+1]:=QUERY1.FIELDBYNAME(QUERY1.Fields[I].FieldName).ASSTRING;
          END;
          J:=J+1;
          QUERY1.Next;
     END;
end;


procedure TForm1.btnsaveClick(Sender: TObject);
var
lsfilename: string;
begin
     if edtexceltable.Text='' then
     begin
          showmessage('Enter The Excel Table Name');
          exit;
     end;
     lsfilename:=trim(edtexceltable.text);
     lsfiletype:=string(filtercombobox1.Text);
     lsdirectory:=DirectoryListBox1.Directory;
     lspath:=lsdirectory+'\'+lsfilename+'.xls';
     if lsfiletype='Excel-4 files[*.xls]' then
     begin
          f1book1.Write(lspath,2);
          showmessage('FILE SAVED AS EXCEL VER-4 FORMAT');
          exit;
     end;
     if lsfiletype='Excel-5 files[*.xls]' then
     begin
          f1book1.Write(lspath,4);
          showmessage('FILE SAVED AS EXCEL VER-5 FORMAT');
          exit;
     end;
end;

procedure TForm1.Timer1Timer(Sender: TObject);
begin
edttime.Text :=Timetostr(Time);
edtdate.Text :=datetostr(date);
end;
procedure TForm1.FilterComboBox1Change(Sender: TObject);
begin
     lsfiletype:=string(filtercombobox1.Text);
     if lsfiletype='Excel-4 files[*.xls]' then
     begin
          FileListBox1.Mask:='*.xls';
          exit;
     end;
     if lsfiletype='Excel-5 files[*.xls]' then
     begin
          FileListBox1.Mask:='*.xls';
          exit;
     end;
end;
procedure TForm1.edtexceltableKeyPress(Sender: TObject; var Key: Char);
begin
   if key in['.'] then
   begin
    Key := #0;
    MessageBeep ($FFFFFFFF);
   end;
end;



end.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6867453
shyampaliyath,

actually comments are recommended rather than answers, so that the questioner can decide who to grade and to keep the question open for other experts

read
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#7

meikl ;-)
0
 

Author Comment

by:Laci030999
ID: 6876183
I think Kretzschmar is wright,  the fair thing to do is reopening the Q.
I was away on friday ... now I am trying to use the info you provided ... will be back when I am through.(or need some more help :-) )

Regards,  Laci.  
0
 

Author Comment

by:Laci030999
ID: 6876184
I think Kretzschmar is wright,  the fair thing to do is reopening the Q.
I was away on friday ... now I am trying to use the info you provided ... will be back when I am through.(or need some more help :-) )

Regards,  Laci.  
0
 

Author Comment

by:Laci030999
ID: 6876193
I think Kretzschmar is wright,  the fair thing to do is reopening the Q.
I was away on friday ... now I am trying to use the info you provided ... will be back when I am through.(or need some more help :-) )

Regards,  Laci.  
0
 

Author Comment

by:Laci030999
ID: 6876195
I think Kretzschmar is wright,  the fair thing to do is reopening the Q.
I was away on friday ... now I am trying to use the info you provided ... will be back when I am through.(or need some more help :-) )

Regards,  Laci.  
0
 

Author Comment

by:Laci030999
ID: 6876198
uupss, I only ment one time :))
0
 

Author Comment

by:Laci030999
ID: 6876346
I am trying to start Excel and load an excel sheet.
It seems easy, yet I can't get it working. Could  someone possibly show me a working example?
0
 
LVL 2

Expert Comment

by:gallaghe
ID: 6876739
If you want an example, email me at
kevin.s.gallagher@state.or.us and I will send one.
0
 

Expert Comment

by:Rabster
ID: 6882629
I could post code to load and save data to/from an Excel file using COMOBJ to/from a TListView component if this is what you are looking for. It can be adapted for other target components too.
0
 
LVL 2

Expert Comment

by:gallaghe
ID: 6883139
Here is how to create a NamedRange in an Excel file. You will need to download www.djpate.freeserve.co.uk/D5Excel.zip, unzip and make the following changes:


1) Add procedure CreateNamedRange to the private section of the forms interface
type
  TMain = class(TForm)
  ...
  private
  ...
    procedure CreateNamedRange ;
  end;

2) Add the procedure to which creates the NamedRange called KevinOne from A2 to E10
procedure TMain.CreateNamedRange;
var
  WB: _Workbook;
  N: Excel97.Name;
  oMakeRangeVisible: OleVariant;
begin
  WB := ExcelApplication1.ActiveWorkbook ;

  oMakeRangeVisible := True ;

  N := WB.Names.Add('KevinOne', '=Sheet1!$A$2:$E$10',
    oMakeRangeVisible, EmptyParam, EmptyParam,
    EmptyParam, EmptyParam, EmptyParam, EmptyParam,
    EmptyParam, EmptyParam) ;
end;

3)Replace FormShow with the following
procedure TMain.FormShow(Sender: TObject);
const
  TheFile = 'C:\My Documents\Saveme.xls' ;
var
  WBk: _Workbook;
  WkS: _WorkSheet;
  Filename: OleVariant;
begin
  { No need to start Excel ourselves, since the ExcelApplication's AutoConnect
    property is True }
  lcid := GetUserDefaultLCID;
  ExcelApplication1.Visible[lcid]:=True;


  { Create a new workbook }
  if not FileExists('C:\My Documents\Saveme.xls') then
  begin
    WkBk.ConnectTo(ExcelApplication1.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid));
    WS.ConnectTo(WkBk.Worksheets[1] as _Worksheet);
    WS.Name := 'Data';
    EnterData;
    CreateNamedRange ;
    WS.SaveAs(TheFile) ;
    ShowMessage('Just created spreadsheet') ;
    Application.Terminate ;
    Application.ProcessMessages ;
  end ;


  { Open existing workbook }
  FileName := TheFile ;
  WBk := ExcelApplication1.Workbooks.Open(Filename, EmptyParam, EmptyParam,
                                EmptyParam, EmptyParam, EmptyParam,
                                EmptyParam, EmptyParam, EmptyParam,
                                EmptyParam, EmptyParam, EmptyParam,
                                EmptyParam, LCID) ;

    WkS := WBk.Worksheets.Item['Data'] as _Worksheet ;
    WkS.Activate(LCID) ;

end;


NOTE: Make sure the const "TheFile" points to a valid path on your PC.

Okay when you first run the project we check to see if the XLS file is on disk, if not create it (along with defining our NamedRange) and then close the demo. Now start it again, it opens the newly created XLS file.

Now you have the code to create new spreadsheets with NamedRanges along with using ADO components to access the NamedRanges.

PS
What a great asset Deborah Pate is to the Delphi community for all she has done showing programmers how to work with ole automation.

Hope this is of use.
Kevin S. Gallagher
Project JEDI http://www.delphi-jedi.org/ 


0
 

Author Comment

by:Laci030999
ID: 6883191
Hi Rabster,

Sounds like one more way to connect to exel. I'd be glad to take a look at it. :)


Experts

Gallaghe's ADO based solution was perfect to read the data from an exel sheet. The only problem with it is that a named range has to exists in every file. My program is getting many of them because it is a data exchange format between companies. It would be to much for the users to make a name for every file they get. So now I am looking for a way to name that range for them.  

... I have no experience with OLE, and was not able to start excel and have it load a file. So right now a working example for that would be a great help. :)

Regards, Laci.


   
0
 

Author Comment

by:Laci030999
ID: 6883202
gallaghe

uuppss, while I was typing I did not  recognize your comment. Looks like it is worth to refresh the Q before adding a new one :-))
0
 

Author Comment

by:Laci030999
ID: 6883223
www.djpate.freeserve.co.uk/D5Excel.zip - I'm using D6 Enterprise, do you think they are compatible?
0
 

Author Comment

by:Laci030999
ID: 6883259
Kevin

there could be something wrong about the link
: "The page cannot be found" ;-)
0
 

Author Comment

by:Laci030999
ID: 6883292
This Q is deeper then I thought. Well worth for more points...
0
 
LVL 2

Expert Comment

by:gallaghe
ID: 6883342
I just tried the link and it worked fine for me. Concerning working under D6 I don't know why it would not other then the comment on her page about

N: Excel_TLB.Name;  // or N := Excel97.Name; if you're using D5

under the heading "How to add a name to a workbook"

BTW I emailed you the project under you personal email address.
0
 

Author Comment

by:Laci030999
ID: 6883366
Thanks, I will check it out.

I know why the link didn't work for me: There is an extra comma at the end. Writing the URL to the browser without it solved the problem.

Thanks again, Laci.
0
 
LVL 2

Expert Comment

by:gallaghe
ID: 6883407
Okay
0
 
LVL 2

Expert Comment

by:gallaghe
ID: 6899193
How are you making out?
0
 

Author Comment

by:Laci030999
ID: 6899295

Kevin,

Thanx for your help, I have just made a range in an excel sheet. :-))

Since you gave me all the info I needed it is time to close this Q.


EXPERTS
thank you for your time ...


Regards, Laci
 


   
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month11 days, 14 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question