< Back

Merge-Worksheet

Wed Jan 15, 2020 1:00 am

NAME Merge-Worksheet



SYNOPSIS

Merges two Worksheets (or other objects) into a single Worksheet with differences marked up.





SYNTAX

Merge-Worksheet [-Referencefile] <Object> [-Differencefile] <Object> [[-WorksheetName] <Object>] [[-OutputFile]

<Object>] [[-OutputSheetName] <Object>] [-Startrow <Int32>] -NoHeader [-Property <Object>] [-ExcludeProperty

<Object>] [-Key <Object>] [-KeyFontColor <Object>] [-ChangeBackgroundColor <Object>] [-DeleteBackgroundColor

<Object>] [-AddBackgroundColor <Object>] [-HideEqual] [-Passthru] [-Show] [-WhatIf] [-Confirm] [<CommonParameters>]



Merge-Worksheet [-Referencefile] <Object> [-Differencefile] <Object> [[-WorksheetName] <Object>] [[-OutputFile]

<Object>] [[-OutputSheetName] <Object>] [-Startrow <Int32>] -Headername <String[]> [-Property <Object>]

[-ExcludeProperty <Object>] [-Key <Object>] [-KeyFontColor <Object>] [-ChangeBackgroundColor <Object>]

[-DeleteBackgroundColor <Object>] [-AddBackgroundColor <Object>] [-HideEqual] [-Passthru] [-Show] [-WhatIf]

[-Confirm] [<CommonParameters>]



Merge-Worksheet [-Referencefile] <Object> [-Differencefile] <Object> [[-WorksheetName] <Object>] [[-OutputFile]

<Object>] [[-OutputSheetName] <Object>] [-Startrow <Int32>] [-Property <Object>] [-ExcludeProperty <Object>] [-Key

<Object>] [-KeyFontColor <Object>] [-ChangeBackgroundColor <Object>] [-DeleteBackgroundColor <Object>]

[-AddBackgroundColor <Object>] [-HideEqual] [-Passthru] [-Show] [-WhatIf] [-Confirm] [<CommonParameters>]



Merge-Worksheet [-Differencefile] <Object> [[-WorksheetName] <Object>] [[-DiffPrefix] <Object>] [[-OutputFile]

<Object>] [[-OutputSheetName] <Object>] [-Startrow <Int32>] -NoHeader -ReferenceObject <Object> [-Property

<Object>] [-ExcludeProperty <Object>] [-Key <Object>] [-KeyFontColor <Object>] [-ChangeBackgroundColor <Object>]

[-DeleteBackgroundColor <Object>] [-AddBackgroundColor <Object>] [-HideEqual] [-Passthru] [-Show] [-WhatIf]

[-Confirm] [<CommonParameters>]



Merge-Worksheet [-Differencefile] <Object> [[-WorksheetName] <Object>] [[-DiffPrefix] <Object>] [[-OutputFile]

<Object>] [[-OutputSheetName] <Object>] [-Startrow <Int32>] -Headername <String[]> -ReferenceObject <Object>

[-Property <Object>] [-ExcludeProperty <Object>] [-Key <Object>] [-KeyFontColor <Object>] [-ChangeBackgroundColor

<Object>] [-DeleteBackgroundColor <Object>] [-AddBackgroundColor <Object>] [-HideEqual] [-Passthru] [-Show]

[-WhatIf] [-Confirm] [<CommonParameters>]



Merge-Worksheet [-Differencefile] <Object> [[-WorksheetName] <Object>] [[-DiffPrefix] <Object>] [[-OutputFile]

<Object>] [[-OutputSheetName] <Object>] [-Startrow <Int32>] -ReferenceObject <Object> [-Property <Object>]

[-ExcludeProperty <Object>] [-Key <Object>] [-KeyFontColor <Object>] [-ChangeBackgroundColor <Object>]

[-DeleteBackgroundColor <Object>] [-AddBackgroundColor <Object>] [-HideEqual] [-Passthru] [-Show] [-WhatIf]

[-Confirm] [<CommonParameters>]



Merge-Worksheet [-DifferenceObject] <Object> [[-DiffPrefix] <Object>] [[-OutputFile] <Object>] [[-OutputSheetName]

<Object>] -ReferenceObject <Object> [-Property <Object>] [-ExcludeProperty <Object>] [-Key <Object>]

[-KeyFontColor <Object>] [-ChangeBackgroundColor <Object>] [-DeleteBackgroundColor <Object>] [-AddBackgroundColor

<Object>] [-HideEqual] [-Passthru] [-Show] [-WhatIf] [-Confirm] [<CommonParameters>]





DESCRIPTION

The Compare-Worksheet command takes two Worksheets and marks differences in the source document, and optionally

outputs a grid showing the changes.



By contrast the Merge-Worksheet command takes the Worksheets and combines them into a single sheet showing the old

and new data side by side. Although it is designed to work with Excel data it can work with arrays of any kind of

object; so it can be a merge of Worksheets, or a merge to a Worksheet.





PARAMETERS

-Referencefile <Object>

First Excel file to compare. You can compare two Excel files or two other objects or a reference obhct against

a difference file, but not a reference file against an object. works with the following parameter sets



* A = Compare two files default headers



* B = Compare two files user supplied headers



* C = Compare two files headers P1, P2, P3 etc



Required? true

Position? 1

Default value None

Accept pipeline input? False

Accept wildcard characters? false



