view release on metacpan or  search on metacpan

lib/Excel/PowerPivot/Utils.pm  view on Meta::CPAN

                          Formula           => 'SUMX(InvoiceLine, InvoiceLine[UnitPrice] * InvoiceLine[Quantity]'
                         },
                         ...
                        ]);



=head1 DESCRIPTION

This module uses OLE automation to interact with an Excel Power Pivot model.
It can be used for example for

=over

=item * 

documenting existing models

=item *

scripting series of updates or inserts on measures or queries as batch operations -- useful
for propagating similar changes to a series of models.

=item *

use a version control system on textual exports of the model

=back


Obviously, this module only works on a Windows platform with a local installation of
Microsoft Office 2016 or greater.

The exposed interface hides details about the interaction with the Excel object model as documented in
L<https://learn.microsoft.com/en-us/office/vba/excel/concepts/about-the-powerpivot-model-object-in-excel>;
nevertheless, some knowledge of that model and of the L<Win32::OLE> module is recommended to fully understand
what is going on.

=head1 CONSTRUCTOR

  my $ppu = Excel::PowerPivot::Utils->new(%options);

Creates a new instance. Options are :

=over

=item workbook

A C<Win32::OLE> object representing an Excel workbook.
If none is supplied, it will connect to the currently running Excel instance and take
the active workbook as default.

=item log

A logger object equipped with C<debug>, C<info> and C<warning> methods.
If none is supplied, a simple logger is automatically created from L<Log::Dispatch>.

=item UTF8

A boolean flag for setting the L<Win32::OLE> codepoint option to UTF8, so that
strings are properly encoded/decoded between Perl and the OLE server.
It is highly recommended to I<systematically set this option to true>, since
this module is mostly used together with L<YAML>, which uses UTF8 encoding.

This option will automatically trigger C<< Win32::OLE->Option(CP => CP_UTF8) >>
at object construction time, and will set it back to the previous value at object
destruction time. Beware however that this is a change in global state, so if
your program performs other operations through L<Win32::OLE> during the lifetime
of the C<Excel::PowerPivot::Utils> object, string handling might be affected.
This is the reason why we require this option to be set explicitly instead of
being enabled automatically by default.

=back



=head1 METHODS

=head2 Utilities for Power Query

=head3 queries

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

Content of the L</queries> method as a L<YAML> string, nicely formatted
so that it is easily readable by humans.

=head3 inject_queries

  $ppu->inject_queries($queries, %options);

Takes an arrayref or query specifications. Each specification must be a hashref
with keys C<Name>, C<Formula> and optionally C<Description>.
For names corresponding to queries already in the workbook, this is an update operation;
other queries in the list are added to the workbook.

Options are :

=over

=item delete_others