Hallo zusammen!
Kann man - und wenn ja wie - mit Perl eine Excel-Datei erstellen und/oder bearbeiten?
Gruß
Chris
http://aspn.activestate.com/ASPN/docs/ActivePerl/faq…
How do I extract a series of cells from Microsoft Excel?
If you have a sheet object you can extract the values of a series of cells through $Sheet->Range->{‚Value‘}, for example:
my $array = $Sheet-\>Range("A8:B9")-\>{'Value'};
Now $array[0][0] contains the value of cell A8, $array[0][1] the value of cell B8, $array[1][0] the value of cell A9 and $array[1][1] the value of cell B9.
What is returned is an two-dimensional array (OK, an array with references to arrays) that contains the values of the requested cells.
A complete example is here:
use strict;
use Win32::open\_mouth:LE qw(in with);
use Win32::open\_mouth:LE::Const 'Microsoft Excel';
$Win32::open\_mouth:LE::Warn = 3; # die on errors...
my $Excel = Win32::open\_mouth:LE-\>GetActiveObject('Excel.Application')
|| Win32::open\_mouth:LE-\>new('Excel.Application', 'Quit'); # get already active Excel
# application or open new
my $Book = $Excel-\>Workbooks-\>Open("C:\\DOCUMENTS\\test.xls"); # open Excel file
my $Sheet = $Book-\>Worksheets(1); # select worksheet number 1
my $array = $Sheet-\>Range("A8:B9")-\>{'Value'}; # get the contents
$Book-\>Close;
foreach my $ref\_array (@$array) { # loop through the array
# referenced by $array
foreach my $scalar (@$ref\_array) {
print "$scalar\t";
}
print "\n";
}
To retrieve the formatted value of a cell you should use the {‚Text‘} property instead of the {‚Value‘} property. This returns exactly what is being displayed on the screen though! If the column is not wide enough, you get a value of ‚######‘:
my $array = $Sheet-\>Range("A8:B9")-\>{'Text'};
How do I make a chart in Microsoft Excel?
A good idea would be to record a macro in Microsoft Excel and then convert it to Perl. But here is a complete example:
use strict;
use Win32::open\_mouth:LE;
use Win32::open\_mouth:LE::Const 'Microsoft Excel';
my $Excel = Win32::open\_mouth:LE-\>new("Excel.Application");
$Excel-\>{Visible} = 1;
my $Book = $Excel-\>Workbooks-\>Add;
my $Sheet = $Book-\>Worksheets(1);
my $Range = $Sheet-\>Range("A2:C7");
$Range-\>{Value} =
[['Delivered', 'En route', 'To be shipped'],
[504, 102, 86],
[670, 150, 174],
[891, 261, 201],
[1274, 471, 321],
[1563, 536, 241]];
my $Chart = $Excel-\>Charts-\>Add;
$Chart-\>{ChartType} = xlAreaStacked;
$Chart-\>SetSourceData({Source =\> $Range, PlotBy =\> xlColumns});
$Chart-\>{HasTitle} = 1;
$Chart-\>ChartTitle-\>{Text} = "Items delivered, en route and to be shipped";
Gruß
Christian