Perl如何在一个(多个工作表)中合并两个或多个excel文件?

I need to merge a few excel file into one, multiple sheets. I do not care too much about the sheet name on the new file.

我需要将一些excel文件合并到一个多张表中。我不太关心新文件上的表名。

I do not have Excel on the computer I plan to run this. so I cannot use Win32 OLE. I attempted to run this code https://sites.google.com/site/mergingxlsfiles/ but it is not working, I get a new empty excel file.

我在电脑上没有Excel我计划运行这个。所以我不能用Win32 OLE。我尝试运行这个代码https://sites.google.com/site/mergingxlsfiles/，但是它不工作，我得到一个新的空excel文件。

I attempt to run http://www.perlmonks.org/?node_id=743574 but I only obtained one of the file in the new excel file.

我试图运行http://www.perlmonks.org/?node_id=743574，但我在新的excel文件中只获取了一个文件。

My input excel files have some french characters (é for e.g.) I believe these are cp1252.

我的输入excel文件有一些法语字符(例如e)我想这些是cp1252。

Code used :

代码使用:

    #!/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 named: 2.xls (only 1 sheet named 2 in it), 3.xls (only 1 sheet named 3)

我有2个excel文件。xls(其中只有一个名为2的表)，3。xls(仅1页命名为3)

I launched the script as this:

我把剧本写成这样:

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

Results: results-20121024.xls empty nothing in it.

结果:结果——20121024。xls没有空任何东西。

Then I tried

然后我试着

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

And it worked. I am not sure why is it failing while adding the Sheetname

它工作。我不知道为什么在添加Sheetname时失败了

1 个解决方案

#1

It appears that there is a bug in this line of the script:

似乎在这一行脚本中有一个错误:

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

It looks to me like the goal of that line is to allow arguments either in the form

在我看来，这一行的目标是允许形式的参数

spreadsheet.xls:source_worksheet

or in another form allowing the name of the target sheet to be specified:

或以另一种可指明目标纸张名称的表格填写:

spreadsheet.xls:source_worksheet:target_worksheet

The last grouping appears intended to capture that last, optional argument: (?::([\w ]+)). The only problem is, this grouping was not made optional. Thus, when you only specify the source sheet and not the target, the regex fails to match and it falls to the backup behavior, which is to treat the whole argument as the filename. But this fails, too, because you don't have a file called 2.xls:2.

最后一个分组似乎是为了捕获最后一个可选参数:(?)::((\ w)+))。唯一的问题是，这个分组不是可选的。因此，当您只指定源表而不是目标时，regex将无法匹配，而它将属于备份行为，即将整个参数视为文件名。但这也失败了，因为您没有一个名为2.xls:2的文件。

The solution would be to introduce the ? modifier after the last group in the regex to make it optional:

解决方案是引入?在regex中的最后一个组之后修改，使其可选:

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

Of course, that may not be the only problem. If the script was posted with an error, there could be other errors, too. I don't have Perl available to test it at the moment.

当然，这可能不是唯一的问题。如果脚本被发布时出现了错误，也可能有其他错误。我现在没有Perl可用来测试它。

#1