Perl script to fill the entire row of Excel file with color based on pattern match

Shell Programming and Scripting
1

Hi All ,

I have to write one Perl script in which I need to read one pre-existing xls and based on pattern match for one word in some cells of the XLS , I need to fill the entire row with one color of that matched cell and write the content to another excel

Please find the below stated script which I have written , Unfortunately it is just coloring the particular cell which is being matched but not the entire row.
I am not sure how to extract the rows of the excel based on the pattern match and then fill the color of the designated rows and write to another excel.

#!/usr/bin/perl -w
 
use strict;
use Spreadsheet::ParseExcel;
 use Spreadsheet::WriteExcel;

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('status.xls');
 
if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}
 
my $workbook1 = Spreadsheet::WriteExcel->new('Report.xls');
# Add a worksheet
my $worksheet1 = $workbook1->add_worksheet();
#  Add and define a format
my $format = $workbook1->add_format(bg_color => 'green');

for my $worksheet ( $workbook->worksheets() ) {
 
    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();
 
    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
 
            my $cell = $worksheet->get_cell( $row, $col );
            next unless $cell;

    my $string = $cell->value();
      if ($string  =~ m/Completed/i)    { 

$worksheet1->write($row, $col, $cell_value,$format);

   }
else {

 $worksheet1->write($row, $col, $cell->value());
}
}
                 }
    }

This will write the content to another Excel but only the cells having "Completed" word is colored green but not the entire row having the cell "Completed".
Could you help me out in completing this script ?

Thanks and Regards
Kshitij Kulshreshtha

2

Can anybody help me our by replying to this query ?

3

As per the documentation of this module at: https://metacpan.org/pod/Spreadsheet::WriteExcel\#set_row\($row,-$height,-$format,-$hidden,-$level,-$collapsed\)
I guess you could probably do something like this:

...
            my $string = $cell->value();
            if ($string  =~ m/Completed/i) {
                $worksheet1->set_row($row, undef, $format);
                $worksheet1->write($row, $col, $cell->value(), $format);
            } else {
                $worksheet1->write($row, $col, $cell->value());
            }
...