如何使用Perl向Excel添加多维数组（AoA）？

I want to add my data stored in 2x2 dimension array in Excel using Perl. I know how to open and add simple data. This I can do using for loop. But how can I do it elegantly?

我想使用Perl在Excel中添加存储在2x2维数组中的数据。我知道如何打开和添加简单数据。我可以使用for循环。但我怎么能优雅地做到这一点？

This is what I am trying to do

这就是我想要做的

$sheet->Range("A1:B"."$size")->{Value} = @$data;
                                         or   @data;
                                         or   {@data};
                                         or   {\@data};

where @data is two dimensional array.

其中@data是二维数组。

# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
    $ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
            or die "Oops, cannot start Excel";
}


# get a new workbook
$book = $ex->Workbooks->Add;

# write to a particular cell
$sheet = $book->Worksheets(1);
print "A1:B"."$size";
# write a 2 rows by 3 columns range

$sheet->Range("A1:B"."$size")->{Value} = @$data;

2 个解决方案

#1

I see that you are using Win32::OLE but this sort of thing is also quite easy with Spreadsheet::WriteExcel:

我看到你使用的是Win32 :: OLE，但是对于Spreadsheet :: WriteExcel来说这种事情也很简单：

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

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

# Get your AoA from somewhere.
my $data = [
    [ 'Hello', 'world', 123   ],
    [ 'Bye',   'bye',   4.567 ],
];

# Write the data.
$worksheet->write_col( 'A1', $data );

#2

Based on some reading (I don't have a Win32 box in front of me), it looks like the Value attribute of Range handles a reference to an AoA correctly, so try saying:

根据一些阅读（我前面没有Win32框），看起来Range的Value属性正确处理对AoA的引用，所以试着说：

my $data = [
    ["a1", "b1"],
    ["a2", "b2"],
];
$sheet->Range("A1:B" . @$data)->{Value} = $data;

#1