-
Notifications
You must be signed in to change notification settings - Fork 401
/
Add-Subtotals.ps1
133 lines (125 loc) · 6.27 KB
/
Add-Subtotals.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
Function Add-Subtotals {
param(
[Parameter(Mandatory=$true, Position=0)]
$ChangeColumnName , # = "Location"
[Parameter(Mandatory=$true, Position=1)]
[hashtable]$AggregateColumn , #= @{"Sales" = "SUM" }
[Parameter(Position=2)]
$ExcelPath = ([System.IO.Path]::GetTempFileName() -replace "\.tmp", ".xlsx") ,
[Parameter(Position=3)]
$WorksheetName = "Sheet1",
[Parameter(ValueFromPipeline=$true)]
$InputObject, #$DataToPivot | Sort location, product
[switch]$HideSingleRows,
[switch]$NoSort,
[switch]$NoOutLine,
[switch]$Show
)
begin {
if (-not $PSBoundParameters.ContainsKey('ExcelPath')) {$Show = $true}
$data = @()
$aggFunctions = [ordered]@{
"AVERAGE" = 1; "COUNT" = 2; "COUNTA" = 3 #(non empty cells) f
"MAX" = 4; "MIN" = 5; "PRODUCT" = 6; "STDEV" = 7 # (sample)
"STDEVP" = 8 # (whole population);
"SUM" = 9; "VAR" = 10 # (Variance sample)
"VARP" = 11 # (whole population) #add 100 to ignore hidden cells
}
}
process {
$data += $InputObject
}
end {
if (-not $NoSort) {$data = $data | Sort-Object $changeColumnName}
$Header = $data[0].PSObject.Properties.Name
#region turn each entry in $AggregateColumn "=SUBTOTAL(a,x{0}}:x{1})" where a is the aggregate function number and x is the column letter
$aggFormulas = @{}
foreach ($k in $AggregateColumn.Keys) {
$columnNo = 0 ;
while ($columnNo -lt $header.count -and $header[$columnNo] -ne $k) {$columnNo ++}
if ($columnNo -eq $header.count) {
throw "'$k' isn't a property of the first row of data."; return
}
if ($AggregateColumn[$k] -is [string]) {
$aggfn = $aggFunctions[$AggregateColumn[$k]]
if (-not $aggfn) {
throw "$($AggregateColumn[$k]) is not a valid aggregation function - these are $($aggFunctions.keys -join ', ')" ; return
}
}
else {$aggfn = $AggregateColumn[$k]}
$aggFormulas[$k] = "=SUBTOTAL({0},{1}{{0}}:{1}{{1}})" -f $aggfn , (Get-ExcelColumnName ($columnNo+1) ).ColumnName
}
if ($aggformulas.count -lt 1) {throw "We didn't get any aggregation formulas"}
$aggFormulas | out-string -Stream | Write-Verbose -Verbose
#endregion
$insertedRows = @()
$singleRows = @()
$previousValue = $data[0].$changeColumnName
$currentRow = $lastChangeRow = 2
#region insert subtotals and send to excel:
#each time there is a change in the column we're intetersted in.
#either Add a row with the value and subtotal(s) function(s) if there is more than one row to total
#or note the row if there was only one row with that value (we may hide it later.)
$excel = $data |
ForEach-Object -process {
if ($_.$changeColumnName -ne $previousValue) {
if ($lastChangeRow -lt ($currentrow - 1)) {
$NewObj = @{$changeColumnName = $previousValue}
foreach ($k in $aggFormulas.Keys) {
$newobj[$k] = $aggformulas[$k] -f $lastChangeRow, ($currentRow - 1)
}
$insertedRows += $currentRow
[pscustomobject]$newobj
$currentRow += 1
}
else {$singleRows += $currentRow }
$lastChangeRow = $currentRow
$previousValue = $_.$changeColumnName
}
$_
$currentRow += 1
} -end { # the process block won't output the last row
if ($lastChangeRow -lt ($currentrow - 1)) {
$NewObj = @{$changeColumnName = $previousValue}
foreach ($k in $aggFormulas.Keys) {
$newobj[$k] = $aggformulas[$k] -f $lastChangeRow, ($currentRow - 1)
}
$insertedRows += $currentRow
[pscustomobject]$newobj
}
else {$singleRows += $currentRow }
} | Export-Excel -Path $ExcelPath -PassThru -AutoSize -AutoFilter -AutoNameRange -BoldTopRow -WorksheetName $WorksheetName -Activate -ClearSheet #-MaxAutoSizeRows 10000
#endregion
#Put the subtotal rows in bold optionally hide rows where only one has the value of interest.
$ws = $excel.$WorksheetName
#We kept lists of the total rows Since 1 rows won't get expand/collapse we can hide them.
foreach ($r in $insertedrows) {$ws.Row($r).style.font.bold = $true }
if ($HideSingleRows) {
foreach ($r in $hideRows) { $ws.Row($r).hidden = $true}
}
$range = $ws.Dimension.Address
$ExcelPath = $excel.File.FullName
$SheetIndex = $ws.index
if ($NoOutline) {
Close-ExcelPackage $excel -show:$Show
return
}
else {
Close-ExcelPackage $excel
try { $excelApp = New-Object -ComObject "Excel.Application" }
catch { Write-Warning "Could not start Excel application - which usually means it is not installed." ; return }
try { $excelWorkBook = $excelApp.Workbooks.Open($ExcelPath) }
catch { Write-Warning -Message "Could not Open $ExcelPath." ; return }
$ws = $excelWorkBook.Worksheets.item($SheetIndex)
$null = $ws.Range($range).Select()
$null = $excelapp.ActiveCell.AutoOutline()
$null = $ws.Outline.ShowLevels(1,$null)
$excelWorkBook.Save()
if ($show) {$excelApp.Visible = $true}
else {
[void]$excelWorkBook.close()
$excelapp.Quit()
}
}
}
}