Public/Add-PivotTable.ps1
|
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, [OfficeOpenXml.Table.PivotTable.eDateGroupBy[]]$GroupDatePart, [String]$GroupNumericRow, [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)} } 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)} } } 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} } |