Formula in NoteProperty exported using Export-Excel is broken with #REF!

Hi Doug et al. Thanks v much for Import-Excel. Class!

I've discovered an issue, described below; Module version used

Version    Name                                Repository           Description                                                
-------    ----                                ----------           -----------                                                
7.8.6      ImportExcel                         PSGallery            PowerShell module to import/export Excel spreadsheets, w...

Before exporting PSCustomObject using Export-Excel I modify the object to add two NoteProperty members.


function "Add-ParameterToObject" implements "Add-Member" {
    ...
    foreach ($key in $Parameters.Keys) {
            $InputObject | Add-Member -NotePropertyName $key -NotePropertyValue $Parameters[$key] -Force
        }
    ...
}

$object = Add-ParameterToObject -InputObject $object -Parameters ([ordered]@{
    Developer = $DeveloperFormula
    Team = $TeamFormula
})

   TypeName: Selected.System.Management.Automation.PSCustomObject

Name         MemberType   Definition                                                                                                               
----         ----------   ----------                                                                                                               
Developer    NoteProperty string Developer==IF(OR([@status]="Closed",[@status]="Resolved"),[@LastResolutionUserEmailName],[@assignee])             
Team         NoteProperty string Team==VLOOKUP([@Developer],ResourcesTable,2,0)

The content added are formula (known to work) that use column name references.

When I open and review the excel, the formula are broken #REF! as follows =IF(OR(#REF!="Closed",#REF!="Resolved"),#REF!,#REF!) =VLOOKUP(#REF!,ResourcesTable,2,0)

Checking the xml source of the xlsx document by decompressing the archive, I observe the xml representation of in the columns is matching the inserted strings.

\ExcelFile.xlsx\xl\worksheets\sheet1.xml

IF(OR([@status]="Closed",[@status]="Resolved"),[@LastResolutionUserEmailName],[@assignee]) VLOOKUP([@Developer],ResourcesTable,2,0)

The opening and closing tags encapsulate the correct formula definition. And with the file open in Excel, if I overtype/paste the same formula into the cells, that works.

Sample code

# Define XL formula used in two new columns.
$DeveloperFormula = [string]'=IF(OR([@status]="Closed",[@status]="Resolved"),[@LastResolutionUserEmailName],[@assignee])'
$TeamFormula = [string]'=VLOOKUP([@Developer],ResourcesTable,2,0)' # VLOOKUP 

# Add two columns containing XL formula before exporting to Excel.
$Object = Add-ParameterToObject -InputObject $Jira -Parameters ([ordered]@{
    Developer = $DeveloperFormula
    Team = $TeamFormula
}) 

# Export the data
$ExcelPackage = $Object | Export-Excel -Path $excelPath -TableName CCdata -TableStyle Medium5 -FreezeTopRow -BoldTopRow -WorksheetName $excelName -PassThru

# Close and show ExcelPackage
Close-ExcelPackage $ExcelPackage -Show

dfinke / ImportExcel

Formula in NoteProperty exported using Export-Excel is broken with #REF! #1534