ImportExcel

7.1.0

Public/Copy-ExcelWorksheet.ps1

function Copy-ExcelWorksheet {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true,ValueFromPipeline=$true)]
        [Alias('SourceWorkbook')]
        $SourceObject,
        $SourceWorkSheet = 1 ,
        [Parameter(Mandatory = $true)]
        $DestinationWorkbook,
        $DestinationWorksheet,
        [Switch]$Show
    )
    begin {
        #For the case where we are piped multiple sheets, we want to open the destination in the begin and close it in the end.
        if ($DestinationWorkbook -is [OfficeOpenXml.ExcelPackage] ) {
            if ($Show) {$package2 = $DestinationWorkbook}
            $DestinationWorkbook  = $DestinationWorkbook.Workbook
        }
        elseif ($DestinationWorkbook -is [string] -and ($DestinationWorkbook -ne $SourceObject)) {
            $package2 = Open-ExcelPackage -Create  -Path $DestinationWorkbook
            $DestinationWorkbook = $package2.Workbook
        }
    }
    process {
        #Special case - given the same path for source and destination worksheet
        if ($SourceObject -is [System.String] -and $SourceObject -eq $DestinationWorkbook) {
            if (-not $DestinationWorksheet) {Write-Warning -Message "You must specify a destination worksheet name if copying within the same workbook."; return}
            else {
                Write-Verbose -Message "Copying "
                $excel = Open-ExcelPackage -Path $SourceObject
                if (-not $excel.Workbook.Worksheets[$Sourceworksheet]) {
                    Write-Warning -Message "Could not find Worksheet $sourceWorksheet in $SourceObject"
                    Close-ExcelPackage -ExcelPackage $excel -NoSave
                    return
                }
                elseif ($excel.Workbook.Worksheets[$Sourceworksheet].name -eq $DestinationWorksheet) {
                    Write-Warning -Message "The destination worksheet name is the same as the source. "
                    Close-ExcelPackage -ExcelPackage $excel -NoSave
                    return
                }
                else {
                    $null = Add-Worksheet -ExcelPackage $excel -WorkSheetname $DestinationWorksheet -CopySource ($excel.Workbook.Worksheets[$SourceWorkSheet])
                    Close-ExcelPackage -ExcelPackage $excel -Show:$Show
                    return
                }
            }
        }
        else {
            if     ($SourceObject -is [OfficeOpenXml.ExcelWorksheet]) {$sourceWs = $SourceObject}
            elseif ($SourceObject -is [OfficeOpenXml.ExcelWorkbook])  {$sourceWs = $SourceObject.Worksheets[$SourceWorkSheet]}
            elseif ($SourceObject -is [OfficeOpenXml.ExcelPackage] )  {$sourceWs = $SourceObject.Workbook.Worksheets[$SourceWorkSheet]}
            else {
                $SourceObject = (Resolve-Path $SourceObject).ProviderPath
                try {
                    Write-Verbose "Opening worksheet '$Worksheetname' in Excel workbook '$SourceObject'."
                    $stream = New-Object -TypeName System.IO.FileStream -ArgumentList $SourceObject, 'Open', 'Read' , 'ReadWrite'
                    $package1 = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $stream
                    $sourceWs = $Package1.Workbook.Worksheets[$SourceWorkSheet]
                }
                catch {Write-Warning -Message "Could not open $SourceObject - the error was '$($_.exception.message)' " ; return}
            }
            if (-not $sourceWs) {Write-Warning -Message "Could not find worksheet '$Sourceworksheet' in the source workbook." ; return}
            else {
                try {
                    if ($DestinationWorkbook -isnot [OfficeOpenXml.ExcelWorkbook]) {
                        Write-Warning "Not a valid workbook" ; return
                    }
                    #check if we have a destination sheet name and set one if not. Because we might loop round check $psBoundParameters, not the variable.
                    if (-not $PSBoundParameters['DestinationWorksheet']) {
                        #if we are piped files, use the file name without the extension as the destination sheet name, Otherwise use the source sheet name
                        if ($_ -is [System.IO.FileInfo]) {$DestinationWorksheet = $_.name -replace '\.xlsx$', '' }
                        else { $DestinationWorksheet = $sourceWs.Name}
                    }
                    if ($DestinationWorkbook.Worksheets[$DestinationWorksheet]) {
                        Write-Verbose "Destination workbook already has a sheet named '$DestinationWorksheet', deleting it."
                        $DestinationWorkbook.Worksheets.Delete($DestinationWorksheet)
                    }
                    Write-Verbose "Copying '$($sourcews.name)' from $($SourceObject) to '$($DestinationWorksheet)' in $($PSBoundParameters['DestinationWorkbook'])"
                    $null = Add-Worksheet -ExcelWorkbook $DestinationWorkbook -WorkSheetname $DestinationWorksheet -CopySource  $sourceWs
                    #Leave the destination open but close the source - if we're copying more than one sheet we'll re-open it and live with the inefficiency
                    if ($stream)   {$stream.Close()                                        }
                    if ($package1) {Close-ExcelPackage -ExcelPackage $package1 -NoSave     }
                }
                catch {Write-Warning -Message "Could not write to sheet '$DestinationWorksheet' in the destination workbook. Error was '$($_.exception.message)'" ; return}
            }
        }
    }
    end {
        #OK Now we can close the destination package
        if ($package2) {Close-ExcelPackage -ExcelPackage $package2 -Show:$Show }
        if ($Show -and -not $package2) {
            Write-Warning -Message "-Show only works if the Destination workbook is given as a file path or an ExcelPackage object."
        }
    }
}