There is one more issue in the below stated script. Though we are using set_text_wrap property to wrap the content of the cell but the problem is the generated XLS is having big string in the cells and it is not coming with the newlines "\n" . It is wrapping the string of one cell in one line but not splitting it in multiple lines. How can we add newline to the below piece of code so that wrapping should happen properly.

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

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('Brickyard_status.xls');
 
if ( !defined $workbook ) {
  die $parser->error(), ".\n";
}
 
my $workbook1 = Spreadsheet::WriteExcel->new('Brickyard_Status_Report.
+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) {
        $value[$col] = $cell->value() ;
        if ($value[$col] =~ m/Completed/i){
          $fmt = $format[1]
        } elsif ($value[$col] =~ m/In Progress/i){
          $fmt = $format[2]
        } elsif ($value[$col] =~ m/Planning/i) {
          $fmt = $format[3]
        } elsif ($value[$col] =~ m/On Hold/i) {
          $fmt = $format[4]
        } elsif ( $value[$col] =~ m/END OF/) {
          $fmt = $format[5]
        } elsif ( $value[$col] =~ m/Current Status/i) {
          $fmt = $format[6]
        }
        
        # 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

Can you provide an example of the contents of one cell which is not 'wrapping'. Is this the same problem as XLS to CSV file with Text wrap ?

poj

Hi Poj

I think the main problem is the conversion of the XLS into CSV file since it is not getting converted properly and missing the newlines in the CSV and not doing the Text wrap in the cells. I have tried earlier solutions which have been provided in the conversion from XLS into CSV with Text wrap post but they are not working.

Thanks Kshitij