trickyq:

If you want to build the sheet in order, then you may find it simpler to build your data in an array, then at the final step, write the table to a spreadsheet. Something like:

#!/usr/bin/perl 
use strict;     
use warnings;   
use Spreadsheet::WriteExcel;
          
# First build your data table in an array
my @T;    
while (<DATA>) {
    chomp;                                                            
+                                                         
    my ($row, $col, $val) = split /,\s*/, $_;
    last if ! defined $col;
    $T[$row][$col] = $val;
}         
          
my $WB = Spreadsheet::WriteExcel->new("FOO.xls");
my $WS = $WB->add_worksheet("FRED");
          
# Then write the data into your spreadsheet in order
for (my $row=0; $row<@T; ++$row) {
    next if !defined $T[$row];
    for (my $col=0; $col < @{$T[$row]}; ++$col) {
        if (defined $T[$row][$col]) {
            $WS->write($row, $col, $T[$row][$col]);
            print "T[$row][$col] = $T[$row][$col]\n";
        } 
    }     
}         
          
__DATA__  
1, 2, banana    
0, 0, Cell A1   
0, 7, bazooka   
3, 3, Cell D4   
1, 1, apple     
7, 0, flooglehorn

[download]

As you can see, the data is in a random order, but it writes it out in the desired order:

$ perl 974548.pl
T[0][0] = Cell A1
T[0][7] = bazooka
T[1][1] = apple
T[1][2] = banana
T[3][3] = Cell D4
T[7][0] = flooglehorn

[download]

And if you want to prevent skipped lines, keep a separate variable for the output row:

# Then write the data into your spreadsheet in order
my $out_row=0;
for (my $row=0; $row<@T; ++$row) {
    next if !defined $T[$row];
    for (my $col=0; $col < @{$T[$row]}; ++$col) {
        if (defined $T[$row][$col]) {
            $WS->write($out_row, $col, $T[$row][$col]);
            print "T[$out_row][$col] = $T[$row][$col]\n";
        } 
    }
    ++$out_row;
}

[download]

Those changes give you:

$ perl 974548.pl
T[0][0] = Cell A1
T[0][7] = bazooka
T[1][1] = apple
T[1][2] = banana
T[2][3] = Cell D4
T[3][0] = flooglehorn

[download]

Update: Forgot to implement the bit to prevent skipped rows on the first pass. Just added it to the end.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

In reply to Re: the final thorn in your toe by roboticus
in thread the final thorn in your toe by trickyq

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.