Win32::OLE - how to effectively create large spreadsheet?

[Tom Niesytto]

Howdy - I am using Win32::OLE to create a spreasheet from data.
Spreadsheet is fairly big (2000 rows x 30 columns) and cells contain
embedded newlines (if itwas not for that I would simbly import tab
delimited file).
To write to Excel spreadsheet I do sth like:
-----------------------------------------
sub write_array_as_excel_file
{
my $output_file = shift;
my $array_ref = shift; #reference to array of references to arrays

eval{$excel = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;

unless (defined $excel) {
$excel = Win32::OLE->new('Excel.Application', 'Quit')
or die "Oops, cannot start Excel";
}
#to avoid excessive dialogs when saving in non-Excel format
$excel->{DisplayAlerts} = 0;

# get a new workbook
my $wbook = $excel->Workbooks->Add
|| print STDERR "didnt add new workbook: $!\n";

# write to a particular cell
my $wsheet = $wbook->Worksheets(1);
my $ref;
my $cell_value;
my $row_num = 0;
my $col_num = 0;

foreach $ref (@{$array_ref}) {
$row_num++;
$col_num = 0;

foreach $cell_value (@{$ref}){
$col_num++;
$wsheet->Cells($row_num,$col_num)->{Value} = "$cell_value";
}

}
$wbook->SaveAs($fullname_output_file);
undef $wbook;
}
-----------------------------

Problem is - it is slow.
Takes about 20 mins to write out the output.
What am I doing wrong here?

Thanks for any pointers,

JT

 

[Brian Helterline]

Howdy - I am using Win32::OLE to create a spreasheet from data.
Spreadsheet is fairly big (2000 rows x 30 columns) and cells contain
embedded newlines (if itwas not for that I would simbly import tab
delimited file).
To write to Excel spreadsheet I do sth like:
-----------------------------------------
# write to a particular cell
my $wsheet = $wbook->Worksheets(1);
my $ref;
my $cell_value;
my $row_num = 0;
my $col_num = 0;

foreach $ref (@{$array_ref}) {
$row_num++;
$col_num = 0;

foreach $cell_value (@{$ref}){
$col_num++;
$wsheet->Cells($row_num,$col_num)->{Value} = "$cell_value";
}

Have you tried writing it all at once? It *may* be faster
check out the examples under Win32::OLE:

# write a 2 rows by 3 columns range
$sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
[ 42, 'Perl', 3.1415 ]];


It appears you already have the correct reference in $array_ref. You just
have
to figure out the correct argument to the Range method.

 

[Jay Tilton]

(e-mail address removed) (Tom Niesytto) wrote:

: Howdy - I am using Win32::OLE to create a spreasheet from data.
: Spreadsheet is fairly big (2000 rows x 30 columns) and cells contain
: embedded newlines (if itwas not for that I would simbly import tab
: delimited file).
: To write to Excel spreadsheet I do sth like:
: -----------------------------------------
: sub write_array_as_excel_file
: {
: my $output_file = shift;
: my $array_ref = shift; #reference to array of references to arrays
:
: eval{$excel = Win32::OLE->GetActiveObject('Excel.Application')};
: die "Excel not installed" if $@;
:
: unless (defined $excel) {
: $excel = Win32::OLE->new('Excel.Application', 'Quit')
: or die "Oops, cannot start Excel";
: }
: #to avoid excessive dialogs when saving in non-Excel format
: $excel->{DisplayAlerts} = 0;
:
: # get a new workbook
: my $wbook = $excel->Workbooks->Add
: || print STDERR "didnt add new workbook: $!\n";

That failure message is misleading.
$! has nothing to do with OLE errors.

: # write to a particular cell
: my $wsheet = $wbook->Worksheets(1);
: my $ref;
: my $cell_value;
: my $row_num = 0;
: my $col_num = 0;
: foreach $ref (@{$array_ref}) {
: $row_num++;
: $col_num = 0;
: foreach $cell_value (@{$ref}){
: $col_num++;
: $wsheet->Cells($row_num,$col_num)->{Value} = "$cell_value";
^^^^^^^^^^^^^
See perlfaq4, `` What's wrong with always quoting "$vars"? ''

: }
: }
: $wbook->SaveAs($fullname_output_file);
: undef $wbook;

Explicitly undef'ing a lexical variable that is about to fall out of scope
is unnecessary.

: }
: -----------------------------
:
: Problem is - it is slow.
: Takes about 20 mins to write out the output.
: What am I doing wrong here?

Assigning values to 60,000 cells one at a time is like filling a bathtub
with an eyedropper.

Excel can fill an entire range of cells for you. Tell it the size of your
tub and open the faucet.

sub write_array_as_excel_file {
my $output_file = shift;
my $array_ref = shift; #reference to AoA

# Determine the dimensions of a rectangular range of
# cells that will hold the data.
require List::Util;
my $ncols = List::Util::max( map scalar @$_, @$array_ref );
my $nrows = @$array_ref;

# Creation of excel object, workbook object, and
# worksheet object elided.

$wsheet->range(
$wsheet->cells( 1, 1),
$wsheet->cells( $nrows, $ncols ),
) -> {Value} = $array_ref;
}

Many orders of magnitude faster.

 

[Tom Niesytto]

Folks - thanks A LOT for all the replies.
I learned several good lessons from them.

And YES - writing ia all at once takes no time whatsoever.
All you have to do is to calculate the Range parameters and
move the line that set Value property outside of the loop:

foreach $ref (@{$array_ref})
{
$row_num++;
$col_num = 0;
foreach $cell_value (@{$ref})
{
$col_num++;
}
}
$wsheet->Range($wsheet->Cells(1,1), $wsheet->Cells($row_num, $col_num)
)->{Value} = $array_ref;

Now it takes about a second ;-)

Cheers,

JT