Solved

Why is this error coming in Perl spreadsheets?

Posted on 2010-08-30
8
492 Views
Last Modified: 2012-05-10
Cell1 value isBA_NID_VAL
Cell3 value isINPUT
The delta col is BA_NID


The First Final Query is --

 select b.BA_NID from PFND.CACHE_BA_HRCHY b where b.BA_HRCHY_ID = 11 and b.TAPS_ACCT_NUM = '083920660'

The value returned from the db is 10214529
Can't call method "value" on an undefined value at /ms/user/s/sayantag/Verify_Col_Value1_Test.pl line 186.
 at /ms/user/s/sayantag/Verify_Col_Value1_Test.pl line 30
        main::__ANON__('Can\'t call method "value" on an undefined value at /ms/user/...') called at /ms/user/s/sayantag/Verify_Col_Value1_Test.pl line 186

#!/ms/dist/perl5/bin/perl5.10 -w


use MSDW::Version
  'Spreadsheet-WriteExcel' => '2.35',
  'Spreadsheet-ParseExcel' => '0.57',
  'OLE-Storage_Lite'   => '0.19', # Used by Spreadsheet::WriteExcel
  'IO-stringy'         => '2.110',# IO::Scalar may be used by Spreadsheet::WriteExcel
  ;

use MSDW::Version ('DBD-DB2'   => '1.76-9.5.4', # or higher
                   'DBI'       => '1.609', # or higher
                   'DBD-MSDB2' => '1.6');

use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel::Big;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;



$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };

my $posting_event_type = "PBTRDPL";#"PBCLCASHPAY";

#shift;

my $audit_funding_rule = "PB36";#shift;#"PB1";

#shift;
#"PBCLCASHPAY";
#shift;

my $col_fmt = "INPUT";#shift;#"CCY";

#shift;
#"INPUT"; 

#shift;

#"INPUT";
my $ba_nid =  10214529;#shift;#10180184;

#shift;
#shift; 
#11082557;
my $le_nid = 20000033;#shift;#20000033;

#shift;
#20000050;
#shift;
#my @c_arr = ($ba_nid, $le_nid);
#11082557;

my $asset_id = 0;#shift;
my $audit_acct_num = '083920660';#shift;
#my $sf = shift;
my $actual_file2 = shift;#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";

#shift;

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";
#shift

#my $cc1 = "BA_NID_VAL"; 
 
#shift;
#print "the ba-nid value in Verify Col script is $ba_nid\n"; 

#`cp Temp_Res.xls Temp_Res_bkp.xls`;

my $xls_parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $xls_workbook = $xls_parser->Parse("Temp_Res3_Int.xls");
my $xls_worksheet = $xls_workbook->{Worksheet}[0];

my ( $s_row_min, $s_row_max ) = $xls_worksheet->row_range();
my ( $s_col_min, $s_col_max ) = $xls_worksheet->col_range();

my $format1 = $xls_workbook->AddFormat(Fill => [0,11,0]);
my $format2 = $xls_workbook->AddFormat(Fill => [1,10,0]);

#$format1->set_size(12);
#$format1->set_bold();
##$format1->set_color('red');
#$format1->set_align('center');
#$format1->set_bg_color('red');


my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Value_Type_Query1.xls");
my $sql_worksheet = $sql_workbook->worksheet("Queries");

my ( $row_min, $row_max ) = $sql_worksheet->row_range();

