in reply to Re^2: Perl script to fill the entire row of Excel file with color based on pattern match
in thread Perl script to fill the entire row of Excel file with color based on pattern match

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

Replies are listed 'Best First'.
Re^4: Perl script to fill the entire row of Excel file with color based on pattern match
by kshitij (Sexton) on Aug 30, 2018 at 08:36 UTC
    Hi Poj

    Thanks a lot for your help Please find my complete script . I have used set_text_wrap() property as well to wrap the content in the cells Now I am facing one problem the generated XLS file with the script is having an issue pertaining to the column widths It seems that I am looking for a mechansim to adjust column widths to match the length of the longest string in a column. Could you help me out in the same ? 

    
#!/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('_Status_Report.xls');
my $format1 = $workbook1->add_format(bg_color => 'green' );
$format1->set_text_wrap();
my $format2 = $workbook1->add_format(bg_color => 'orange');
$format2->set_text_wrap();
my $format3 = $workbook1->add_format(bg_color => 'pink');
$format3->set_text_wrap();
my $format4 = $workbook1->add_format(bg_color => 'blue' );
$format4->set_text_wrap();
my $format5 = $workbook1->add_format(bg_color => 'magenta');
$format5->set_text_wrap();
my $format6 = $workbook1->add_format(bg_color => 'silver');
$format6->set_text_wrap();


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
        } elsif ($value[$col] =~ m/Planning/i) {
          $fmt = $format3
       } elsif ($value[$col] =~ m/On Hold/i) {
          $fmt = $format4
       } elsif ( $value[$col] =~ m/END OF/) {
          $fmt = $format5
       } elsif ( $value[$col] =~ m/Current Status/i) {
          $fmt = $format6
       }
      }
    }
    # write row
    for my $col ( $col_min .. $col_max ) {
      $worksheet1->write($row, $col, $value[$col],$fmt);
    }
  }
}

    [download]
[reply]
[d/l]

      Build an array of the maximum width for each column

       my $len = length($value[$col]); 
 $width[$col] = $len if $len > $width[$col];

      [download]

      and set the column widths at the end of the worksheet loop

      for my $col ( $col_min .. $col_max ) {
  $worksheet1->set_column($col,$col,$width[$col]) 
}

      [download]
      Read more... (2 kB)
      poj
[reply]
[d/l]
[select]
        Thanks a lot poj ! It is working now . Kshitij
[reply]

In Section Seekers of Perl Wisdom