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 51 52 53 54 55 56 57 58 59 60
| #!/usr/bin/perl
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use constant True => 1;
use constant False => 0;
# Start Excel and make it visible
# $xlApp = Win32::OLE->new('Excel.Application');
my $xlApp = Win32::OLE->GetActiveObject('Excel.Application');
unless($xlApp)
{
$xlApp = new Win32::OLE('Excel.Application', \&QuitApp)
or die "Could not create Excel Application object";
}
sub QuitApp
{
my ($object) = @_;
$object->Quit();
}
$xlApp->{Visible} = 1;
# Create a new workbook
$xlBook = $xlApp->Workbooks->Add;
# Our data that we will add to the workbook...
$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...
$rng = $xlBook->ActiveSheet->Range("A1:C7");
$rng->{Value} = $mydata;
# Create a PivotTable for the data...
$tbl = $xlBook->ActiveSheet->PivotTableWizard(1, $rng, "", "MyPivotTable");
$tbl->{ManualUpdate} = True;
# Set pivot fields...
$tbl->AddDataField ( $tbl->PivotFields("Price"), "XPrice", xlSum);
$tbl->AddFields ( { RowFields => ['Category', 'Item'] });
$tbl->PivotFields("XPrice")->{NumberFormat} = "# ##0,00";
$tbl->PivotFields("Category")->{Orientation} = xlRowField;
$tbl->PivotFields("Category")->{Position} = 1;
$tbl->PivotFields("Item")->{Orientation} = xlRowField;
$tbl->PivotFields("Item")->{Position} = 2;
$tbl->PivotFields("Item")->Subtotals(1) = False;
#Sub Macro2()
# ActiveSheet.PivotTables("MyPivotTable").PivotFields("Item").Subtotals(1) = False
#End Sub
$tbl->{ManualUpdate} = False;
$tbl->{ManualUpdate} = True;
$tbl->PivotCache->Refresh; |
Partager