for my $row ( $row_min+1 .. $row_max ) {

my $cell =  $sql_worksheet->get_cell($row, 0); 
#print $cell->value . "\n";


#my $srl = $sql_worksheet->get_cell($row, 0)->value;
#my $cc1 = $sql_worksheet->get_cell($row, 1)->value;
#my $vt1 = $sql_worksheet->get_cell($row, 2)->value;
#my $flag =  $sql_worksheet->get_cell($row, 3)->value;

my $srl = $sql_worksheet->get_cell($row, 0);
my $cc1 = $sql_worksheet->get_cell($row, 1);
my $vt1 = $sql_worksheet->get_cell($row, 3);
if (not defined $srl or not defined $cc1 or not defined $vt1) {
#    warn "row $row has blanks in one or more of columns 0-2 - skipping";
    next;
}
$srl = $srl->value;
$cc1 = $cc1->value;
$vt1 = $vt1->value;
#print "$srl\n";
#print "$cc1\n";
#print "$vt1\n";

 if (defined $cell and $cell->value eq "$srl") {
   my $cell1 = $sql_worksheet->get_cell($row, 1);
   print "Cell1 value is" .  $cell1->value . "\n";

   if (defined $cell1 and $cell1->value eq "$cc1") {
    
   my $cell3 = $sql_worksheet->get_cell($row, 3);
   print "Cell3 value is" . $cell3->value . "\n";   

    if (defined $cell3  and $cell3->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 4)->value eq "Q" ) {

############ This is for all values equal to "col_fmt" and flag "Q" ####################################################################

      my $dcol = $sql_worksheet->get_cell($row, 2);
      $dcol = $dcol->value; 
      print "The delta col is $dcol\n";
     
      my $query1 = $sql_worksheet->get_cell($row, 5)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;
      $query1 =~ s/\$audit_acct_num/$audit_acct_num/;

      print "\n\nThe First Final Query is --\n\n $query1\n\n";
#      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2); 

################## Checkit against same col in delta temp spreadsheet ##############################################

 
     my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or
          die "Can't connect to database: $DBI::errstr";


    my $sql = "$query1";


    my $sth = $dbh->prepare($sql);


    $sth->execute();

    while (my @data = $sth->fetchrow_array) {

    print "The value returned from the db is " . "@data\n";

      my $s_row = 0;
      my $s_col = 0;
     for my $s_row($s_row_min+1..$s_row_max) { 
     for my $s_col($s_col_min+1..$s_col_max) {
      my $xls_cell = $xls_worksheet->get_cell($s_row_min, $s_col);
      unless (defined $xls_cell) {next;}
      if (defined $xls_cell and $xls_cell->value eq "$dcol") {
#         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";
         my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;
         next unless defined $delta_col_val;

         $delta_col_val =~ s/^0*//;
         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";

        if ($delta_col_val eq "@data" ) 
         {
#          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);
          print "\n\nTEST PASSED\n\n";
          }
        else {
          
          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);
          
          print "\n\nTEST FAILED\n\n";
#          $xls_workbook->SaveAs("Temp_Res_bkp.xls");
           }
         }
      }
     }
    }

      $xls_workbook->SaveAs("Temp_Res2.xls");

###################################################################################################################        
          }
    elsif (defined $cell3  and $cell3->value ne "NA" and $cell3->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 4)->value eq "H" ) 
         {
############ This is for Value_Type not "NA" but equal to "col_fmt" and flag "H" ######################################################################

          my $query1 = $sql_worksheet->get_cell($row, 6)->value;
          print "\n\nThe Hard-coded value is --\n\n $query1\n\n";
         # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);
          
         #########################################################################################################




         #########################################################################################################
           }

    elsif (defined $cell3  and $cell3->value eq "NA" and $sql_worksheet->get_cell($row, 4)->value eq "Q" ) {

########### This is for Value_Type equal to "NA" and flag "Q" ###########################################################
       ##############################################################################################
       my $query1 = $sql_worksheet->get_cell($row, 5)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;
      $query1 =~ s/\$audit_acct_num/$audit_acct_num/;

      print "\n\nThe Second Final Query is --\n\n $query1\n\n";
      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2);

       ############################################################################################

       }

    elsif (defined $cell3 and $cell3->value eq "$col_fmt" and $sql_worksheet->get_cell($row, 4)->value eq "QV" ) {

############# This is for Value_Type equal to "col_fmt" and flag "QV" ###########################################################################
          ##############################################################################################
       my $query1 = $sql_worksheet->get_cell($row, 5)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;
      $query1 =~ s/\$asset_id/$asset_id/;
      $query1 =~ s/\$audit_acct_num/$audit_acct_num/;
      
      print "\n\nThe Third Final Query is --\n\n $query1\n\n";
      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Search_View1_Test.pl", $query1, $actual_file2);

       ############################################################################################

       }


    elsif (defined $cell3 and $cell3->value eq "$col_fmt" and $cell3->value eq "NA" and $sql_worksheet->get_cell($row, 4)->value eq "H" ) {
    
########### This is for Value_Type equal to "col_fmt" and flag "H" ##############################################################################

    my $query1 = $sql_worksheet->get_cell($row, 6)->value;
          print "\n\nThe Hard-coded value is --\n\n $query1\n\n";
         # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2);

         #########################################################################################################




         #########################################################################################################
           }



    else {
########### This is for all other cases like not equal to "col_fmt", blank rows or unknown flags ###########################################
     next;}
        }
       }
 
  
      }

