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

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]

Replies are listed 'Best First'.
Re^3: Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser
by poj (Abbot) on Aug 12, 2015 at 09:21 UTC

    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]
Re^3: Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser
by Corion (Patriarch) on Aug 12, 2015 at 09:11 UTC

    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]

        This means that $template was not initialized properly. Check the return value of

        my $template = $parser->Parse('B_MUM_dashboard_month_latency_corrected
+_FDB.XLS');

        [download]

        like this

        my $filename = 'B_MUM_dashboard_month_latency_corrected_FDB.XLS';
my $template = $parser->Parse($filename)
    or die "Couldn't read '$filename': " . $parser->error;

        [download]

        That way, you find out why your template was not initialized properly.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom