Public/New-PivotTableDefinition.ps1
|
function New-PivotTableDefinition { [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '',Justification='Does not change system State')] param( [Parameter(Mandatory)] [Alias("PivtoTableName")]#Previous typo - use alias to avoid breaking scripts $PivotTableName, $SourceWorkSheet, $SourceRange, $PivotRows, [hashtable]$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 ) $validDataFuntions = [system.enum]::GetNames([OfficeOpenXml.Table.PivotTable.DataFieldFunctions]) if ($PivotData.values.Where( {$_ -notin $validDataFuntions}) ) { Write-Warning -Message ("Pivot data functions might not be valid, they should be one of " + ($validDataFuntions -join ", ") + ".") } $parameters = @{} + $PSBoundParameters if ($NoTotalsInPivot) { $parameters.Remove('NoTotalsInPivot') $parameters["PivotTotals"] = "None" } if ($PSBoundParameters.ContainsKey('ChartType') -and -not $PSBoundParameters.ContainsKey('IncludePivotChart')) { $parameters['IncludePivotChart'] = $true } $parameters.Remove('PivotTableName') if ($PivotChartDefinition) { $parameters.PivotChartDefinition.XRange = $null $parameters.PivotChartDefinition.YRange = $null $parameters.PivotChartDefinition.SeriesHeader = $null } @{$PivotTableName = $parameters} } |