At sign added to formula definition, causing broken formulas

[RobertoPrevato]

Hi,
Can you please point me to a solution for the issue below?

I have data like in the CSV below, in a images.csv file.

"architecture","offer","publisher","sku","urn","version"
"x64","0001-com-ubuntu-confidential-vm-jammy","canonical-test","22_04-lts-cvm","canonical-test:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202211230","22.04.202211230"
"x64","0001-com-ubuntu-confidential-vm-jammy","canonical-test","22_04-lts-cvm","canonical-test:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202212060","22.04.202212060"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202210040","22.04.202210040"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202212130","22.04.202212130"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202301090","22.04.202301090"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202302080","22.04.202302080"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202304010","22.04.202304010"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202304260","22.04.202304260"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202305310","22.04.202305310"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202306200","22.04.202306200"
"x64","0001-com-ubuntu-confidential-vm-jammy","Canonical","22_04-lts-cvm","Canonical:0001-com-ubuntu-confidential-vm-jammy:22_04-lts-cvm:22.04.202306300","22.04.202306300"
"x64","debian-12-daily","Debian","12-gen2","Debian:debian-12-daily:12-gen2:0.20241102.1919","0.20241102.1919"
"x64","debian-12-daily","Debian","12-gen2","Debian:debian-12-daily:12-gen2:0.20241103.1920","0.20241103.1920"
"x64","debian-12-daily","Debian","12-gen2","Debian:debian-12-daily:12-gen2:0.20241104.1921","0.20241104.1921"

I read the CSV and create an Excel file with formulas, like here:

$data = Import-Csv "images.csv"

$outputFileName = "Example$(Get-Date -Format 'yyyyMMddHHmmss').xlsx"

$xl = $data | Export-Excel $outputFileName `
    -Append `
    -WorksheetName "vms" `
    -TableStyle Medium16 `
    -AutoSize `
    -PassThru

$ws = $xl.Workbook.Worksheets["vms"]

# Add columns with UNIQUE functions
$ws.Cells["G1"].Value = "unique offers"
$ws.Cells["G2"].Formula = "UNIQUE(Table1[offer])"

$ws.Cells["H1"].Value = "unique publishers"
$ws.Cells["H2"].Formula = "UNIQUE(Table1[publisher])"

$ws.Cells["I1"].Value = "unique skus"
$ws.Cells["I2"].Formula = "UNIQUE(Table1[sku])"

$ws.Cells["J1"].Value = "unique versions"
$ws.Cells["J2"].Formula = "UNIQUE(Table1[version])"

Write-Host "Writing to $outputFileName"
Close-ExcelPackage $xl

But for some reason, the Excel file is generated with broken formulas containing the '@' sign after the '=' sign.

Functions work when I remove the '@' sign.

I looked for information, tried using the FormulaR1C1 property instead of Formula and also the Set-ExcelRange method, but I always get the same result. I also tried adding formulas and saving the Excel in different steps.

Thank You for this wonderful library!

[dfinke]

@RobertoPrevato hmm, nothing off the top of my head where the @ is coming from

[RobertoPrevato]

@dfinke Thank You for taking the time to reply so fast. While I was googling for information, I saw a thread on StackOverflow about openpyxl, and apparently there is a way to tell Excel to handle a formula like an Array formula.

https://stackoverflow.com/questions/66008301/symbol-appearing-after-inserting-if-formula-into-excel-using-openpyxl

https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula

[UPDATE] this is the most interesting part: Formula vs Formula2
https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2

If I find the answer, I will share it here.
👀

[MarkLynch-EptaUK]

I was finding the same when I used Export-Excel

Originally my code was

    [    # Add data for Excel output using IMAGE formula
       $trophyData_Excel += [PSCustomObject]@{
		TitleIcon        = if ($title.trophyTitleIconUrl) { "=_xlfn.IMAGE(`"$($title.trophyTitleIconUrl -replace '([`"])', '')`",,3,50)" } else { "No Icon" }
		Title            = $title.trophyTitleName
		Platform         = $title.trophyTitlePlatform
		TrophyName       = $trophy.trophyName
		TrophyImage      = if ($trophy.trophyIconUrl) { "=_xlfn.IMAGE(`"$($trophy.trophyIconUrl -replace '([`"])', '')`",,3,50)" } else { "No Image" }
		TrophyDescription = $trophy.trophyDetail
		TrophyType       = $trophy.trophyType
		TrophyHidden     = $trophy.trophyHidden
		Earned           = $earnedInfo.earned
		ProgressRate     = if ($earnedInfo.progressRate) { "$($earnedInfo.progressRate)%" } else { "" }
		EarnedDate       = if ($earnedInfo.earned) { $earnedInfo.earnedDateTime } else { "Not Earned" }
		Rarity           = $rarity
		EarnedRate       = if ($earnedInfo.trophyEarnedRate) { "$($earnedInfo.trophyEarnedRate)%" } else { "No Rate" }
		RewardName       = $trophy.trophyRewardName
		RewardImage      = if ($trophy.trophyReward) { "=_xlfn.IMAGE(`"$($trophy.trophyRewardImageUrl -replace '([`"])', '')`",,3,50)" } else { "No Image" }
}](url)
# Export to Excel (this may add =@IMAGE)
$trophyData_Excel | Export-Excel -Path "PSN_Trophy_Report.xlsx" -AutoSize -WorksheetName "Trophies" -NoNumberConversion '*'

Which would give me =@image for any image cells

Now it is

$trophyData_Excel += [PSCustomObject]@{
    TitleIcon        = if ($title.trophyTitleIconUrl) { "=_xlfn.IMAGE(""$($title.trophyTitleIconUrl)"",,3,50)" } else { "No Icon" }
    Title            = $title.trophyTitleName
    Platform         = $title.trophyTitlePlatform
    TrophyName       = $trophy.trophyName
    TrophyImage      = if ($trophy.trophyIconUrl) { "=_xlfn.IMAGE(""$($trophy.trophyIconUrl)"",,3,50)" } else { "No Image" }
    TrophyDescription = $trophy.trophyDetail
    TrophyType       = $trophy.trophyType
    TrophyHidden     = $trophy.trophyHidden
    Earned           = $earnedInfo.earned
    ProgressRate     = if ($earnedInfo.progressRate) { "$($earnedInfo.progressRate)%" } else { "" }
    EarnedDate       = if ($earnedInfo.earned) { $earnedInfo.earnedDateTime } else { "Not Earned" }
    Rarity           = $rarity
    EarnedRate       = $earnedInfo.trophyEarnedRate + "%"
    RewardName       = $trophy.trophyRewardName
    RewardImage      = if ($trophy.trophyReward) { "=_xlfn.IMAGE(""$($trophy.trophyRewardImageUrl)"",,3,50)" } else { "No Image" }
}
# Export to Excel (this may add =@IMAGE)
$trophyData_Excel | Export-Excel -Path "PSN_Trophy_Report.xlsx" -AutoSize -WorksheetName "Trophies" -NoNumberConversion '*'

This has stopped it adding @ into my formula, I now get correctly =IMAGE

[dfinke]

@MarkLynch-EptaUK thanks could you dial in on the smallest change you did to make it work?

[MarkLynch-EptaUK]

@dfinke Onto the formula, I simply added _xlfn. so for my example, I changed =IMAGE to =_xlfn.IMAGE

[RobertoPrevato]

I tried with the example I provided when I opened this issue, and it still fails for me, also using _xlfn..

It adds the @ sign which breaks the 'UNIQUE' function.