Match on cell value not $cell object

if (defined $cell) {
   my $value = $cell->value(); ## add
 # if ( $cell =~ /DFT Tasks for Ration/ .. /END OF RATION/) { 
 #     $value[$col] = $cell->value() ;
   if ( $value =~ /DFT Tasks for Ration/ .. $value =~ /END OF RATION/)
+ { 
       $value[$col] = $value;
       if ($value[$col] =~ m/Completed/i){
         $fmt = $format[1]
       }
[download]

Hi Poj

Thanks for sharing the code . I tested your code , it is working and I have tried below stated coded , this is also working . Both codes are having only one problem , the generated XLS is still having problems with the preformatted text wrap which was there in the original xls . The Text wrap which was there in the original XLS is not coming in the generated XLS and the entire content which supposed to be there with newlines for one cell is coming in a single line in the generated XLS . I am not sure why the text wrap is not happening . But there is one interesting thing which I have noticed which I will explain later in this report.

The script which I have written and is working . Though this script gives some error but it is generating same XLS which it is being generated with your script.

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

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Project_status_tracking_DFT.xls');
 
if ( !defined $workbook ) {
  die $parser->error(), ".\n";
}
 
my $workbook1 = Spreadsheet::WriteExcel->new('Ration_Status_ksh.xls');
my @color = qw(white green orange pink blue magenta silver);
my @format;
for my $i (1..@color){
 $format[$i] = $workbook1->add_format(bg_color => $color[$i]);
 $format[$i]->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();
  
  # set minimum column width
  my @width;
  $width[$_] = 10 for ($col_min .. $col_max);
  
  for my $row ( $row_min .. $row_max ) {
    my @value = ();
    my @value_1 = ();
    my @value_2 = ();

    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_1 = $cell->value();
        foreach my $ele (@value_1) {
        push @value_2, $ele if ($ele =~ /DFT Tasks for Ration/ .. $ele
+ =~ /END OF RATION/);
         #push @value_2, @value_1 if /DFT Tasks for Homestead/;
       #print @value_2;  
       if ($value_2[$col] =~ m/Completed/i){
          $fmt = $format[1]
        }         
        # determine max width for each column
        my $len = length($value_2[$col]); 
        $width[$col] = $len if $len > $width[$col];  
     }

    }
}
    
    # write row
    for my $col ( $col_min .. $col_max ) {
    
      $worksheet1->write($row, $col, $value_2[$col],$fmt);
    }
  }
  # set column widths
  for my $col ( $col_min .. $col_max ) {
    $worksheet1->set_column($col,$col,$width[$col]) 
  }
}
[download]

Now one thing I have observed , If I match only "DFT Tasks for Ration" and "END OF RATION" with something like below stated code, the generated XLS is preserving the text wrap also it is starting the generated XLS from "DFT Tasks for Ration" till end of File and it is not ending it at "END OF RATION" line.

For example , I am changing the below code of the above stated script

Original

 push @value_2, $ele if ($ele =~ /DFT Tasks for Ration/ .. $ele =~ /EN
+D OF RATION/);
[download]

Modified

 push @value_2, $ele if ($ele =~ /DFT Tasks for Ration/ ..  /END OF RA
+TION/);
[download]

I am not sure with the above stated modification , why the generated XLS is preserving the text wrap from the original XLS.

Not sure what is the mystery here . Could you provide your inputs ?

Thanks Kshitij

I am not sure why the text wrap is not happening

Probably because you are setting the column widths to the maximum length of the string. Also you are only applying a text wrap format to rows that match 'completed'. You need a default text wrap for the other rows. It looks like you want to create the new spreadsheet with the same column widths as the original. Try this

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

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Project_status_tracking_DFT.xls');
 
if ( !defined $workbook ) {
  die $parser->error(), ".\n";
}
 
my $workbook1 = Spreadsheet::WriteExcel->new('Ration_Status_ksh.xls');
my @color = qw(white green orange pink blue magenta silver);
my @format;
$format[0] = $workbook1->add_format();
$format[0]->set_text_wrap();
for my $i (1..@color){
  $format[$i] = $workbook1->add_format(bg_color => $color[$i]);
  $format[$i]->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();
  my $col_widths  = $worksheet->get_col_widths();
  my $flag = 0;
  
  for my $row ( $row_min .. $row_max ) {
    my $fmt = $format[0]; # default format for complete row
    # store row in array
    my @value = ();
  
    for my $col ( $col_min .. $col_max ) {
    
      # set column width same as source
      $worksheet1->set_column($col, $col, $col_widths->[$col]) if ($ro
+w == $row_min);

      my $cell = $worksheet->get_cell( $row, $col );
      if (defined $cell) {
        my $value = $cell->value();
        $value[$col] = $value;
        if ($value =~ /DFT Tasks for Ration/){
          $flag = 1;
        } elsif ($value =~ /END OF RATION/){
          $flag = 2;
        };

        if ($value =~ m/Completed/i){
          $fmt = $format[1]
        }   
      }
    }
    
    # write row
    if ($flag){    
      for my $col ( $col_min .. $col_max ) { 
        $worksheet1->write($row, $col, $value[$col],$fmt);
      }
      $flag = 0 if $flag == 2;
    }
  }
}
[download]

poj