view release on metacpan or  search on metacpan

t/02_chinook.t  view on Meta::CPAN

use utf8;
use strict;
use warnings;
use Win32::OLE      qw/CP_UTF8/;
use YAML            qw/LoadFile/;
use Test::More;
use Excel::PowerPivot::Utils;
use Excel::ValueWriter::XLSX;
use LWP::UserAgent;
use DBI;
use Cwd;


#======================================================================
# GLOBALS
#======================================================================

# VBA constants
use constant {

  # https://learn.microsoft.com/en-us/office/vba/api/excel.xlpivotfieldorientation
  xlColumnField => 2,
  xlDataField 	=> 4,
  xlHidden 	=> 0,
  xlPageField 	=> 3,
  xlRowField 	=> 1,

  # 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";
my $ua       = LWP::UserAgent->new(timeout => 5);
my $response = $ua->mirror($db_URL, $db_file);
$response->is_success
  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";

  # load Power Query and Power Pivot settings
  my $ppu = Excel::PowerPivot::Utils->new(workbook => $workbook);
  my $model_instructions = LoadFile *DATA;
  $ppu->inject_whole_model($model_instructions);

  # create a Pivot Table (percentage of sales per genre, for each customer country)
  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;

  #======================================================================
  # 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;



__DATA__
#======================================================================
QUERIES :
#======================================================================


  #======================================================================
  - Name        : Album
  #======================================================================
    Description : 
    Formula     : |-
      let
          Album_Table = Excel.CurrentWorkbook(){[Name="Album"]}[Content],
          #"Modified type" = Table.TransformColumnTypes(Album_Table,{
              {"AlbumId", Int64.Type},
              {"Title", type text},
              {"ArtistId", Int64.Type}})
      in
          #"Modified type"

  #======================================================================
  - Name        : Artist
  #======================================================================
    Description : 
    Formula     : |-
      let
          Artist_Table = Excel.CurrentWorkbook(){[Name="Artist"]}[Content],
          #"Modified type" = Table.TransformColumnTypes(Artist_Table,{
              {"ArtistId", Int64.Type},
              {"Name", type text}})
      in
          #"Modified type"

  #======================================================================
  - Name        : Customer
  #======================================================================
    Description : 
    Formula     : |-
      let
          Customer_Table = Excel.CurrentWorkbook(){[Name="Customer"]}[Content],
          #"Modified type" = Table.TransformColumnTypes(Customer_Table,{
              {"CustomerId", Int64.Type},
              {"FirstName", type text},
              {"LastName", type text},
              {"Company", type text},
              {"Address", type text},
              {"City", type text},
              {"State", type text},