Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser

by Corion (Patriarch)
on Aug 12, 2015 at 07:41 UTC ( [id://1138280]=note: print w/replies, xml ) Need Help??


in reply to Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser

Your error happens earlier in your script. $sheet is not defined, which means that my $sheet = $template->worksheet($MIS_SHEET); returned undef. Maybe there is no such worksheet in your template?

Replies are listed 'Best First'.
Re^2: Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser
by ravi45722 (Pilgrim) on Aug 12, 2015 at 09:03 UTC

    My table names(mysql) & sheets names are same which I saved in array  @tables 

     
foreach my $MIS_SHEET (@tables)
{
    # Get the first worksheet.
    my $sheet = $template->worksheet($MIS_SHEET);

    [download]
    In this i am trying to open each sheet ony by one. 

    my $sth = $dbh->prepare("SELECT * from $MIS_SHEET"); #Getting data fro
+m table
    $sth->execute() or die $DBI::errstr;
    while (my @row = $sth->fetchrow_array())
    {
        ++$row;
        foreach my $value (@row)
        {
            $sheet->AddCell( $row, $col, $value );
            ++$col;
        }
    }

    [download]
    Here i am saving the data fetch from my DB into excel sheet. 
    +-----------------------+
| Tables_in_MIS_Reports |
+-----------------------+
| MIS_A2P               |
| MIS_Overall           |
| MIS_P2A               |
| MIS_P2P               |
+-----------------------+

mysql> select * from MIS_P2P;
+------------+-------+---------+---------+--------------+-----------+-
+--------------+--------------+-------------+----------+
| Date       | MDA   | Success | FDA     | Success_rate | Sub_Error | 
+Network_Error | System_Error | Total_Error | Total_MO |
+------------+-------+---------+---------+--------------+-----------+-
+--------------+--------------+-------------+----------+
| 2015-07-31 | 38002 | 95.7437 | 93.1952 |      93.0325 |   85.5974 | 
+      10.5565 |    0.0818331 |     6.34294 |    29494 |
| 2015-07-31 | 38002 | 95.7437 | 93.1952 |      93.0325 |   85.5974 | 
+      10.5565 |    0.0818331 |     6.34294 |    29494 |
| 2015-07-31 | 38002 | 95.7437 | 93.1952 |      93.0325 |   85.5974 | 
+      10.5565 |    0.0818331 |     6.34294 |    29494 |
| 2015-08-01 | 38002 | 95.7437 | 93.1952 |      93.0325 |   85.5974 | 
+      10.5565 |    0.0818331 |     6.34294 |    29494 |
| 2015-08-02 | 38002 | 95.7437 | 93.1952 |      93.0325 |   85.5974 | 
+      10.5565 |    0.0818331 |     6.34294 |    29494 |
| 2015-08-03 | 38002 | 95.7437 | 93.1952 |      93.0325 |   85.5974 | 
+      10.5565 |    0.0818331 |     6.34294 |    29494 |
+------------+-------+---------+---------+--------------+-----------+-
+--------------+--------------+-------------+----------+
6 rows in set (0.00 sec)

    [download]
    This is the data available in my DB.

    <h> I am Sure that my sheet name are correctly spelled & no problem with that</h> 
    Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 242.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 242.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 242.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 242.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.
Use of uninitialized value $iC in numeric le (<=) at /root/perl5/lib/p
+erl5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 274.
Use of uninitialized value $iC in array element at /root/perl5/lib/per
+l5/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 281.

    [download]
[reply]
[d/l]
[select]

      You initialize 

       
my $row_in  = 2;
my $col_in  = 0;

      [download]
      but then use 
      $sheet->AddCell( $row, $col, $value );

      [download]
      I suggest you uncomment these and fix the errors/warnings 
      #use strict;
#use warnings;

      [download]
      poj
[reply]
[d/l]
[select]

      Where does this new error message come from? Does your script still die with the error message you mentioned in the top thread?

      Error: Can't call method "AddCell" on an undefined value at parse_exce
+l.pl line 38

      [download]

      Please show us the exact source code you are running, and tell us the exact error message you get.

[reply]
[d/l]
        #!usr/bin/perl
use DBI;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

$month=$ARGV[0];
$month or $month=`date --date='1 day ago' +%B`;
chomp $month;
print "Month:$month\n";

my $dbh = DBI->connect("DBI:mysql:database=MIS_Reports;host=172.16.15.
+104;mysql_socket=/opt/lampstack-5.5.27-0/mysql/tmp/mysql.sock","root"
+,"",      {'RaiseError' => 1});

#---------------------------------------------------------------------
+------------------------------------#


# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('B_MUM_dashboard_month_latency_corrected
+_FDB.XLS');

@tables=("MIS_Overall","MIS_P2P","MIS_P2A","MIS_A2P");
$sheet_no=0;



foreach $table (@tables)
{
    chomp $table;

    my $sth = $dbh->prepare("SELECT * from $table where monthname(Date
+)='$month'" );
    $sth->execute() or die $DBI::errstr;

    # Get the first worksheet.
    my $sheet = $template->worksheet($sheet_no);
    my $row  = 2;
    my $col  = 1;

    while (my @row = $sth->fetchrow_array())
    {
        $col=1;
            foreach my $value (@row)
            {
            $sheet->AddCell( $row, $col, $value );
            ++$col;
        }
        $row++;
    }
    $sheet_no++;
}
$template->SaveAs('B_MUM_dashboard_month_latency_corrected_FDB_12-08-2
+015.XLS');

        [download]

        Error: Can't call method "worksheet" on an undefined value at parse_excel_multisheet.pl line 33

[reply]
[d/l]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://1138280]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2024-04-23 09:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found