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

In reply to Re^6: XLS to CSV file with Text wrap by kshitij
in thread XLS to CSV file with Text wrap by kshitij

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.