DiffExcel

1.3.3

DiffExcel.ps1


<#PSScriptInfo
 
.VERSION 1.3.3
 
.GUID c623da06-d9d6-4890-8171-627b0023c972
 
.AUTHOR zhangkq2000@hotmail.com
 
.COMPANYNAME ExcelBDD.com
 
.COPYRIGHT Copyright (c) 2023 by ExcelBDD Team, licensed under Apache 2.0 License.
 
.TAGS Excel Diff Compare
 
.LICENSEURI https://www.apache.org/licenses/LICENSE-2.0.html
 
.PROJECTURI https://dev.azure.com/simplopen/ExcelBDD/_wiki/wikis/ExcelBDD.wiki/39/ExcelBDD-Homepage
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
 
.RELEASENOTES
now supports the following features:
compare two excel files, support git diff after the responding configuration
 
#>

<#
  .SYNOPSIS
  Compare two excel files
  .DESCRIPTION
  Compare two excel files. and support git diff, for example: git diff theChangedExcelFile.
  The git diff config is needed, which is described in the below online version link.
  It provides great supports to ExcelBDD to check changes in excel file.
 
  .INPUTS
  The two files
 
  .OUTPUTS
  None.
 
  .EXAMPLE
  DiffExcel oldfile.xlsx newfile.xlsx
 
  .EXAMPLE
  DiffExcel oldfile.xlsx newfile.xlsx -Open
  #Because -Open is set, then the new file will open in Excel if any change is found.
 
  .EXAMPLE
  git diff theExcelfile.xlsx
  #if git diff config is setup,the above command will invoke DiffExcel automatically to find latest changes after last commit
  
 
  .EXAMPLE
  git diff head~2 theExcelfile.xlsx
 
  .LINK
  Online version: https://dev.azure.com/simplopen/ExcelBDD/_wiki/wikis/ExcelBDD.wiki/57/DiffExcel-Guideline
 
  .LINK
  ExcelBDD website: https://dev.azure.com/simplopen/ExcelBDD/
#> 
[CmdletBinding()]
param (
    [Parameter()]
    [String]
    # Specifies the old file path and name.
    $OldFile,
    [Parameter()]
    [String]
    # Specifies the new file path and name.
    $NewFile,
    [Switch]
    # if Open switch is set, the new Excel file will open if any change is found.
    $Open
)

# $script:StartPath = (Resolve-Path "$PSScriptRoot").Path
# Write-Host $StartPath
# $modulePath = Join-Path $PSScriptRoot "DiffExcel.psm1"
# Import-Module $modulePath
Write-Host "==== DiffExcel ==="
Write-Host " V1.3" 
Write-Host "Author: Zhang Keqiang Mike"
Write-Host "Email: zhangkq2000@hotmail.com"

if ((-not $OldFile) -or (-not $NewFile)) {
    Write-Host "lack of file."
    return
}
if ($NewFile.IndexOf(":") -lt 0) {
    $NewFile = Join-Path $(Get-Location) $NewFile
}
if ($OldFile.IndexOf(":") -lt 0) {
    $OldFile = Join-Path $(Get-Location) $OldFile
}
Write-Host "New File: $NewFile"
Write-Host "Old File: $OldFile"
if ($Open) {
    Write-Host "The new file will open if any change is found."
}

