Solved

How do I move  rows from one sheet to another?

Posted on 2010-08-31
35
1,229 Views
Last Modified: 2012-05-10
I want to append every row in Temp_Res3_Int.xls to another spreadsheet Final_Res.xls. At a time, Temp_Res3_Int.xls  contains only 1 row. So one by one, all the rows will get appended to Final_Res.xls after the data validations are done like TEST PASSED or TEST FAILED. How can I do it?

Here is the code and the attached Temp_Res3_Int.xls spreadsheet.


#!/usr/bin/perl5.10 -w





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 = shift;#"PBTRDPL";#"PBCLCASHPAY";



#shift;



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



#shift;

#"PBCLCASHPAY";

#shift;



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



#shift;

#"INPUT"; 



#shift;



#"INPUT";

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



#shift;

#shift; 

#11082557;

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



#shift;

#20000050;

#shift;

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

#11082557;



my $asset_id = shift;#0;#shift;

my $audit_acct_num = shift;#'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 $v_parser   = Spreadsheet::ParseExcel::SaveParser->new();

my $v_workbook = $v_parser->Parse("Validation_Results.xls");

my $v_worksheet = $v_workbook->{Worksheet}[0];





my ( $v_row_min, $v_row_max ) = $v_worksheet->row_range();

my ( $v_col_min, $v_col_max ) = $v_worksheet->col_range();





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

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 dcol 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";



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

          $xls_worksheet->AddCell($s_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {

          

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

          $xls_worksheet->AddCell($s_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }

         else {next;}

         }

        } 

         else {next;}

     }

    }



      $xls_workbook->SaveAs("Temp_Res3_Int.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
0
Comment
Question by:sunny82
  • 26
  • 9
35 Comments
 

Author Comment

by:sunny82
ID: 33571277
Independently, I am trying to do the above...i.e. copy 1 row into another spreadsheet, but getting this error --- Can't call method "row_range" on an undefined value at /ms/user/s/sayantag/MoveIntExcel1.pl line 45.
 at /ms/user/s/sayantag/MoveIntExcel1.pl line 30
        main::__ANON__('Can\'t call method "row_range" on an undefined value at /ms/u...') called at /ms/user/s/sayantag/MoveIntExcel1.pl line 45

Pls help...where am I going wrong?
#!/usr/bin/perl5.10 -w





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 $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res3_Int.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");





my $xls = "Final_Res.xls";

my $parser  = Spreadsheet::ParseExcel::SaveParser->new();

my $workbook = $parser->Parse($xls);

my $worksheet = $workbook->{Worksheet}[0];





my ( $v_row_min, $v_row_max ) = $worksheet->row_range();

my ( $v_col_min, $v_col_max ) = $worksheet->col_range();





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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();



for my $v_row ( $v_row_min .. $v_row_max ) {

for my $v_col ($v_col_min .. $v_col_max) {

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

   

   for my $col ($col_min .. $col_max) {

      my $cell = $sql_worksheet->get_cell($row,$col);

      

      next unless defined $cell;

      my $cv = $cell->value;



if (defined $cell) {

$worksheet->AddCell($v_row,$v_col,$cv);

}

else {next;}

















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

   }







    $workbook->close();

    $workbook->SaveAs($xls);

}

}

}

Open in new window

0
 

Author Comment

by:sunny82
ID: 33571800
Pls note that this is in relation to an earlier question

http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26435497.html

The spreadsheet will get created for 1 row at every run and those rows have to be appended to the new spreadsheet.

I am trying an independent code here so that I can copy one row to another spreadsheet using SaveParser. can this be done? Pls help..
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33576320
Sure.  This should copy one row from Temp_Res3_Int.xls to Final_Res.xls (appending it after the last line).

If it doesn't work, let me know...
#!/usr/bin/perl5.10

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 $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Temp_Res3_Int.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");

my $xls = "Final_Res.xls";
my $parser  = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook = $parser->Parse($xls);
my $worksheet = $workbook->{Worksheet}[0];

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

my $row = $row_min; # possibly +1 if there is a header row

my ( $junk, $v_row ) = $worksheet->row_range();
$v_row++; # increment to be the line beyond the end of the current xls

for my $col ($col_min .. $col_max) {
    my $cell = $sql_worksheet->get_cell($row,$col);

    next unless defined $cell;
    my $cv = $cell->value;
    $worksheet->AddCell($v_row, $col, $cv);
}

$workbook->close();
$workbook->SaveAs($xls);

Open in new window

0
 

Author Comment

by:sunny82
ID: 33577026
Hi,

I got this below error. Pls note that the "Final_Res.xls" is  not there at all. Does SaveParser  create it?

Can't call method "row_range" on an undefined value at /ms/user/s/sayantag/MoveIntExcel2.pl line 48.
 at /ms/user/s/sayantag/MoveIntExcel2.pl line 30
        main::__ANON__('Can\'t call method "row_range" on an undefined value at /ms/u...') called at /ms/user/s/sayantag/MoveIntExcel2.pl line 48


#!/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 $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls = "Final_Res.xls";

my $parser  = Spreadsheet::ParseExcel::SaveParser->new();

my $workbook = $parser->Parse($xls);

my $worksheet = $workbook->{Worksheet}[0];



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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();



my $row = $row_min; # possibly +1 if there is a header row



my ( $junk, $v_row ) = $worksheet->row_range();

$v_row++; # increment to be the line beyond the end of the current xls



for my $col ($col_min .. $col_max) {

    my $cell = $sql_worksheet->get_cell($row,$col);



    next unless defined $cell;

    my $cv = $cell->value;

    $worksheet->AddCell($v_row, $col, $cv);

}

$workbook->close();

$workbook->SaveAs($xls);

Open in new window

Temp-Res4.xls
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33577149
No.  I'm pretty sure SaveParser only works with xls that already exist.  If it doesn't exist, you probably need to do something like this...

I'm curious - why are you using WriteExcel::Big?  It requires a very big spreadsheet before that is required.  Is your real xls really that large?
#!/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 $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");

my $xls = "Final_Res.xls";
my ($parser, $workbook, $worksheet);
if (-f $xls) {
    $parser  = Spreadsheet::ParseExcel::SaveParser->new();
    $workbook = $parser->Parse($xls) or die "could not read $xls: $!";
    $worksheet = $workbook->{Worksheet}[0];
} else { # create it
    $workbook = Spreadsheet::WriteExcel::Big->new($xls) or die "could not create $xls: $!";
    $worksheet = $workbook->add_worksheet();
}

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

