Spreadsheet::WriteExcel - writes the header but no subsequent rows

princepawn has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on Spreadsheet::WriteExcel - writes the header but no subsequent rows Select or Download Code

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel - writes the header but no subsequent rows by jmcnamara (Monsignor) on Nov 22, 2002 at 15:05 UTC
The problem is due to the fact that the Workbook and Worksheet objects have different scope. The Workbook object falls out of scope at the end of new_agency() and its DESTROY sub is invoked. This reaps the data written thus far and writes it out to a file. The Worksheet object isn't DESTROYed since you maintain a reference to it. So, you can continue writing to it but it's parent Workbook is no longer there. The solution is to return a reference to the Workbook as well as the Worksheet so that they both have the same scope: `sub new_agency { #... return($worksheet, $workbook); } # And later: my ($w, $wb) = new_agency ($header, $acronym);` [download] In situations where you are creating Workbook and Worksheet objects in subs it is best to explicitly call the Workbook `close()` method to ensure that destruction is performed in the correct order. -- John.	[reply] [d/l]
Re: Spreadsheet::WriteExcel - writes the header but no subsequent rows by aging acolyte (Pilgrim) on Nov 22, 2002 at 15:03 UTC
Princepawn, I can't be too sure but it sounds like you may be flattening an array when passing it into a sub routine i.e instead of passing an array by reference you are actaually passing it as the array and so the sub routine reads only the first few values. From a quick look thru it looks like $header is a suitable candidate. Although as you use the same variable name in a few places it is not so easy to tell. Hope that helps. AA	[reply]
Re: Spreadsheet::WriteExcel - writes the header but no subsequent rows by princepawn (Parson) on Nov 22, 2002 at 14:39 UTC
More news: If I inline `new_agency` instead of calling it as a subroutine and using the returned worksheet, it works fine. Working 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 write_excel { my ($header, $agency, $acronyms) = @_; warn 'write_excel'; for my $acronym (keys %$agency) { my @agency_rows = @ { $agency->{$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; 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 "$worksheet->write($row, $col, $_)"; $worksheet->write($row, $col, $_); warn "$worksheet->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] Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality	[reply] [d/l] [select]
Re: Spreadsheet::WriteExcel - writes the header but no subsequent rows by princepawn (Parson) on Nov 22, 2002 at 14:26 UTC
Also, two basic examples which came with Spreadsheet::ParseExcel both ran fine and both were more complex than what I am doing... Carter's compass: I know I'm on the right track when by deleting something, I'm adding functionality	[reply]