-Differencefile <Object>

Second Excel file to compare. Works with paramter sets A,B,C as well as the following



* D = Compare two objects;



* E = Compare one object one file that uses default headers



* F = Compare one object one file that uses user supplied headers



* G = Compare one object one file that uses headers P1, P2, P3 etc



Required? true

Position? 2

Default value None

Accept pipeline input? False

Accept wildcard characters? false



-WorksheetName <Object>

Name(s) of Worksheets to compare. Applies to all parameter sets EXCEPT D which is two objects (no sheets)



Required? false

Position? 3

Default value Sheet1

Accept pipeline input? False

Accept wildcard characters? false



-Startrow <Int32>

The row from where we start to import data, all rows above the StartRow are disregarded. By default this is

the first row. Applies to all sets EXCEPT D which is two objects (no sheets, so no start row )



Required? false

Position? named

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.

Works with the following parameter sets:



* B 2 sheets with user supplied headers



* F Compare object + sheet



Required? true

Position? named

Default value None

Accept pipeline input? False

Accept wildcard characters? false



-NoHeader [<SwitchParameter>]

Automatically generate property names (P1, P2, P3, ..) instead of using the values the top row of the sheet.

Works with parameter sets



* C 2 sheets with headers of P1, P2, P3 ...



* G Compare object + sheet



Required? true

Position? named

Default value False

Accept pipeline input? False

Accept wildcard characters? false



-ReferenceObject <Object>

Reference object to compare if a Worksheet is NOT being used. Reference object can combine with a difference

sheet or difference object



Required? true

Position? named

Default value None

Accept pipeline input? False

Accept wildcard characters? false



-DifferenceObject <Object>

Difference object to compare if a Worksheet is NOT being used for either half. Can't have a reference sheet

and difference object.



Required? true

Position? 2

Default value None

Accept pipeline input? False

Accept wildcard characters? false



-DiffPrefix <Object>

If there isn't a filename to use to label data from the "Difference" side, DiffPrefix is used, it defaults to

"=>"



Required? false

Position? 3

Default value =>

Accept pipeline input? False

Accept wildcard characters? false



-OutputFile <Object>

File to hold merged data.



Required? false

Position? 4

Default value None

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? 5

Default value Sheet1

Accept pipeline input? False

Accept wildcard characters? false



-Property <Object>

Properties to include in the DIFF - supports wildcards, default is "*".



Required? false

Position? named

Default value *

Accept pipeline input? False

Accept wildcard characters? false



-ExcludeProperty <Object>

Properties to exclude from the the search - supports wildcards.



Required? false

Position? named

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 refence and difference side, default is "Name".



Required? false

Position? named

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? named

Default value [System.Drawing.Color]::DarkRed

Accept pipeline input? False

Accept wildcard characters? false



-ChangeBackgroundColor <Object>

Sets the background color for changed rows.



Required? false

Position? named

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? named

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? named

Default value [System.Drawing.Color]::PaleGreen

Accept pipeline input? False

Accept wildcard characters? false



-HideEqual [<SwitchParameter>]

if specified, hides the rows in the spreadsheet that are equal and only shows changes, added or deleted rows.



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 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



-WhatIf [<SwitchParameter>]

Shows what would happen if the cmdlet runs. The cmdlet is not run.



Required? false

Position? named

Default value False

Accept pipeline input? False

Accept wildcard characters? false



-Confirm [<SwitchParameter>]

Prompts you for confirmation before running the cmdlet.



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\\> Merge-Worksheet "Server54.xlsx" "Server55.xlsx" -WorksheetName services -OutputFile Services.xlsx

-OutputSheetName 54-55 -show



The workbooks contain audit information for two servers, one sheet contains a list of services.



This command creates a worksheet named "54-55" in a workbook named "services.xlsx" which shows all the services

and their differences, and opens the new workbook in Excel.

-------------------------- EXAMPLE 2 --------------------------



PS\\> Merge-Worksheet "Server54.xlsx" "Server55.xlsx" -WorksheetName services -OutputFile Services.xlsx

-OutputSheetName 54-55 -HideEqual -AddBackgroundColor LightBlue -show



This modifies the previous command to hide the equal rows in the output sheet and changes the color used to mark

rows added to the second file.

-------------------------- EXAMPLE 3 --------------------------



PS\\> Merge-Worksheet -OutputFile .\\j1.xlsx -OutputSheetName test11 -ReferenceObject (dir .\\ImportExcel\\4.0.7)

-DifferenceObject (dir .\\ImportExcel\\4.0.8) -Property Length -Show



This version compares two directories, and marks what has changed. Because no "Key" property is given, "Name" is

assumed to be the key and the only other property examined is length. Files which are added or deleted or have

changed size will be highlighed in the output sheet. Changes to dates or other attributes will be ignored.

-------------------------- EXAMPLE 4 --------------------------



PS\\> Merge-Worksheet -RefO (dir .\\ImportExcel\\4.0.7) -DiffO (dir .\\ImportExcel\\4.0.8) -Pr Length | Out-GridView



This time no file is written and the results - which include all properties, not just length, are output and sent

to Out-Gridview.



This version uses aliases to shorten the parameters, (OutputFileName can be "outFile" and the Sheet can

be"OutSheet"; DifferenceObject & ReferenceObject can be DiffObject & RefObject respectively).



RELATED LINKS

Online Version: https://github.com/dfinke/ImportExcel