Perl How to combine two or more excel files in one (multiple sheets)?

Question

I need to merge several excel files into one, several sheets. I don't care about the sheet name in the new file.

I do not have Excel on the computer that I plan to run. therefore I can not use Win32 OLE. I tried to run this code https://sites.google.com/site/mergingxlsfiles/ but it does not work, I get a new empty excel file.

I am trying to run http://www.perlmonks.org/?node_id=743574 , but I only got one of the files in the new excel file.

In my excel files for input there are some french characters (like for example). I believe this is cp1252.

Used code:

#!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; use Spreadsheet::WriteExcel; use File::Glob qw(bsd_glob); use Getopt::Long; use POSIX qw(strftime); GetOptions( 'output|o=s' => \my $outfile, 'strftime|t' => \my $do_strftime, ) or die; if ($do_strftime) { $outfile = strftime $outfile, localtime; }; my $output = Spreadsheet::WriteExcel->new($outfile) or die "Couldn't create '$outfile': $!"; for (@ARGV) { my ($filename,$sheetname,$targetname); my @files; if (m!^(.*\.xls):(.*?)(?::([\w ]+))$!) { ($filename,$sheetname,$targetname) = ($1,qr($2),$3); warn $filename; if ($do_strftime) { $filename = strftime $filename, localtime; }; @files = glob $filename; } else { ($filename,$sheetname,$targetname) = ($_,qr(.*),undef); if ($do_strftime) { $filename = strftime $filename, localtime; }; push @files, glob $filename; }; for my $f (@files) { my $excel = Spreadsheet::ParseExcel::Workbook->Parse($f); foreach my $sheet (@{$excel->{Worksheet}}) { if ($sheet->{Name} !~ /$sheetname/) { warn "Skipping '" . $sheet->{Name} . "' (/$sheetname/)"; next; }; $targetname ||= $sheet->{Name}; #warn sprintf "Copying %s to %s\n", $sheet->{Name}, $targetname; my $s = $output->add_worksheet($targetname); $sheet->{MaxRow} ||= $sheet->{MinRow}; foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) { my @rowdata = map { $sheet->{Cells}->[$row]->[$_]->{Val}; } $sheet->{MinCol} .. $sheet->{MaxCol}; $s->write($row,0,\@rowdata); } } }; }; $output->close; 

I have 2 excel files with the name: 2.xls (only 1 sheet with the name 2 in it), 3.xls (only 1 sheet with the name 3)

I ran the script as follows:

 xlsmerge.pl -s -o results-%Y%m%d.xls 2.xls:2 3.xls:3 

Results: results-20121024.xls nothing is empty.

Then i tried

 xlsmerge.pl -s -o results-%Y%m%d.xls 2.xls 3.xls 

And it worked. I'm not sure why this happens when adding Sheetname

Answer 1

There seems to be an error in this line of script:

 if (m!^(.*\.xls):(.*?)(?::([\w ]+))$!) { ($filename,$sheetname,$targetname) = ($1,qr($2),$3); ... 

It seems to me that the purpose of this line is to resolve the arguments either in the form

 spreadsheet.xls:source_worksheet 

or in another form that allows you to specify the name of the target sheet:

 spreadsheet.xls:source_worksheet:target_worksheet 

The last grouping is designed to capture the last optional argument: (?::([\w ]+)) . The only problem is that this grouping has not become optional. Thus, when you specify only the source sheet and not the target, the regular expression cannot match, and it refers to the backup behavior, which should treat the entire argument as a file name. But this also fails because you do not have a file named 2.xls:2 .

The solution is to enter a modifier ? after the last group in a regular expression to make it optional:

 if (m!^(.*\.xls):(.*?)(?::([\w ]+))?$!) { ($filename,$sheetname,$targetname) = ($1,qr($2),$3); ... 

Of course, this may not be the only problem. If the script was sent with an error, there may be other errors. I do not have Perl to test at the moment.