Open in new window

#!/ms/dist/perl5/bin/perl5.10 -w


use MSDW::Version
  'Spreadsheet-WriteExcel' => '2.35',
  'Spreadsheet-ParseExcel' => '0.57',
  'OLE-Storage_Lite'   => '0.19', # Used by Spreadsheet::WriteExcel
  'IO-stringy'         => '2.110',# IO::Scalar may be used by Spreadsheet::WriteExcel
  ;

use MSDW::Version ('DBD-DB2'   => '1.76-9.5.4', # or higher
                   'DBI'       => '1.609', # or higher
                   'DBD-MSDB2' => '1.6');

use warnings FATAL => qw(all);
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel::Big;

use strict;
use IO::Scalar;
use Data::Dumper;
use DBI;
use DBD::MSDB2;
use File::Copy qw(cp);
use Carp;



$SIG{__DIE__} = sub { confess @_ };
$SIG{__WARN__} = sub { confess @_ };

my $posting_event_type = "PBTRDPL";#"PBCLCASHPAY";

#shift;

my $audit_funding_rule = "PB36";#shift;#"PB1";

#shift;
#"PBCLCASHPAY";
#shift;

my $col_fmt = "INPUT";#shift;#"CCY";

#shift;
#"INPUT"; 

#shift;

#"INPUT";
my $ba_nid =  10214529;#shift;#10180184;

#shift;
#shift; 
#11082557;
my $le_nid = 20000033;#shift;#20000033;

#shift;
#20000050;
#shift;
#my @c_arr = ($ba_nid, $le_nid);
#11082557;

my $asset_id = 0;#shift;
my $audit_acct_num = '083920660';#shift;
#my $sf = shift;
my $actual_file2 = shift;#"/v/region/na/appl/phoenixfunding/etl/data/qa/TgtFiles/delta_LibraPrimeBrokerage.txt";

#shift;

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";

#"/v/region/na/appl/qatools/util/data/reports/SF/OtcTaxlotOpenDelta.txt";
#shift

#my $cc1 = "BA_NID_VAL"; 
 
#shift;
#print "the ba-nid value in Verify Col script is $ba_nid\n"; 

#`cp Temp_Res.xls Temp_Res_bkp.xls`;

my $xls_parser   = Spreadsheet::ParseExcel::SaveParser->new();
my $xls_workbook = $xls_parser->Parse("Temp_Res3_Int.xls");
my $xls_worksheet = $xls_workbook->{Worksheet}[0];

my ( $s_row_min, $s_row_max ) = $xls_worksheet->row_range();
my ( $s_col_min, $s_col_max ) = $xls_worksheet->col_range();

my $format1 = $xls_workbook->AddFormat(Fill => [0,11,0]);
my $format2 = $xls_workbook->AddFormat(Fill => [1,10,0]);

#$format1->set_size(12);
#$format1->set_bold();
##$format1->set_color('red');
#$format1->set_align('center');
#$format1->set_bg_color('red');


