Public/Open-ExcelPackage.ps1
|
function Open-ExcelPackage { [CmdLetBinding()] [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingPlainTextForPassword", "")] [OutputType([OfficeOpenXml.ExcelPackage])] param( #The path to the file to open. [Parameter(Mandatory = $true)]$Path, #If specified, any running instances of Excel will be terminated before opening the file. [switch]$KillExcel, #The password for a protected worksheet, as a [normal] string (not a secure string). [String]$Password, #By default Open-ExcelPackage will only opens an existing file; -Create instructs it to create a new file if required. [switch]$Create ) if ($KillExcel) { Get-Process -Name "excel" -ErrorAction Ignore | Stop-Process while (Get-Process -Name "excel" -ErrorAction Ignore) {} } $Path = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($Path) #If -Create was not specified only open the file if it exists already (send a warning if it doesn't exist). if ($Create -and -not (Test-Path -Path $path)) { #Create the directory if required. $targetPath = Split-Path -Parent -Path $Path if (!(Test-Path -Path $targetPath)) { Write-Debug "Base path $($targetPath) does not exist, creating" $null = New-item -ItemType Directory -Path $targetPath -ErrorAction Ignore } New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Path } elseif (Test-Path -Path $path) { if ($Password) { $pkgobj = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Path , $Password } else { $pkgobj = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Path } if ($pkgobj) { foreach ($w in $pkgobj.Workbook.Worksheets) { $sb = [scriptblock]::Create(('$this.workbook.Worksheets["{0}"]' -f $w.name)) try { Add-Member -InputObject $pkgobj -MemberType ScriptProperty -Name $w.name -Value $sb -ErrorAction Stop } catch { Write-Warning "Could not add sheet $($w.name) as 'short cut', you need to access it via `$wb.Worksheets['$($w.name)'] " } } return $pkgobj } } else { Write-Warning "Could not find $path" } } |