Open-ExcelPackage.ps1

Function Open-ExcelPackage  {
<#
.Synopsis 
    Returns an Excel Package Object with for the specified XLSX ile 
.Example
    $excel  = Open-ExcelPackage -path $xlPath 
    $sheet1 = $excel.Workbook.Worksheets["sheet1"] 
    set-Format -Address $sheet1.Cells["E1:S1048576"], $sheet1.Cells["V1:V1048576"]  -NFormat ([cultureinfo]::CurrentCulture.DateTimeFormat.ShortDatePattern) 
    close-ExcelPackage $excel -Show

   This will open the file at $xlPath, select sheet1 apply formatting to two blocks of the sheet and close the package 
#>
    [OutputType([OfficeOpenXml.ExcelPackage])]
    Param ([Parameter(Mandatory=$true)]$path,
           [switch]$KillExcel)

        if($KillExcel)         {
            Get-Process -Name "excel" -ErrorAction Ignore | Stop-Process
            while (Get-Process -Name "excel" -ErrorAction Ignore) {}
        }

        $Path          = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($Path)
        if (Test-Path $path) {New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Path }
        Else                 {Write-Warning "Could not find $path" } 
 }

Function Close-ExcelPackage {
<#
.Synopsis 
    Closes an Excel Package, saving, saving under a new name or abandoning changes and opening the file as required 
#>
    Param (
    #File to close
    [parameter(Mandatory=$true, ValueFromPipeline=$true)]
    [OfficeOpenXml.ExcelPackage]$ExcelPackage,
    #Open the file
    [switch]$Show, 
    #Abandon the file without saving
    [Switch]$NoSave,
    #Save file with a new name (ignored if -NoSaveSpecified)
    $SaveAs
    )
    if ( $NoSave)      {$ExcelPackage.Dispose()}
    else {
          if ($SaveAs) {$ExcelPackage.SaveAs( $SaveAs ) } 
          Else         {$ExcelPackage.Save(); $SaveAs = $ExcelPackage.File.FullName }
          $ExcelPackage.Dispose() 
          if ($show)   {Start-Process -FilePath $SaveAs } 
    }
}