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.
CODE:
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. #>
[CmdletBinding()]
param
(
[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,
[Parameter(Mandatory=$false)]
[switch] $OverWrite,
[Parameter(Mandatory=$false)]
[switch] $Open
)
BEGIN{
} # end BEGIN
PROCESS {
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))){
continue
}
}
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{
} # end PROCESS
} # end FUNCTION
powerwisescripting.blog 