my $row = $row_min; # possibly +1 if there is a header row

my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, 0);
$v_row++; # increment to be the line beyond the end of the current xls

for my $col ($col_min .. $col_max) {
    my $cell = $sql_worksheet->get_cell($row,$col);

    next unless defined $cell;
    my $cv = $cell->value;
    if ($parser) {
        $worksheet->AddCell($v_row, $col, $cv);
    } else {
        $worksheet->write($v_row, $col, $cv);
    }
}
$workbook->close();
$workbook->SaveAs($xls) if $parser;

Open in new window

0
 

Author Comment

by:sunny82
ID: 33577288
No, actually WriteExcel::Big was from some previous code so I kept on using it, we can replace it with just WriteExcel. Regarding the code above, it ran fine, but only generated the column headers and not the data. Here is the output I got.


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





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 $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls = "Final_Res.xls";

my ($parser, $workbook, $worksheet);

if (-f $xls) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls) or die "could not read $xls: $!";

    $worksheet = $workbook->{Worksheet}[0];

} else { # create it

    $workbook = Spreadsheet::WriteExcel::Big->new($xls) or die "could not create $xls: $!";

    $worksheet = $workbook->add_worksheet();

}



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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();



my $row = $row_min; # possibly +1 if there is a header row



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, 0);

$v_row++; # increment to be the line beyond the end of the current xls



for my $col ($col_min .. $col_max) {

    my $cell = $sql_worksheet->get_cell($row,$col);



    next unless defined $cell;

    my $cv = $cell->value;

    if ($parser) {

        $worksheet->AddCell($v_row, $col, $cv);

    } else {

        $worksheet->write($v_row, $col, $cv);

    }

}

$workbook->close();

$workbook->SaveAs($xls) if $parser;

Open in new window

Final-Res.xls
0
 

Author Comment

by:sunny82
ID: 33577561
Why do you think this is happening, the data part is getting stripped off and only the headers remain?
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33577871
I'm not sure.  You are calling this script once per existence of Temp_Res*.xls, right?  This will only copy one row per invocation (since there's only one row in Temp_Res).

Here's some minor modifications.  This will copy as many rows as exist in Temp_Res (but only copy the headers on the first run when Final_Res is created).

If you still see problems (probably), could you post all of (or at least a few of) the different Temp_Res files that this script is being run against?
#!/ms/dist/perl5/bin/perl5.10

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 $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");

my $xls = "Final_Res.xls";
my ($parser, $workbook, $worksheet);
if (-f $xls) {
    $parser  = Spreadsheet::ParseExcel::SaveParser->new();
    $workbook = $parser->Parse($xls) or die "could not read $xls: $!";
    $worksheet = $workbook->{Worksheet}[0];
} else { # create it
    $workbook = Spreadsheet::WriteExcel::Big->new($xls) or die "could not create $xls: $!";
    $worksheet = $workbook->add_worksheet();
}

my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();
$row_min++ if $parser; # skip header row after first time

my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);
$v_row++; # increment to be the line beyond the end of the current xls

ROW:
for my $row ($row_min .. $row_max) {
COL:
    for my $col ($col_min .. $col_max) {
        my $cell = $sql_worksheet->get_cell($row,$col);

        next COL unless defined $cell;
        my $cv = $cell->value;
        if ($parser) {
            $worksheet->AddCell($v_row, $col, $cv);
        } else {
            $worksheet->write($v_row, $col, $cv);
        }
    }
    $v_row++;
}

$workbook->close();
$workbook->SaveAs($xls) if $parser;

Open in new window

0
 

Author Comment

by:sunny82
ID: 33577940
The column headers of Temp_Res4.xls are coming into Final_Res.xls in the second row and in the process, it seems removes the column values.

Pls let me know what you think..  
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33578074
With the latest version of the script above, try the following:

1) delete Final_Res.xls
2) run the script
3) what is in Final_Res.xls?
4) modify Temp_Res4.xls
5) run the script again
6) what is in Final_Res.xls?

I would expect to see the headers on the first row (now) and the data on subsequent rows.
0
 

Author Comment

by:sunny82
ID: 33579741
1. done
2. done
3. Pls see attached
4. Changed one col value
5. If I run it now without deleting Final_Res.xls
getting this error --

Can't locate object method "close" via package "Spreadsheet::ParseExcel::SaveParser::Workbook" at /ms/user/s/sayantag/MoveIntExcel2.pl line 68.
 at /ms/user/s/sayantag/MoveIntExcel2.pl line 30
        main::__ANON__('Can\'t locate object method "close" via package "Spreadsheet:...') called at /ms/user/s/sayantag/MoveIntExcel2.pl line 68

6.Now deleted Final_Res.xls
7. Now ran the script again, same result
8. Attached is Final_Res.xls, same no data...


this is the code I am running..

#!/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;



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 $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls = "Final_Res.xls";

my ($parser, $workbook, $worksheet);

if (-f $xls) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls) or die "could not read $xls: $!";

    $worksheet = $workbook->{Worksheet}[0];

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls) or die "could not create $xls: $!";

    $worksheet = $workbook->add_worksheet();

}



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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();



my $row = $row_min; # possibly +1 if there is a header row



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, 0);

$v_row++; # increment to be the line beyond the end of the current xls



for my $col ($col_min .. $col_max) {

    my $cell = $sql_worksheet->get_cell($row,$col);



    next unless defined $cell;

    my $cv = $cell->value;

    if ($parser) {

        $worksheet->AddCell($v_row, $col, $cv);

    } else {

        $worksheet->write($v_row, $col, $cv);

    }

}

$workbook->close();

$workbook->SaveAs($xls) if $parser;

Open in new window

Final-Res.xls
Final-Res.xls
0
 

Author Comment

by:sunny82
ID: 33579824
Yes, really sorry I ran the code with the prev version in it. this version worked fine.
Attached are the results.

One question -- I have to delete Final_Res.xls after it is generated the first time. Otherwise getting this error--

