Help: Delphi <-> Excel
Craig Jackson
Greetings,
What is the "best" way to exchange data between a Delphi
program and Excel? I need to read a large set of data
(approximately 20,000 cells) from an Excel workbook,
perform some complex calculations, and then store the
output data in another worksheet within the same excel
workbook. So far, I've come up with the following
approaches:
1. Export the Excel spreadhsheet as a CSV file and read
this into my Delphi program using standard text
file I/O. Output is written to a text file and then
imported/parsed back into Excel.
This will force my users to mess around with CSV files.
Lots of manual intervention. Not a good solution!
2. Use ODBC and treat the Excel spreadsheet as a database.
This approach requires my users to have ODBC configured
properly on their PCs. Too much room for error.
Not a good solution!
3. Use DDE to read/write data to/from Excel.
I downloaded the TExcel component from the Delphi Super
Page. This component greatly simplifies DDE for use with
Excel, but performance is abyssmally slow on my Pentium
PC.
4. Use OLE.
I haven't explored OLE yet, so I'm not certain what
advantages it has over DDE.
Can anyone suggest any other techniques?
Is DDE always so slow? Since all the transfers are done
in memory, it would seem that it should be faster than
file I/O, but it's not. Since DDE transfers all data as
ASCIIZ strings, each cell needs to be converted. This
may be the bottleneck, although Read() does conversions
too. Is it possible to exchange more than one cell at a
time (i.e., read an entire excel record)?
What advantages does OLE have over DDE?
Finally, I've been getting some pressure to convert my app
from Delphi to VB. Does VB offer any better way to
exchange data with Excel?
Thanks for any help,
Craig
--
-----------------------------------------------------------------------------
Craig Jackson
General Motors R&D Center
Operations Research Department
** My address has been altered to discourage automated unsolicited
junk E-Mail. Remove the "[NO SPAM FOR ME]" phrase when replying.
Stefan Hoffmeister
: Craig Jackson <"cjackson[NO SPAM FOR ME]"@gmr.com> wrote:
>I need to read a large set of data
>(approximately 20,000 cells) from an Excel workbook,
>perform some complex calculations, and then store the
>output data in another worksheet within the same excel
>workbook. So far, I've come up with the following
>approaches:
>
>1. Export the Excel spreadhsheet as a CSV file and read
> this into my Delphi program using standard text
> file I/O. Output is written to a text file and then
> imported/parsed back into Excel.
>
> This will force my users to mess around with CSV files.
> Lots of manual intervention. Not a good solution!
But definitely the fastest! You can use TExcel (below) to drive
Excel to do all this, i.e. instruct Excel so save this range to
that file... No user intervention at all!
>2. Use ODBC and treat the Excel spreadsheet as a database.
<Shudder>
>3. Use DDE to read/write data to/from Excel.
>
> I downloaded the TExcel component from the Delphi Super
> Page. This component greatly simplifies DDE for use with
> Excel, but performance is abyssmally slow on my Pentium
> PC.
This is the co-author in defense mode<g>: TExcel was constructed
to push data INTO Excel, not to retrieve it.
You should be able to get much better performance, if you *send*
the data from Excel via DDE to a Delphi form (TDDEServerConv) and
control the whole operation via TExcel through the client
conversation.
>4. Use OLE.
>
> I haven't explored OLE yet, so I'm not certain what
> advantages it has over DDE.
Speed. It is SLOWER (so its a DISadvantage).
>Is DDE always so slow? Since all the transfers are done
>in memory, it would seem that it should be faster than
>file I/O, but it's not. Since DDE transfers all data as
>ASCIIZ strings, each cell needs to be converted. This
>may be the bottleneck, although Read() does conversions
>too.
You need the right technique. With TExcel you are "pulling" data
from Excel; if you are "pushing" it from Excel DDE should be darn
fast (like in the case when TExcel pushes data into Excel),
> Is it possible to exchange more than one cell at a
>time (i.e., read an entire excel record)?
Yes, it is possible to exchange the xlTable type (currently the
conversation is using CF_TEXT), but I have no documentation about
xlTable. I am desperately waiting for the Excel SDK to arrive...
>What advantages does OLE have over DDE?
Its slower. Oh, and it is easier to use.
>Finally, I've been getting some pressure to convert my app
>from Delphi to VB. Does VB offer any better way to
>exchange data with Excel?
Yes <g>. DDE and OLE. Honestly: None at all. Using the same
techniques in VB you will have the same problems.
I have no idea whether VB would be able to process the data as
fast as Delphi, so it might be slower.
--
Stefan.Ho...@UNI-Passau.DE
http://kakadu.rz.uni-passau.de/~w4hoff01/
University of Passau, Bavaria, Germany
Brian C. Wright
>What is the "best" way to exchange data between a Delphi
>program and Excel? I need to read a large set of data
>(approximately 20,000 cells) from an Excel workbook,
>perform some complex calculations, and then store the
>output data in another worksheet within the same excel
>workbook. So far, I've come up with the following
>approaches:
>
>1. Export the Excel spreadhsheet as a CSV file and read
> this into my Delphi program using standard text
> file I/O. Output is written to a text file and then
> imported/parsed back into Excel.
>
> This will force my users to mess around with CSV files.
> Lots of manual intervention. Not a good solution!
[ODBC, OLE and DDE options snipped]
In case you haven't considered it, you can import/export DBase files
from Excel and read them using the BDE.
A little less messy than CSV files, but has some of the same problems.
Could you automate the process in Excel (Write a VBA app to export
information as DBF file, launch your analysis program, and read
results back in)?
Brian
Jeff Zeanah
bcwright@spam_me_not.ucsd.edu (Brian C. Wright) wrote:
>On Thu, 08 May 1997 09:43:51 -0700, Craig Jackson <"cjackson[NO SPAM
>FOR ME]"@gmr.com> wrote:
>
>>What is the "best" way to exchange data between a Delphi
>>program and Excel? I need to read a large set of data
>>(approximately 20,000 cells) from an Excel workbook,
>>perform some complex calculations, and then store the
>>output data in another worksheet within the same excel
>>workbook. So far, I've come up with the following
>>approaches:
>>
>>1. Export the Excel spreadhsheet as a CSV file and read
>> this into my Delphi program using standard text
>> file I/O. Output is written to a text file and then
>> imported/parsed back into Excel.
>>
>> This will force my users to mess around with CSV files.
>> Lots of manual intervention. Not a good solution!
>[ODBC, OLE and DDE options snipped]
>
>In case you haven't considered it, you can import/export DBase files
>from Excel and read them using the BDE.
>
>A little less messy than CSV files, but has some of the same problems.
>
>
We have done dbf saves but have found the process "quirky" . IE, the
user has to export from a very clean sheet. For example, if
operations have been performed in a cell and removed (leaving the cell
blank) it can still make a mess of the dbf save.
If you choose to go this route. Save a few dbf files from excel and
look at them in the database desktop and see how well it works. Some
problems may have been removed in later versions of Excel.
We are doing a similar application and are planning to go to DDE but
now I am concerned about your comments about speed.
Regards,
Jeff Zeanah
Z Solutions, LLC
jeffz%zsolutions.com (please change the % to @ to e-mail)
Fredrik Larsson
I have seen an Excel component on Delphi super page. That one might do the
job for you. Need the URL check World of links below and then Delphi VCLs
and that will lead your on the right path to Delphi Super Page.
--
|_o_
| | This message was brought to you by Fredrik Larsson.
_/ \_ mail2fred @ hotmail.com (space spam protect)
___________________________________________________________________
Mood at the moment:
"...I don't want your magazines, I don't want your clothes,
take them from my house, let me be alone,
never try to catch me and/or call me on the phone,
don't send me letters I don't want to know..."
URLs:
* Home http://home3.swipnet.se/~w-33398/
* World of links http://home3.swipnet.se/~w-33398/world/world.html
* Developers
* corner: http://home3.swipnet.se/~w-33398/developer/
___________________________________________________________________
Peter Hill
Brian C. Wright wrote:
[snip]
> >What is the "best" way to exchange data between a Delphi
> >program and Excel? I need to read a large set of data
> >(approximately 20,000 cells) from an Excel workbook,
> >1. Export the Excel spreadhsheet as a CSV file and read
> > this into my Delphi program using standard text
> [ODBC, OLE and DDE options snipped]
>
> In case you haven't considered it, you can import/export DBase files
> from Excel and read them using the BDE.
>
> Brian
The "standard" answer to this seems to be that there is a component
TExcel on Delphi Super Page, but I'm finding that it (or Windows or
Excel) chokes after about 1000 items are passed, freezing my machine. It
uses DDE, so now I'm moving on to testing some OLE-based approaches.
Any new solutions welcome!!!
HTH
Peter Hill.
Kenneth A. Faw
OLE Automation is probably the simplest approach, but somewhat slow. You
do have the advantage of knowing your progress in the data exchange,
however, and you can always keep your user entertained. If you are using
D2 or D3, you could spawn a thread, and the speed issue may go away (again,
if you have something else your user or your application can be doing at
the time).
Back in my VB3 days, I moved about the same amount of data between Excel
and VB using Automation. Without multi-threading, it provided my users
some time to go and get coffee, but as I said, I was aware of the progress,
and could entertain them in the meantime, at least with a progress bar.
Ken
--------------------------------------
Notice at no time do my fingers leave my hands...
Quality training touches your technical side and helps you grow.
--------------------------------------
Master Certified Delphi 3 Instructor
Master Certified C++Builder Instructor
http://www.knowledgeable.com
kf...@knowledgeable.com
+
Peter Hill <ph...@labyrinth.net.au> wrote in article
<338A46...@labyrinth.net.au>...
Stefan Hoffmeister
: Peter Hill <ph...@labyrinth.net.au> wrote:
>Brian C. Wright wrote:
>[snip]
>> >What is the "best" way to exchange data between a Delphi
>> >program and Excel? I need to read a large set of data
>> >(approximately 20,000 cells) from an Excel workbook,
>[snip]
>> >1. Export the Excel spreadhsheet as a CSV file and read
>> > this into my Delphi program using standard text
>[snip]
>> [ODBC, OLE and DDE options snipped]
>>
>> In case you haven't considered it, you can import/export DBase files
>> from Excel and read them using the BDE.
>>
>[snip]
>> Brian
>The "standard" answer to this seems to be that there is a component
>TExcel on Delphi Super Page, but I'm finding that it (or Windows or
>Excel) chokes after about 1000 items are passed, freezing my machine. It
>uses DDE, so now I'm moving on to testing some OLE-based approaches.
No longer <g>. I am about to release version 2.3 with many
improvements, including an automatic cache flush...
The problem is with Excel which seems to cache statements and
becomes incredibly slow after a couple of hundreds (oh, and it
still ACCEPTS these statements while in fact it should better
not).
Anyway, version 2.3 will fix this. I will be happy to privately
send final betas to people interested.
Fredrik Larsson
> Any new solutions welcome!!!
Haven't tried this and don't know if it works.
1. Create an ODBC alias that references to an excel folder.
2. Create your BDE connection with an alias. It can use excel drivers (just
checked)
3. Two tables and a batchmove component
4. Move 'em over!
Please let us know if this work. If possible also mail me.
--
|_o_
| | This message was brought to you by Fredrik Larsson.
_/ \_ mail2fred @ hotmail.com (space spam protect)
===================================================================
* Home http://home3.swipnet.se/~w-33398/
* World of links http://home3.swipnet.se/~w-33398/world/world.html
* Developers
* corner: http://home3.swipnet.se/~w-33398/developer/
===================================================================
Dalal Chirag Deepak
Peter Hill (ph...@labyrinth.net.au) wrote:
: Brian C. Wright wrote:
: [snip]
: > >What is the "best" way to exchange data between a Delphi
: > >program and Excel? I need to read a large set of data
: > >(approximately 20,000 cells) from an Excel workbook,
: [snip]
: > >1. Export the Excel spreadhsheet as a CSV file and read
: > > this into my Delphi program using standard text
: [snip]
: > [ODBC, OLE and DDE options snipped]
: >
: > In case you haven't considered it, you can import/export DBase files
: > from Excel and read them using the BDE.
: >
: [snip]
: > Brian
: The "standard" answer to this seems to be that there is a component
: TExcel on Delphi Super Page, but I'm finding that it (or Windows or
: Excel) chokes after about 1000 items are passed, freezing my machine. It
: uses DDE, so now I'm moving on to testing some OLE-based approaches.
: Any new solutions welcome!!!
: HTH
: Peter Hill.
DSP has another component (delphi/d10free/aexcel.zip) which is claimed
to be advanced TExcel. Try it out.
HTH.
- Chirag Dalal
----------------------------------------------------------
M. Tech., Department of Computer Science and Engineering
Indian Institute of Technology, Powai, Bombay.
----------------------------------------------------------
E-Mail Address: da...@cse.iitb.ernet.in
For Cool Delphi Components See
Home page: http://www.cse.iitb.ernet.in/~dalal/delphi
----------------------------------------------------------