Re^2: isn't numeric in substr error

[Anonymous Monk]

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

Also, it's usually not a good idea to mix my $var = $_[0] and my $var = shift-style handling of arguments (unless you know what you're doing and why...:)

In this case, $worksheet will have the same value as $reportHandle. Not sure if this is intended. If so, it's clearer to directly write my $worksheet = $reporthandle;

[begood321]

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 {

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

[kcott]

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"}

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.

[jmcnamara]

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.

[begood321]

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.