Can't locate object method "close" via package "Spreadsheet::ParseExcel::SaveParser::Workbook" at /ms/user/s/sayantag/MoveIntExcel2.pl line 73.
 at /ms/user/s/sayantag/MoveIntExcel2.pl line 30
        main::__ANON__('Can\'t locate object method "close" via package "Spreadsheet:...') called at /ms/user/s/sayantag/MoveIntExcel2.pl line 73

And when I delete it and I change the Temp4_Res.xls and run the script, it is getting created with the new value and not storing the earlier one.



Also another question-- where are the coloring formats gone here like in Temp_Res4.xls. Do I need to re-define them?

Below is the code ...
#!/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;



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 $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls = "Final_Res.xls";

my ($parser, $workbook, $worksheet);

if (-f $xls) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls) or die "could not read $xls: $!";

    $worksheet = $workbook->{Worksheet}[0];

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls) or die "could not create $xls: $!";

    $worksheet = $workbook->add_worksheet();

}



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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

$row_min++ if $parser; # skip header row after first time



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls



ROW:

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

COL:

    for my $col ($col_min .. $col_max) {

        my $cell = $sql_worksheet->get_cell($row,$col);



        next COL unless defined $cell;

        my $cv = $cell->value;

        if ($parser) {

            $worksheet->AddCell($v_row, $col, $cv);

        } else {

            $worksheet->write($v_row, $col, $cv);

        }

    }

    $v_row++;

}



$workbook->close();

$workbook->SaveAs($xls) if $parser;

Open in new window

Final-Res.xls
Final-Res.xls
0
 

Author Comment

by:sunny82
ID: 33580095
Any solutions for this? Pls let me know...
0
 

Author Comment

by:sunny82
ID: 33580327
I have another question at
http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_26445308.html

solving either one of them will do for me. If that one can be solved , it is well and good.

Pls help me...
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33580620
To fix the error, change line 73 to:

$workbook->close() unless $parser;

It seems that Spreadsheet::ParseExcel::SaveParser doesn't have a close method.

As to the color formats, yes, you'll have to redefine them in this script.

Let me know if it still doesn't work properly.
0
 

Author Comment

by:sunny82
ID: 33580836
Thx a lot, that worked great.

Actually I was wondering whether the color formats can be carried over to this new excel, that would be great then. Because the formatting was done based on some calculations with the database on that single row for col X say and I wanted both that particular column and the TEST PASSED or FAILED message to be colored. In that way, It was done in the excel. Pls see the attached excel on how the coloring format is done and whether it can be carried over to the resultant sheet for all the rows after parsing it with the above code. It is like simple copying and pasting it manually, whether it can be done.

Pls let me know..
Int-Res18-2-9.xls
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33581082
Try this.  I'm not 100% sure it will work since I haven't used get_format before...

Hopefully, the format object used by WriteExcel is the same as the one used by ParseExcel.

I'm not sure if Spreadsheet::ParseExcel supports $cell->{FormatNo} and Spreadsheet::ParseExcel::SaveParser->AddCell doesn't support format object.  If it doesn't work, it will be a major pain to get all of the formatting into the new xls.
#!/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;

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 $sql_parser   = Spreadsheet::ParseExcel->new();
my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");
my $sql_worksheet =  $sql_workbook->worksheet("Delta");

my $xls = "Final_Res.xls";
my ($parser, $workbook, $worksheet);
if (-f $xls) {
    $parser  = Spreadsheet::ParseExcel::SaveParser->new();
    $workbook = $parser->Parse($xls) or die "could not read $xls: $!";
    $worksheet = $workbook->{Worksheet}[0];
} else { # create it
    $workbook = Spreadsheet::WriteExcel->new($xls) or die "could not create $xls: $!";
    $worksheet = $workbook->add_worksheet();
}

my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();
$row_min++ if $parser; # skip header row after first time

my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);
$v_row++; # increment to be the line beyond the end of the current xls

ROW:
for my $row ($row_min .. $row_max) {
COL:
    for my $col ($col_min .. $col_max) {
        my $cell = $sql_worksheet->get_cell($row,$col);

        next COL unless defined $cell;
        my $cv = $cell->value;
        if ($parser) {
            my $format = $cell->{FormatNo};
            $worksheet->AddCell($v_row, $col, $cv, $format);
        } else {
            my $format = $cell->get_format;
            $worksheet->write($v_row, $col, $cv, $format);
        }
    }
    $v_row++;
}

$workbook->close() unless $parser;
$workbook->SaveAs($xls) if $parser;

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:sunny82
ID: 33581684
Getting the following error

Use of uninitialized value in numeric eq (==) at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Worksheet.pm line 1798.
 at /ms/user/s/sayantag/MoveIntExcel4.pl line 31
        main::__ANON__('Use of uninitialized value in numeric eq (==) at //ms/dist/pe...') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Worksheet.pm line 1798
        Spreadsheet::WriteExcel::Worksheet::_XF('Spreadsheet::WriteExcel::Worksheet=HASH(0x8bc6f60)', 0, 1, 'Spreadsheet::ParseExcel::Format=HASH(0x8ba63cc)') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Worksheet.pm line 2122
        Spreadsheet::WriteExcel::Worksheet::write_string('Spreadsheet::WriteExcel::Worksheet=HASH(0x8bc6f60)', 0, 1, 'POSTING_EVENT_TYPE', 'Spreadsheet::ParseExcel::Format=HASH(0x8ba63cc)') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Worksheet.pm line 1637
        Spreadsheet::WriteExcel::Worksheet::write('Spreadsheet::WriteExcel::Worksheet=HASH(0x8bc6f60)', 0, 1, 'POSTING_EVENT_TYPE', 'Spreadsheet::ParseExcel::Format=HASH(0x8ba63cc)') called at /ms/user/s/sayantag/MoveIntExcel4.pl line 72
 at /ms/user/s/sayantag/MoveIntExcel4.pl line 30
        main::__ANON__('Use of uninitialized value in numeric eq (==) at //ms/dist/pe...') called at /ms/dist/perl5/PROJ/core/5.8.8-2/.exec/ia32.linux.2.4.glibc.2.3/lib/perl5/Carp.pm line 270
        Carp::confess('Use of uninitialized value in numeric eq (==) at //ms/dist/pe...') called at /ms/user/s/sayantag/MoveIntExcel4.pl line 31
        main::__ANON__('Use of uninitialized value in numeric eq (==) at //ms/dist/pe...') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Worksheet.pm line 1798
        Spreadsheet::WriteExcel::Worksheet::_XF('Spreadsheet::WriteExcel::Worksheet=HASH(0x8bc6f60)', 0, 1, 'Spreadsheet::ParseExcel::Format=HASH(0x8ba63cc)') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Worksheet.pm line 2122
        Spreadsheet::WriteExcel::Worksheet::write_string('Spreadsheet::WriteExcel::Worksheet=HASH(0x8bc6f60)', 0, 1, 'POSTING_EVENT_TYPE', 'Spreadsheet::ParseExcel::Format=HASH(0x8ba63cc)') called at //ms/dist/perl5/PROJ/Spreadsheet-WriteExcel/2.35/lib/perl5/Spreadsheet/WriteExcel/Worksheet.pm line 1637
        Spreadsheet::WriteExcel::Worksheet::write('Spreadsheet::WriteExcel::Worksheet=HASH(0x8bc6f60)', 0, 1, 'POSTING_EVENT_TYPE', 'Spreadsheet::ParseExcel::Format=HASH(0x8ba63cc)') called at /ms/user/s/sayantag/MoveIntExcel4.pl line 72

