in reply to CONVERTING CSV TO XLS

Okay i re did the code can any monk please help me in extracting the array contents in such a way that each sheet will have data of the text files in rows like 0-10(1 worksheet),11-20(2nd worksheet)..... currently my code is giving an output of 0-10 rows of the text file in all the worksheets :S because of an incorrect method of data extraction from the array 
 
#!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;


#count the number of records
use strict;
open(IN, "t.txt");
my @str = <IN>;
close(IN);
print scalar(@str);
#end counting the no.of records

open (TABFILE, 't.txt') or die "t.txt: $!";

my $worksheetno=(int(@str/10))+1;
print "\n$worksheetno\n";

    my $workbook  = Spreadsheet::WriteExcel->new('tabnew.xls');


    my $row = 0;
    my $rowcount=0;


     while($worksheetno>0)
      {
      my $worksheet = $workbook->add_worksheet();
       while (<TABFILE>) {

      if ($row>9)
      {last;}


       chomp;
        my @Fld = split(',');
        print "@Fld\n";

        my $col = 0;
        foreach my $token (@Fld) {
            $worksheet->write($row, $col, $token);
            $col++;

        }
        $row++; }

        open (TABFILE, 't.txt') or die "t.txt: $!";
        $row=0;

      $worksheetno--;
    }

[download]

Replies are listed 'Best First'.
Re^2: CONVERTING CSV TO XLS
by poj (Abbot) on May 26, 2014 at 12:26 UTC

    The code isn't working because you re-open the TABFILE at every new sheet. Another problem is that exiting the while loop with last discards a line of data just read in from TABFILE.

    Try this;

    #!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $MAX_ROW = 9;

my $workbook  = Spreadsheet::WriteExcel->new('tabnew.xls');
my $worksheet = $workbook->add_worksheet();
my $row = 0;
my $sheetno = 1;
print "Sheet $sheetno\n";

open (TABFILE, 't.txt') or die "t.txt: $!";
while (<TABFILE>) {
  if ($row > $MAX_ROW){
    $worksheet = $workbook->add_worksheet();
    ++$sheetno;
    $row = 0;
    print "Sheet $sheetno\n";
  }

  chomp;
  my @Fld = split(',');
  print "Row $row = @Fld\n";
  $worksheet->write_row($row, 0, \@Fld);
  $row++; 
}

    [download]
    poj
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom