I am having trouble getting this simple Pester mocking code to work on this simple function. The $worksheets variable returns nothing and then I ultimately get the following error when the Clear() method is called.

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

Can anyone assist? Copilot got me something to work with but doesn't understand either why this does not work.

    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()
}




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" {
            # Arrange
            $ExcelFilePath = "C:\path\to\file.xlsx"
            $WorkSheetName = "Sheet1"

            # Act
            Clear-ExcelWorksheet -ExcelFilePath $ExcelFilePath -WorkSheetName $WorkSheetName

            # Assert
            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
        }

}

I am having trouble getting this simple Pester mocking code to work on this simple function. The $worksheets variable returns nothing and then I ultimately get the following error when the Clear() method is called.

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

Can anyone assist? Copilot got me something to work with but doesn't understand either why this does not work.

    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()
}




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" {
            # Arrange
            $ExcelFilePath = "C:\path\to\file.xlsx"
            $WorkSheetName = "Sheet1"

            # Act
            Clear-ExcelWorksheet -ExcelFilePath $ExcelFilePath -WorkSheetName $WorkSheetName

            # Assert
            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
        }

}

• powershell
• mocking
• pester-5

2 Answers 2

I believe that it is not actually mocking the New-Object command which is causing the failure. I've tried rescoping the Mock inside the "It" but still getting the same results. The problem seems to be with my Mock statement.

You've got two issues with your mock object:

Mock Item Property

The structure of the Worksheets part of the mock object matches the literal structure of the Worksheets type:

Worksheets = @{
  Item = @{
    ...
  }
}

but in the real Excel COM objects the Item property is the default indexer property got the Worksheets type, so your code:

$package.Workbook.Worksheets[$WorkSheetName]

actually invokes

$package.Workbook.Worksheets.Item[$WorkSheetName]
#                           ^^^^^

The mock object doesn't have the Item property defined as an indexer so you need to explicitly access the property with $package.Workbook.Worksheets.Item[$WorkSheetName] per the second example above. If you change your function code to this it will work with the real Excel COM objects *and * the mock object.

Mock Methods

The Clear, Save and Dispose methods are being mocked as empty scriptblocks, but you can't invoke a scriptblock with the () syntax - that's only for external methods (COM / dotnet).

You'll need to attach synthetic methods onto the objects instead like this:

... | Add-Member -MemberType ScriptMethod -Name Save -Value { }

Solution

If you change your mock object to the following and update the .Item reference it will solve both those problems...

Mock -CommandName New-Object -MockWith {
    [PSCustomObject] @{
        Workbook = [PSCustomObject] @{
            Worksheets = @{
                Item = @{
                    "Sheet1" = [PSCustomObject] @{
                        Cells = [PSCustomObject] @{
                        } `
                        | Add-Member -MemberType ScriptMethod -Name Clear -Value { } -PassThru
                    }
                }
            }
        } `
        | Add-Member -MemberType ScriptMethod -Name Save -Value { } -PassThru `
        | Add-Member -MemberType ScriptMethod -Name Dispose -Value { } -PassThru
}