#!/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;



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 $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res4.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls = "Final_Res1.xls";



my ($parser, $workbook, $worksheet);

if (-f $xls) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls) or die "could not read $xls: $!";

    $worksheet = $workbook->{Worksheet}[0];

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls) or die "could not create $xls: $!";

    $worksheet = $workbook->add_worksheet();

}



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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

$row_min++ if $parser; # skip header row after first time



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls



ROW:

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

COL:

    for my $col ($col_min .. $col_max) {

        my $cell = $sql_worksheet->get_cell($row,$col);



        next COL unless defined $cell;

        my $cv = $cell->value;

        if ($parser) {

            my $format = $cell->{FormatNo};

            $worksheet->AddCell($v_row, $col, $cv, $format);

        } else {

            my $format = $cell->get_format;

            $worksheet->write($v_row, $col, $cv, $format);

        }

    }

    $v_row++;

}



$workbook->close() unless $parser;

$workbook->SaveAs($xls) if $parser;

Open in new window

0
 

Author Comment

by:sunny82
ID: 33581725
I tried to do the whole calculation in this code itself --
but getting this error for WriteExcel, SaveParser worked fine. I just want to color the first row which is not happening by this..

The error is --

Can't locate object method "get_cell" via package "Spreadsheet::WriteExcel::Worksheet" at /ms/user/s/sayantag/MoveIntExcel3.pl line 106.
 at /ms/user/s/sayantag/MoveIntExcel3.pl line 30
        main::__ANON__('Can\'t locate object method "get_cell" via package "Spreadshe...') called at /ms/user/s/sayantag/MoveIntExcel3.pl line 106

What should I do? As I said, the coloring and validation logic in Save parser  is working fine but WriteExcel is creating the problem with get_cell method, seems it is not there. Also after writing every row there is an extra blank row, is there any way I can delete it?

Attached is the excel I generated --



#!/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;



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 $xls = shift;

my $dcol = shift;

my @data = @ARGV;







my $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse($xls);

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls_res = "Final_Res.xls";

my ($parser, $workbook, $worksheet, $format1, $format2);

if (-f $xls_res) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

    $worksheet = $workbook->{Worksheet}[0];

    $format1 = $workbook->AddFormat(Fill => [0,11,0]);

    $format2 = $workbook->AddFormat(Fill => [1,10,0]);

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls_res) or die "could not create $xls_res: $!";

    $worksheet = $workbook->add_worksheet();

    $format1 = $workbook->add_format();

    $format2 = $workbook->add_format();



    $format1->set_bg_color('green');

    $format2->set_bg_color('red');



}









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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

$row_min++ if $parser; # skip header row after first time



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls



ROW:

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

COL:

    for my $col ($col_min .. $col_max) {

        my $cell = $sql_worksheet->get_cell($row,$col);



        next COL unless defined $cell;

        my $cv = $cell->value;

        if ($parser) {

            $worksheet->AddCell($v_row, $col, $cv);

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

         my $xls_cell = $worksheet->get_cell(0, $col);

         if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         

         my $delta_col_val = $worksheet->get_cell($v_row, $col);  

         $delta_col_val = $delta_col_val->value;

         if ($delta_col_val eq "@data" )

         {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }



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

        } else {

          $worksheet->write($v_row, $col, $cv);

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

         

         my $xls_cell = $worksheet->get_cell(0, $col);

         next unless defined $xls_cell;

         if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

         my $delta_col_val = $worksheet->get_cell($v_row, $col);

         $delta_col_val = $delta_col_val->value;

         if ($delta_col_val eq "@data" )

         {

          $worksheet->write($v_row, $col, $delta_col_val, $format1);

          $worksheet->write($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->write($v_row, $col, $delta_col_val, $format2);

          $worksheet->write($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }





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

        }

    }

    $v_row++;

}





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









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

$workbook->close() unless $parser;

$workbook->SaveAs($xls_res) if $parser;

Open in new window

Final-Res.xls
0
 

Author Comment

by:sunny82
ID: 33581753
Sorry about the attachment, here is the excel
Final-Res.xls
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33581943
get_cell doesn't exist in WriteExcel.  Rather than checking values in the new spreadsheet, you should be checking the value in the source spreadsheet ($sql_worksheet I think).  Probably better to check the source ($sql_worksheet) in both cases to be consistent and reduce different code.
0
 

Author Comment

by:sunny82
ID: 33582106
yes, that check is already there in the code which calls this code and there it is working fine too, but that $sql_worksheet once it writes into "Final_Res.xls", all the coloring disappears, thats why want to make the check here itself. Can I somehow read that row once it finishes writing in this loop

} else {
          $worksheet->write($v_row, $col, $cv);

The validation logic and coloring should be just how I implemented it in the Saveparser loop

Below is the code which calls the code above..pls check lines 246-286 in the code which calls the code above at ID: 33581725
#!/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 MSDW::Version

  'Date-Calc'          => '5.7',

  ;





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;

use Date::Calc qw(:all);





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

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



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



#shift;



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



#shift;

#"PBCLCASHPAY";

#shift;



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



#shift;

#"INPUT"; 



#shift;



#"INPUT";

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



#shift;

#shift; 

#11082557;

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



#shift;

#20000050;

#shift;

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

#11082557;



my $asset_id = shift;#0;#shift;

my $audit_acct_num = shift;#'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`;



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

########## Getting Timestamp ################



sub _getCOB {



my $mon = 'JanFebMarAprMayJunJulAugSepOctNovDec';



my @date;



my ($year,$month,$day) = Add_Delta_Days(Today(),0);



my $dow = Day_of_Week($year,$month,$day);



#if($dow == 1) {

#  @date = Add_Delta_Days(Today(), -3);

# }



#elsif ($dow == 7) {

#  @date = Add_Delta_Days(Today(), -2);

#  }



#else {

  @date = Add_Delta_Days(Today(), -0);

# }



my $date = $date[2] . '-' . substr($mon, $date[1]*3 - 3, 3) . '-' . $date[0];



return $date;



}











my $cob_date;



$cob_date = &_getCOB();



print "COB Date is $cob_date\n\n";





my $cob_date1 = join ':', Now();

$cob_date1 =~ s/:/_/g;



















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

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 $v_parser   = Spreadsheet::ParseExcel::SaveParser->new();

#my $v_workbook = $v_parser->Parse("Validation_Results.xls");

#my $v_worksheet = $v_workbook->{Worksheet}[0];





#my ( $v_row_min, $v_row_max ) = $v_worksheet->row_range();

#my ( $v_col_min, $v_col_max ) = $v_worksheet->col_range();





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

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 dcol 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";



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

          $xls_worksheet->AddCell($s_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {

          

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

          $xls_worksheet->AddCell($s_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

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

       my $xls = "Int_Res" . $cob_date1 . ".xls";

      $xls_workbook->SaveAs($xls);

      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/MoveIntExcel3.pl", $xls, $dcol, @data);

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

          }

         else {next;}

         }

        } 

         else {next;}

     }

    }



