Add-PivotChart.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 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 |
function Add-PivotChart { <# .SYNOPSIS Add a pivot chart to an Excel worksheet .DESCRIPTION Add a pivot chart to an Excel worksheet Note: Each time you call this function, you need to save and re-create your Excel Object. If you attempt to modify the Excel object, save, modify, and save a second time, it will fail. See Save-Excel Passthru parameter for a workaround .PARAMETER Path Path to an xlsx file to add the pivot chart to If Path is specified and you do not use passthru, we save the file .PARAMETER Excel ExcelPackage to add the pivot chart to We do not save the ExcelPackage upon completion. See Save-Excel. .PARAMETER PivotTableName Pivot table for chart data. If not specified, we add a chart to all pivot tables .PARAMETER TargetWorkSheetName Optional target worksheet for the chart. If not specified, we use the existing pivot table worksheet .PARAMETER ChartName Optional, use this to ensure chart names are unique. Defaults to CT-<PivotTableName> .PARAMETER ChartType If specified, add a chart with this type .PARAMETER ChartTitle Optional chart title .PARAMETER ChartWidth Width of the chart .PARAMETER ChartHeight Height of the chart .PARAMETER Passthru If specified, pass the ExcelPackage back .EXAMPLE Get-ChildItem C:\ -file | Export-XLSX -Path C:\temp\files.xlsx -PivotRows Extension -PivotValues Length Add-PivotChart -Path C:\Temp\files.xlsx -ChartType Pie -ChartName CT1 Add-PivotChart -Path C:\Temp\files.xlsx -ChartType Area3D -ChartName CT2 # Get files, create an xlsx in C:\temp\ps.xlsx # Pivot rows on 'Extension' # Pivot values on 'Length' # Take the xlsx and add a pie pivot chart # Take the xlsx and add an Area3D pivot chart #This example gives you a pie chart breaking down storage by file extension .EXAMPLE #Create an xlsx and pivot table Get-ChildItem C:\ -file | Export-XLSX -Path C:\temp\files.xlsx -PivotRows Extension -PivotValues Length # Open the excel file, add a pivot chart (this won't save), add another pivot chart (this won't save), save. New-Excel -Path C:\temp\files.xlsx | Add-PivotChart -ChartType Pie -ChartTitle "Space per Extension" -ChartWidth 800 -ChartHeight 600 -Passthru | Add-PivotChart -ChartType PieExploded3D -ChartTitle "Why Do I Want This?" -ChartName CT2 -Passthru | Save-Excel -Close .NOTES Thanks to Doug Finke for his example This function borrows heavily if not everything from Doug: https://github.com/dfinke/ImportExcel Thanks to Philip Thompson for an expansive set of examples on working with EPPlus in PowerShell: https://excelpslib.codeplex.com/ .LINK https://github.com/RamblingCookieMonster/PSExcel .FUNCTIONALITY Excel #> [OutputType([OfficeOpenXml.ExcelPackage])] [cmdletbinding(DefaultParameterSetName = 'Excel')] param( [parameter( Position = 0, ParameterSetName = 'Excel', Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$false)] [OfficeOpenXml.ExcelPackage]$Excel, [parameter( Position = 0, ParameterSetName = 'File', Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$false)] [validatescript({Test-Path $_})] [string]$Path, [parameter( Position = 1, Mandatory=$false, ValueFromPipeline=$false, ValueFromPipelineByPropertyName=$false)] [string]$PivotTableName = '*', [parameter( Position = 2, Mandatory=$false, ValueFromPipeline=$false, ValueFromPipelineByPropertyName=$false)] [string]$TargetWorkSheetName, [string]$ChartName = 'Chart1', [parameter( Mandatory=$true, ValueFromPipeline=$false, ValueFromPipelineByPropertyName=$false)] [OfficeOpenXml.Drawing.Chart.eChartType]$ChartType, [string]$ChartTitle, [int]$ChartWidth = 600, [int]$ChartHeight = 400, [switch]$Passthru ) Process { Write-Verbose "PSBoundParameters: $($PSBoundParameters | Out-String)" $SourceWS = @{} #Find sheets with pivot tables Try { if($PSCmdlet.ParameterSetName -like 'File') { $Excel = New-Excel -Path $Path -ErrorAction Stop } $PivotTableWorkSheets = @( $Excel | Get-Worksheet -ErrorAction Stop | Where-Object {$_.PivotTables} ) } Catch { Throw "Could not get ExcelPackage or Worksheets to search: $_" } #Filter those tables If($PivotTableWorkSheets.Count -eq 0) { Throw "Something went wrong, we didn't find any worksheets with a pivot table" } else { $PivotTables = @( $PivotTableWorkSheets | Select -ExpandProperty PivotTables | Where-Object {$_.Name -Like $PivotTableName}) } if($PivotTables.count -gt 0) { Foreach($PivotTable in $PivotTables) { #No chart name? Take the pivottable name, prepend CT if(-not $PSBoundParameters.ContainsKey('ChartName')) { $ChartName = "CT-$( $PivotTable.Name )" } #We need a worksheet for the chart if( @( $Excel.WorkBook.Worksheets | Select -ExpandProperty Name -ErrorAction SilentlyContinue) -notcontains $TargetWorkSheetName) { $TargetWorkSheet = $Excel.Workbook.Worksheets | Where-Object {$_.Name -like $PivotTable.Worksheet.Name} Write-Verbose "Could not find target worksheet '$TargetWorkSheetName', picking $($TargetWorkSheet.Name)" } else { $TargetWorkSheet = $Excel.Workbook.Worksheets[$TargetWorkSheetName] } #We need to avoid dupes if( @( $TargetWorkSheet.Drawings | Select -ExpandProperty Name -ErrorAction SilentlyContinue) -contains $ChartName) { Write-Error "Duplicate drawing found for ChartName '$ChartName', please specify a unique chart name" continue } #We have all we need, create the chart! Write-Verbose "Adding $ChartType chart" $chart = $TargetWorkSheet.Drawings.AddChart("$ChartName", $ChartType, $PivotTable) $chart.SetPosition(1, 0, 6, 0) $chart.SetSize($ChartWidth, $ChartHeight) if($ChartTitle) { $chart.title.text = $ChartTitle } } } else { Throw "Found no pivot tables matching '$PivotTableName'. Existing pivot tables:`n$($PivotTableWorkSheets | Select -ExpandProperty PivotTables | Select -ExpandProperty Name )" } #Clean up if($PSCmdlet.ParameterSetName -like 'File' -and -not $Passthru) { Write-Verbose "Saving '$($Excel.File)'" $Excel.save() $Excel.Dispose() } if($Passthru) { $Excel } } } |