Solved

Why is this error coming in Perl spreadsheets?

Posted on 2010-08-30
8
487 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macro Capitalize 4 54
SUMIFS with a criteria that could be in multiple rows 21 33
Sum iF  based on a null cell 11 29
Excel callender with date slider 5 27
There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now