Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

Why is this error coming in Perl spreadsheets?

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
sunny82
Asked:
sunny82
  • 5
  • 2
1 Solution
 
ozoCommented:
$xls_worksheet->get_cell($s_row, $s_col) must have returned undef
0
 
sunny82Author Commented:
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
 
ozoCommented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 
sunny82Author Commented:
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
 
sunny82Author Commented:
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
 
TobiasCommented:
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
 
sunny82Author Commented:
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
 
sunny82Author Commented:
Both the solutions worked...Thx..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now