blib/lib/Excel/PowerPivot/Utils.pm

filter: Statementstmt 88.2% 15 / 17 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 6 / 6 Podpod n/a totaltotal 91.3% 21 / 23 CC 1 SCAR 0.5
CC
1
Cov
88%
BEGIN
1.3
BEGIN
1.0
BEGIN
1.0
CRAP 1.0
↑ Summary
1 1 ▶use 5.20.0; # because this is the minimal perl version with hash slices
Subroutine BEGIN: called CC=1 SCAR=0.0
2 package Excel::PowerPivot::Utils;
3 1 ▶use utf8;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 1 ▶use feature 'postderef'; # required for perl 5.20 and 5.22; no longer required after 5.24
Subroutine BEGIN: called CC=1 SCAR=0.0
5 1 ▶use Moose;
Subroutine BEGIN: called CC=1 SCAR=0.0
6 1 ▶use Moose::Util::TypeConstraints qw/duck_type/;
Subroutine BEGIN: called CC=1 SCAR=0.0
7 0 ▶use Win32::OLE qw/in CP_UTF8/;
Subroutine BEGIN: called CC=1 SCAR=2.6
8 use Scalar::Does qw/does/;
9 use List::Util qw/all/;
10 use POSIX qw/strftime/;
11 use Log::Dispatch;
12  
13  
14 #======================================================================
15 # GLOBALS
16 #======================================================================
17  
18 our $VERSION = '0.3';
19  
20 use constant {
21 True => 1,
22 False => 0,
23 xlCmdTableCollection => 6, # see https://learn.microsoft.com/en-us/office/vba/api/excel.xlcmdtype
24 };
25  
26 my %ModelFormat_properties = ( # see https://learn.microsoft.com/en-us/office/vba/api/excel.model
27 Currency => [qw/Symbol DecimalPlaces/],
28 Date => [qw/FormatString /],
29 DecimalNumber => [qw/UseThousandSeparator DecimalPlaces/],
30 General => [ ],
31 PercentageNumber => [qw/UseThousandSeparator DecimalPlaces/],
32 ScientificNumber => [qw/DecimalPlaces /],
33 WholeNumber => [qw/UseThousandSeparator /],
34 );
35  
36  
37 my $YAML_separator_line = '#' . '='x70;
38  
39  
40 #======================================================================
41 # ATTRIBUTES AND THEIR BUILDERS, CONSTRUCTOR AND DESTRUCTOR
42 #======================================================================
43  
44 has 'workbook' => (is => 'ro', isa => 'Win32::OLE', lazy => True, builder => '_default_workbook');
45 has 'log' => (is => 'ro', isa => duck_type([qw/debug info warning/]), lazy => True, builder => '_default_logger');
46 has 'UTF8' => (is => 'ro', isa => 'Bool');
47  
48  
49 sub _default_workbook {
50 my $xl = Win32::OLE->GetActiveObject("Excel.Application")
51 or die "cannot connect to an Excel instance";
52 my $wb = $xl->ActiveWorkbook
53 or die "Excel application has no active workbook";
54 return $wb;
55 }
56  
57  
58 sub _default_logger {
59 Log::Dispatch->new(outputs => [[ 'Screen', min_level => 'debug' ]],
60 callbacks => sub {
61 my %h = @_;
62 my $now = strftime "%Y.%m.%d %H:%M:%S", localtime;
63 return "[$now $h{level}] $h{message}\n";
64 });
65 }
66  
67 sub BUILD {
68 my ($self) = @_;
69  
70 if ($self->UTF8) {
71 my $previous_CP = Win32::OLE->Option('CP');
72 if (($previous_CP // -1) != CP_UTF8) {
73 $self->{previous_CP} = $previous_CP;
74 Win32::OLE->Option(CP => CP_UTF8);
75 }
76 }
77 }
78  
79  
80 sub DEMOLISH {
81 my ($self) = @_;
82 if (exists $self->{previous_CP}) {
83 Win32::OLE->Option(CP => $self->{previous_CP});
84 }
85 }
86  
87  
88  
89 #======================================================================
90 # UTILS FOR POWER PIVOT DAX MEASURES
91 #======================================================================
92  
93  
94 sub measures {
95 my ($self) = @_;
96  
97 my $excel_model = $self->workbook->Model;
98  
99 my @measures;
100 for my $wb_measure (in $excel_model->ModelMeasures) {
101 my %measure = (
102 Name => $wb_measure->Name,
103 AssociatedTable => $wb_measure->AssociatedTable->Name,
104 Formula => $wb_measure->Formula,
105 Description => $wb_measure->Description,
106 FormatInformation => flatten_format_information($wb_measure),
107 );
108 push @measures, \%measure;
109 }
110  
111 return @measures;
112 }
113  
114 sub measures_as_YAML {
115 my ($self) = @_;
116  
117 # ad hoc YAML dump instead of YAML::Dump -- for a nicer formatting, easily readable by humans
118 my $yaml = "";
119  
120 foreach my $measure ($self->measures) {
121  
122 # measure formats are expressed as : [$classname, @args]
123 my $format_info = $measure->{FormatInformation} // [];
124 my $format_string = sprintf "[%s]", join ", ", map {$_ // '~'} @$format_info;
125  
126 $yaml .= "\n\n$YAML_separator_line\n"
127 . "- Name : $measure->{Name}\n"
128 . "$YAML_separator_line\n"
129 . " AssociatedTable : $measure->{AssociatedTable}\n"
130 . " Description : $measure->{Description}\n"
131 . " FormatInformation : $format_string\n"
132 . " Formula : |-\n"
133 . $measure->{Formula} =~ s/^/ /gmr;
134 }
135  
136 return $yaml;
137 }
138  
139  
140 sub inject_measures {
141 my ($self, $measures_to_inject, %options) = @_;
142  
143 # check options
144 warn "->inject_measures(..) : option '$_' is invalid"
145 foreach invalid_options(\%options, qw/delete_others dont_refresh_pivots/);
146  
147 # default options
148 $options{dont_refresh_pivots} //= True;
149  
150 # check well-formedness of $measures_to_inject
151 does $measures_to_inject, 'ARRAY'
152 or die "parameter to inject_measures() is not an arrayref";
153 all {has_nonempty_keys(qw/Name AssociatedTable Formula/)->($_)} @$measures_to_inject
154 or die "missing mandatory properties in parameter to ->inject_measures()";
155  
156 # deactivate refresh in pivot caches
157 my @refreshable_pivots;
158 if ($options{dont_refresh_pivots}) {
159 $self->log->debug("deactivate refresh in pivot caches");
160 @refreshable_pivots = grep {$_->{EnableRefresh}} in $self->workbook->PivotCaches;
161 $_->{EnableRefresh} = 0 foreach @refreshable_pivots;
162 }
163  
164  
165 # gather measures already existing in the Excel model
166 $self->log->info("gathering measures from the existing Excel model");
167 my %existing_measures = map {($_->Name => $_)} in $self->workbook->Model->ModelMeasures;
168  
169 # handle each measure to inject
170 foreach my $measure (@$measures_to_inject) {
171  
172 # if that measure is alreay in the Excel model, update it
173 if (my $existing = delete $existing_measures{$measure->{Name}}) {
174  
175 # check that the associated table has not changed
176 my $table_name = $existing->AssociatedTable->Name;
177 $measure->{AssociatedTable} eq $table_name
178 or die "$measure->{Name} is already associated to table '$table_name'; "
179 . "if you really want to change to '$measure->{AssociatedTable}', first delete "
180 . "the existing measure";
181  
182 # update properties of the existing measure -- Description & Formula are just strings, FormatInformation is more complex
183 $self->log->info("updating measure $measure->{Name}");
184 for my $property (qw/Description Formula/) {
185 my $current = $existing->{$property} // "";
186 my $new_val = $measure->{$property} // "";
187 $existing->{$property} = $new_val
188 if $new_val ne $current;
189 }
190 my $existing_format = flatten_format_information($existing);
191 my $new_format = $measure->{FormatInformation} // [];
192 $existing->{FormatInformation} = $self->_build_model_format($measure)
193 if join("\t", @$existing_format) ne join("\t", @$new_format);
194 }
195  
196 # otherwise, create a new measure in the Excel model
197 else {
198 $self->log->info("creating measure $measure->{Name} in table $measure->{AssociatedTable}");
199  
200 # see https://learn.microsoft.com/en-us/office/vba/api/excel.modelmeasures.add
201 $self->workbook->Model->ModelMeasures->Add(
202 $measure->{Name},
203 $self->workbook->Model->ModelTables($measure->{AssociatedTable}),
204 $measure->{Formula},
205 $self->_build_model_format($measure),
206 $measure->{Description}
207 );
208 }
209 }
210  
211 # if requested, delete remaining measures in model
212 if ($options{delete_others}) {
213 $self->log->info("deleting measure $_->{Name}"), $_->Delete
214 foreach values %existing_measures;
215 }
216  
217 # reactivate refresh in pivot caches
218 $self->log->debug("reactivate refresh in pivot caches") if @refreshable_pivots;
219 $_->{EnableRefresh} = 1 foreach @refreshable_pivots;
220  
221 $self->log->info("done injecting measures");
222 }
223  
224  
225 sub _build_model_format {
226 my ($self, $measure) = @_;
227  
228 # get format information as specified in the $measure hash
229 my $format_info = $measure->{FormatInformation} // [General => ()];
230  
231 # invoke proper OLE method to build the ModelFormat object
232 my ($short_classname, @args) = @$format_info;
233 my $model_method = "ModelFormat$short_classname";
234 my $model_format = $self->workbook->Model->$model_method(@args);
235  
236 return $model_format;
237 }
238  
239  
240 #======================================================================
241 # UTILS FOR POWER QUERY
242 #======================================================================
243  
244 sub queries {
245 my ($self) = @_;
246  
247 my @wb_queries = in $self->workbook->Queries;
248 my @queries = map { { %$_{qw/Name Formula Description/} } } @wb_queries; # hash slice
249  
250 return @queries;
251 }
252  
253  
254 sub queries_as_YAML {
255 my ($self, $col_name, $col_type) = @_;
256  
257 # build code for nice reformatting of type arguments to the TransformColumnTypes() function
258 $col_name //= 8;
259 $col_type //= 40;
260 my $reformat_types = sub {
261 my ($types) = @_;
262 $types =~ s[{]["\n" . (' ' x $col_name) . '{']eg;
263 $types =~ s[("\w+",) ][$1 . (' 'x($col_type-$col_name-length($1)))]eg;
264 return $types;
265 };
266  
267 my $yaml = "";
268  
269 foreach my $query ($self->queries) {
270  
271 # get the Power Query formula and reformat it if type arguments are on a single-line
272 my $formula = $query->{Formula};
273 $formula =~ s[(TransformColumnTypes.*?{)({.*})}][$1 . $reformat_types->($2) . '}']eg
274 if $col_name;
275  
276 # build the YAML entry for that query
277 $yaml .= "\n\n$YAML_separator_line\n"
278 . "- Name : $query->{Name}\n"
279 . "$YAML_separator_line\n"
280 . " Description : $query->{Description}\n"
281 . " Formula : |-\n"
282 . $formula =~ s/^/ /gmr; # indentation of the formula code
283 }
284  
285 return $yaml;
286 }
287  
288  
289 sub inject_queries {
290 my ($self, $queries_to_inject, %options) = @_;
291  
292 # check options
293 warn "->inject_queries(..) : option '$_' is invalid"
294 foreach invalid_options(\%options, qw/delete_others fast_combine handle_connections/);
295  
296 # default options
297 $options{fast_combine} //= True;
298 $options{handle_connections} //= True;
299  
300 # check well-formedness of $queries_to_inject
301 does $queries_to_inject, 'ARRAY'
302 or die "parameter to inject_measures() is not an arrayref";
303 all {has_nonempty_keys(qw/Name Formula/)->($_)} @$queries_to_inject
304 or die "missing mandatory properties in parameter to ->inject_queries()";
305  
306 # gather queries already existing in the workbook
307 $self->log->info("gathering existing queries in workbook");
308 my %existing_queries = map {($_->Name => $_)} grep {$_} in $self->workbook->Queries;
309  
310 # this is supposed to accelerate operations -- not sure it makes a difference, though
311 # see https://learn.microsoft.com/en-us/office/vba/api/excel.queries.fastcombine
312 $self->workbook->Queries->{FastCombine} = True if $options{fast_combine} && $self->workbook->Queries;
313  
314 # handle each query to inject
315 foreach my $query (@$queries_to_inject) {
316  
317 my $q_name = $query->{Name};
318  
319 # if that query is alreay in the workbook, update it
320 if (my $existing = delete $existing_queries{$q_name}) {
321 $self->log->info("updating query $q_name");
322 $existing->{Description} = $query->{Description};
323 $existing->{Formula} = $query->{Formula};
324 }
325  
326 # otherwise, create a new query
327 else {
328 $self->log->info("creating query $q_name");
329 $self->workbook->Queries->Add(@{$query}{qw/Name Formula Description/});
330  
331 if ($options{handle_connections}) {
332 $self->log->info("creating connection to query $q_name");
333 # see https://learn.microsoft.com/en-us/office/vba/api/excel.connections.add &
334 # https://learn.microsoft.com/en-gb/dotnet/api/microsoft.office.interop.excel.connections.add2?view=excel-pia
335 $self->workbook->Connections->Add2({
336 Name => "Query - $q_name",
337 Description => "Connection to query '$q_name' in the workbook",
338 ConnectionString => "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=\$Workbook\$;Location=$q_name",
339 CommandText => $q_name,
340 lCmdType => xlCmdTableCollection,
341 CreateModelConnection => True,
342 ImportRelationships => False,
343 });
344 }
345 }
346 }
347  
348 if ($options{delete_others}) {
349 foreach my $wb_query (values %existing_queries) {
350  
351 my $q_name = $wb_query->Name;
352  
353 # first delete the connection to the query -- and the associated table in the Excel model
354 $self->delete_connection_for_query($q_name) if $options{handle_connections};
355  
356 # delete the Power Query
357 $self->log->info("deleting query $q_name");
358 $wb_query->Delete;
359 }
360 }
361  
362 $self->log->info("done injecting queries");
363 }
364  
365  
366 sub delete_connection_for_query {
367 my ($self, $query_name) = @_;
368  
369 # find which connections in workbook refer to that query -- normally there is exactly 1
370 my @connections = grep {my $OLEDBConn = $_->OLEDBConnection;
371 $OLEDBConn &&
372 $OLEDBConn->CommandType == xlCmdTableCollection &&
373 $OLEDBConn->CommandText eq qq{"$query_name"}} in $self->workbook->Connections
374 or $self->log->warning("no OLEDB connection to delete for query '$query_name'");
375  
376 # delete these connections
377 foreach my $connection (@connections) {
378 $self->log->info("deleting connection $connection->{Name}");
379 $connection->Delete;
380 }
381 }
382  
383  
384 #======================================================================
385 # RELATIONSHIPS
386 #======================================================================
387  
388 sub relationships {
389 my ($self) = @_;
390  
391 my @relationships;
392  
393 foreach my $wb_rel (in $self->workbook->Model->ModelRelationships) {
394 my $relationship = {
395 ForeignKey => join('.', $wb_rel->ForeignKeyTable->Name, $wb_rel->ForeignKeyColumn->Name),
396 PrimaryKey => join('.', $wb_rel->PrimaryKeyTable->Name, $wb_rel->PrimaryKeyColumn->Name),
397 Active => $wb_rel->Active,
398 };
399 push @relationships, $relationship;
400 }
401  
402 return @relationships;
403 }
404  
405  
406 sub relationships_as_YAML {
407 my ($self) = @_;
408  
409 my $yaml = "";
410  
411 foreach my $rel ($self->relationships) {
412 $yaml .= "\n\n$YAML_separator_line\n"
413 . "- ForeignKey : $rel->{ForeignKey}\n"
414 . " PrimaryKey : $rel->{PrimaryKey}\n"
415 . " Active : $rel->{Active}\n"
416 . "$YAML_separator_line\n";
417 }
418  
419 return $yaml;
420 }
421  
422  
423 sub inject_relationships {
424 my ($self, $relationships_to_inject, %options) = @_;
425  
426 # check options
427 warn "->inject_relationships(..) : option '$_' is invalid"
428 foreach invalid_options(\%options, qw/delete_others/);
429  
430 # gather relationships already existing in the model
431 $self->log->info("gathering existing relationships in model");
432 my %existing_relationships
433 = map {my $fk_pk = sprintf "%s.%s=>%s.%s",
434 $_->ForeignKeyTable->Name, $_->ForeignKeyColumn->Name,
435 $_->PrimaryKeyTable->Name, $_->PrimaryKeyColumn->Name;
436 ($fk_pk => $_)
437 } in $self->workbook->Model->ModelRelationships;
438  
439 # handle each relationship to inject
440 foreach my $rel (@$relationships_to_inject) {
441 my $fk_pk = "$rel->{ForeignKey}=>$rel->{PrimaryKey}";
442  
443 # if that relationship is alreay in the model, update its activity status
444 if (my $existing = delete $existing_relationships{$fk_pk}) {
445 if ($existing->Active xor $rel->{Active}) {
446 $self->log->info("updating relationship $fk_pk");
447 $existing->{Active} = $rel->{Active};
448 }
449 }
450  
451 # otherwise, create a new relationship
452 else {
453 $self->log->info("creating relationship $fk_pk");
454 $self->workbook->Model->ModelRelationships->Add(
455 $self->_find_ModelTableColumn($rel->{ForeignKey}),
456 $self->_find_ModelTableColumn($rel->{PrimaryKey}),
457 )
458 }
459 }
460  
461 if ($options{delete_others}) {
462 while (my ($fk_pk, $wb_rel) = each %existing_relationships) {
463 $self->log->info("deleting relationship $fk_pk");
464 $wb_rel->Delete;
465 }
466 }
467  
468 $self->log->info("done injecting relationships");
469 }
470  
471  
472 sub _find_ModelTableColumn {
473 my ($self, $table_column) = @_;
474  
475 my ($table, $column) = split /\./, $table_column;
476 my $wb_table = $self->workbook->Model->ModelTables($table)
477 or die "could not find table '$table' in Excel model";
478 my $wb_column = $wb_table->ModelTableColumns($column)
479 or die "could not find column '$column' in table '$table'";
480  
481 return $wb_column;
482 }
483  
484  
485  
486 #======================================================================
487 # WHOLE MODEL
488 #======================================================================
489  
490  
491 sub whole_model_as_YAML {
492 my ($self) = @_;
493  
494 my $yaml = "";
495  
496 CHAPTER:
497 for my $chapter (qw/QUERIES RELATIONSHIPS MEASURES/) {
498  
499 # produce YAML representation of this chapter
500 my $method = lc($chapter) . "_as_YAML";
501 my $sub_yaml = $self->$method
502 or next CHAPTER;
503 $sub_yaml =~ s/^/ /mg; # indent all lines by 2 spaces
504  
505 # add this chapter to the global YAML
506 $yaml .= "\n\n$YAML_separator_line\n"
507 . "$chapter :\n"
508 . "$YAML_separator_line\n"
509 . $sub_yaml;
510 }
511  
512 return $yaml;
513 }
514  
515  
516 sub inject_whole_model {
517 my ($self, $model_to_inject, %options) = @_;
518  
519 CHAPTER:
520 for my $chapter (qw/QUERIES RELATIONSHIPS MEASURES/) {
521 my $data_to_inject = $model_to_inject->{$chapter}
522 or next CHAPTER;
523 my $method = "inject_" . lc($chapter);
524 $self->$method($data_to_inject, %options);
525 }
526  
527 $self->workbook->Model->Refresh();
528 }
529  
530  
531  
532 #======================================================================
533 # HELPER FUNCTIONS
534 #======================================================================
535  
536  
537 sub has_nonempty_keys {
538 my @keys = @_;
539 return sub {my $hash = shift; all {$hash->{$_}} @keys};
540 }
541  
542 sub invalid_options {
543 my ($options, @valid_keys) = @_;
544 my %is_valid = map {($_ => 1)} @valid_keys;
545 return grep {!$is_valid{$_}} keys %$options;
546 }
547  
548  
549 sub flatten_format_information {
550 my ($wb_measure) = @_;
551  
552 my @format_information; # of shape: ($classname, $property1, ...)
553  
554 if (my $format_obj = $wb_measure->FormatInformation) {
555 my $format_class = Win32::OLE->QueryObjectType($format_obj) =~ s/^ModelFormat//r;
556 push @format_information, $format_class,
557 map {$format_obj->{$_}} $ModelFormat_properties{$format_class}->@*;
558 }
559  
560 return \@format_information
561 }
562  
563  
564 1;
565  
566 __END__
↑ Summary