Click here to get back home

Excel file manipulation in HPUX system
 Home | NewsGroups | Search | About
Login Password
Register Now!
 comp.lang.perl.modules    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
Excel file manipulation in HPUX system Jim Chiang 10-14-2004
Posted by Jim Chiang on October 14, 2004, 8:13 am
Please log in for more thread options
 Everyone,
Regarding various modules that opens up access to Excel files, namely
--
* Win32::OLE
* Spreadsheet::WriteExcel and Spreadsheet::ParseExcel
Can anyone comment on the performance aspect of reading and writing
large Excel files with these modules?
Thanks all,
J.C.


Posted by John McNamara on October 14, 2004, 4:46 pm
Please log in for more thread options
 Jim Chiang wrote:

> Can anyone comment on the performance aspect of reading and
> writing large Excel files with these modules?


Here are some personal observations on the performance of these
modules.

Win32::OLE
==========

* Instantiating an Excel automation object has a large overhead. As
such it is better suited to once-off or one-at-a-time usage then as a
server solution.

* Writing data is a lot faster if you use the Range() method as
opposed to one cell at a time:

my $range = $worksheet->Range("A1:B4");
$range-> = @data;



Spreadsheet::WriteExcel
=======================

* Generally fast for creating files.

* Use Spreadsheet::WriteExcel::Big for file over 7MB.

* In the 2.xx versions (Excel97), all unique strings to be written are
stored in memory. Thus, performance will degrade when writing files
with a large amount of unique string data. There is no degradation
related to writing numbers.

* Writing formulas is slow due to the overhead of Parse::RecDescent.
Use the store_formula() and repeat_formula() methods if you need to
write a lot of similar formulas. See "Improving performance when
working with formulas" in the docs:

http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#Improving_performance_when_working_with_formulas


Spreadsheet::ParseExcel
=======================

* Can be very memory intensive when dealing with large files. See
"Reducing the memory usage of Spreadsheet::ParseExcel":

http://www.perlmonks.org/index.pl?node_id=379743



But as always when you are interested in performance the best thing to
do is to write some benchmarks. :-)

John.
--


Similar ThreadsPosted
Reading contents of an excel file from a test file May 15, 2007, 2:49 am
Spreadsheet-ParseExcel: Parsing various MS Excel file versions / grabing checkbox values? September 17, 2004, 3:11 am
Mysql-DBD Perl module Installation Problem in HPUX June 30, 2005, 9:05 am
cron manipulation and perl-cgi September 20, 2004, 5:02 pm
perl date manipulation October 21, 2004, 5:02 pm
MS SQL server and SQL DATETIME manipulation in perl April 29, 2005, 2:23 am
Windows Registry manipulation using Unix April 29, 2005, 11:46 am
Help Perl and Excel August 16, 2006, 9:25 am
OLE Excel AutoFilter September 10, 2006, 4:28 pm
Perl and Excel April 6, 2007, 1:06 am

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap