#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

How To: Merge Multiple Spreadsheets Using New-XLSXWorkBook

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

Recently, I had created a couple of reports, or rather spreadsheets, and realized this information would be more useful and easier to distribute, read, etc. if it was all contained in one Excel spreadsheet. So, I created a new function, Merge-XLSXWorkbooks using the New-XLSXWorkbook cmdlet available with the PWPS_DAB module.

# Look at the help for New-XLSXWorkbook within PowerShell.
Get-Help New-XLSXWorkbook -Full

# Look at the help for Merge-XLSXWorkbooks within PowerShell after importing.
Get-Help Merge-XLSXWorkbooks -Full

The Merge-XLSXWorkbook allows you a few different methods in which to merge spreadsheets.

First, you can pass a folder path to merge all spreadsheets within the folder.

# Pass a path and output filename
Merge-XLSXWorkbooks -Path 'D:\Projects' -OutputFileName 'ConsolidatedReport.xlsx' -Verbose
# Include the -Open switch parameter to open Excel once the new spreadsheet is created.Merge-XLSXWorkbooks -Path 'D:\Projects' -OutputFileName 'ConsolidatedReport.xlsx' -Open -Verbose

# Include the -Overwrite switch parameter to replace the output file if it already exists.Merge-XLSXWorkbooks -Path 'D:\Projects' -OutputFileName 'ConsolidatedReport.xlsx' -Overwrite -Verbose

Second, you can supply multiple spreadsheets to be merged together into a new spreadsheet.

# Using splatting for readability.
$MergeFileInfo = @{
    Path = 'D:\!Projects'
    Spreadsheets = 'SourceSpreadsheet_01.xlsx', 'SourceSpreadsheet_02.xlsx'
    OutputFileName = 'ConsolidatedReport.xlsx'
# Include the -Overwrite switch parameter to overwrite the output file if it already exists. And include the -Open switch parameter to open the output file once created.
Merge-XLSXWorkBooks @MergeFileInfo -OverWrite -Open -Verbose

Give this a try and let me know what you think. Hopefully, you find it useful.


FUNCTION Merge-XLSXWorkbooks {
    <#         .SYNOPSIS         Merge multiple Excel Spreadsheets into a new spreadsheet.        .DESCRIPTION         Merges specified spreadsheets or all spreadsheet within a specified folder. There is an option to overwrite if the output file exists.                     .EXAMPLE         Merge-XLSXWorkBooks -Path 'D:\!Projects' -OutputFileName 'ConsolidatedReport.xlsx' -Open                      In this example, all spreadsheets contained in the !Projects folder with be combined into the ConsolidatedReport.xlsx file.                       The new spreadsheet will be opened once created.                     .EXAMPLE           # Using splatting for readability.                      $MergeFileInfo = @{                  Path = 'D:\!Projects'              Spreadsheets = 'Production_Report.xlsx', 'Production_Template Report_FoldersContainingDocuments.xlsx'              OutputFileName = 'ConsolidatedReport.xlsx'          }                      Merge-XLSXWorkBooks @MergeFileInfo -OverWrite -Verbose          In this example, only the two specified spreadsheets will be merged into the new 'ConsolidatedReport.xlsx'. If the outputfile exists within the specified folder, it will be overwritten.     #>
        [ValidateScript({ Test-Path -Path $_ })]
        [Parameter(Mandatory=$true, Position=0)]
        [System.String] $Path,
        [Parameter(Mandatory=$false, Position=1)]
        [System.String[]] $Spreadsheets,
        [Parameter(Mandatory=$true, Position=2)]
        [System.String] $OutputFileName,
        [switch] $OverWrite,
        [switch] $Open

    } # end BEGIN
        try {
            Write-Verbose -Message "[PROCESS] Getting all Excel file (xlsx, xls) objects from folder '$Path'."
            $ChildItems = Get-ChildItem -Path $Path -Filter '*.xls*' -File -ErrorAction Stop
            if($ChildItems) {
                Write-Verbose -Message ("[PROCESS] {0} documents returned." -f $ChildItems.Count)
            } else {
                Write-Warning -Message "[PROCESS] No documents returned."
        } catch {
            Write-Error -Message ("[PROCESS] {0}" -f $error[0].Exception.Message)
        } # end try/catch

        if($ChildItems) {
            Write-Verbose -Message "[PROCESS] Creating new list to store datatable objects."
            $dtArray = @() #New-Object System.Collections.ArrayList
            # Counter for the progress bar.
            $Count = 1
            Write-Verbose -Message "[PROCESS] Looping through all childitems found."
            foreach ($ChildItem in $ChildItems) {
                $percentComplete = $(($Count / $ChildItems.Count) * 100 )
                $Progress = @{
                    Activity = "Getting information for '$($ChildItem.Name)'."
                    Status = "Processing $Count of $($ChildItems.Count)"
                    PercentComplete = $([math]::Round($percentComplete, 2))
                Write-Progress @Progress -CurrentOperation ChildItems
                # If spreadsheets are included, determine if the $childitem name is contained in the spreadsheets variable.
                # if not, continue to the next childitem.
                if($SpreadSheets) {
                    if(-not ($Spreadsheets.Contains($ChildItem.Name))){
                Write-Verbose -Message ("[PROCESS] Importing worksheets from {0}." -f "$Path\$($ChildItem.Name)")
                $dtFromExcel = Get-TablesFromXLSXWorkbook -InputFileName "$Path\$($ChildItem.Name)"
                Write-Verbose -Message ("[PROCESS] Imported {0} tables from {1}." -f $dtFromExcel.Count, $ChildItem.Name)
                Write-Verbose -Message ("[PROCESS] Adding {0} datatables to the list." -f $dtFromExcel.Count)
                $dtArray += $dtFromExcel
                Write-Verbose -Message ("[PROCESS] Removing {0} variable." -f $dtFromExcel)
                Remove-Variable dtFromExcel
            } # end foreach ($ChildItem...
            $CreateNewFile = $true
            Write-Verbose -Message ("Determine if outputfile '{0}' exists." -f $OutputFileName)              
            if(Test-Path -Path "$Path\$OutputFileName" -PathType Leaf -Verbose) {
                if($OverWrite) {
                    Write-Warning -Message ("Outputfile '{0}' already exists in folder '{1}'. Overwrite is set, therefore existing file will be deleted." -f $OutputFileName, $Path)
                    Remove-Item -Path "$Path\$OutputFileName" -Force -Verbose
                } else {
                    Write-Warning -Message ("Outputfile '{0}' already exists in folder '{1}'. Overwrite is not set, therefore file will not be created." -f $OutputFileName, $Path)
                    $CreateNewFile = $false        
            if($CreateNewFile) {
                try {
                    Write-Verbose -Message ("[PROCESS] Preparing to export {0} datatables to {1}." -f $dtArray.Count, $("$Path\$OutputFileName"))
                    New-XLSXWorkbook -InputTables $dtArray -OutputFileName "$Path\$OutputFileName" -Open:$Open -ErrorAction Stop
                } catch {
                    Write-Error -Message ("[PROCESS] {0}" -f $error[0].Exception.Message)
                } # end try/catch                                  
        } # end if
    } # end PROCESS
    } # end PROCESS
} # end FUNCTION

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.