#      my $xls = "Int_Res" . $cob_date1 . ".xls";

#      $xls_workbook->SaveAs($xls);

#      system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/MoveIntExcel2.pl", $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

0
 

Author Comment

by:sunny82
ID: 33582231
if can read the value written in by may be using ParseExcel

} else {
          $worksheet->write($v_row, $col, $cv);

and do the validation as in the SaveParser loop and write the TEST FAILED/PASSED with coloring, I will be done..

Pls let me know if this can be done..
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 33583329
I think this will work (but it's ugly)...

It could probably be cleaned up some but I'm leaving it like this as I'm heading to bed...
#!/ms/dist/perl5/bin/perl5.10



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;



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 $xls = shift;

my $dcol = shift;

my @data = @ARGV;



my $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse($xls);

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls_res = "Final_Res.xls";

my ($parser, $workbook, $worksheet, $format1, $format2);

if (-f $xls_res) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

    $worksheet = $workbook->{Worksheet}[0];

    $format1 = $workbook->AddFormat(Fill => [0,11,0]);

    $format2 = $workbook->AddFormat(Fill => [1,10,0]);

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls_res) or die "could not create $xls_res: $!";

    $worksheet = $workbook->add_worksheet();

    $format1 = $workbook->add_format();

    $format2 = $workbook->add_format();



    $format1->set_bg_color('green');

    $format2->set_bg_color('red');

}



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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

$row_min++ if $parser; # skip header row after first time



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls



ROW:

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

COL:

    for my $col ($col_min .. $col_max) {

        my $cell = $sql_worksheet->get_cell($row,$col);



        next COL unless defined $cell;

        my $cv = $cell->value;

        if ($parser) {

            $worksheet->AddCell($v_row, $col, $cv);

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

            my $xls_cell = $worksheet->get_cell(0, $col);

            if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         

                my $delta_col_val = $worksheet->get_cell($v_row, $col);  

                $delta_col_val = $delta_col_val->value;

                if ($delta_col_val eq "@data" ) {

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

                    $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

                    print "\n\nTEST PASSED\n\n";

                } else {

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

                    $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

                    print "\n\nTEST FAILED\n\n";

                }

            }



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

        } else {

            $worksheet->write($v_row, $col, $cv);

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

            $workbook->close;

            # add formatting

            $parser  = Spreadsheet::ParseExcel::SaveParser->new();

            $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

            $worksheet = $workbook->{Worksheet}[0];

            $format1 = $workbook->AddFormat(Fill => [0,11,0]);

            $format2 = $workbook->AddFormat(Fill => [1,10,0]);

            my $xls_cell = $worksheet->get_cell(0, $col);

            if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         

                $delta_col_val = $worksheet->get_cell($v_row, $col);  

                $delta_col_val = $delta_col_val->value;

                if ($delta_col_val eq "@data" ) {

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

                    $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

                    print "\n\nTEST PASSED\n\n";

                } else {

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

                    $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

                    print "\n\nTEST FAILED\n\n";

                }

            }



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

        }

    }

    $v_row++;

}





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









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

$workbook->SaveAs($xls_res) if $parser;

Open in new window

0
 

Author Comment

by:sunny82
ID: 33583760
Thx so much...I don't have words to appreciate your efforts...I will try this first thing tomorrow morning and let you know...
0
 

Author Comment

by:sunny82
ID: 33587298
Hi,

It ran fine and I got the coloring and the TEST PASSED/FAILED message based on validations. Attached is the excel I am getting. I have just two issues --

1. I don't want the first row to get colored or the TEST FAILED/PASSED message appearing(.i.e in the header row). The 1st column of 1st row should be just blank like other rows. The second row is absolutely fine.

2. The more greater concern is why is an extra blank row coming after every row. After 1st row, there is 1 blank row, after 2nd row

I will be done if these 2 are solved.

Many thanks again and pls let me know what can be done.

Attached is my code and the Fina_Res excel sheet. Pls note that the code below is being called by the code in ID 33582106
#!/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;



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 $xls = shift;

my $dcol = shift;

my @data = @ARGV;







my $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse($xls);

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls_res = "Final_Res.xls";

my ($parser, $workbook, $worksheet, $format1, $format2);

if (-f $xls_res) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

    $worksheet = $workbook->{Worksheet}[0];

    $format1 = $workbook->AddFormat(Fill => [0,11,0]);

    $format2 = $workbook->AddFormat(Fill => [1,10,0]);

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls_res) or die "could not create $xls_res: $!";

    $worksheet = $workbook->add_worksheet();

    $format1 = $workbook->add_format();

    $format2 = $workbook->add_format();



    $format1->set_bg_color('green');

    $format2->set_bg_color('red');



}









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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

