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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.