For the life of me, I cannot figure out why my program is writing only the first row of my excel spreadsheet. Granted a different function is writing the rest of the rows, but I am passing the worksheet back to this function to do so. I am including both the code and the output. The output clearly indicates that what I am writing should lead to

code

#!/usr/bin/perl 

use lib 'SPE/blib/lib';

use strict;
use warnings;
#===========================================================
#  DECLARATIONS
#===========================================================
use Data::Dumper;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
use Text::ParseWords;

#===========================================================
#  VARIABLES
#===========================================================

my $dir = 'M:\Business Process Reengineering\Taxonomy\Reviewed Content
+\Group D';

my $column_max = 26;

#===========================================================
#  SUBROUTINES
#===========================================================
sub trim {
    $_[0] =~ s/\s+$//;
    $_[0] =~ s/^\s+//;
}

sub parse_excel {

  my %by_agency;

  my $excel_file = shift;
  my $acronyms = shift;

  -e $excel_file or 
    die "Must provide valid XLS file! $excel_file, $!\n";

  # create a ParseExcel object
  my $excel_obj = Spreadsheet::ParseExcel->new();

  warn "Parsing $excel_file (may take a long time)";
    
  my $workbook = $excel_obj->Parse($excel_file);
    
  # we need to get each worksheet from the workbook in turn

  my @worksheet = @{$workbook->{Worksheet}};

  my $worksheet_i;
  my $header;
    
  for my $worksheet ( @{$workbook->{Worksheet}} ) {
    
    # {Cells}[row][col]
    
    my $last_col = $worksheet->{MaxCol};
    my $last_row = $worksheet->{MaxRow};

    warn "Last row: $last_row";
    warn "Last col: $last_col";

    $header = [ map { $_->Value } @{$worksheet->{Cells}[0]} ] ;

    for my $row ( 1 .. $last_row ) {

      my $row = $worksheet->{Cells}[$row];

      ref($row->[2]) and (my $V = $row->[2]->Value) or next;

#      print "row: " . $V;
#      print "\n";

      trim $V;

      my $key = $acronyms -> { $V } ;

      if ($key) {
      push @ { $by_agency{$key} } ,  [ map { $_->Value }  @$row ] ;
      } else {
      print qq(no key found for "$V"\n);
      }

    }
    last; # only process one worksheet
  }

  ($header, \%by_agency);
  
}

sub read_acronyms {

    my $f = "$dir\\..\\A-B-C-D Acronyms.csv";

    open F, $f or die "couldnt open $f: $!";

    <F>;

    my %ret;

    while (<F>) {

    my @data = quotewords("\t", 0, $_);
    for (@data) {
        trim ($_);
    } 

    $ret{$data[1]} = $data[0];

    }

    \%ret;

}

sub new_agency {

    my ($header, $acronym) = @_;

    my $w = "Organization_$acronym.xls";

    # Create a new workbook called simple.xls and add a worksheet
    my $workbook  = Spreadsheet::WriteExcel->new($w);
    my $worksheet = $workbook->addworksheet();

    # The general syntax is write($row, $column, $token). Note that ro
+w and
    # column are zero indexed

    my $i;

    # Write some text
    
    $worksheet->write(0, $i++,  $_) for @$header;
    $worksheet;
}

sub write_excel {

    my ($header, $agency, $acronyms) = @_;

    warn 'write_excel';

    for my $acronym (keys %$agency) {

    my @agency_rows = @ { $agency->{$acronym} } ;

    my $w = new_agency ($header, $acronym) ;

    my ($row, $col) = (1, 0);
    
    for my $agency_row (@agency_rows) {

        warn "agency_row: " . Dumper($agency_row);
        warn "witdth of row: " . scalar @$agency_row;

        for (@$agency_row) {

        warn "$w->write($row, $col,  $_)";
                  $w->write($row, $col,  $_);
        warn "$w->write($row, $col,  $_) WRITTEN";

        ++$col;
        }

        ++$row;

    }

    return; # why write all the excel files when one is failing...

    }

}


