Have you ever worked with Excel files where you wanted to match and compare Columns/Rows? In the past, this has been quite difficult tasks to achieve using the native commands within PowerShell.
So have you heard of the PowerShell module ImportExcel?
It’s is a PowerShell module that is available on the PowerShell Gallery and introduces a number of functions that allow you to work with Excel files using the good old blue background.
Merge-Worksheet [-Referencefile] [-Differencefile] [[-WorksheetName] ] [-Startrow ]
-Headername [[-OutputFile] ] [[-OutputSheetName] ] [-Property ] [-ExcludeProperty ]
[-Key ] [-KeyFontColor ] [-ChangeBackgroundColor ] [-DeleteBackgroundColor ]
[-AddBackgroundColor ] [-HideEqual] [-Passthru] [-Show] [-WhatIf] [-Confirm] 
Example usage of Function
The below shows the Reference and Difference Excel files that are being used in this example. I am going to merge the two excel files based on Column A the EmployeeNumber. During my testing, I have had issues using -HeaderName parameter. In this
$ref = “$env:USERPROFILE\desktop\test\ref.xlsx”
$dif = “$env:USERPROFILE\desktop\test\dif.xlsx”
$out = “$env:USERPROFILE\desktop\test\out.xlsx”
# Script Block
Merge-Worksheet -Referencefile $ref -Differencefile $dif -OutputFile $out -WorksheetName Sheet1 -Startrow 1 -OutputSheetName Sheet1 -NoHeader
As we can see from below, the output field has organised Column A and aligned the rows
Every useful if you are working with Excel files but only annoying thing is the HeaderName parameter not working.
The Author – Blogabout.Cloud