my $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Value_Type_Query1.xls");
my $sql_worksheet = $sql_workbook->worksheet("Queries");

my ( $row_min, $row_max ) = $sql_worksheet->row_range();

for my $row ( $row_min+1 .. $row_max ) {

my $cell =  $sql_worksheet->get_cell($row, 0); 
#print $cell->value . "\n";


#my $srl = $sql_worksheet->get_cell($row, 0)->value;
#my $cc1 = $sql_worksheet->get_cell($row, 1)->value;
#my $vt1 = $sql_worksheet->get_cell($row, 2)->value;
#my $flag =  $sql_worksheet->get_cell($row, 3)->value;

my $srl = $sql_worksheet->get_cell($row, 0);
my $cc1 = $sql_worksheet->get_cell($row, 1);
my $vt1 = $sql_worksheet->get_cell($row, 3);
if (not defined $srl or not defined $cc1 or not defined $vt1) {
#    warn "row $row has blanks in one or more of columns 0-2 - skipping";
    next;
}
$srl = $srl->value;
$cc1 = $cc1->value;
$vt1 = $vt1->value;
#print "$srl\n";
#print "$cc1\n";
#print "$vt1\n";

 if (defined $cell and $cell->value eq "$srl") {
   my $cell1 = $sql_worksheet->get_cell($row, 1);
   print "Cell1 value is" .  $cell1->value . "\n";

   if (defined $cell1 and $cell1->value eq "$cc1") {
    
   my $cell3 = $sql_worksheet->get_cell($row, 3);
   print "Cell3 value is" . $cell3->value . "\n";   

    if (defined $cell3  and $cell3->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 4)->value eq "Q" ) {

############ This is for all values equal to "col_fmt" and flag "Q" ####################################################################

      my $dcol = $sql_worksheet->get_cell($row, 2);
      $dcol = $dcol->value; 
      print "The delta col is $dcol\n";
     
      my $query1 = $sql_worksheet->get_cell($row, 5)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;
      $query1 =~ s/\$audit_acct_num/$audit_acct_num/;

      print "\n\nThe First Final Query is --\n\n $query1\n\n";
#      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2); 

################## Checkit against same col in delta temp spreadsheet ##############################################

 
     my $dbh = DBI->connect('dbi:MSDB2:NQ230001', '', '', { 'RaiseError' => 1 }) or
          die "Can't connect to database: $DBI::errstr";


    my $sql = "$query1";


    my $sth = $dbh->prepare($sql);


    $sth->execute();

    while (my @data = $sth->fetchrow_array) {

    print "The value returned from the db is " . "@data\n";

      my $s_row = 0;
      my $s_col = 0;
     for my $s_row($s_row_min+1..$s_row_max) { 
     for my $s_col($s_col_min+1..$s_col_max) {
      my $xls_cell = $xls_worksheet->get_cell($s_row_min, $s_col);
      unless (defined $xls_cell) {next;}
      if (defined $xls_cell and $xls_cell->value eq "$dcol") {
#         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";
         my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;
         next unless defined $delta_col_val;

         $delta_col_val =~ s/^0*//;
         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";

        if ($delta_col_val eq "@data" ) 
         {
#          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);
          print "\n\nTEST PASSED\n\n";
          }
        else {
          
          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);
          
          print "\n\nTEST FAILED\n\n";
#          $xls_workbook->SaveAs("Temp_Res_bkp.xls");
           }
         }
      }
     }
    }

      $xls_workbook->SaveAs("Temp_Res2.xls");

###################################################################################################################        
          }
    elsif (defined $cell3  and $cell3->value ne "NA" and $cell3->value eq "$col_fmt" and  $sql_worksheet->get_cell($row, 4)->value eq "H" ) 
         {
############ This is for Value_Type not "NA" but equal to "col_fmt" and flag "H" ######################################################################

          my $query1 = $sql_worksheet->get_cell($row, 6)->value;
          print "\n\nThe Hard-coded value is --\n\n $query1\n\n";
         # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2.pl", $query1, $actual_file2);
          
         #########################################################################################################




         #########################################################################################################
           }

    elsif (defined $cell3  and $cell3->value eq "NA" and $sql_worksheet->get_cell($row, 4)->value eq "Q" ) {

########### This is for Value_Type equal to "NA" and flag "Q" ###########################################################
       ##############################################################################################
       my $query1 = $sql_worksheet->get_cell($row, 5)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;
      $query1 =~ s/\$audit_acct_num/$audit_acct_num/;

      print "\n\nThe Second Final Query is --\n\n $query1\n\n";
      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2);

       ############################################################################################

       }

    elsif (defined $cell3 and $cell3->value eq "$col_fmt" and $sql_worksheet->get_cell($row, 4)->value eq "QV" ) {

############# This is for Value_Type equal to "col_fmt" and flag "QV" ###########################################################################
          ##############################################################################################
       my $query1 = $sql_worksheet->get_cell($row, 5)->value;

      $query1 =~ s/\$le_nid/$le_nid/;
      $query1 =~ s/\$ba_nid/$ba_nid/;
      $query1 =~ s/\$posting_event_type/$posting_event_type/;
      $query1 =~ s/\$audit_funding_rule/$audit_funding_rule/;
      $query1 =~ s/\$asset_id/$asset_id/;
      $query1 =~ s/\$audit_acct_num/$audit_acct_num/;
      
      print "\n\nThe Third Final Query is --\n\n $query1\n\n";
      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Search_View1_Test.pl", $query1, $actual_file2);

       ############################################################################################

       }


    elsif (defined $cell3 and $cell3->value eq "$col_fmt" and $cell3->value eq "NA" and $sql_worksheet->get_cell($row, 4)->value eq "H" ) {
    
########### This is for Value_Type equal to "col_fmt" and flag "H" ##############################################################################

    my $query1 = $sql_worksheet->get_cell($row, 6)->value;
          print "\n\nThe Hard-coded value is --\n\n $query1\n\n";
         # system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Finding_Act_Num2_Test.pl", $query1, $actual_file2);

         #########################################################################################################




         #########################################################################################################
           }



    else {
########### This is for all other cases like not equal to "col_fmt", blank rows or unknown flags ###########################################
     next;}
        }
       }
 
  
      }

