search

dfinke / ImportExcel

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

Set-ExcelRange -Formula issue #1559

Open jhejgard opened 9 months ago

jhejgard commented 9 months ago

First many thanks for importExcel, it is a great Powershell Module.

In my scripting I use Excel formulas a lot and normally have no issues with it on Set-ExcelRange like -Formula '=COUNTIFS(J11:J100000,"Ansible LIN OS")'

Next Excel formula works if I create it manually in an Excel file, but when I use importExcel module and Set-ExcelRange it fails all the time. The formula counts how many different names are in column D where column J is "Ansible LIN OS"

-Formula '=SUM(--(LEN(UNIQUE(FILTER(D11:D100000,J11:J100000="Ansible LIN OS","")))>0))'

I wonder what I am doing wrong, what is special with this Excel formula is that is has two = signs, not very often used, but I could not come across a workaround on this one. Hope some one can put some clarity on this. Thanks.

dfinke commented 9 months ago

Thanks for using ImportExcel.

Please do this to get some clues. The xlsx is a zip file. Create one xl file using the PS script. Name it fail.xlsx. Then create another pass.xlsx where you manually create the formula.

You can unzip both xlsx files to separate directories (rename them, change the ext to .zip).

Each should give you a bunch of xml files. That is how Excel stores the data. You can use an edit to inspect the xml. Find the formula that works in pass then find it in fail we can compare how it gets generated and go from there.