在PowerShell的Pester中模拟New-Object报错：无法调用空值表达式方法

问题场景 #

编写了如下PowerShell函数用于清空Excel工作表内容：

function Clear-ExcelWorksheet {
[CmdletBinding()]
param (

    [Parameter(Mandatory = $true)]
    [string]$ExcelFilePath,

    [Parameter(Mandatory = $true)]
    [string]$WorkSheetName

)


    $package = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $ExcelFilePath
    $worksheet = $package.Workbook.Worksheets[$WorkSheetName]
    $worksheet.Cells.Clear()
    $package.Save()
    $package.Dispose()
}

在编写Pester测试时，模拟New-Object后，$worksheet变量始终返回null，调用Clear()方法时报错：

Error occurred clearing the Worksheet: You cannot call a method on a null-valued expression.

使用Copilot生成的测试代码也无法解决问题：

Describe "Clear-ExcelWorksheet" {
    Mock -CommandName New-Object -MockWith {
        [PSCustomObject]@{
            Workbook = [PSCustomObject]@{
                Worksheets = @{
                    Item = @{
                        "Sheet1" = [PSCustomObject]@{
                            Cells = [PSCustomObject]@{
                                Clear = { }
                            }
                        }
                    }
                }
            }
            Save = { }
            Dispose = { }
        }
    }

    Context "When clearing a worksheet" {
        It "should clear the worksheet cells and save the package" {
            $ExcelFilePath = "C:\path\to\file.xlsx"
            $WorkSheetName = "Sheet1"

            Clear-ExcelWorksheet -ExcelFilePath $ExcelFilePath -WorkSheetName $WorkSheetName

            Assert-MockCalled -CommandName New-Object -Exactly 1 -Scope It
            Assert-MockCalled -CommandName Save -Exactly 1 -Scope It
            Assert-MockCalled -CommandName Dispose -Exactly 1 -Scope It
        }

}

问题原因 #

1. 索引器模拟错误：Copilot生成的Mock中，Worksheets对象嵌套了Item哈希表，但PowerShell无法将这种结构自动识别为索引器（[]访问），导致$package.Workbook.Worksheets[$WorkSheetName]返回null。
2. 方法断言错误：Assert-MockCalled仅适用于PowerShell命令，无法断言对象方法（如Save()、Dispose()）的调用情况。

修正后的测试代码 #

Describe "Clear-ExcelWorksheet" {
    # 跟踪对象方法的调用状态
    $clearInvoked = $false
    $saveInvoked = $false
    $disposeInvoked = $false

    Mock -CommandName New-Object -MockWith {
        [PSCustomObject]@{
            Workbook = [PSCustomObject]@{
                # 将Worksheets设为哈希表，直接支持索引访问
                Worksheets = @{
                    "Sheet1" = [PSCustomObject]@{
                        Cells = [PSCustomObject]@{
                            Clear = { $script:clearInvoked = $true }
                        }
                    }
                }
            }
            Save = { $script:saveInvoked = $true }
            Dispose = { $script:disposeInvoked = $true }
        }
    }

    Context "操作存在的工作表" {
        It "应清空单元格、保存并释放资源" {
            $testPath = "C:\path\to\file.xlsx"
            $testSheet = "Sheet1"

            # 验证执行过程无异常
            { Clear-ExcelWorksheet -ExcelFilePath $testPath -WorkSheetName $testSheet } | Should -Not -Throw

            # 验证各方法是否被正确调用
            $clearInvoked | Should -Be $true
            $saveInvoked | Should -Be $true
            $disposeInvoked | Should -Be $true
            Assert-MockCalled -CommandName New-Object -Exactly 1 -Scope It
        }
    }

    Context "操作不存在的工作表" {
        It "应抛出空值调用异常" {
            $testPath = "C:\path\to\file.xlsx"
            $testSheet = "NonExistentSheet"

            { Clear-ExcelWorksheet -ExcelFilePath $testPath -WorkSheetName $testSheet } | Should -Throw "You cannot call a method on a null-valued expression."
        }
    }
}

关键修正点 #

• 将Worksheets模拟为哈希表，让$package.Workbook.Worksheets[$WorkSheetName]能直接通过键名获取工作表对象，匹配真实EPPlus组件的索引访问逻辑。
• 使用脚本级变量跟踪对象方法的调用状态，替代错误的Assert-MockCalled断言方式。
• 增加了工作表不存在的异常场景测试，覆盖更多边界情况。

内容的提问来源于stack exchange，提问作者KJK