In the TPJ#9 there is a sample of loading an xls file from a text file. I can load it, but all rows are the same- the first record. I think the problem is in the way I populate the array. Can someone steer me back on track? Thanks.

The sample code says - 
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{SheetsInNewWorkbook} = 1;
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
$Sheet->{Name} = 'Candle';

# Insert column titles
my $Range = $Sheet->Range("A1:E1");
$Range->{Value} = [qw(Time Open High Low Close)];
$Range->Font->{Bold} = 1;

$Sheet->Columns("A:A")->{NumberFormat} = "h:mm";
# Open/High/Low/Close to be displayed in 32nds
$Sheet->Columns("B:E")->{NumberFormat} = "# ?/32";

# Add 15 minute data to spreadsheet
print "Add data\n";
$Range = $Sheet->Range(sprintf "A2:E%d", 2+$#Bars);
$Range->{Value} = \@Bars;

[download]
The last statement shows how to pass arrays to OLE objects. The Win32::OLE module automatically translates each array reference to a SAFEARRAY, the internal OLE array data type. This translation first determines the maximum nesting level used by the Perl array, and then creates a SAFEARRAY of the same dimension. The @Bars array already contains the data in the correct form for the spreadsheet:
(Time1, Open1, High1, Low1, Close1,
... TimeN, OpenN, HighN, LowN, CloseN)

What I don't understand is HOW the data gets into the @Bars array. It looks like its an Array of Arrays, but I'm confused as to how to generate it. If my code reads a line, should I split it at the commas into my @fields, & then push the @fields entry onto my @rows array.
Here's my code. 
while ($inBuf = <csvFILE>) {
  chomp($inBuf);
  ++$line_count; 

  $inBuf =~ s/^\"//;         # Take out any LEADING or
  $inBuf =~ s/\"$//;         # TRAILING double quotes

# OK, Process this record
  @fields = split(/\,/,$inBuf);
  print "\nProcessing record $fields[0]";
  push @rows, @fields; 
  ++$item_count;

} # End of csvFILE or we reached our Runaway count

close csvFILE;

[download]

In reply to Using Array of Arrays by Aim9b

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.