Formula cell value returns #Value

[rafall92]

I try to read value from my formula cell. I find out that if i save file manualy, and then read file again and get value from cell I can get proper value. But if i do it with this script, I always get "#Value" in return

# Define the file path
$filePath = "C:\Path\To\Your\File111.xlsx"

# Create Excel data and export it to a file


$null | Export-Excel -Path $filePath -WorksheetName "Sheet1"

# Open the Excel file and add formulas
$excelPackage = Open-ExcelPackage -Path $filePath
$worksheet = $excelPackage.Workbook.Worksheets["Sheet1"]

$worksheet.Cells[1,1].Value = 1
$worksheet.Cells[1,2].Value = 32

$worksheet.Cells[2,1].Value = 5
$worksheet.Cells[1,2].Value = 20

$worksheet.Cells[1,3].Formula = "=SUM(A1:B1)"
$worksheet.Cells[2,3].Formula = "=SUM(A2:B2)"

Close-ExcelPackage -ExcelPackage $excelPackage -Calculate -SaveAs $filePath

$excelPackage = Open-ExcelPackage -Path $filePath
$worksheet = $excelPackage.Workbook.Worksheets["Sheet1"]

# Read the value from the cell where the formula is located
$cellValue1 = $worksheet.Cells[1, 3].Value
$cellValue2 = $worksheet.Cells[2, 3].Value

Write-Output "The result of the formula in cell C1 is: $cellValue1"
Write-Output "The result of the formula in cell C2 is: $cellValue2"

# Save and close the Excel package
Close-ExcelPackage -ExcelPackage $excelPackage -Calculate -SaveAs $filePath

[scriptingstudio]

To get a real cell value the one must be rendered first. The module does not render Excel file, it just creates a declaration of Excel document in form of a file. Then Excel app renders the content of your file.

[dfinke]

@rafall92 - @scriptingstudio is correct. -Calculate was added to do some basics without rendering.

I worked it up this way. Will try the Close-ExcelPackage later.

$data = ConvertFrom-Csv @"
data1,data2
1,32
5,30
=Sum(A2:B2),=Sum(A3:B3)
"@ 

$xlfile = "$psscriptroot\testData.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$data | Export-Excel $xlfile -Calculate

Import-Excel $xlfile

data1 data2
----- -----
 1.00 32.00
 5.00 30.00
33.00 35.00

[rafall92]

I found solution, but forgot to reply. I fix problem by removing "=" sign from begining of formula
its simply
$worksheet.Cells[1,3].Formula = "SUM(A1:B1)" $worksheet.Cells[2,3].Formula = "SUM(A2:B2)"

instead of
$worksheet.Cells[1,3].Formula = "=SUM(A1:B1)" $worksheet.Cells[2,3].Formula = "=SUM(A2:B2)"

[dfinke]

Great, thanks for the update