#==================
#Functions of DiffExcel
function Compare-Excel {
    param (
        $OldFile,
        $NewFile,
        [Switch]$Open
    )
    $ExcelApp1 = New-Object -ComObject Excel.Application
    $ExcelApp2 = New-Object -ComObject Excel.Application
    $NewWorkBook = $ExcelApp1.Workbooks.Open($NewFile)
    try {
        $OldWorkBook = $ExcelApp2.Workbooks.Open($OldFile)
    }
    catch {
        Write-Host "Old file is missing."
        return
    }
    
    if ($null -eq $NewWorkBook) {
        Write-Host "New file is missing."
        return
    }
    if ($null -eq $OldWorkBook) {
        Write-Host "Old file is missing."
        return
    }

    $Result = @{}
    $IsChanged = $false
    foreach ($Worksheet in $NewWorkBook.Worksheets) {
        # Write-Host $Worksheet.Name
        try {
            $Result[$Worksheet.Name] = Compare-Worksheet $OldWorkBook.Worksheets[$Worksheet.Name] $Worksheet
            if ($Result[$Worksheet.Name].GetType().Name -ne "String") {
                $IsChanged = $true
            }
        }
        catch {
            # Write-Host $_
            $IsChanged = $true
            $Result[$Worksheet.Name] = "New"
        }
    }

    foreach ($Worksheet in $OldWorkBook.Worksheets) {
        try {
            if (-Not $NewWorkBook.Worksheets[$Worksheet.Name]) {
                $IsChanged = $true
                $Result[$Worksheet.Name] = "Missing worksheet"
            }
        }
        catch {
            # Write-Host $_
            $IsChanged = $true
            $Result[$Worksheet.Name] = "Missing"
        }
    }

    if ($IsChanged) {
        Show-Result $Result
    }
    else {
        Write-Host "No change."
    }

    [void]$OldWorkBook.Close($false)
    [void]$ExcelApp2.Quit()
    [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp2)

    if ($Open -and $IsChanged) {
        $ExcelApp1.Visible = $true
        Write-Host "The new file is open, please check."
    }
    else {
        [void]$NewWorkBook.Close($false)
        [void]$ExcelApp1.Quit()
        [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($ExcelApp1)
    }
    return $Result
}

function Compare-Worksheet {
    param (
        $OldWorksheet,
        $NewWorksheet
    )

    # Write-Host "===Compare-Worksheet==="
    $DiffList = @()
    $NewRowsCount = $NewWorksheet.UsedRange.Row + $NewWorksheet.UsedRange.Rows.Count - 1
    # Write-Host "RowsCount $NewRowsCount"
    $NewColumnsCount = $NewWorksheet.UsedRange.Column + $NewWorksheet.UsedRange.Columns.Count - 1
    # Write-Host "ColumnsCount $NewColumnsCount"

    $OldRowsCount = $OldWorksheet.UsedRange.Row + $OldWorksheet.UsedRange.Rows.Count - 1
    # Write-Host "OldRowsCount $OldRowsCount"
    $OldColumnsCount = $OldWorksheet.UsedRange.Column + $OldWorksheet.UsedRange.Columns.Count - 1
    # Write-Host "OldColumnsCount $OldColumnsCount"
    
    for ($iRow = 1; $iRow -le $NewRowsCount; $iRow++) {
        for ($iColumn = 1; $iColumn -le $NewColumnsCount; $iColumn++) {
            try {
                if ($NewWorksheet.Cells.Item($iRow, $iColumn).Text -cne $OldWorksheet.Cells.Item($iRow, $iColumn).Text) {
                    $DiffItem = [PSCustomObject]@{
                        Cell = "$([char]($iColumn+64))$iRow"
                        # Row = $iRow
                        # Column = $iColumn
                        Old  = $OldWorksheet.Cells.Item($iRow, $iColumn).Text
                        New  = $NewWorksheet.Cells.Item($iRow, $iColumn).Text
                    }
                    $DiffList += $DiffItem
                }
            }
            catch {
                # Write-Host $_
                $DiffItem = [PSCustomObject]@{
                    Cell = "$([char]($iColumn+64))$iRow"
                    # Row = $iRow
                    # Column = $iColumn
                    Old  = $null
                    New  = $NewWorksheet.Cells.Item($iRow, $iColumn).Text
                }
                $DiffList += $DiffItem
            }
        }
    }

    if ($OldRowsCount -gt $NewRowsCount) {
        $MaxRowCount = $OldRowsCount
        for ($iRow = $NewRowsCount + 1; $iRow -le $OldRowsCount; $iRow++) {
            for ($iColumn = 1; $iColumn -le $NewColumnsCount; $iColumn++) {
                if (-Not [String]::IsNullOrWhiteSpace($OldWorksheet.Cells.Item($iRow, $iColumn).Text)) {
                    $DiffItem = [PSCustomObject]@{
                        Cell = "$([char]($iColumn+64))$iRow"
                        # Row = $iRow
                        # Column = $iColumn
                        Old  = $OldWorksheet.Cells.Item($iRow, $iColumn).Text
                        New  = $null
                    }
                    $DiffList += $DiffItem
                }
            }
        }
    }
    else {
        $MaxRowCount = $NewRowsCount
    }

    if ($OldColumnsCount -gt $NewColumnsCount) {
        for ($iRow = 1; $iRow -le $MaxRowCount; $iRow++) {
            for ($iColumn = $NewColumnsCount + 1; $iColumn -le $OldColumnsCount; $iColumn++) {
                if (-Not [String]::IsNullOrWhiteSpace($OldWorksheet.Cells.Item($iRow, $iColumn).Text)) {
                    $DiffItem = [PSCustomObject]@{
                        Cell = "$([char]($iColumn+64))$iRow"
                        # Row = $iRow
                        # Column = $iColumn
                        Old  = $OldWorksheet.Cells.Item($iRow, $iColumn).Text
                        New  = $null
                    }
                    $DiffList += $DiffItem
                }
            }
        }
    }
    if ($DiffList.Count -gt 0) {
        return $DiffList
    }
    return "No Change"
}

function Show-Result {
    param (
        $Result
    )
    foreach ($WorksheetName in $Result.Keys) {
        Write-Host "--- Worksheet $WorksheetName ---"
        foreach ($DiffItem in $Result[$WorksheetName]) {
            if ($DiffItem.GetType().Name -eq "String") {
                Write-Host $DiffItem
            }
            else {
                Write-Host "Diff Cell:$($DiffItem.Cell), New:'$($DiffItem.New)', old:'$($DiffItem.Old)'"
            }
        }
    }
}

#End of Functions of DiffExcel
#==================
Compare-Excel $OldFile $NewFile -Open:$Open | Out-Null