I think you are right I am looking for a pre formatted xls into CSV and I think CSV is not used for that. I am changing my strategy. Now I am thinking to split the original XLS into multiple small XLS based on the pattern match and then format these XLS files based on other pattern match.

To achieve this , I am trying below code. My intention is to split the big XLS file into small xls file based on the pattern match of the range of lines but while writing out the smaller xls based on above pattern match , I am getting empty xls.

I am trying to match range of lines between /DFT Tasks for Ration/ and /END OF RATION/ and write it into separate xls.

#!/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.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 $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) {
   if ( $cell =~ /DFT Tasks for Ration/ .. /END OF RATION/) { 
        $value[$col] = $cell->value() ;
        if ($value[$col] =~ m/Completed/i){
          $fmt = $format[1]
        }         
        # determine max width for each column
        my $len = length($value[$col]); 
        $width[$col] = $len if $len > $width[$col];  
      }
    }
}
    
    # write row
    for my $col ( $col_min .. $col_max ) {
    
      $worksheet1->write($row, $col, $value[$col],$fmt);
    }
  }
  # set column widths
  for my $col ( $col_min .. $col_max ) {
    $worksheet1->set_column($col,$col,$width[$col]) 
  }
}
[download]

Thanks Kshitij

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]

poj

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