Hi team i having following error pls help me

[pandairaj94]

Can't call method "worksheet" on an undefined value at /var/www/cgi-bin/Daily_Transaction_Reports_folder/Scripts_Folder/RCAB_Trans_Report.pl line 60.


This is my script i define worksheet and i m giving correct file name and path

#!/usr/bin/perl
use IO::Handle;
use DBI;
use Spreadsheet::SimpleExcel;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::ParseExcel::Format;
use Spreadsheet::WriteExcel::Format;
use Time::Local qw( timelocal_nocheck );
use POSIX qw(strftime);
use Time::Local qw( timegm_nocheck );
#use warnings;
 
# Parameters
 
my $driver = "mysql";
my $database = "insalldatadb";
my $host = "172.24.16.62";
my $port = "3323";
my $dsn = "DBI:$driver:database=$database;host=$host;port=$port";
my $userid = "solreaduser";
my $password = '^rfvbnhy7*';
#my $date=strftime '%Y%m%d',localtime();
my $date=strftime "%Y%m%d",localtime(time() - 24*60*60);
 
my $sqlFile = "/var/www/cgi-bin/Daily_Transaction_Reports_folder/Scripts_Folder/RCAB_Report.sql";
 
 
# Connect Database
 
my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr;
 
# Execute SQL
 
open (SQL,"$sqlFile");
 
my $sql = $dbh->prepare(<SQL>);
 
my $out = $sql->execute()
or die "Unable to execute sql: $sql->errstr";
 
 
 
#Month_Name
my @month_name = qw(January Febuary March April May June July August September October November December);
print "$month_name\n";
my ( $day, $month, $year ) = (localtime)[ 3, 4, 5 ];
$year = $year + 1900;
my $mnth = "$month_name[$month]_$year";
print "$mnth\n";
 
# Open existing spread-sheet with SaveParser
 
my $read_excel   = Spreadsheet::ParseExcel::SaveParser->new();
my $excel_data = $read_excel->Parse("/var/www/cgi-bin/Daily_Transaction_Reports_folder/Ouput_Folder/RCAB_Daily_Transactions_$mnth.xls");
 
# Get the first worksheet
 
my $worksheet = $excel_data->worksheet("$mnth");
 
#Add format
 
 
 
 
#my $format = $excel_data->AddFormat('Verdana');
 
# Get Last date of month
 
my @time_data;
 
@time_data = localtime();
 
$time_data[4]++;   # Next month.
$time_data[3] = 0; # Last day of previous month.
 
@time_data = localtime(timelocal_nocheck(@time_data));
 
my $last_day = $time_data[3];
 
my $var1 = ("%d\n",$last_day);
 
 
open(STDIN_0, '/var/www/cgi-bin/Daily_Transaction_Reports_folder/Scripts_Folder/RCAB_Column.txt');
$col_index= <STDIN_0>;
$var=++$col_index;
 
 
if ( $var > $var1 )
{
my $start = 1;
open(STDIN_1, '>', '/var/www/cgi-bin/Daily_Transaction_Reports_folder/Scripts_Folder/RCAB_Column.txt');
print STDIN_1 "$start\n";
}
else
{
open(STDIN_1, '>', '/var/www/cgi-bin/Daily_Transaction_Reports_folder/Scripts_Folder/RCAB_Column.txt');
print STDIN_1 "$var\n";
}
 
my $row = 1;
my $col = 1;
my $i = 0;
 
open(STDIN_0, '/var/www/cgi-bin/Daily_Transaction_Reports_folder/Scripts_Folder/Excel_Column.txt');
$index= <STDIN_0>;
 
#Add dates
my @time_data;
 
@time_data = localtime();
 
$time_data[4]++;   # Next month.
$time_data[3] = 0; # Last day of previous month.
 
@time_data = localtime(timelocal_nocheck(@time_data));
 
my $last_day = $time_data[3];
my $var = ("%d\n",$last_day);
 
my ($y, $m, $d) = (localtime)[5,4,3];
$y += 1900;
$m += 1;
 
my $ds = 1;
my $de = (gmtime(timegm_nocheck(0,0,0, (1-1), ($m+1)-1, $y)))[3];
 
