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]

In reply to Re: Slow Excel access via Win32::OLE by Unanimous Monk
in thread Slow Excel access via Win32::OLE by josephjohn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.