Open in new window

Temp-Res3-Int.xls
Value-Type-Query1.xls
0
Comment
Question by:sunny82
  • 5
  • 2
8 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 33561565
$xls_worksheet->get_cell($s_row, $s_col) must have returned undef
0
 

Author Comment

by:sunny82
ID: 33561847
There are a couple of blank lines in the Temp-Res3_Init.xls sheet. I am matching the column header from Value_Type_Query1.xls sheet  with the first row which contains col headers. If it matches it should go to the corresponding value in that column.

I did

my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;
         next unless defined $delta_col_val;
to take care of blank lines, but still no help. Pls help.
0
 
LVL 84

Expert Comment

by:ozo
ID: 33561892
try
 next unless defined $xls_worksheet->get_cell($s_row, $s_col);
my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:sunny82
ID: 33562052
Trying the above, I am getting

Cell1 value isBA_NID_VAL
Cell3 value isINPUT
The dcol is BA_NID


The First Final Query is --

 select b.BA_NID from PFND.CACHE_BA_HRCHY b where b.BA_HRCHY_ID = 11 and b.TAPS_ACCT_NUM = '083920660'

The value returned from the db is 10214529
Cell1 value isBA_NID_VAL
Cell3 value isBACPMOTCCOLL

It does not contain any errors but its not being able to print $delta_col_val. After the header is read if you look into my excel sheet Temp_Res3_Init.xls, there are a couple of blank rows then the BA_NID value is there. It is not being able to read it.

here is what i did --