$date2 = sprintf('%02d%02d%04d', $m,$ds,$y);
 
my @m = (3,4,7,8,11,12,15,16,19,20,21,24,25,26,29,30,33,34,37,40,43,44,45,46,47,48);
#my $i = 0;
 
do {
while (my @row = $sql->fetchrow_array()) {
 
  $worksheet->AddCell($m[$i], $col_index, $row[i]);
  $i++;
 }
};
my $workbook1 = $excel_data->SaveAs("/var/www/cgi-bin/Daily_Transaction_Reports_folder/Ouput_Folder/RCAB_Daily_Transactions_$mnth.xls");
 
 
#format
my $format2 = $workbook1->add_format();
$format2->set_align('center');
$format2->set_font('Verdana');
my $worksheet = $workbook1->sheets("$index");
 
#Formula
$worksheet->write(B4, "=SUM(C4:AG4)", $format2);
$worksheet->write(B5, "=SUM(C5:AG5)", $format2);
$worksheet->write(B8, "=SUM(C8:AG8)", $format2);
$worksheet->write(B9, "=SUM(C9:AG9)", $format2);
$worksheet->write(B12, "=SUM(C12:AG12)", $format2);
$worksheet->write(B13, "=SUM(C13:AG13)", $format2);
$worksheet->write(B16, "=SUM(C16:AG16)", $format2);
$worksheet->write(B17, "=SUM(C17:AG17)", $format2);
$worksheet->write(B20, "=SUM(C20:AG20)", $format2);
$worksheet->write(B21, "=SUM(C21:AG21)", $format2);
$worksheet->write(B22, "=SUM(C22:AG22)", $format2);
$worksheet->write(B25, "=SUM(C25:AG25)", $format2);
$worksheet->write(B26, "=SUM(C26:AG26)", $format2);
$worksheet->write(B27, "=SUM(C27:AG27)", $format2);
$worksheet->write(B30, "=SUM(C30:AG30)", $format2);
$worksheet->write(B31, "=SUM(C31:AG31)", $format2);
$worksheet->write(B34, "=SUM(C34:AG34)", $format2);
$worksheet->write(B35, "=SUM(C35:AG35)", $format2);
$worksheet->write(B38, "=SUM(C38:AG38)", $format2);
$worksheet->write(B41, "=SUM(C41:AG41)", $format2);
$worksheet->write(B44, "=SUM(C44:AG44)", $format2);
$worksheet->write(B45, "=SUM(C45:AG45)", $format2);
$worksheet->write(B46, "=SUM(C46:AG46)", $format2);
$worksheet->write(B47, "=SUM(C47:AG47)", $format2);
$worksheet->write(B48, "=SUM(C48:AG48)", $format2);
$worksheet->write(B49, "=SUM(C49:AG49)", $format2);
 
my $format3 = $workbook1->add_format();
$format3->set_bold();
$format3->set_align('center');
$format3->set_font('Verdana');
$format3->set_bg_color('Gray');
$format3->set_pattern();
 
my $format14 = $workbook1->add_format(num_format => '@');
 
my $inc = 1;
my $col = 2;
my $test = 0;
 
 
while( $inc <= $var ){
$worksheet->write_string(0,$col, $date2, $format3, $format14);
$date3 = $date2 + 10000;
$date2 = $test.$date3;
$inc = $inc + 1;
$col++;
}
 
 
$workbook1->close();

[RonB]

First, you left out the 2 most important modules (pragmas) which should be in every perl script.

use strict;
use warnings;
 

If you add those pragmas, you'll find that your code has a bunch of compilation errors. Ignoring those errors is never the right thing to do.

c:\test>perl -Mwarnings -Mstrict -c pandairaj94.pl

