Solved

Connecting to EXEL file

Posted on 2002-03-14
30
425 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
  • 17
  • 7
  • 3
  • +3
30 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
yes, via ole or via odbc-driver
0
 

Author Comment

by:Laci030999
Comment Utility
Hi Kretzschmar ,


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

0
 

Author Comment

by:Laci030999
Comment Utility
Hi Kretzschmar ,


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

0
 

Expert Comment

by:revdmv
Comment Utility
Here's a web site with some info

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

Expert Comment

by:kretzschmar
Comment Utility
0
 

Author Comment

by:Laci030999
Comment Utility
Hi Kretzschmar ,


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

0
 
LVL 2

Accepted Solution

by:
gallaghe earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Laci030999
Comment Utility
uupss, I only ment one time :))
0
 

Author Comment

by:Laci030999
Comment Utility
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
Comment Utility
If you want an example, email me at
kevin.s.gallagher@state.or.us and I will send one.
0
 

Expert Comment

by:Rabster
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
www.djpate.freeserve.co.uk/D5Excel.zip - I'm using D6 Enterprise, do you think they are compatible?
0
 

Author Comment

by:Laci030999
Comment Utility
Kevin

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

Author Comment

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

Expert Comment

by:gallaghe
Comment Utility
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
Comment Utility
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
Comment Utility
Okay
0
 
LVL 2

Expert Comment

by:gallaghe
Comment Utility
How are you making out?
0
 

Author Comment

by:Laci030999
Comment Utility

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now