======================================
      my $s_row = 0;
      my $s_col = 0;
     for my $s_row($s_row_min+1 .. $s_row_max) {
     for my $s_col($s_col_min+1 .. $s_col_max) {
      my $xls_cell = $xls_worksheet->get_cell($s_row_min, $s_col);
      next unless defined $xls_cell;
      if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

#         print "The column name is " .  $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";

#         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";
#         print $s_row;
#         print $s_col;
#         my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;
         next unless defined my $delta_col_val;
         $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;

         $delta_col_val =~ s/^0*//;
         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";

        if ($delta_col_val eq "@data" )
         {
#          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);
          print "\n\nTEST PASSED\n\n";
          }
        else {

          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);

          print "\n\nTEST FAILED\n\n";
#          $xls_workbook->SaveAs("Temp_Res_bkp.xls");
           }
         }
      }
     }
    }

      $xls_workbook->SaveAs("Temp_Res2.xls");
================================================


0
 

Author Comment

by:sunny82
ID: 33562348
Any solutions?

Trying this also, same result, $delta_col_val not getting printed probably because it is a blank, so have to find a way to skip those blank rows, and take the first non blank value in the same column..

==================================
for my $s_row($s_row_min .. $s_row_max) {
     for my $s_col($s_col_min+1 .. $s_col_max) {
      my $xls_cell = $xls_worksheet->get_cell($s_row, $s_col);
      next unless defined $xls_cell;
      if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

#         print "The column name is " .  $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";

#         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row, $s_col)->value . "\n";
#         print $s_row;
#         print $s_col;
#         my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;
         next unless defined my $delta_col_val;
         $delta_col_val = $xls_worksheet->get_cell($s_row+1, $s_col)->value;

         $delta_col_val =~ s/^0*//;
         print "The Delta Col value is " . $xls_worksheet->get_cell($s_row+1, $s_col)->value . "\n";

        if ($delta_col_val eq "@data" )
         {
#          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);
          print "\n\nTEST PASSED\n\n";
          }
        else {

          $xls_worksheet->AddCell($s_row+1, $s_col, $delta_col_val, $format1);

          print "\n\nTEST FAILED\n\n";
#          $xls_workbook->SaveAs("Temp_Res_bkp.xls");
           }
         }
      }
     }
    }
 $xls_workbook->SaveAs("Temp_Res2.xls");

==============================================
0
 
LVL 10

Accepted Solution

by:
MadShiva earned 500 total points
ID: 33565316
Dear sunny82,

Change the line :

my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value;

to

my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col)->value if $xls_worksheet;

Should do the error checking.

Best Regards
0
 

Author Comment

by:sunny82
ID: 33567943
Your solution worked. I also tried an alternate solution by changing the loop structure and it worked fine too.

I did this

================================

or my $s_col($s_col_min+1 .. $s_col_max) {
      my $xls_cell = $xls_worksheet->get_cell(0, $s_col);
      next unless defined $xls_cell;
      if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

         for my $s_row($s_row_min+1 .. $s_row_max) {
         my $delta_col_val = $xls_worksheet->get_cell($s_row, $s_col);

         if (defined $delta_col_val) {
         $delta_col_val = $delta_col_val->value;
#         $delta_col_val =~ s/^0*//;
         print "The Delta Col value is " . $delta_col_val . "\n";

           if ($delta_col_val eq "@data" )
         {
#          $xls_worksheet->AddCell($s_row, $s_col, $delta_col_val, $format1);
          print "\n\nTEST PASSED\n\n";
          }
           else {

          $xls_worksheet->AddCell($s_row+1, $s_col, $delta_col_val, $format1);

           print "\n\nTEST FAILED\n\n";
#          $xls_workbook->SaveAs("Temp_Res_bkp.xls");
           }
          }
         else {next;}
         }
        }
         else {next;}
     }
    }

=========================================
0
 

Author Closing Comment

by:sunny82
ID: 33567961
Both the solutions worked...Thx..
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question