search

dfinke / ImportExcel

PowerShell module to import/export Excel spreadsheets, without Excel
https://www.powershellgallery.com/packages/ImportExcel/
Apache License 2.0
2.48k stars 399 forks source link

At sign added to formula definition, causing broken formulas #1653

Open RobertoPrevato opened 2 weeks ago

RobertoPrevato commented 2 weeks ago

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.

image

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 commented 2 weeks ago

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

RobertoPrevato commented 2 weeks ago

@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. 👀