Convert csv file into Multi tab xls (excel) file using perl

Question

I am using perl module WriteExcel to convert a | delimited text file into xls file, I am using below code to do so

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

# Create a new workbook and add a worksheet
my $workbook  = Spreadsheet::WriteExcel->new($filename);
my $worksheet = $workbook->add_worksheet("Colorful Example");

open(FH,"<$my_path/source_file.txt")
   or die "Cannot open file: $!\n";
my ($x,$y) = (0,0);
while (<FH>){
   chomp;
   my @list = split /\t/,$_;
   foreach my $c (@list){
      $worksheet->write($x, $y++, $c);
   }
   $x++; $y=0;
}
close(FH);
$workbook->close();     # Close Workbook

By this code i can convert the file into single tab excel. I am wondering how can i convert the text file into multi tab xls (excel file having multiple worksheets) file when number of rows exceed 65000.

yes by using same package.

Vicky K
– Vicky K

2014-12-28 09:04:08 +00:00
Commented Dec 28, 2014 at 9:04 — Vicky K
– Vicky K, Commented Dec 28, 2014 at 9:04

simbabque · Accepted Answer · 2014-12-27 19:07:14Z

1

You are adding your worksheet with this line.

my $worksheet = $workbook->add_worksheet("Colorful Example");

What you need to do is check in your loop where you process lines if you have exceeded the line limit, and if you did, replace the worksheet handle.

$worksheet = $workbook->add_worksheet('foo') if $rows > 65_000;

answered Dec 27, 2014 at 19:07

simbabque

54.4k8 gold badges77 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vicky K Over a year ago

in your code $rows is getting calculated from where??

simbabque Over a year ago

That would be your $x I guess. Remember to always chose good descriptive variable names so you don't get confused.

Collectives™ on Stack Overflow

Convert csv file into Multi tab xls (excel) file using perl

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related