It's easy enough to take a flat file (with rows as records and columns as fields) and dump it into a single Excel spreadsheet. However, I want to extend the "2-dimensional" -ness of the standard way to include another dimension. This does what I want: 
$output = [
               [ [0],[0],[0] ],
               [ [1,1,1],[1,1,1],[1,1,1] ],
               [ [2,2,2],[2,2,2],[2,2,2] ],
               [ [3,3,3],[3,3,3],[3,3,3] ],
               [ [4,4,4],[4,4,4],[4,4,4] ],
               [ [5,5,5],[5,5,5],[5,5,5] ],
               [ [6,6,6],[6,6,6],[6,6,6] ],
               [ [7,7,7],[7,7,7],[7,7,7] ],
               [ [8,8,8],[8,8,8],[8,8,8] ],
               [ [9,9,9],[9,9,9],[9,9,9] ],
              ];

#...other code that sets up the Excel sheet...

my $Book = $Excel->Workbooks->Open("$excelfile");

for $i (1..9) {
  my $Sheet = $Book->Worksheets("$i");
  $Sheet->Range("A1:C3")->{'Value'} = $$output[$i];
}

[download]
In this case, I have 9 sheets, named literally "1" through "9". Sheet "1" has the number 1 in cells A1:C3, Sheet "2" has the number 2 in cells A1:C3, etc. But I cannot define the "3-d" array all at once as in the example. I have a _huge_ file to deal with, one line at a time. I have a field which I want to use to define the sheet name. As I process the flat file, I want to push records into reference arrays specific to the sheet. This does the trick for dumping the flat file into one Excel sheet: 
open (FILEIN,"$filein") or die "Open $filein failed: $!";
while (<FILEIN>) {
    @data = split;
    push @$output, [@data];
}    
close FILEIN;

#...other code that sets up the Excel sheet...

my $Book = $Excel->Workbooks->Open("$excelfile");
my $Sheet = $Book->Worksheets("$sheetname");

# ...other code that determines
#       $begcol,$begrow,$endcol,endrow

$Sheet->Range("$begcol$begrow:$endcol$endrow")->{'Value'} = $output;

[download]
So how do I take this line: 
push @$output, [@data];

[download]
and add the extra dimension? $data[1] has the sheet name I want to use. I tried 
push $$day_output[$data[1]], [@data];

[download]
but it doesn't compile and complains thus: "Type of arg 1 to push must be array (not array element) at blah blah blah..." I'm new to references so perhaps this is easy with the right syntax... Thanks, Scott Job

In reply to Excel, multiple sheets, and reference arrays, oh my! by scottjob

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.