-
Notifications
You must be signed in to change notification settings - Fork 410
/
Copy pathAdd-PivotTable.ps1
188 lines (186 loc) · 10.9 KB
/
Add-PivotTable.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
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
function Add-PivotTable {
[CmdletBinding(defaultParameterSetName = 'ChartbyParams')]
[OutputType([OfficeOpenXml.Table.PivotTable.ExcelPivotTable])]
param (
[Parameter(Mandatory = $true)]
[string]$PivotTableName,
[OfficeOpenXml.ExcelAddressBase]
$Address,
$ExcelPackage,
$SourceWorksheet,
$SourceRange,
$PivotRows,
$PivotData,
$PivotColumns,
$PivotFilter,
[Switch]$PivotDataToColumn,
[ValidateSet("Both", "Columns", "Rows", "None")]
[String]$PivotTotals = "Both",
[Switch]$NoTotalsInPivot,
[String]$GroupDateRow,
[String]$GroupDateColumn,
[OfficeOpenXml.Table.PivotTable.eDateGroupBy[]]$GroupDatePart,
[String]$GroupNumericRow,
[String]$GroupNumericColumn,
[double]$GroupNumericMin = 0 ,
[double]$GroupNumericMax = [Double]::MaxValue ,
[double]$GroupNumericInterval = 100 ,
[string]$PivotNumberFormat,
[OfficeOpenXml.Table.TableStyles]$PivotTableStyle,
[Parameter(ParameterSetName = 'ChartbyDef', Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
$PivotChartDefinition,
[Parameter(ParameterSetName = 'ChartbyParams')]
[Switch]$IncludePivotChart,
[Parameter(ParameterSetName = 'ChartbyParams')]
[String]$ChartTitle = "",
[Parameter(ParameterSetName = 'ChartbyParams')]
[int]$ChartHeight = 400 ,
[Parameter(ParameterSetName = 'ChartbyParams')]
[int]$ChartWidth = 600,
[Parameter(ParameterSetName = 'ChartbyParams')]
[Int]$ChartRow = 0 ,
[Parameter(ParameterSetName = 'ChartbyParams')]
[Int]$ChartColumn = 4,
[Parameter(ParameterSetName = 'ChartbyParams')]
[Int]$ChartRowOffSetPixels = 0 ,
[Parameter(ParameterSetName = 'ChartbyParams')]
[Int]$ChartColumnOffSetPixels = 0,
[Parameter(ParameterSetName = 'ChartbyParams')]
[OfficeOpenXml.Drawing.Chart.eChartType]$ChartType = 'Pie',
[Parameter(ParameterSetName = 'ChartbyParams')]
[Switch]$NoLegend,
[Parameter(ParameterSetName = 'ChartbyParams')]
[Switch]$ShowCategory,
[Parameter(ParameterSetName = 'ChartbyParams')]
[Switch]$ShowPercent,
[switch]$Activate,
[Switch]$PassThru
)
if ($PivotTableName.length -gt 250) {
Write-warning -Message "PivotTable name will be truncated"
$PivotTableName = $PivotTableName.Substring(0, 250)
}
if ($Address) {
[OfficeOpenXml.ExcelWorksheet]$wsPivot = $address.Worksheet
}
else {
try {
if (-not $ExcelPackage) {Write-Warning -message "This combination of Parameters needs to include the ExcelPackage." ; return }
[OfficeOpenXml.ExcelWorksheet]$wsPivot = Add-Worksheet -ExcelPackage $ExcelPackage -WorksheetName $pivotTableName -Activate:$Activate
if ($wsPivot.Name -ne $PivotTableName) {Write-Warning -Message "The Worksheet name for the PivotTable does not match the table name '$PivotTableName'; probably because excess or illegal characters were removed." }
if ($PivotFilter) {$Address = $wsPivot.Cells["A3"]} else { $Address = $wsPivot.Cells["A1"]}
}
catch {throw "Could not create the sheet for the PivotTable. $_" }
}
#if the pivot doesn't exist, create it.
if (-not $wsPivot) {throw "There was a problem getting the worksheet for the PivotTable"}
if (-not $wsPivot.PivotTables[$pivotTableName] ) {
try {
#Accept a string or a worksheet object as $SourceWorksheet - we don't need a worksheet if we have a Rangebase .
if ( $SourceWorksheet -is [string]) {
$SourceWorksheet = $ExcelPackage.Workbook.Worksheets.where( {$_.name -Like $SourceWorksheet})[0]
}
elseif ( $SourceWorksheet -is [int] ) {
$SourceWorksheet = $ExcelPackage.Workbook.Worksheets[$SourceWorksheet]
}
if ( $SourceRange -is [OfficeOpenXml.Table.ExcelTable]) {$SourceRange = $SourceRange.Address }
if ( $sourceRange -is [OfficeOpenXml.ExcelRange] -or
$SourceRange -is [OfficeOpenXml.ExcelAddress]) {
$pivotTable = $wsPivot.PivotTables.Add($Address, $SourceRange, $pivotTableName)
}
elseif (-not $SourceRange) {
$pivotTable = $wsPivot.PivotTables.Add($Address, $SourceWorksheet.cells[$SourceWorksheet.Dimension.Address], $pivotTableName)
}
elseif ($SourceWorksheet -isnot [OfficeOpenXml.ExcelWorksheet] ) {
Write-Warning -Message "Could not find source Worksheet for pivot-table '$pivotTableName'." ; return
}
elseif ( $SourceRange -is [String] -or $SourceRange -is [OfficeOpenXml.ExcelAddress]) {
$pivotTable = $wsPivot.PivotTables.Add($Address, $SourceWorksheet.Cells[$SourceRange], $pivotTableName)
}
else {Write-warning "Could not create a PivotTable with the Source Range provided."; return}
foreach ($row in $PivotRows) {
try {$null = $pivotTable.RowFields.Add($pivotTable.Fields[$row]) }
catch {Write-Warning -message "Could not add '$row' to Rows in PivotTable $pivotTableName." }
}
foreach ($Column in $PivotColumns) {
try {$null = $pivotTable.ColumnFields.Add($pivotTable.Fields[$Column])}
catch {Write-Warning -message "Could not add '$Column' to Columns in PivotTable $pivotTableName." }
}
if ($PivotData -is [HashTable] -or $PivotData -is [System.Collections.Specialized.OrderedDictionary]) {
$PivotData.Keys | ForEach-Object {
try {
$df = $pivotTable.DataFields.Add($pivotTable.Fields[$_])
$df.Function = $PivotData.$_
if ($PivotNumberFormat) {$df.Format = (Expand-NumberFormat -NumberFormat $PivotNumberFormat)}
}
catch {Write-Warning -message "Problem adding data fields to PivotTable $pivotTableName." }
}
}
else {
foreach ($field in $PivotData) {
try {
$df = $pivotTable.DataFields.Add($pivotTable.Fields[$field])
$df.Function = 'Count'
}
catch {Write-Warning -message "Problem adding data field '$field' to PivotTable $pivotTableName." }
}
}
foreach ( $pFilter in $PivotFilter) {
try { $null = $pivotTable.PageFields.Add($pivotTable.Fields[$pFilter])}
catch {Write-Warning -message "Could not add '$pFilter' to Filter/Page fields in PivotTable $pivotTableName." }
}
if ($NoTotalsInPivot) {$PivotTotals = "None" }
if ($PivotTotals -eq "None" -or $PivotTotals -eq "Columns") { $pivotTable.RowGrandTotals = $false }
elseif ($PivotTotals -eq "Both" -or $PivotTotals -eq "Rows") { $pivotTable.RowGrandTotals = $true }
if ($PivotTotals -eq "None" -or $PivotTotals -eq "Rows") { $pivotTable.ColumGrandTotals = $false } # Epplus spelling mistake, not mine!
elseif ($PivotTotals -eq "Both" -or $PivotTotals -eq "Columns") { $pivotTable.ColumGrandTotals = $true }
if ($PivotDataToColumn ) { $pivotTable.DataOnRows = $false }
if ($PivotTableStyle) { $pivotTable.TableStyle = $PivotTableStyle}
if ($GroupNumericRow) {
$r = $pivotTable.RowFields.Where( {$_.name -eq $GroupNumericRow })
if (-not $r ) {Write-Warning -Message "Could not find a Row field named '$GroupNumericRow'; no numeric grouping will be done."}
else {$r.AddNumericGrouping($GroupNumericMin, $GroupNumericMax, $GroupNumericInterval)}
}
elseif ($GroupNumericColumn) {
$c = $pivotTable.ColumnFields.Where( {$_.name -eq $GroupNumericColumn })
if (-not $c ) {Write-Warning -Message "Could not find a Column field named '$GroupNumericColumn'; no numeric grouping will be done."}
else {$c.AddNumericGrouping($GroupNumericMin, $GroupNumericMax, $GroupNumericInterval)}
}
if ($GroupDateRow -and $PSBoundParameters.ContainsKey("GroupDatePart")) {
$r = $pivotTable.RowFields.Where( {$_.name -eq $GroupDateRow })
if (-not $r ) {Write-Warning -Message "Could not find a Row field named '$GroupDateRow'; no date grouping will be done."}
else {$r.AddDateGrouping($GroupDatePart)}
}
elseif ($GroupDateColumn -and $PSBoundParameters.ContainsKey("GroupDatePart")) {
$c = $pivotTable.ColumnFields.Where( {$_.name -eq $GroupDateColumn })
if (-not $c ) {Write-Warning -Message "Could not find a Column field named '$GroupDateColumn'; no date grouping will be done."}
else {$c.AddDateGrouping($GroupDatePart)}
}
}
catch {Write-Warning -Message "Failed adding PivotTable '$pivotTableName': $_"}
}
else {
Write-Warning -Message "PivotTable defined in $($pivotTableName) already exists, only the data range will be changed."
$pivotTable = $wsPivot.PivotTables[$pivotTableName]
if (-not $SourceRange) { $SourceRange = $SourceWorksheet.Dimension.Address}
$pivotTable.CacheDefinition.SourceRange = $SourceWorksheet.cells[$SourceRange]
#change for epPlus 4.5 - Previously needed to hack the xml
# $pivotTable.CacheDefinition.CacheDefinitionXml.pivotCacheDefinition.cacheSource.worksheetSource.ref = $SourceRange
}
#Create the chart if it doesn't exist, leave alone if it does.
if ($IncludePivotChart -and -not $wsPivot.Drawings["Chart$pivotTableName"] ) {
try {Add-ExcelChart -PivotTable $pivotTable -ChartType $ChartType -Width $ChartWidth -Height $ChartHeight -Row $ChartRow -Column $ChartColumn -RowOffSetPixels $ChartRowOffSetPixels -ColumnOffSetPixels $ChartColumnOffSetPixels -Title $ChartTitle -NoLegend:$NoLegend -ShowCategory:$ShowCategory -ShowPercent:$ShowPercent }
catch {Write-Warning -Message "Failed adding chart for pivotable '$pivotTableName': $_"}
}
elseif ($PivotChartDefinition -and -not $wsPivot.Drawings["Chart$pivotTableName"]) {
if ($PivotChartDefinition -is [System.Management.Automation.PSCustomObject]) {
$params = @{PivotTable = $pivotTable }
$PivotChartDefinition.PSObject.Properties | ForEach-Object {if ( $null -ne $_.value) {$params[$_.name] = $_.value}}
Add-ExcelChart @params
}
elseif ($PivotChartDefinition -is [hashtable] -or $PivotChartDefinition -is [System.Collections.Specialized.OrderedDictionary]) {
Add-ExcelChart -PivotTable $pivotTable @PivotChartDefinition
}
}
if ($PassThru) {return $pivotTable}
}