"my" variable @time_data masks earlier declaration in same scope at pandairaj94.pl line 110.
"my" variable $last_day masks earlier declaration in same scope at pandairaj94.pl line 119.
"my" variable $worksheet masks earlier declaration in same scope at pandairaj94.pl line 148.
"my" variable $col masks earlier declaration in same scope at pandairaj94.pl line 188.
Argument "i" isn't numeric in array element at pandairaj94.pl line 137.
Global symbol "$month_name" requires explicit package name at pandairaj94.pl line 47.
Global symbol "$col_index" requires explicit package name at pandairaj94.pl line 86.
Global symbol "$var" requires explicit package name at pandairaj94.pl line 87.
Global symbol "$col_index" requires explicit package name at pandairaj94.pl line 87.
Global symbol "$var" requires explicit package name at pandairaj94.pl line 90.
Global symbol "$var" requires explicit package name at pandairaj94.pl line 99.
Global symbol "$index" requires explicit package name at pandairaj94.pl line 107.
Global symbol "$date2" requires explicit package name at pandairaj94.pl line 129.
Global symbol "$col_index" requires explicit package name at pandairaj94.pl line 137.
Global symbol "$index" requires explicit package name at pandairaj94.pl line 148.
Global symbol "$date2" requires explicit package name at pandairaj94.pl line 193.
Global symbol "$date3" requires explicit package name at pandairaj94.pl line 194.
Global symbol "$date2" requires explicit package name at pandairaj94.pl line 194.
Global symbol "$date2" requires explicit package name at pandairaj94.pl line 195.
Global symbol "$date3" requires explicit package name at pandairaj94.pl line 195.
Bareword "i" not allowed while "strict subs" in use at pandairaj94.pl line 137.
Bareword "B4" not allowed while "strict subs" in use at pandairaj94.pl line 151.
Bareword "B5" not allowed while "strict subs" in use at pandairaj94.pl line 152.
Bareword "B8" not allowed while "strict subs" in use at pandairaj94.pl line 153.
Bareword "B9" not allowed while "strict subs" in use at pandairaj94.pl line 154.
Bareword "B12" not allowed while "strict subs" in use at pandairaj94.pl line 155.
Bareword "B13" not allowed while "strict subs" in use at pandairaj94.pl line 156.
Bareword "B16" not allowed while "strict subs" in use at pandairaj94.pl line 157.
Bareword "B17" not allowed while "strict subs" in use at pandairaj94.pl line 158.
Bareword "B20" not allowed while "strict subs" in use at pandairaj94.pl line 159.
Bareword "B21" not allowed while "strict subs" in use at pandairaj94.pl line 160.
Bareword "B22" not allowed while "strict subs" in use at pandairaj94.pl line 161.
Bareword "B25" not allowed while "strict subs" in use at pandairaj94.pl line 162.
Bareword "B26" not allowed while "strict subs" in use at pandairaj94.pl line 163.
Bareword "B27" not allowed while "strict subs" in use at pandairaj94.pl line 164.
Bareword "B30" not allowed while "strict subs" in use at pandairaj94.pl line 165.
Bareword "B31" not allowed while "strict subs" in use at pandairaj94.pl line 166.
Bareword "B34" not allowed while "strict subs" in use at pandairaj94.pl line 167.
Bareword "B35" not allowed while "strict subs" in use at pandairaj94.pl line 168.
Bareword "B38" not allowed while "strict subs" in use at pandairaj94.pl line 169.
Bareword "B41" not allowed while "strict subs" in use at pandairaj94.pl line 170.
Bareword "B44" not allowed while "strict subs" in use at pandairaj94.pl line 171.
Bareword "B45" not allowed while "strict subs" in use at pandairaj94.pl line 172.
Bareword "B46" not allowed while "strict subs" in use at pandairaj94.pl line 173.
Bareword "B47" not allowed while "strict subs" in use at pandairaj94.pl line 174.
Bareword "B48" not allowed while "strict subs" in use at pandairaj94.pl line 175.
Bareword "B49" not allowed while "strict subs" in use at pandairaj94.pl line 176.
pandairaj94.pl had compilation errors.

Now, regarding your error message. It would appear that line 56

my $excel_data = $read_excel->Parse("/var/www/cgi-bin/Daily_Transaction_Reports_folder/Ouput_Folder/RCAB_Daily_Transactions_$mnth.xls");

failed which left $excel_data undefined when it was used in line 60.

Try adding some error handling to line 56. But before you do that, you should add the pragmas and fix those other errors.

[RonB]

Besides the errors I've already pointed out, there are a number of other things you're doing which are poor coding practices thus reducing the quality of your code.