< Back
Merge-MultipleSheets
Post
NAME Merge-MultipleSheets
SYNOPSIS
Merges Worksheets into a single Worksheet with differences marked up.
SYNTAX
Merge-MultipleSheets [-Path] <Object> [[-KeyFontColor] <Object>] [[-ChangeBackgroundColor] <Object>]
[[-DeleteBackgroundColor] <Object>] [[-AddBackgroundColor] <Object>] [[-Startrow] <Int32>] [[-Headername]
<String[]>] [[-WorksheetName] <Object>] [[-OutputFile] <Object>] [[-OutputSheetName] <Object>] [[-Property]
<Object>] [[-ExcludeProperty] <Object>] [[-Key] <Object>] [-NoHeader] [-HideRowNumbers] [-Passthru] [-Show]
[<CommonParameters>]
DESCRIPTION
The Merge Worksheet command combines two sheets. Merge-MultipleSheets is designed to merge more than two.
If asked to merge sheets A,B,C which contain Services, with a Name, Displayname and Start mode, where "Name" is
treated as the key, Merge-MultipleSheets:
* Calls Merge-Worksheet to merge "Name", "Displayname" and "Startmode" from sheets A and C; the result has column
headings "_Row", "Name", "DisplayName", "Startmode", "C-DisplayName", "C-StartMode", "C-Is" and "C-Row".
* Calls Merge-Worksheet again passing it the intermediate result and sheet B, comparing "Name", "Displayname" and
"Start mode" columns on each side, and gets a result with columns "_Row", "Name", "DisplayName", "Startmode",
"B-DisplayName", "B-StartMode", "B-Is", "B-Row", "C-DisplayName", "C-StartMode", "C-Is" and "C-Row".
Any columns on the "reference" side which are not used in the comparison are added on the right, which is why we
compare the sheets in reverse order.
The "Is" columns hold "Same", "Added", "Removed" or "Changed" and is used for conditional formatting in the output
sheet (these columns are hidden by default), and when the data is written to Excel the "reference" columns, in
this case "DisplayName" and "Start" are renamed to reflect their source, so they become "A-DisplayName" and
"A-Start".
Conditional formatting is also applied to the Key column ("Name" in this case) so the view can be filtered to rows
with changes by filtering this column on color.
Note: the processing order can affect what is seen as a change.For example, if there is an extra item in sheet B
in the example above, Sheet C will be processed first and that row and will not be seen to be missing. When sheet
B is processed it is marked as an addition, and the conditional formatting marks the entries from sheet A to show
that a values were added in at least one sheet.
However if Sheet B is the reference sheet, A and C will be seen to have an item removed; and if B is processed
before C, the extra item is known when C is processed and so C is considered to be missing that item.
PARAMETERS
-Path <Object>
Paths to the files to be merged. Files are also accepted
Required? true
Position? 1
Default value None
Accept pipeline input? True (ByValue)
Accept wildcard characters? false
-Startrow <Int32>
The row from where we start to import data, all rows above the Start row are disregarded. By default this is
the first row.
Required? false
Position? 2
Default value 1
Accept pipeline input? False
Accept wildcard characters? false
-Headername <String[]>
Specifies custom property names to use, instead of the values defined in the column headers of the Start row.
Required? false
Position? 3
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-NoHeader [<SwitchParameter>]
If specified, property names will be automatically generated (P1, P2, P3, ..) instead of using the values from
the start row.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-WorksheetName <Object>
Name(s) of Worksheets to compare.
Required? false
Position? 4
Default value Sheet1
Accept pipeline input? False
Accept wildcard characters? false
-OutputFile <Object>
File to write output to.
Required? false
Position? 5
Default value .\\temp.xlsx
Accept pipeline input? False
Accept wildcard characters? false
-OutputSheetName <Object>
Name of Worksheet to output - if none specified will use the reference Worksheet name.
Required? false
Position? 6
Default value Sheet1
Accept pipeline input? False
Accept wildcard characters? false
-Property <Object>
Properties to include in the comparison - supports wildcards, default is "*".
Required? false
Position? 7
Default value *
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeProperty <Object>
Properties to exclude from the the comparison - supports wildcards.
Required? false
Position? 8
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Key <Object>
Name of a column which is unique used to pair up rows from the reference and difference sides, default is
"Name".
Required? false
Position? 9
Default value Name
Accept pipeline input? False
Accept wildcard characters? false
-KeyFontColor <Object>
Sets the font color for the Key field; this means you can filter by color to get only changed rows.
Required? false
Position? 10
Default value [System.Drawing.Color]::Red
Accept pipeline input? False
Accept wildcard characters? false
-ChangeBackgroundColor <Object>
Sets the background color for changed rows.
Required? false
Position? 11
Default value [System.Drawing.Color]::Orange
Accept pipeline input? False
Accept wildcard characters? false
-DeleteBackgroundColor <Object>
Sets the background color for rows in the reference but deleted from the difference sheet.
Required? false
Position? 12
Default value [System.Drawing.Color]::LightPink
Accept pipeline input? False
Accept wildcard characters? false
-AddBackgroundColor <Object>
Sets the background color for rows not in the reference but added to the difference sheet.
Required? false
Position? 13
Default value [System.Drawing.Color]::Orange
Accept pipeline input? False
Accept wildcard characters? false
-HideRowNumbers [<SwitchParameter>]
If specified, hides the columns in the spreadsheet that contain the row numbers.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<SwitchParameter>]
If specified, outputs the data to the pipeline (you can add -whatif so it the command only outputs to the
pipeline).
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Show [<SwitchParameter>]
If specified, opens the output workbook.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> dir Server*.xlsx | Merge-MulipleSheets -WorksheetName Services -OutputFile Test2.xlsx -OutputSheetName
Services -Show
Here we are auditing servers and each one has a workbook in the current directory which contains a "Services"
Worksheet (the result of Get-WmiObject -Class win32_service | Select-Object -Property Name, Displayname,
Startmode). No key is specified so the key is assumed to be the "Name" column. The files are merged and the
result is opened on completion.
-------------------------- EXAMPLE 2 --------------------------
PS\\> dir Serv*.xlsx | Merge-MulipleSheets -WorksheetName Software -Key "*" -ExcludeProperty Install* -OutputFile
Test2.xlsx -OutputSheetName Software -Show
The server audit files in the previous example also have "Software" worksheet, but no single field on that sheet
works as a key. Specifying "*" for the key produces a compound key using all non-excluded fields (and the
installation date and file location are excluded).
-------------------------- EXAMPLE 3 --------------------------
Merge-MulipleSheets -Path hotfixes.xlsx -WorksheetName Serv* -Key hotfixid -OutputFile test2.xlsx -OutputSheetName
hotfixes -HideRowNumbers -Show
This time all the servers have written their hotfix information to their own worksheets in a shared Excel workbook
named "Hotfixes.xlsx" (the information was obtained by running Get-Hotfix | Sort-Object -Property
description,hotfixid | Select-Object -Property Description,HotfixID) This ignores any sheets which are not named
"Serv*", and uses the HotfixID as the key; in this version the row numbers are hidden.
RELATED LINKS
Online Version: https://github.com/dfinke/ImportExcel
SYNOPSIS
Merges Worksheets into a single Worksheet with differences marked up.
SYNTAX
Merge-MultipleSheets [-Path] <Object> [[-KeyFontColor] <Object>] [[-ChangeBackgroundColor] <Object>]
[[-DeleteBackgroundColor] <Object>] [[-AddBackgroundColor] <Object>] [[-Startrow] <Int32>] [[-Headername]
<String[]>] [[-WorksheetName] <Object>] [[-OutputFile] <Object>] [[-OutputSheetName] <Object>] [[-Property]
<Object>] [[-ExcludeProperty] <Object>] [[-Key] <Object>] [-NoHeader] [-HideRowNumbers] [-Passthru] [-Show]
[<CommonParameters>]
DESCRIPTION
The Merge Worksheet command combines two sheets. Merge-MultipleSheets is designed to merge more than two.
If asked to merge sheets A,B,C which contain Services, with a Name, Displayname and Start mode, where "Name" is
treated as the key, Merge-MultipleSheets:
* Calls Merge-Worksheet to merge "Name", "Displayname" and "Startmode" from sheets A and C; the result has column
headings "_Row", "Name", "DisplayName", "Startmode", "C-DisplayName", "C-StartMode", "C-Is" and "C-Row".
* Calls Merge-Worksheet again passing it the intermediate result and sheet B, comparing "Name", "Displayname" and
"Start mode" columns on each side, and gets a result with columns "_Row", "Name", "DisplayName", "Startmode",
"B-DisplayName", "B-StartMode", "B-Is", "B-Row", "C-DisplayName", "C-StartMode", "C-Is" and "C-Row".
Any columns on the "reference" side which are not used in the comparison are added on the right, which is why we
compare the sheets in reverse order.
The "Is" columns hold "Same", "Added", "Removed" or "Changed" and is used for conditional formatting in the output
sheet (these columns are hidden by default), and when the data is written to Excel the "reference" columns, in
this case "DisplayName" and "Start" are renamed to reflect their source, so they become "A-DisplayName" and
"A-Start".
Conditional formatting is also applied to the Key column ("Name" in this case) so the view can be filtered to rows
with changes by filtering this column on color.
Note: the processing order can affect what is seen as a change.For example, if there is an extra item in sheet B
in the example above, Sheet C will be processed first and that row and will not be seen to be missing. When sheet
B is processed it is marked as an addition, and the conditional formatting marks the entries from sheet A to show
that a values were added in at least one sheet.
However if Sheet B is the reference sheet, A and C will be seen to have an item removed; and if B is processed
before C, the extra item is known when C is processed and so C is considered to be missing that item.
PARAMETERS
-Path <Object>
Paths to the files to be merged. Files are also accepted
Required? true
Position? 1
Default value None
Accept pipeline input? True (ByValue)
Accept wildcard characters? false
-Startrow <Int32>
The row from where we start to import data, all rows above the Start row are disregarded. By default this is
the first row.
Required? false
Position? 2
Default value 1
Accept pipeline input? False
Accept wildcard characters? false
-Headername <String[]>
Specifies custom property names to use, instead of the values defined in the column headers of the Start row.
Required? false
Position? 3
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-NoHeader [<SwitchParameter>]
If specified, property names will be automatically generated (P1, P2, P3, ..) instead of using the values from
the start row.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-WorksheetName <Object>
Name(s) of Worksheets to compare.
Required? false
Position? 4
Default value Sheet1
Accept pipeline input? False
Accept wildcard characters? false
-OutputFile <Object>
File to write output to.
Required? false
Position? 5
Default value .\\temp.xlsx
Accept pipeline input? False
Accept wildcard characters? false
-OutputSheetName <Object>
Name of Worksheet to output - if none specified will use the reference Worksheet name.
Required? false
Position? 6
Default value Sheet1
Accept pipeline input? False
Accept wildcard characters? false
-Property <Object>
Properties to include in the comparison - supports wildcards, default is "*".
Required? false
Position? 7
Default value *
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeProperty <Object>
Properties to exclude from the the comparison - supports wildcards.
Required? false
Position? 8
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-Key <Object>
Name of a column which is unique used to pair up rows from the reference and difference sides, default is
"Name".
Required? false
Position? 9
Default value Name
Accept pipeline input? False
Accept wildcard characters? false
-KeyFontColor <Object>
Sets the font color for the Key field; this means you can filter by color to get only changed rows.
Required? false
Position? 10
Default value [System.Drawing.Color]::Red
Accept pipeline input? False
Accept wildcard characters? false
-ChangeBackgroundColor <Object>
Sets the background color for changed rows.
Required? false
Position? 11
Default value [System.Drawing.Color]::Orange
Accept pipeline input? False
Accept wildcard characters? false
-DeleteBackgroundColor <Object>
Sets the background color for rows in the reference but deleted from the difference sheet.
Required? false
Position? 12
Default value [System.Drawing.Color]::LightPink
Accept pipeline input? False
Accept wildcard characters? false
-AddBackgroundColor <Object>
Sets the background color for rows not in the reference but added to the difference sheet.
Required? false
Position? 13
Default value [System.Drawing.Color]::Orange
Accept pipeline input? False
Accept wildcard characters? false
-HideRowNumbers [<SwitchParameter>]
If specified, hides the columns in the spreadsheet that contain the row numbers.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Passthru [<SwitchParameter>]
If specified, outputs the data to the pipeline (you can add -whatif so it the command only outputs to the
pipeline).
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
-Show [<SwitchParameter>]
If specified, opens the output workbook.
Required? false
Position? named
Default value False
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> dir Server*.xlsx | Merge-MulipleSheets -WorksheetName Services -OutputFile Test2.xlsx -OutputSheetName
Services -Show
Here we are auditing servers and each one has a workbook in the current directory which contains a "Services"
Worksheet (the result of Get-WmiObject -Class win32_service | Select-Object -Property Name, Displayname,
Startmode). No key is specified so the key is assumed to be the "Name" column. The files are merged and the
result is opened on completion.
-------------------------- EXAMPLE 2 --------------------------
PS\\> dir Serv*.xlsx | Merge-MulipleSheets -WorksheetName Software -Key "*" -ExcludeProperty Install* -OutputFile
Test2.xlsx -OutputSheetName Software -Show
The server audit files in the previous example also have "Software" worksheet, but no single field on that sheet
works as a key. Specifying "*" for the key produces a compound key using all non-excluded fields (and the
installation date and file location are excluded).
-------------------------- EXAMPLE 3 --------------------------
Merge-MulipleSheets -Path hotfixes.xlsx -WorksheetName Serv* -Key hotfixid -OutputFile test2.xlsx -OutputSheetName
hotfixes -HideRowNumbers -Show
This time all the servers have written their hotfix information to their own worksheets in a shared Excel workbook
named "Hotfixes.xlsx" (the information was obtained by running Get-Hotfix | Sort-Object -Property
description,hotfixid | Select-Object -Property Description,HotfixID) This ignores any sheets which are not named
"Serv*", and uses the HotfixID as the key; in this version the row numbers are hidden.
RELATED LINKS
Online Version: https://github.com/dfinke/ImportExcel