$row_min++ if $parser; # skip header row after first time



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls



ROW:

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

COL:

    for my $col ($col_min .. $col_max) {

        my $cell = $sql_worksheet->get_cell($row,$col);



        next COL unless defined $cell;

        my $cv = $cell->value;

        if ($parser) {

            $worksheet->AddCell($v_row, $col, $cv);

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

         my $xls_cell = $worksheet->get_cell(0, $col);

         if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         

         my $delta_col_val = $worksheet->get_cell($v_row, $col);  

         $delta_col_val = $delta_col_val->value;

         if ($delta_col_val eq "@data" )

         {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }



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

        } else {

          $worksheet->write($v_row, $col, $cv);

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

         

          $workbook->close;

            # add formatting

            $parser  = Spreadsheet::ParseExcel::SaveParser->new();

            $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

            $worksheet = $workbook->{Worksheet}[0];

            $format1 = $workbook->AddFormat(Fill => [0,11,0]);

            $format2 = $workbook->AddFormat(Fill => [1,10,0]);

            

            my $xls_cell = $worksheet->get_cell(0, $col);

            if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

               # $v_row++;         

                my $delta_col_val = $worksheet->get_cell($v_row, $col);  

                $delta_col_val = $delta_col_val->value;

                if ($delta_col_val eq "@data" ) {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

                    $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

                    print "\n\nTEST PASSED\n\n";

                } else {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

                    $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

                    print "\n\nTEST FAILED\n\n";

                }

            }





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

        }

    }

    $v_row++;

}





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









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

$workbook->close() unless $parser;

$workbook->SaveAs($xls_res) if $parser;

Open in new window

Final-Res.xls
0
 

Author Comment

by:sunny82
ID: 33587307
The point no.2 is

2. The more greater concern is why is an extra blank row coming after every row. After 1st row, there is 1 blank row, after 2nd row 2 blank rows and so on.
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 33587714
I think this will do it.

I hard-coded $row_max to try avoiding the extra lines.  If that doesn't fix it, I'd suggest removing the $row_max hard-coding and try commenting out the first $v_row++ (outside the loop).

I added a check to make sure $v_row is > 0 to add formatting which should fix the formatted header.
#!/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;

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 $xls = shift;
my $dcol = shift;
my @data = @ARGV;



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

my $xls_res = "Final_Res.xls";
my ($parser, $workbook, $worksheet, $format1, $format2);
if (-f $xls_res) {
    $parser  = Spreadsheet::ParseExcel::SaveParser->new();
    $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";
    $worksheet = $workbook->{Worksheet}[0];
    $format1 = $workbook->AddFormat(Fill => [0,11,0]);
    $format2 = $workbook->AddFormat(Fill => [1,10,0]);
} else { # create it
    $workbook = Spreadsheet::WriteExcel->new($xls_res) or die "could not create $xls_res: $!";
    $worksheet = $workbook->add_worksheet();
    $format1 = $workbook->add_format();
    $format2 = $workbook->add_format();

    $format1->set_bg_color('green');
    $format2->set_bg_color('red');

}




my ( $row_min, $row_max ) = $sql_worksheet->row_range();
my ( $col_min, $col_max ) = $sql_worksheet->col_range();
$row_min++ if $parser; # skip header row after first time
# hard-code row_max to try avoiding extra rows
$row_max = $parser ? 0 : 1;

my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);
$v_row++; # increment to be the line beyond the end of the current xls

ROW:
for my $row ($row_min .. $row_max) {
COL:
    for my $col ($col_min .. $col_max) {
        my $cell = $sql_worksheet->get_cell($row,$col);

        next COL unless defined $cell;
        my $cv = $cell->value;
        if ($parser) {
            $worksheet->AddCell($v_row, $col, $cv);
###################################################################   
         my $xls_cell = $worksheet->get_cell(0, $col);
         if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         
         my $delta_col_val = $worksheet->get_cell($v_row, $col);  
         $delta_col_val = $delta_col_val->value;
         if ($v_row) {
         if ($delta_col_val eq "@data" )
         {
          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);
          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);
          print "\n\nTEST PASSED\n\n";
          }
           else {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);
          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);
          print "\n\nTEST FAILED\n\n";
           }
          }
         }

#################################################################
        } else {
          $worksheet->write($v_row, $col, $cv);
#################################################################
         
          $workbook->close;
          if ($v_row) {
            # add formatting
            $parser  = Spreadsheet::ParseExcel::SaveParser->new();
            $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";
            $worksheet = $workbook->{Worksheet}[0];
            $format1 = $workbook->AddFormat(Fill => [0,11,0]);
            $format2 = $workbook->AddFormat(Fill => [1,10,0]);
            
            my $xls_cell = $worksheet->get_cell(0, $col);
            if (defined $xls_cell  and $xls_cell->value eq "$dcol") {
               # $v_row++;         
                my $delta_col_val = $worksheet->get_cell($v_row, $col);  
                $delta_col_val = $delta_col_val->value;
                if ($delta_col_val eq "@data" ) {
                    
                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);
                    $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);
                    print "\n\nTEST PASSED\n\n";
                } else {
                    
                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);
                    $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);
                    print "\n\nTEST FAILED\n\n";
                }
            }
          }


#################################################################
        }
    }
    $v_row++;
}


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




################################################################################
$workbook->close() unless $parser;
$workbook->SaveAs($xls_res) if $parser;

Open in new window

0
 

Author Comment

by:sunny82
ID: 33587925
I am only getting the first row using the code above with no formatting, it has got distorted.
#!/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;



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 $xls = shift;

my $dcol = shift;

my @data = @ARGV;







my $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse($xls);

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls_res = "Final_Res.xls";

my ($parser, $workbook, $worksheet, $format1, $format2);

if (-f $xls_res) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

    $worksheet = $workbook->{Worksheet}[0];

    $format1 = $workbook->AddFormat(Fill => [0,11,0]);

    $format2 = $workbook->AddFormat(Fill => [1,10,0]);

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls_res) or die "could not create $xls_res: $!";

    $worksheet = $workbook->add_worksheet();

    $format1 = $workbook->add_format();

    $format2 = $workbook->add_format();



    $format1->set_bg_color('green');

    $format2->set_bg_color('red');



}





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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

