ravi45722 has asked for the wisdom of the Perl Monks concerning the following question: 

#!usr/bin/perl

#use strict;
#use warnings;
use DBI;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::Read;

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});

#Selecting the data to fetch
@tables=("MIS_Overall","MIS_P2P","MIS_P2A","MIS_A2P");
#my $sth = $dbh->prepare("SELECT * from MIS_P2P");
#my $dates=$dbh->prepare("select Date from MIS_Overall where monthname
+(Date)='August'");

#$dates->execute() or die $DBI::errstr;



# Open an existing file with SaveParser
my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $template = $parser->Parse('B_MUM_dashboard_month_latency_corrected
+_FDB.XLS');
#*********************************************************************
+************************************#
foreach my $MIS_SHEET (@tables)
{
    # Get the first worksheet.
    my $sheet = $template->worksheet($MIS_SHEET);
    my $row_in  = 2;
    my $col_in  = 0;
    my $sth = $dbh->prepare("SELECT * from $MIS_SHEET");
    $sth->execute() or die $DBI::errstr;
    while (my @row = $sth->fetchrow_array())
    {
        ++$row;
        foreach my $value (@row)
        {
            $sheet->AddCell( $row, $col, $value );
            ++$col;
        }
    }
}
$template->SaveAs('B_MUM_dashboard_month_latency_corrected_FDB_2015_08
+_01.XLS');

[download]

Here I have four sheets(@tables). I want to modify the four sheets. I tried upto here. But it showing an error. Error: Can't call method "AddCell" on an undefined value at parse_excel.pl line 38.

How can i insert new row in the middle of the sheet using this ParseExcel
  • Comment on Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser
  • Download Code

Replies are listed 'Best First'.
Re: Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser
by Corion (Patriarch) on Aug 12, 2015 at 07:41 UTC

    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?

[reply]
[d/l]
[select]

      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]
Re: Trying to modify multisheets using Spreadsheet::ParseExcel::SaveParser
by poj (Abbot) on Aug 12, 2015 at 08:05 UTC

    Check the names of your worksheets, maybe you have leading spaces

    #!usr/bin/perl
use strict;
use warnings;
use Spreadsheet::ParseExcel::SaveParser;

my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $xls = 'B_MUM_dashboard_month_latency_corrected_FDB.XLS';
my $wb = $parser->Parse($xls);
for ($wb->worksheets){
  print "'$_->{Name}'\n";
}

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

Back to Seekers of Perl Wisdom