Re^3: isn't numeric in substr error

Thanks Ken and Anonymous your suggestions helped which I have implemented. I'm getting undef where I defined sub/variables. Errors location are numbered in code (line no.)

Updated code.

our %worksheet;

#header
foreach my $criterion (@criteria) {
  $reportPath = "$report_loc/$criterion.xls";
  $reportHash{$criterion}{"all"}{"reportPath"} = $reportPath;
  local *REPORT_HANDLE;
  if (! open(REPORT_HANDLE, ">$reportPath")) {
    abort("Couldn't open $reportPath: $!");
  }
    binmode REPORT_HANDLE; # Always do this regardless of whether the 
+platform requires it.

    $reportPath  = Spreadsheet::WriteExcel->new(\*REPORT_HANDLE);
    $worksheet{$criterion}   = $reportPath->add_worksheet($criterion);
    &outputHeader(*REPORT_HANDLE,$reportPath,"all",$criterion,$workshe
+et{$criterion});

      $reportHash{$criterion}{"all"}{"reportHandle"} = *REPORT_HANDLE;

      $reportHash{$criterion}{"all"}{"total"} = 0;

    foreach my $group (@groups) {
    $reportPath = "$report_loc_ts_groups/$group/$group" . "_" . "$crit
+erion.xls";
    $reportHash{$criterion}{$group}{"reportPath"} = $reportPath;
    local *REPORT_HANDLE;
    if (! open(REPORT_HANDLE, ">$reportPath")) {
      abort("Couldn't open $reportPath: $!");
    }
    binmode REPORT_HANDLE; # Always do this regardless of whether the 
+platform requires it.

        my $reportPath  = Spreadsheet::WriteExcel->new(\*REPORT_HANDLE
+);
        $worksheet{$group}   = $reportPath->add_worksheet($group); 
        &outputHeader(*REPORT_HANDLE,$reportPath,$group,$criterion,$wo
+rksheet{$group});
  
        $reportHash{$group}{$criterion}{$worksheet{$criterion}}{"repor
+tHandle"} = *REPORT_HANDLE;
      
        $reportHash{$group}{$criterion}{$worksheet{$criterion}}{"total
+"} = 0;
    }
}


# Print totals and close files!
foreach my $criterion (@criteria) {
(line 229)  wrapUp($reportHash{"all"}{$criterion}{$worksheet{$criterio
+n}}{"reportHandle"},$reportHash{"all"}{$criterion}{$worksheet{$criter
+ion}}{"total"});

  foreach my $group (@groups) {
    wrapUp($reportHash{$group}{$criterion}{$worksheet{$group}}{"report
+Handle"},$reportHash{$group}{$criterion}{$worksheet{$group}}{"total"}
+);

  } # End of foreach my $group (@groups) {

} # End of foreach my $criterion (@criteria) {

exit;


sub outputHeader {
  my $reportHandle = $_[0];
  my $reportPath = $_[1];
  my $group = $_[2];
  my $criterion = $_[3];
  my $worksheet = $_[4];

    my ($x,$y)=(0,0);
    
  my $groupOut = "";
  if ($groupOut ne "all") {
    $groupOut = "$group - ";
  } 
  
 my $criterionOut = "Files expire within current month ($current_y-$cu
+rrent_mth)";

 if ($criterion eq $criteria[1]) {
    $criterionOut = "Deleted (expired) Files";
  }
  elsif ($criterion eq $criteria[2]) {
    $criterionOut = "Files found with invalid expiry_date";
  }

#$worksheet->write($y++, $x, "$groupOut$criterionOut"                 
+, $format{bold}); # write_string()
$worksheet->write($y++, $x, "$groupOut$criterionOut"                  
+              ); # write_string()
$worksheet->write($y++, $x, "Date Run: ".localtime().""               
+                 ); # write_string()
$worksheet->write($y++, $x,                                           
+                 ); # write_blank()
$worksheet->write($y++, $x, "Criteria:  today's date   : $tod_date"   
+                 ); # write_string()
$worksheet->write($y++, $x, "           time interval  : on or before 
+today's date"    ); # write_string()

if ($group ne "all") {
    $worksheet->write($y++, $x, "           teamsite group : $group"  
+                  ); # write_string()
}

$worksheet->write($y++, $x, "           workarea       : \$area"      
+                  ); # write_string()
$worksheet->write($y++, $x, "           directories    : "            
+                ); # write_string()

$worksheet->write($y++, $x,                                           
+                 ); # write_blank()
$worksheet->write($y++, $x,                                           
+                 ); # write_blank()

$worksheet->write($y++, $x, uc($groupOut.$criterionOut)               
+                 ); # write_string()
$worksheet->write($y++, $x,                                           
+                 ); # write_blank()
  
    # Simple text
    $worksheet->write($y++, $x, "Files");
} # End of sub outputHeader {



sub wrapUp {
  my $reportHandle = $_[0];
  my $reportPath = $_[1];
  my $group = $_[2];
  my $criterion = $_[3];
  my $worksheet = $_[4];
  my $total = $_[5];

  my ($x,$y) = (0,16);

(line 314)    $worksheet->write($y++, $x, "total"); 
    close($reportHandle);
} # End of sub wrapUp {
[download]

Log error I'm getting

Uncaught exception from user code:

Can't call method "write" on an undefined value at /home/bsmart/excel_workbooks.ipl line 314.

main::wrapUp('undef','undef') called at /home/bsmart/excel_workbooks.ipl line 229

Comment on Re^3: isn't numeric in substr error Download Code

Replies are listed 'Best First'.
Re^4: isn't numeric in substr error by kcott (Archbishop) on Nov 17, 2010 at 18:08 UTC
After `foreach my $criterion (@criteria) {` (presumably line 228), you should inspect the values of the variables used in both of these: `$reportHash{"all"}{$criterion}{$worksheet{$criterion}}{"reportHandle"} $reportHash{"all"}{$criterion}{$worksheet{$criterion}}{"total"}` [download] Data::Dumper may help with the overall hash but you'll still need to inspect the variables `$criterion` and `$worksheet{$criterion}` so you know what keys to look for. Determine what hasn't been set (resulting in an undefined value) and fix that. I'd also take a close look at `wrapUp()`. You're reading in 6 arguments but only using 2 of them. And, what's `16`? Don't tell me - document it in your code! Also, are you aware that `$y` is incremented after the call to `write()` which actually makes the increment pointless: perhaps you want a prefix increment `++$y` instead; although, with the code you have there, you might as well just write `17` and document that. -- Ken	[reply] [d/l] [select]
Re^4: isn't numeric in substr error by jmcnamara (Monsignor) on Nov 19, 2010 at 13:12 UTC
Can't call method "write" on an undefined value at /home/bsmart/excel_workbooks.ipl line 314. This probably occurs because the worksheet object has already been garbage collected at that point in your program. Try adding an explicit Worksbook `close()` to synchronise the WriteExcel object destruction. See the WriteExcel docs on Workbook::close() for more details. -- John.	[reply]
Re^5: isn't numeric in substr error by begood321 (Novice) on Nov 24, 2010 at 20:36 UTC
Thanks John that could be the case. I have changed code from searching within spreadsheet to check for appriate value in variable, which appears to be easier. For now I'm good. Thanks guys for your help.	[reply]