1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
| #!/usr/local/bin/perl
use strict;
use warnings;
use Win32::OLE;
# Start Excel and make it visible
my $xlApp = Win32::OLE->new('Excel.Application');
$xlApp->{Visible} = 1;
# Create a new workbook
my $xlBook = $xlApp->Workbooks->Add;
# Our data that we will add to the workbook...
my $mydata = [["Item", "Category", "Price"],
["Nails", "Hardware", "5.25"],
["Shirt", "Clothing", "23.00"],
["Hammer", "Hardware", "16.25"],
["Sandwich", "Food", "5.00"],
["Pants", "Clothing", "31.00"],
["Drinks", "Food", "2.25"]];
# Write all the data at once...
my $rng = $xlBook->ActiveSheet->Range("A1:C7");
$rng->{Value} = $mydata;
# Create a PivotTable for the data...
my $tbl = $xlBook->ActiveSheet->PivotTableWizard(1, $rng, "", "MyPivotTable");
# Set pivot fields...
$tbl->AddFields("Price", "Item");
$tbl->PivotFields("Price")->{Orientation} = 4; # 4=xlDataField
my $Chart = $xlBook->Charts->add;
$Chart->{ChartType} = 'xlLine';
$Chart->SetSourceData({Source => $rng, PlotBy => 'xlColumns'});
$Chart->{HasTitle} = 1;
$Chart->ChartTitle->{Text} = "Items delivered, en route and to be shipped";
# Clean up
$xlBook->SaveAs('test.xls');
$xlApp->{Visible} = 0;
$xlApp->Quit;
$xlBook = 0;
$xlApp = 0;
print "All done."; |
