How do I move rows from one sheet to another?

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

Select allOpen in new window

Pls note that this is in relation to an earlier question

https://www.experts-exchange.com/questions/26435497/How-do-I-copy-one-row-at-atime-from-1-excel-to-another.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..

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

Select allOpen in new window

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

Select allOpen in new window

Temp-Res4.xls

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;

Select allOpen in new window

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;

Select allOpen in new window

Final-Res.xls

Why do you think this is happening, the data part is getting stripped off and only the headers remain?

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;

Select allOpen in new window

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..  

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.

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;

Select allOpen in new window

Final-Res.xls
Final-Res.xls

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;

Select allOpen in new window

Final-Res.xls
Final-Res.xls

Any solutions for this? Pls let me know...

I have another question at
https://www.experts-exchange.com/questions/26445308/How-do-I-append-excel-sheets-into-a-single-excel-file-by-grepping-on-file-creation-time.html

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

Pls help me...

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.

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

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;

Select allOpen in new window

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;

Select allOpen in new window

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;

Select allOpen in new window

Final-Res.xls

Sorry about the attachment, here is the excel
Final-Res.xls

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.

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;}
        }
       }
 
  
      }

Select allOpen in new window

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..

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;

Select allOpen in new window

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

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;

Select allOpen in new window

Final-Res.xls

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.

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;

Select allOpen in new window

Final-Res.xls

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;

Select allOpen in new window

Final-Res.xls

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?

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.

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

Select allOpen in new window

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

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?

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.