josephjohn has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,
I have a script which produces output in a Excel sheet. I use Win32::OLE. The file has 7 work sheets and one of them is quite big - 10 columns and 8000 to 10000 rows. I use a for loop to write the data cell by cell from an array. Most columns have string data except two which has date time values. This operation takes few minutes to complete.
Is there a way to speed it up by alternate methods (e.g. creating CSV files and joining them together or such). Any comments and suggestions are welcome.
I don't have much formatting at the moment, but need to introduce it also (bold/ italics/ colors).
Following is the piece of code I use currently. 
$Sheet = $Book-> Worksheets->Add;
$Sheet = $Book-> Worksheets("Sheet7");
$Sheet-> Activate();       
$Sheet-> {Name} = "BU Detail";
$range = 1;
foreach my $detail_line (@bu_detail)
{
    my @detail_fields = split(/,/,$detail_line);
    $Sheet-> Range('A'.$range)->{Value} = $detail_fields[0];
    $Sheet-> Range('B'.$range)->{Value} = $detail_fields[1];
    $Sheet-> Range('C'.$range)->{Value} = $detail_fields[2];
    $Sheet-> Range('D'.$range)->{Value} = $detail_fields[3];
    $Sheet-> Range('E'.$range)->{Value} = $detail_fields[4];
    $Sheet-> Range('F'.$range)->{Value} = $detail_fields[5];
    $Sheet-> Range('G'.$range)->{Value} = $detail_fields[6];
    $Sheet-> Range('H'.$range)->{Value} = $detail_fields[7];
    $Sheet-> Range('I'.$range)->{Value} = $detail_fields[8];
    $Sheet-> Range('J'.$range)->{Value} = $detail_fields[9];
    $range++;
}    
@columnheaders = qw(A:J);
foreach $range(@columnheaders)
{
    $Sheet-> Columns($range)->AutoFit();
}

[download]
Regards Joseph John

Replies are listed 'Best First'.
Re: Slow Excel access via Win32::OLE
by hesco (Deacon) on Apr 26, 2006 at 09:10 UTC
    I can't speak for whether it would be any faster, but a couple of years ago, I generated some nightly reports using Spreadsheet::WriteExcel. As I recall, we exported data to csv, then used this module to prepare it for distribution. In fact, here is one that could have saved us a few steps. Perhaps it could nudge your performance. See: Spreadsheet::WriteExcel::FromDB. Hope that might help. -- Hugh
[reply]
Re: Slow Excel access via Win32::OLE
by samizdat (Vicar) on Apr 26, 2006 at 14:18 UTC
    OLE transfer is an old dog, and that's giving MS-resident canines a bad name. :) Think about what it's doing... your program and Excel both reside in main memory, along with the workbook you're creating. Your program talks to the OLE DLLs, which talk to Excel. Excel interprets the command, modifying the workbook. On and on we go, almost certainly causing a few virtual memory swaps long the way.

    Spreadsheet::WriteExcel (or WriteExcel::Big) can be your good and loyal friend, giving MS dogs a chance at redemption. S::WE has a much simpler feature set, geared only towards writing content to .XLS files. Much more direct, and much less memory footprint.

    Don Wilde
    "There's more than one level to any answer."
[reply]
Re: Slow Excel access via Win32::OLE
by Unanimous Monk (Sexton) on Apr 26, 2006 at 19:39 UTC
    It will be much faster if you copy the whole array into Excel at once. It may be faster to use one of the other solutions provided above or to create a .csv file, but copying the whole array at once should be around 10x faster than looping through each cell.

    Also, see code below for formatting bold, italic, and color.

    use strict;
use Win32::OLE;

my $rowCount = 10000;
my $colCount = 10;

print "Build array\n";

my @data;
for my $row (0..$rowCount) {
  for my $col (0..$colCount) {
    $data[$row][$col] = "Row: $row, Col: $col";
  };
};

print "Open Excel\n";

my $app = Win32::OLE->new('Excel.Application');
my $book = $app->Workbooks->Add;
my $sheet = $book-> Worksheets->Add;
my $cell = $sheet->Range('A1');
$app->{Visible} = 1;

print "Copy array to Excel\n";

$sheet->Range($cell, $cell->offset($rowCount, $colCount))->{Value} = \
+@data;

print "Format\n";
my $red    = 0x0000FF;
my $green  = 0x00FF00;
my $blue   = 0xFF0000;
my $yellow = 0x00FFFF;

$cell->EntireColumn->Font->{Bold} = 1;
$cell->EntireColumn->Font->{Italic} = 1;
$cell->EntireColumn->Interior->{Color} = $yellow;

    [download]
[reply]
[d/l]
      Hi Unanimous,
      Thanks a lot for the new method. It works great. I should say the data transfer speed is more than 10x. It saved my work.
      Thanks to others for suggestions on WriteExcel. I kept away from it as i've heard that existing excel files cannot be worked on with that module.
      Joseph.
[reply]

Back to Seekers of Perl Wisdom