Board index » delphi » USING EXCEL IN DELPHI 4.0

USING EXCEL IN DELPHI 4.0

Hi everyone,

How can i retrieve fields in a MS Excel document using Delphi ?

Thanks,

David

 

Re:USING EXCEL IN DELPHI 4.0


There is an ODBC driver for Excel files.
Quote
David Lepage wrote in message <36E48C86.D2DDD...@cgocable.ca>...
>Hi everyone,

>How can i retrieve fields in a MS Excel document using Delphi ?

>Thanks,

>David

Re:USING EXCEL IN DELPHI 4.0


If you need to retrieve the value of a few cells here and there, I recommend
TAdvExcel. It's available on the Delphi Super Page. If you want to import a
whole Excel file into any database that Delphi supports, try creating an
alias that points to an Excel file. Your Excel file actually becomes a table
within a database (or alias). You can then do Batchmoves, etc. I'm sorry but
I've never done this, although I'm sure it can be done.

Do you want the end user to read the file themselves, or will you be doing
the import operation? To import Excel files into a Paradox table, I use
Paradox 7 for Windows ; it's very fast. I do the importing myself.

--
Alain Quesnel

P.S.: remove the brackets from my e-mail address to reply.

Quote
David Lepage wrote in message <36E48C86.D2DDD...@cgocable.ca>...
>Hi everyone,

>How can i retrieve fields in a MS Excel document using Delphi ?

>Thanks,

>David

Re:USING EXCEL IN DELPHI 4.0


To read an Excel worksheet:

Create an ODBC DSN that points to the worksheet.

Excel files will not appear in the TTable.TableName property drop-down.
Enter the worksheet file name with no extension followed by a $ in the
TableName property.  You can optionally append a cell range.  For exmaple:
projects$A2:J1000

To read all of the rows in the worksheet when specifying a cell range make
the ending row number larger than the last possible row.

Bill

--

Bill Todd - TeamB
(TeamB cannot respond to email questions. To contact me
 for any other reason remove nospam from my address.)

Re:USING EXCEL IN DELPHI 4.0


I enter the excel worksheet file name(Book1A1:J1000) in the TableName
property like you say. but it always display "Table does not exist".
How can i do?
and how to specify a sheet in the MSEXCEL workbook?

Thanks,

Joseph

Re:USING EXCEL IN DELPHI 4.0


and how to insert record into MS Excel sheet?

joseph

Re:USING EXCEL IN DELPHI 4.0


You must set TableName to Book1$A1:J1000.  I have note tried inserting rows,
just reading data.

Bill

--

Bill Todd - TeamB
(TeamB cannot respond to email questions. To contact me
 for any other reason remove nospam from my address.)

Re:USING EXCEL IN DELPHI 4.0


But I need insert record into sheet, How can I do?

Joseph

Re:USING EXCEL IN DELPHI 4.0


I everybody,

First I want to thanks Bill and Alian for their answer. I've save a lot
of precious times :)

I,ve try what Bill said and it works properly. But it seem that there is
some conditions that we must respect in order to get good results.
First, the first line cells of your Range MUST be the name of your
columns, not data, because the BDE interpret the first line as the
columns names.

Second, the names of columns must be a name WITHOUT spaces or you will
get an error.

Also, I observe that all data number with decimals are trunced if there
is no decimal value (ex.: 16,0 =16). So it may be a problem if you need
to have it.

Joseph, I've not try to update Excel files using Delphi but you can try
this:

Ex.: if you have a small table like this in an Excel worksheet:

        A              B
1  Country   Population
2  USA       300000000
3  Canada     30000000

and you want to update the population of the USA to 301000000, use a
TQuery that point to your Excel Files and make this query:

update worksheetname$A1:B3
set Population=301000000
where Country='USA'

The' woksheetname' is the name of the worksheet of your Excel file and
A1:B3 is the range of your cells data. But I really don't know if it
work .

David

Re:USING EXCEL IN DELPHI 4.0


I everybody,

First I want to thanks Bill and Alian for their answer. I've save a lot
of precious times :)

I,ve try what Bill said and it works properly. But it seem that there is

some conditions that we must respect in order to get good results.

First, the first line cells of your Range MUST be the name of your
columns, not data, because the BDE interpret the first line as the
columns names.

Second, the names of columns must be a name WITHOUT spaces or you will
get an error.

Also, I observe that all data number with decimals are trunced if there
is no decimal value (ex.: 16,0 =16). So it may be a problem if you need
to have it.

Joseph, I've not try to update Excel files using Delphi but you can try
this:

Ex.: if you have a small table like this in an Excel worksheet:

        A              B
1  Country   Population
2  USA       300000000
3  Canada     30000000

and you want to update the population of the USA to 301000000, use a
TQuery that point to your Excel Files and make this query:

update worksheetname$A1:B3
set Population=301000000
where Country='USA'

The' woksheetname' is the name of the worksheet of your Excel file and
A1:B3 is the range of your cells data. But I really don't know if it
work .

David

Re:USING EXCEL IN DELPHI 4.0


I haven't done too much with Excel, but I've dumped tables to it so
users can play and print with the data.  Here's a proc you can use..

procedure ExportTableToExcel (tbl : tTable; sSheetName : string);
var xlApp : variant;
    sheet : variant;
    i : integer;
    irow : integer;
begin
  with tbl do
       begin
       open;
       first;
       xlApp := CreateOleObject('Excel.Application');
       xlApp.Workbooks.add(xlWBatWorksheet);
       xlApp.Workbooks[1].Worksheets[1].Name := sSheetName;
       sheet := xlApp.Workbooks[1].WorkSheets[sSheetName];
       xlApp.visible := true;

       iRow := 1;
       for i := 0 to fieldCount-1 do
           sheet.cells[iRow,i+1] := fields[i].fieldName;

       while not eof do
         begin
         inc(iRow);
         for i := 0 to fieldCount-1 do
             sheet.cells[iRow,i+1] := fields[i].asString;
         next;
         end;

       end;
end;

--

John Santora

Viridian Technologies, Inc.
2 Tanglewood
Aliso Viejo, CA 92656

Phone: 949-362-0263
Fax:     949-362-4830
Web:   http://www.ViridianTech.com

Other Threads