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

Discussion in 'Perl Misc' started by Tom Niesytto, Feb 27, 2004.

  1. Tom Niesytto

    Tom Niesytto Guest

    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
    Tom Niesytto, Feb 27, 2004
    #1
    1. Advertising

  2. "Tom Niesytto" <> wrote in message
    news:...
    > 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.

    >
    > }
    > $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, Feb 27, 2004
    #2
    1. Advertising

  3. Tom Niesytto

    Jay Tilton Guest

    (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.
    Jay Tilton, Feb 27, 2004
    #3
  4. Tom Niesytto

    Tom Niesytto Guest

    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

    (Tom Niesytto) wrote in message news:<>...
    > 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
    Tom Niesytto, Feb 28, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. J.R.
    Replies:
    51
    Views:
    1,003
    Zoo Keeper
    Jan 6, 2004
  2. Dan Fitzpatrick

    Spreadsheet::Excel and Ole::Storage Error

    Dan Fitzpatrick, Mar 26, 2008, in forum: Ruby
    Replies:
    0
    Views:
    103
    Dan Fitzpatrick
    Mar 26, 2008
  3. Lance Hoffmeyer
    Replies:
    0
    Views:
    238
    Lance Hoffmeyer
    Nov 17, 2003
  4. Zhidian Du
    Replies:
    1
    Views:
    132
    A. Sinan Unur
    Dec 11, 2003
  5. Pam
    Replies:
    5
    Views:
    191
    Matt Garrish
    Sep 27, 2006
Loading...

Share This Page