kshitij has asked for the wisdom of the Perl Monks concerning the following question:

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

[download]

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
  • Comment on Perl script to fill the entire row of Excel file with color based on pattern match
  • Download Code

Replies are listed 'Best First'.
Re: Perl script to fill the entire row of Excel file with color based on pattern match
by poj (Abbot) on Aug 29, 2018 at 06:46 UTC

    Read a complete row and store the values in an array. Then write a complete row.

    #!/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');
my $format = $workbook1->add_format(bg_color => 'green');

for my $worksheet ( $workbook->worksheets() ) {
  my $worksheet1 = $workbook1->add_worksheet();
  my ( $row_min, $row_max ) = $worksheet->row_range();
  my ( $col_min, $col_max ) = $worksheet->col_range();
  
  for my $row ( $row_min .. $row_max ) {
    my @value = ();
    my $fmt; # format for complete row
    # store row in array
    for my $col ( $col_min .. $col_max ) {
      my $cell = $worksheet->get_cell( $row, $col );
      if (defined $cell) {
        $value[$col] = $cell->value() ;
        $fmt = $format if ($value[$col]  =~ m/Completed/i)
      }
    }
    # write row
    for my $col ( $col_min .. $col_max ) {
      $worksheet1->write($row, $col, $value[$col],$fmt);
    }
  }
}

    [download]
    poj
[reply]
[d/l]
      Hi Poj

      Thanks a lot ! I could able to see the rows getting filled with green but I am having another requirement also , if the row matches "In Progress" , it should fill that form with Orange color. I have written below code but it is not filling the entire row which is matching with "In Progress" and only half of the row is getting filled. 

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

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Homestead_status.xls');
 
if ( !defined $workbook ) {
  die $parser->error(), ".\n";
}
 
my $workbook1 = Spreadsheet::WriteExcel->new('Report.xls');
my $format1 = $workbook1->add_format(bg_color => 'green');
my $format2 = $workbook1->add_format(bg_color => 'orange');

for my $worksheet ( $workbook->worksheets() ) {
  my $worksheet1 = $workbook1->add_worksheet();
  my ( $row_min, $row_max ) = $worksheet->row_range();
  my ( $col_min, $col_max ) = $worksheet->col_range();
  
  for my $row ( $row_min .. $row_max ) {
    my @value1 = ();
    my @value2 = ();
    my $fmt1; # format for complete row
    my $fmt2; # format for complete row
    # store row in array
    for my $col ( $col_min .. $col_max ) {
      my $cell = $worksheet->get_cell( $row, $col );
      if (defined $cell) {
        $value1[$col] = $cell->value() ;
        $value2[$col] = $cell->value() ;
        $fmt1 = $format1 if ($value1[$col]  =~ m/Completed/i);
        $fmt2 = $format2 if ($value2[$col]  =~ m/In Progress/i)
      }
    }
    # write row
    for my $col ( $col_min .. $col_max ) {
      $worksheet1->write($row, $col, $value1[$col],$fmt1);
      $worksheet1->write($row, $col, $value2[$col],$fmt2);
    }
  }
}

      [download]
[reply]
[d/l]

        No need for 2 arrays, just use an if-elsif block for the 2 conditions

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

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Homestead_status.xls');
 
if ( !defined $workbook ) {
  die $parser->error(), ".\n";
}
 
my $workbook1 = Spreadsheet::WriteExcel->new('Report.xls');
my $format1 = $workbook1->add_format(bg_color => 'green');
my $format2 = $workbook1->add_format(bg_color => 'orange');

for my $worksheet ( $workbook->worksheets() ) {
  my $worksheet1 = $workbook1->add_worksheet();
  my ( $row_min, $row_max ) = $worksheet->row_range();
  my ( $col_min, $col_max ) = $worksheet->col_range();
  
  for my $row ( $row_min .. $row_max ) {
    my @value = ();
    my $fmt; # format for complete row
    # store row in array
    for my $col ( $col_min .. $col_max ) {
      my $cell = $worksheet->get_cell( $row, $col );
      if (defined $cell) {
        $value[$col] = $cell->value() ;
        if ($value[$col] =~ m/Completed/i){
          $fmt = $format1 
        } elsif ($value[$col] =~ m/In Progress/i){
          $fmt = $format2
        }
      }
    }
    # write row
    for my $col ( $col_min .. $col_max ) {
      $worksheet1->write($row, $col, $value[$col],$fmt);
    }
  }
}

        [download]
        poj
[reply]
[d/l]
[select]
Re: Perl script to fill the entire row of Excel file with color based on pattern match
by bliako (Abbot) on Aug 29, 2018 at 11:33 UTC

    Dear Kshitij, just out of curiousity, if you are on windows and have excel package installed why do you not use vbscript instead Or if you were in windows would you rather use vbscript or Perl?

    I am not suggesting that you should (Edit: i.e. use vbscript)!! but I am curious to see if people feel better or more comfortable using Perl than native tools. Re: The future of Perl.

[reply]
      I am using Linux and using the perl script there. I have not used vbscript but I can give a try
[reply]

        no need, Perl is much better than vbscript.

[reply]

Back to Seekers of Perl Wisdom