#===========================================================
sub usage {
    
  my ( $tool ) = $0 =~ m,([^\/]+$),;
                
  print <<HERE;
------------------------------------------------------------
USAGE:
                
   $tool 
                
Takes an Excel file, parses it into plain text delimited 
fields and rows, and sends the results to STDOUT. The default 
field and record delimiters are "\\t" and "\\n" if neither 
is given.
------------------------------------------------------------
HERE
exit 0;
}
#===========================================================

#===========================================================
#  PROGRAM PROPER
#===========================================================

sub main {

    my $f   = "$dir/Organization_GroupD-no-CR-SMALL.xls";
 
    my $acronyms = read_acronyms;
#    print Dumper $acronyms;
    my ($header, $agency) = parse_excel($f, $acronyms, @_);
#    print Dumper $agency;
#    print Dumper $header;
    write_excel($header, $agency, $acronyms);
    0;
}

exit main(@ARGV);

[download]

output



c:\Documents and Settings\tbrannon\My Documents\Applications\BulkloadC
+heck\Bulkloader\internals>perl convert-org.pl
perl convert-org.pl
Parsing M:\Business Process Reengineering\Taxonomy\Reviewed Content\Gr
+oup D/Organization_GroupD-no-CR-SMALL.xls (may take a long time) at c
+onvert-org.pl line 44, <F> line 353.
Last row: 38 at convert-org.pl line 62.
Last col: 19 at convert-org.pl line 63.
write_excel at convert-org.pl line 144.
agency_row: $VAR1 = [
          'Headquarters',
          'Other',
          'Citizens Committee for New York City',
          'Citizens Committee for New York City',
          '',
          '',
          '',
          '(212) 989-0909',
          '(212) 989-0909',
          'N',
          '',
          '',
          'http://ccnyc.neighborhoodlink.com/ccnyc/',
          '305 7th Avenue',
          '',
          '',
          'New York',
          'NY',
          '10001',
          'Citizens committee for NYC is a non profit organization whi
+ch encourages and supports volunteer action that improves the quality
+ of life in City neighborhoods.'
        ];
witdth of row: 20 at convert-org.pl line 157.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 0,  Headq
+uarters) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 0,  Headq
+uarters) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 1,  Other
+) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 1,  Other
+) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 2,  Citiz
+ens Committee for New York City) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 2,  Citiz
+ens Committee for New York City) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 3,  Citiz
+ens Committee for New York City) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 3,  Citiz
+ens Committee for New York City) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 4,  ) at 
+convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 4,  ) WRI
+TTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 5,  ) at 
+convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 5,  ) WRI
+TTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 6,  ) at 
+convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 6,  ) WRI
+TTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 7,  (212)
+ 989-0909) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 7,  (212)
+ 989-0909) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 8,  (212)
+ 989-0909) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 8,  (212)
+ 989-0909) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 9,  N) at
+ convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 9,  N) WR
+ITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 10,  ) at
+ convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 10,  ) WR
+ITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 11,  ) at
+ convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 11,  ) WR
+ITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 12,  http
+://ccnyc.neighborhoodlink.com/ccnyc/) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 12,  http
+://ccnyc.neighborhoodlink.com/ccnyc/) WRITTEN at convert-org.pl line 
+163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 13,  305 
+7th Avenue) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 13,  305 
+7th Avenue) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 14,  ) at
+ convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 14,  ) WR
+ITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 15,  ) at
+ convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 15,  ) WR
+ITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 16,  New 
+York) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 16,  New 
+York) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 17,  NY) 
+at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 17,  NY) 
+WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 18,  1000
+1) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 18,  1000
+1) WRITTEN at convert-org.pl line 163.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 19,  Citi
+zens committee for NYC is a non profit organization which encourages 
+and supports volunteer action that improves the quality of life in Ci
+ty neighborhoods.) at convert-org.pl line 161.
Spreadsheet::WriteExcel::Worksheet=HASH(0x22402e8)->write(1, 19,  Citi
+zens committee for NYC is a non profit organization which encourages 
+and supports volunteer action that improves the quality of life in Ci
+ty neighborhoods.) WRITTEN at convert-org.pl line 163.

c:\Documents and Settings\tbrannon\My Documents\Applications\BulkloadC
+heck\Bulkloader\internals>

[download]

Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality

In reply to Spreadsheet::WriteExcel - writes the header but no subsequent rows by princepawn

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.