Excel-PowerPivot-Utils
view release on metacpan or search on metacpan
my $ppu = Excel::PowerPivot::Utils->new; # will connect to the currently active workbook
# operations on the whole model ...
print $ppu->whole_model_as_YAML;
$ppu->inject_whole_model({QUERIES => ...,
RELATIONSHIPS => ...
MEASURES => ...});
# .. or specific operations on queries, relationships or measures
print $ppu->queries_as_YAML;
$ppu->inject_queries ([{Name => 'New_table', Formula => $M_formula_for_new_table}]);
print $ppu->relationships_as_YAML;
$ppu->inject_relationships([ {ForeignKey => 'Album.ArtistId',
PrimaryKey => 'Artist.ArtistId',
Active => 1},
...
]);
print $ppu->measures_as_YAML;
$ppu->inject_measures([{Name => 'Invoice Lines Total Amount',
lib/Excel/PowerPivot/Utils.pm view on Meta::CPAN
my ($types) = @_;
$types =~ s[{]["\n" . (' ' x $col_name) . '{']eg;
$types =~ s[("\w+",) ][$1 . (' 'x($col_type-$col_name-length($1)))]eg;
return $types;
};
my $yaml = "";
foreach my $query ($self->queries) {
# get the Power Query formula and reformat it if type arguments are on a single-line
my $formula = $query->{Formula};
$formula =~ s[(TransformColumnTypes.*?{)({.*})}][$1 . $reformat_types->($2) . '}']eg
if $col_name;
# build the YAML entry for that query
$yaml .= "\n\n$YAML_separator_line\n"
. "- Name : $query->{Name}\n"
. "$YAML_separator_line\n"
. " Description : $query->{Description}\n"
. " Formula : |-\n"
. $formula =~ s/^/ /gmr; # indentation of the formula code
}
return $yaml;
}
sub inject_queries {
my ($self, $queries_to_inject, %options) = @_;
# check options
lib/Excel/PowerPivot/Utils.pm view on Meta::CPAN
my $ppu = Excel::PowerPivot::Utils->new(UTF8 => 1);
# operations on the whole model ...
print $ppu->whole_model_as_YAML;
$ppu->inject_whole_model({QUERIES => ...,
RELATIONSHIPS => ...
MEASURES => ...});
# .. or specific operations on queries, relationships or measures
print $ppu->queries_as_YAML;
$ppu->inject_queries ([{Name => 'New_table', Formula => $M_formula_for_new_table}]);
print $ppu->relationships_as_YAML;
$ppu->inject_relationships([ {ForeignKey => 'Album.ArtistId',
PrimaryKey => 'Artist.ArtistId',
Active => 1},
...
]);
print $ppu->measures_as_YAML;
$ppu->inject_measures([{Name => 'Invoice Lines Total Amount',
lib/Excel/PowerPivot/Utils.pm view on Meta::CPAN
Returns information about queries in the Excel workbook. This is a list of hashrefs containing
=over
=item Name
the name of the query
=item Formula
the M formula (Power Query language)
=item Description
optional description text
=back
=head3 queries_as_YAML
lib/Excel/PowerPivot/Utils.pm view on Meta::CPAN
=item Name
the name of the measure
=item AssociatedTable
the name of the table to which this measure is associated
=item Formula
the DAX formula
=item Description
optional description text
=item FormatInformation
an arrayref describing the format for displaying that measure. Formats are
documented in L<https://learn.microsoft.com/en-us/office/vba/api/excel.model>.
The first member of the array is the name of the ModelFormat object, followed by the values
lib/Excel/PowerPivot/Utils.pm view on Meta::CPAN
my $pcache = $workbook->PivotCaches->Create(xlExternal,
$workbook->Connections("ThisWorkbookDataModel"));
my $ptable = $pcache->CreatePivotTable("ComputedPivot!R5C1",
'Sales_by_genre_and_country');
$ptable->CubeFields("[Measures].[Invoice Lines Percentage Sales]")->{Orientation} = xlDataField;
$ptable->CubeFields("[Genre].[Name]") ->{Orientation} = xlColumnField;
$ptable->CubeFields("[Customer].[Country]") ->{Orientation} = xlRowField;
=item e)
Then it is possible to write Excel formulas that extract values from the pivot cache.
So for example here is the formula that retrieves the percentage of sales for the "Classical"
genre in Austria :
= CUBEVALUE("ThisWorkbookDataModel",
"[Measures].[Invoice Lines Percentage Sales]",
"[Genre].[Name].[Classical]",
"[Customer].[Country].[Austria]")
=back
=back
t/02_chinook.t view on Meta::CPAN
# https://learn.microsoft.com/en-us/office/vba/api/excel.xlpivottablesourcetype
xlExternal => 2,
};
# url and file names
my $db_URL = "https://raw.githubusercontent.com/lerocha/chinook-database/master/"
. "ChinookDatabase/DataSources/Chinook_Sqlite.sqlite";
my $db_file = "chinook.sqlite";
my $xl_file = "chinook.xlsx";
# Excel formula for testing the cube result.
# Can't add it through VBA because it would need to be expressed in
# localized language (e.g. 'VALEURCUBE' for a french version of
# Excel). So the trick is to include the formula directly in the XLSX
# file through Excel::ValueWriter::XLSX, before adding the pivot through VBA.
my $formula = 'CUBEVALUE("ThisWorkbookDataModel", '
. '"[Measures].[Invoice Lines Percentage Sales]", '
. '"[Genre].[Name].[Classical]", '
. '"[Customer].[Country].[Austria]")';
#======================================================================
# SETTING UP
#======================================================================
note "downloading $db_URL";
t/02_chinook.t view on Meta::CPAN
or note "could not download Chinook sqlite database: ", $response->status_line;
SKIP : {
skip "no sqlite database" if ! -f $db_file;
note "connecting to the sqlite database";
my $dbh = DBI->connect("dbi:SQLite:dbname=$db_file","","", {sqlite_unicode => 1});
note "generating Excel file $xl_file";
my $writer = Excel::ValueWriter::XLSX->new();
$writer->add_sheet(ComputedPivot => (undef) => [["=$formula"]]);
$writer->add_sheets_from_database($dbh);
$writer->save_as($xl_file);
# connect to the generated file. Need to have an active Excel, because Pivot operations don't work
# if we go through Win32::OLE->GetObject() -- don't know why :-(
my $fullpath_xl_file = (getcwd . "/$xl_file") =~ tr[/][\\]r;
my $xl = Win32::OLE->GetActiveObject("Excel.Application")
or skip "can't connect to an active Excel instance";
my $workbook = $xl->Workbooks->Open($fullpath_xl_file)
or skip "cannot open OLE connection to Excel file $fullpath_xl_file";
t/02_chinook.t view on Meta::CPAN
my $ptable = $pcache->CreatePivotTable("ComputedPivot!R5C1",
'Sales_by_genre_and_country');
$ptable->CubeFields("[Measures].[Invoice Lines Percentage Sales]")->{Orientation} = xlDataField;
$ptable->CubeFields("[Genre].[Name]") ->{Orientation} = xlColumnField;
$ptable->CubeFields("[Customer].[Country]") ->{Orientation} = xlRowField;
#======================================================================
# TESTS
#======================================================================
sleep 3; # give time to Excel to recompute the formula
my $cell = $workbook->Sheets("ComputedPivot")->Range("A1");
my $val = $cell->Value;
ok $val > 0.04 && $val < 0.05, "Pivot correctly computed percentage of sales of Classical in Austria ($val)";
# cleanup
$workbook->Close(1); # 1 = true value for 'SaveChanges'
}
done_testing;
( run in 0.713 second using v1.01-cache-2.11-cpan-3cd7ad12f66 )