Open LittleRags opened 3 months ago
@LittleRags thanks for using the module!
Do you have a simple script that reprost his?
@dfinke The script I set out in point 4 should repro the issue.
@LittleRags I don't see "InvoiceTest.xlsx" attached. I'd prefer a script + xlsx so I can run and see the issue.
@dfinke Ah, I apologise, here is the Excel file, along with the PowerShell code (now that I have figured out how to correctly use the code block) InvoiceTest.xlsx
Import-Module ImportExcel
$InvoicePackage = Open-ExcelPackage -Path "C:\temp\InvoiceTest.xlsx"
$Worksheet = Add-Worksheet -ExcelPackage $InvoicePackage -WorksheetName "Invoice"
$CellA1 = $Worksheet.Cells[1,1]
$CellA2 = $Worksheet.Cells[2,1]
$CellB1 = $Worksheet.Cells[1,2]
$CellB1.Value = 45479 # corresponds to 6 July 2024
$CellB1.Style.Numberformat.Format = "yyyy-mm-dd"
$CellA1.Value = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))"
$CellA2.Formula = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))"
$InvoicePackage.Save()
$InvoicePackage.Dispose()
Thank you for creating ImportExcel - it is massively easier to use through PowerShell than using the COM Object.
When I try to insert a formula which contains a reference to a table, using the .Formula method results in an error message about the content. If I use the .Value method, open Excel, edit the cell and immediately Enter, the formula works correctly. It looks like it is specific to a formula containing a reference to a table and column. To reproduce:
$CellA1 = $Worksheet.Cells[1,1] $CellA2 = $Worksheet.Cells[2,1] $CellB1 = $Worksheet.Cells[1,2] $CellB1.Value = 45479 # corresponds to 6 July 2024 $CellB1.Style.Numberformat.Format = "yyyy-mm-dd"
$CellA1.Value = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))" $CellA2.Formula = "=SUM(FILTER(InvoiceAmounts[InvoiceAmount],(InvoiceAmounts[InvoiceDate]<=B1)))"
$InvoicePackage.Save() $InvoicePackage.Dispose()`
I have tried without the leading "=" and get the same result. It also looks like there is an error returned from Excel, since the process stops making changes after this point, but the error is not returned to PowerShell.