Solved

Why is this error coming in Perl spreadsheets?

Posted on 2010-08-30
8
483 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

758 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

20 Experts available now in Live!

Get 1:1 Help Now