$row_min++ if $parser; # skip header row after first time

# hard-code row_max to try avoiding extra rows

$row_max = $parser ? 0 : 1;



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls



ROW:

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

COL:

    for my $col ($col_min .. $col_max) {

        my $cell = $sql_worksheet->get_cell($row,$col);



        next COL unless defined $cell;

        my $cv = $cell->value;

        if ($parser) {

            $worksheet->AddCell($v_row, $col, $cv);

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

         my $xls_cell = $worksheet->get_cell(0, $col);

         if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         

         my $delta_col_val = $worksheet->get_cell($v_row, $col);  

         $delta_col_val = $delta_col_val->value;

         if ($v_row) {

         if ($delta_col_val eq "@data" )

         {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }

         }



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

        } else {

          $worksheet->write($v_row, $col, $cv);

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

         

          $workbook->close;

          if ($v_row) {

            # add formatting

            $parser  = Spreadsheet::ParseExcel::SaveParser->new();

            $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

            $worksheet = $workbook->{Worksheet}[0];

            $format1 = $workbook->AddFormat(Fill => [0,11,0]);

            $format2 = $workbook->AddFormat(Fill => [1,10,0]);

            

            my $xls_cell = $worksheet->get_cell(0, $col);

            if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

               # $v_row++;         

                my $delta_col_val = $worksheet->get_cell($v_row, $col);  

                $delta_col_val = $delta_col_val->value;

                if ($delta_col_val eq "@data" ) {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

                    $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

                    print "\n\nTEST PASSED\n\n";

                } else {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

                    $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

                    print "\n\nTEST FAILED\n\n";

                }

            }

          }





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

        }

    }

    $v_row++;

}





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









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

$workbook->close() unless $parser;

$workbook->SaveAs($xls_res) if $parser;

Open in new window

Final-Res.xls
0
 

Author Comment

by:sunny82
ID: 33588003
removing the $row_max hard-coding and commenting out the first $v_row++ (outside the loop), I am getting this, which is also distorted --


#!/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;



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 $xls = shift;

my $dcol = shift;

my @data = @ARGV;







my $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse($xls);

my $sql_worksheet =  $sql_workbook->worksheet("Delta");



my $xls_res = "Final_Res.xls";

my ($parser, $workbook, $worksheet, $format1, $format2);

if (-f $xls_res) {

    $parser  = Spreadsheet::ParseExcel::SaveParser->new();

    $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

    $worksheet = $workbook->{Worksheet}[0];

    $format1 = $workbook->AddFormat(Fill => [0,11,0]);

    $format2 = $workbook->AddFormat(Fill => [1,10,0]);

} else { # create it

    $workbook = Spreadsheet::WriteExcel->new($xls_res) or die "could not create $xls_res: $!";

    $worksheet = $workbook->add_worksheet();

    $format1 = $workbook->add_format();

    $format2 = $workbook->add_format();



    $format1->set_bg_color('green');

    $format2->set_bg_color('red');



}





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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

$row_min++ if $parser; # skip header row after first time

# hard-code row_max to try avoiding extra rows

#$row_max = $parser ? 0 : 1;



my ( $junk, $v_row ) = $parser ? $worksheet->row_range() : (0, -1);

$v_row++; # increment to be the line beyond the end of the current xls



ROW:

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

COL:

    for my $col ($col_min .. $col_max) {

        my $cell = $sql_worksheet->get_cell($row,$col);



        next COL unless defined $cell;

        my $cv = $cell->value;

        if ($parser) {

            $worksheet->AddCell($v_row, $col, $cv);

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

         my $xls_cell = $worksheet->get_cell(0, $col);

         if (defined $xls_cell  and $xls_cell->value eq "$dcol") {         

         my $delta_col_val = $worksheet->get_cell($v_row, $col);  

         $delta_col_val = $delta_col_val->value;

         if ($v_row) {

         if ($delta_col_val eq "@data" )

         {

          $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

          $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

          print "\n\nTEST PASSED\n\n";

          }

           else {



          $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

          $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

          print "\n\nTEST FAILED\n\n";

           }

          }

         }



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

        } else {

          $worksheet->write($v_row, $col, $cv);

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

         

          $workbook->close;

          if ($v_row) {

            # add formatting

            $parser  = Spreadsheet::ParseExcel::SaveParser->new();

            $workbook = $parser->Parse($xls_res) or die "could not read $xls_res: $!";

            $worksheet = $workbook->{Worksheet}[0];

            $format1 = $workbook->AddFormat(Fill => [0,11,0]);

            $format2 = $workbook->AddFormat(Fill => [1,10,0]);

            

            my $xls_cell = $worksheet->get_cell(0, $col);

            if (defined $xls_cell  and $xls_cell->value eq "$dcol") {

               # $v_row++;         

                my $delta_col_val = $worksheet->get_cell($v_row, $col);  

                $delta_col_val = $delta_col_val->value;

                if ($delta_col_val eq "@data" ) {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format1);

                    $worksheet->AddCell($v_row, 0, "TEST PASSED", $format1);

                    print "\n\nTEST PASSED\n\n";

                } else {

                    

                    $worksheet->AddCell($v_row, $col, $delta_col_val, $format2);

                    $worksheet->AddCell($v_row, 0, "TEST FAILED", $format2);

                    print "\n\nTEST FAILED\n\n";

                }

            }

          }





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

        }

    }

#    $v_row++;

}





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









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

$workbook->close() unless $parser;

$workbook->SaveAs($xls_res) if $parser;

Open in new window

Final-Res.xls
0
 

Author Comment

by:sunny82
ID: 33588250
Either one of those conditions
at line 87 and at line 67 are distorting the output with wrong results and no formatting. The code without them is working correctly but with the above two issues.

Where do you think is the problem?
0
 

Author Comment

by:sunny82
ID: 33588266
I tried deliberately putting

my $delta_col_val = $worksheet->get_cell($v_row, $col);

as

my $delta_col_val = $worksheet->get_cell(1, $col);

to prevent coloring and validation of the first row but no changes.
0
 

Author Comment

by:sunny82
ID: 33588987
Is it because of the intermediate excels? The first intermediate excel data row after cutting off the first row from another excel sheet is generated in the first row of intermediate excel sheet after the header, the second intermediate excel generated after cutting off the second data row from the excel sheet gets generated in the second row of intermediate excel sheet after the header and so on.

