Search code examples
excelperlcgiperl-modulecgi-bin

Can't call method "add_worksheet" on an undefined value?

here is my cgi code , perl.xls file is present in the save folder as the script.

 #!/usr/bin/perl
 print "Content-type: text/html\n\n";
 use CGI;
 use DBI;
 use Spreadsheet::WriteExcel;
 use strict;
 use warnings;
 use CGI::Carp qw(fatalsToBrowser);
 my $cgi = CGI->new;
 my $workbook = Spreadsheet::WriteExcel->new('perl.xls');
 my $worksheet = $workbook->add_worksheet();
 $worksheet->write(0,0,'value');

And when i run the script i get this error

Software error:

Can't call method "add_worksheet" on an undefined value at /var/www/cgi-bin/excel.cgi line 17.

 For help, please send mail to the webmaster (root@localhost), giving this error message and the time and date of the error.
Solution

  • The error message is pretty clear. You're calling a method (add_worksheet()) on an undefined value. You're calling that method on $workbook, so you need to investigate how that value is set. It comes from this line:

    my $workbook = Spreadsheet::WriteExcel->new('perl.xls');

    So it's probably worth looking at the documentation for the new() method in Spreadsheet::WriteExcel. It says this:

    If the file cannot be created, due to file permissions or some other reason, new will return undef. Therefore, it is good practice to check the return value of new before proceeding. As usual the Perl variable $! will be set if there is a file creation error. You will also see one of the warning messages detailed in "DIAGNOSTICS":

    my $workbook  = Spreadsheet::WriteExcel->new('protected.xls');
die "Problems creating new Excel file: $!" unless defined $workbook;

    I'd guess that your CGI program is trying to write a file in a directory where doesn't have the required permissions. You might want give new() the full path to a directory where the file should be created.

    It's also worth pointing out this warning from the documentation:

    Note: This module is in maintenance only mode and in future will only be updated with bug fixes. The newer, more feature rich and API compatible Excel::Writer::XLSX module is recommended instead. See, "Migrating to Excel::Writer::XLSX".