| 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__ | ||