如何在Perl或批处理中合并两个Excel（xls）文件？

I have two files lets say a.xls and b.xls. The first one contains 2 sheets and the second one 3 of them. Can someone let me know if I can merge them using Perl or batch scripting?

我有两个文件可以说a.xls和b.xls。第一个包含2张，第二个包含3张。如果我可以使用Perl或批处理脚本合并它们，有人可以告诉我吗？

I want an output with 5 sheets in one single XLS file.

我想在一个XLS文件中输出5张纸。

3 个解决方案

#1

For a Perl solution use Spreadsheet::ParseExcel to read the files and Spreadsheet::WriteExcel to save your output. Both modules are well documented and come with lots of example code(e.g. WriteExcel)

对于Perl解决方案，使用Spreadsheet :: ParseExcel读取文件和Spreadsheet :: WriteExcel以保存输出。这两个模块都有详细记录，并附带了大量示例代码（例如WriteExcel）

#2

If you are running on Windows, have Excel installed and can use Win32::OLE (e.g. the script will not be invoked by a web server etc), the following should work:

如果您在Windows上运行，安装了Excel并且可以使用Win32 :: OLE（例如，Web服务器不会调用该脚本等），则以下内容应该有效：

#!/usr/bin/perl

use warnings;
use strict;

use FindBin qw($Bin);
use File::Spec::Functions qw( catfile );

use Win32::OLE qw(in);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;

my $excel = get_excel();
$excel->{Visible} = 1; # for illustration only

my @src = map $excel->Workbooks->Open($_),
          map catfile($Bin, $_),
          qw(one.xls two.xls)
          ;

my $target = $excel->Workbooks->Add(xlWBATWorksheet);
my $before = $target->Worksheets->Item(1);

for my $book ( @src ) {
    my $sheets = $book->Worksheets;
    my $it = Win32::OLE::Enum->new($sheets);

    while (defined(my $sheet = $it->Next)) {
        $sheet->Copy($before);
    }
}

$before->Delete;

$_->Close for @src;
$target->SaveAs(catfile($Bin, 'test.xls'));
$target->Close;

sub get_excel {
    my $excel = Win32::OLE->GetActiveObject('Excel.Application');
    unless(defined $excel) {
        $excel = Win32::OLE->new('Excel.Application', sub { $_[0]->Quit })
            or die "Oops, cannot start Excel: ",
                   Win32::OLE->LastError, "\n";
    }
    return $excel;
}

#3

You can do it with Alacon - command-line utility for Alasql database.

您可以使用Alacon - Alasql数据库的命令行实用程序来完成此操作。

It works with Node.js, so you need to install Node.js and then Alasql package:

它适用于Node.js，因此您需要安装Node.js然后安装Alasql包：

To take data from Excel file you can use the following command:

要从Excel文件中获取数据，可以使用以下命令：

> node alacon "SELECT * INTO XLSX("main.xls",{headers:true}) 
               FROM XLSX('data1.xlsx', {headers:true}) 
               UNION ALL SELECT * FROM XLSX('data2.xlsx', {headers:true})
               UNION ALL SELECT * FROM XLSX('data3.xlsx', {headers:true})
               UNION ALL SELECT * FROM XLSX('data4.xlsx', {headers:true})
               UNION ALL SELECT * FROM XLSX('data5.xlsx', {headers:true})"

This is one very long line. In this example all files have data in "Sheet1" sheets.

这是一个很长的路线。在此示例中，所有文件都包含“Sheet1”表中的数据。

#1