use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents}   = 0;
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $nSheets = $wb->Sheets->Count; #I want 2 sheets - no more, no less. No matter what $luser's default is.
if ($nSheets < 2) {
	for ($nSheets+1..2) {
		$wb->Sheets->Add({After=>$wb->Sheets($nSheets)});
	}
}
if ($nSheets > 2) {
    for (3 .. $nSheets) {
        $wb->Sheets(3)->Delete;
    }
}
my $shtData = $wb->Sheets(1);
my $shtTbl  = $wb->Sheets(2);
$shtData->{Name} = "Data";
$shtTbl ->{Name} = "Table";

$shtData->Cells(1, 1)->{Value} = "Day";
$shtData->Cells(1, 2)->{Value} = "User";
$shtData->Cells(1, 3)->{Value} = "Qty"; #Perhaps, but I have no idea
$shtData->Cells(2, 1)->{Value} = "-";
$shtData->Cells(2, 1)->{HorizontalAlignment} = 5; #xlFill
$shtData->Range("A2")   ->Copy($shtData->Range("B2:C2"));
$shtData->Range("A1:B1")->Copy($shtData->Range("A4"));
$shtData->Range("A1:B1")->Copy($shtData->Range("A7"));

$wb->Names->Add({Name=>'zTopData'      , RefersTo => '=Data!$A$1'});
$wb->Names->Add({Name=>'zEndData'      , RefersTo => '=Data!$C$2'});
$wb->Names->Add({Name=>'zData'         , RefersTo => '=OFFSET(zTopData,0,0,ROW(zEndData)-ROW(zTopData),COLUMN(zEndData)-COLUMN(zTopData)+1)'});
$wb->Names->Add({Name=>'zTopCriterion' , RefersTo => '=Data!$A$4'});
$wb->Names->Add({Name=>'zEndCriterion' , RefersTo => '=Data!$B$5'});
$wb->Names->Add({Name=>'zCriterion'    , RefersTo => '=OFFSET(zTopCriterion,0,0,ROW(zEndCriterion)-ROW(zTopCriterion)+1,COLUMN(zEndCriterion)-COLUMN(zTopCriterion)+1)'});
$wb->Names->Add({Name=>'zDayData'      , RefersTo => '=OFFSET(zTopData,0,0,ROW(zEndData)-ROW(zTopData),1)'});
$wb->Names->Add({Name=>'zUserData'     , RefersTo => '=OFFSET(zTopData,0,1,ROW(zEndData)-ROW(zTopData),1)'});
$wb->Names->Add({Name=>'zDayCriterion' , RefersTo => '=Data!$A$4:$A$5'});
$wb->Names->Add({Name=>'zUserCriterion', RefersTo => '=Data!$B$4:$B$5'});
$wb->Names->Add({Name=>'zDayOutput'    , RefersTo => '=Data!$A$7'});
$wb->Names->Add({Name=>'zUserOutput'   , RefersTo => '=Data!$B$7'});

my $nRows = 98;
for (1 .. $nRows) { #Insert some random data
	$shtData->Range('zEndData')->EntireRow->Insert;
	$shtData->Cells($_ + 1, 1)->{Value} = int(rand(11) + 21); #Dates from 21 to 31
	$shtData->Cells($_ + 1, 2)->{Value} = int(rand(4)  +  1); #4 users
	$shtData->Cells($_ + 1, 3)->{Value} = int(rand(32) +  1); #Something from 1 to 31
}

$shtData->Range('zDayData')->AdvancedFilter({
	Action        => 2,
	CriteriaRange => $shtData->Range('zDayCriterion'), 
	CopyToRange   => $shtData->Range('zDayOutput'), 
	Unique        => 1});

$shtData->Range('zUserData')->AdvancedFilter({
	Action        => 2,
	CriteriaRange => $shtData->Range('zUserCriterion'), 
	CopyToRange   => $shtData->Range('zUserOutput'), 
	Unique        => 1});

my $nLastRowData = $shtData->UsedRange->Rows->{Count};
$shtData->Range($shtData->Range('zUserOutput')->{Address} . ":B" . $nLastRowData)->Copy($shtTbl->Range('A1'));
my $nLastRowTbl = $shtTbl->UsedRange->Rows->{Count};
$shtTbl->Range("A2:A" . $nLastRowTbl)->Sort({
	Key1        => $shtTbl->Range("A2"), 
	Order1      => 1, 
	Header      => 0,
	Orientation => 1,
    DataOption1 => 0});

my $rngDays = $shtData->Range($shtData->Range('zDayOutput')->{Address} . ":A" . $nLastRowData);
my $nMin = $xl->Min($rngDays);
my $nMax = $xl->Max($rngDays);
my $col = 2;
for (my $day = $nMax; $day >= $nMin; $day--) {
	$shtTbl->Cells(1, $col++)->{Value} = $day;
}

$shtTbl->Range("A1:A3")->EntireRow->Insert;
$shtData->Range('zCriterion')->Copy($shtTbl->Range("A1"));
$shtTbl->Range("A4")->{Formula} = "=DCOUNT(zData,Data!C1,Table!A1:B2)";
my $lastrow = $shtTbl->UsedRange->Rows   ->{Count};
my $lastcol = $shtTbl->UsedRange->Columns->{Count};
my $lastcell = $shtTbl->Cells($lastrow+1, $lastcol+1)->{Address};
$shtTbl->Range("A4:" . $lastcell)->Table({
	RowInput    => $shtTbl->Range("A2"), 
	ColumnInput => $shtTbl->Range("B2")});