Ultimately we are putting all those excels into Final_Res by renaming them them as Int_ResTimeStamp in the attached code at ID: 33582106 line 277.

Pls help me on how to fix this?
below is the code which generates them.
Attached are the 1st 3 intermediate excels
#!/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 $sql_parser   = Spreadsheet::ParseExcel->new();

my $sql_workbook = $sql_parser->parse("Temp_Res3.xls");

my $sql_worksheet =  $sql_workbook->worksheet("Delta");





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

my ( $col_min, $col_max ) = $sql_worksheet->col_range();

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

   my $xls = "Temp_Res3_Int.xls";

   my $workbook  = Spreadsheet::WriteExcel->new($xls);

   my $worksheet = $workbook->addworksheet("Delta");

   

   my $format1 = $workbook->add_format();

   my $format2 = $workbook->add_format();









$format1->set_size(12);

$format1->set_bold();

$format1->set_color('blue');

$format1->set_align('center');

$format1->set_bg_color('yellow');





   

   for my $col ($col_min .. $col_max) {

      my $cell = $sql_worksheet->get_cell($row,$col);

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





$worksheet->write(0, 1, "POSTING_EVENT_TYPE", $format1);

$worksheet->write(0, 2, "LEDGER_TYPE", $format1);

$worksheet->write(0, 3, "ACCTNG_PERIOD_ID", $format1);

$worksheet->write(0, 4, "HRCHY_EFFECTIVE_DTE", $format1);

$worksheet->write(0, 5, "BA_HRCHY_ID", $format1);

$worksheet->write(0, 6, "BA_NID", $format1);

$worksheet->write(0, 7, "LE_HRCHY_ID", $format1);

$worksheet->write(0, 8, "LE_NID", $format1);

$worksheet->write(0, 9, "REGION_HRCHY_ID", $format1);

$worksheet->write(0, 10, "REGION_NID", $format1);

$worksheet->write(0, 11, "CHANGE_CTGRY_ID", $format1);

$worksheet->write(0, 12, "ELIMINATION_ID", $format1);

$worksheet->write(0, 13, "SRC_ID", $format1);

$worksheet->write(0, 14, "VALUE", $format1);

$worksheet->write(0, 15, "RUN_GROUP_ID", $format1);

$worksheet->write(0, 16, "ASSET_ID", $format1);

$worksheet->write(0, 17, "EXTERNAL_CNTRPTY_ID", $format1);

$worksheet->write(0, 18, "REGION_CNTRPTY_ID", $format1);

$worksheet->write(0, 19, "BA_CNTRPTY_ID", $format1);

$worksheet->write(0, 20, "AQUISITION_DTE", $format1);

$worksheet->write(0, 21, "AQUISITION_DTE_LPT_ID", $format1);

$worksheet->write(0, 22, "DUE_DTE", $format1);

$worksheet->write(0, 23, "DUE_DTE_LPT_ID", $format1);

$worksheet->write(0, 24, "CLIENT_ID", $format1);

$worksheet->write(0, 25, "INTERCPNY_CNTRPTY_ID", $format1);

$worksheet->write(0, 26, "DEBT_TENURE_NME", $format1);

$worksheet->write(0, 27, "CCY", $format1);

$worksheet->write(0, 28, "TRANSLATED_CCY", $format1);

$worksheet->write(0, 29, "UOM_CCY", $format1);

$worksheet->write(0, 30, "AUDIT_ACCT_NUM", $format1);

$worksheet->write(0, 31, "QTP_KEY_ID", $format1);

$worksheet->write(0, 32, "AUDIT_CPNY_CDE", $format1);

$worksheet->write(0, 33, "AUDIT_COST_CNTR_NME", $format1);

$worksheet->write(0, 34, "AUDIT_JRNL_XML", $format1);

$worksheet->write(0, 35, "AUDIT_ACCOUNT_RULE", $format1);

$worksheet->write(0, 36, "AUDIT_PRODUCT_RULE", $format1);

$worksheet->write(0, 37, "AUDIT_SOURCE", $format1);

$worksheet->write(0, 38, "AUDIT_TD_SD_PEND", $format1);

$worksheet->write(0, 39, "AUDIT_FUNDING_RULE", $format1);

$worksheet->write(0, 40, "CLEARING_HOUSE_CDE", $format1);

$worksheet->write(0, 41, "SEC_POOL_ID", $format1);

$worksheet->write(0, 42, "TICKET_REF_NO", $format1);

$worksheet->write(0, 43, "MASTER_TICKET_REF_NO", $format1);

$worksheet->write(0, 44, "LEG_TYPE_DSC", $format1);

$worksheet->write(0, 45, "NETTING_GRP_ID", $format1);

$worksheet->write(0, 46, "COLLATERAL_GRP_ID", $format1);

$worksheet->write(0, 47, "PROD_GRP_DSC", $format1);

$worksheet->write(0, 48, "HEADER_ID", $format1);











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

if ($col == 30) {      

$worksheet->write_string($row,30,$cell->value);

}

else {$worksheet->write($row,$col,$cell->value);}



















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

   }







    $workbook->close();

    system("/ms/dist/perl5/bin/perl5.10", "/ms/user/s/sayantag/Val_Code.pl", $xls); 

}

Open in new window

Int-Res12-3-28.xls
Int-Res12-3-30.xls
Int-Res12-3-32.xls
0
 

Author Comment

by:sunny82
ID: 33589313
I think if I can somehow manage to write every row of original excel in the second row of intermediate excel, it will be solved.

Beacuse if I write $row = 1 at lines 119 and 121 of the code at ID: 33588987, only 1 row is getting processed by running the code at ID: 33587714, only the first intermediate excel is getting processed, but it is getting processed correctly.

What do you think I should do  to write every row of original excel in the second row of intermediate excel of the code at ID: 33588987?
0
 

Author Comment

by:sunny82
ID: 33589854
I solved the problem.

Changing the following did the trick --


if ($col == 30) {
$worksheet->write_string(1,30,$cell->value);
}
else {

$worksheet->write(1,$col,$cell->value);}

at lines 119 - 122 for code at ID: 33588987

I am getting the output now as expected with all the formatting and coloring perfect.

Thank you soooo much. Really appreciate all your efforts immensely.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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.

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