Close-Excel.ps1
|
function Close-Excel { <# .SYNOPSIS Close an OfficeOpenXml ExcelPackage .DESCRIPTION Close an OfficeOpenXml ExcelPackage .PARAMETER Excel An ExcelPackage object to close .PARAMETER Save Save the ExcelPackage before closing .PARAMETER Path If specified, Save the ExcelPackage as this path before closing .EXAMPLE Close-Excel -Excel $Excel -Save #Save and close $Excel .EXAMPLE Close-Excel -Excel $Excel #Close $Excel without saving .EXAMPLE Close-Excel -Excel $Excel -Path C:\new.xlsx #Save $Excel as C:\new.xlsx and close .NOTES Thanks to Doug Finke for his example: https://github.com/dfinke/ImportExcel/blob/master/ImportExcel.psm1 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 #> [cmdletbinding()] param( [parameter( Mandatory=$true, ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)] [OfficeOpenXml.ExcelPackage]$Excel, [Switch]$Save, [parameter( Mandatory=$false, ValueFromPipeline=$false, ValueFromPipelineByPropertyName=$false)] [validatescript({ $Parent = Split-Path $_ -Parent -ErrorAction SilentlyContinue if( -not (Test-Path -Path $Parent -PathType Container -ErrorAction SilentlyContinue) ) { Throw "Specify a valid path. Parent '$Parent' does not exist: $_" } $True })] [string]$Path ) Process { foreach($xl in $Excel) { Try { if($Path) { Try { #Resolve relative paths... Thanks Oisin! http://stackoverflow.com/a/3040982/3067642 $Path = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($Path) } Catch { Write-Error "Could not resolve path for '$Path': $_" continue } write-verbose "Saving $($xl.File) as $($Path)" $xl.saveas($Path) } elseif($Save) { write-verbose "Saving $($xl.File)" $xl.save() } } Catch { Write-Error "Error saving file. Will not close this ExcelPackage: $_" Continue } Try { write-verbose "Closing $($xl.File)" $xl.Dispose() $xl = $null } Catch { Write-Error $_ Continue } } } } |