Hi Poj , Thanks a lot for your help ! Only one problem I am facing in the generated XLS , the lines in the generated XLS which separate different cells are not visible. There are no break lines from one cell to another. I am not sure which property I need to use for that Thanks and Regards Kshitij Kulshreshtha </poj>

Hi

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