Export-Excel not correctly evaluating formulas containing table references

[zuntua]

When exporting data that contains formulas with table references with Export-Excel, the cells containing formulas with table references don't evaluate correctly.

For example,

$xlFile = "C:\Test\Excel Test.xlsx"
$xlSheet = "TestSheet"
$Data = [PSCustomObject]@{
    "Col1" = 10
    "Col2" = 100
    "Col3" = '=[@Col2]*2'
    "Col4" = '=B11*2'
}
$Data | Export-Excel $xlFile -WorksheetName $xlSheet -Append

Output with the above code

Col 1   Col 2   Col 3   Col 4
-----   -----   -----   -----
10      100     #REF!   200

The example code saves the data to the end of the existing Table but the value in Column C shows #REF! (the formula for the cell is =#REF!*2)
The value in Column C should be 200.

[dfinke]

Please post an xlsx with the data and tables

[zuntua]

Please post an xlsx with the data and tables

@dfinke
Here's the test file. Apologies, I can't upload the actual xlsx file as it contains sensitive information. However, the data types are the as the actual xlsx file.
Excel Test.xlsx

The excel file was originally created with:

$xlFile = "C:\Test\Excel Test.xlsx"
$xlSheet = "TestSheet"
$xl = Open-ExcelPackage -Path $xlFile -Create
        $Sheetxl = Add-WorkSheet -ExcelPackage $xl -WorksheetName $xlSheet
        $Sheetxl.Cells[1,1].Value = "Col1"
        $Sheetxl.Cells[1,2].Value = "Col2"
        $Sheetxl.Cells[1,3].Value = "Col3"
        $Sheetxl.Cells[1,4].Value = "Col4"
        Set-ExcelRange -Range $Sheetxl.Cells["A1:D1048576"] -FontName "Arial" -FontSize 12
        Set-ExcelRange -Range $Sheetxl.Cells["C1:C1048576"] -NumberFormat "0"
        Set-ExcelRange -Range $Sheetxl.Cells["D1:D1048576"] -NumberFormat "0"
        Add-ExcelTable -Range $Sheetxl.Cells["A1:D2"] -TableName "Table1" -TableStyle Medium1
        Set-ExcelRange -Range $Sheetxl.Cells["A1:B1"] -Width 20
        Set-ExcelColumn -Worksheet $Sheetxl -Column 3 -Width 16
        Set-ExcelColumn -Worksheet $Sheetxl -Column 4 -Width 10
Close-ExcelPackage $xl

It then gets populated with:

$xlFile = "C:\Test\Excel Test.xlsx"
$xlSheet = "TestSheet"
$Data = [PSCustomObject]@{
    "Col1" = 40
    "Col2" = 400
    "Col3" = '=[@Col2]*2'
    "Col4" = '=B5*2'
}
$Data | Export-Excel $xlFile -WorksheetName $xlSheet -Append

[stahler]

replace Col3 = '=[@Col2]*2'
with Col3 = '=[Col2]*2'

[zuntua]

replace Col3 = '=[@Col2]*2'
with Col3 = '=[Col2]*2'

Wow, that works! Interesting, =[Col2]*2 is eventually replaced with =[@Col2]*2 in the excel file (or at least that is the case with Excel 2021). Is anyone able to explain why this works?

[dfinke]

Thanks @stahler I was looking for in the examples for how to get this right, couldn't find it.

@zuntua I would need to look at Export-Excel for these specifics, there are places where =[Name] may be detected and transformed or it may be in the DLL.

[oliwex]

TIPS from me: Try to pass object only with data(no formulas,no formatting,only data) into export-excel and then format file. It works better

[zuntua]

replace Col3 = '=[@Col2]*2'
with Col3 = '=[Col2]*2'

Wow, that works! Interesting, =[Col2]*2 is eventually replaced with =[@Col2]*2 in the excel file (or at least that is the case with Excel 2021). Is anyone able to explain why this works?

@dfinke
Upon further investigation, it appears that the behavior of replacing =[Col2]*2 (from powershell) with =[@Col2]*2 (in excel) is inconsistent.

When adding data to an existing file populated with data where some formulas don't contain table references. For instance, see Excel Test 2.xlsx

Data in Excel Test 2.xlsx

Col1	Col2	Col3	        Col4
10	100	=[@Col2]*2	=$B2*2

When adding data to Excel Test 2.xlsx with something like this:

# Code 1
$xlfile = "C:\Test\Excel Test 2.xlsx"
$xlsheet = "testsheet"
$Data = @([PSCustomObject]@{
    "Col1" = 20
    "Col2" = 200
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 30
    "Col2" = 300
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 40
    "Col2" = 400
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 50
    "Col2" = 500
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
})
$Data | Export-Excel $xlFile -WorksheetName $xlSheet -Append

You get this output, see Excel Test 2_Code 1.xlsx

Output from Excel Test 2_Code 1.xlsx

Col1	Col2	Col3	        Col4
10	100	=[@Col2]*2	=$B2*2
20	200	=[@Col2]*2	=[@Col2]*2
30	300	=[@Col2]*2	=[@Col2]*2
40	400	=[@Col2]*2	=[@Col2]*2
50	500	=[@Col2]*2	=[@Col2]*2

However, when adding data to Excel Test 2.xlsx with something like this:

# Code 2
$xlfile = "C:\Test\Excel Test 2.xlsx"
$xlsheet = "testsheet"
$Data = @([PSCustomObject]@{
    "Col1" = 20
    "Col2" = 200
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 30
    "Col2" = 300
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 40
    "Col2" = 400
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 50
    "Col2" = 500
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
})
ForEach ($Save in $data) {
   $Save | Export-Excel $xlFile -WorksheetName $xlSheet -Append
}

You get this output, see Excel Test 2_Code 2.xlsx

Output from Excel Test 2_Code 2.xlsx

Col1	Col2	Col3	        Col4
10	100	=[@Col2]*2	=$B2*2
20	200	=[@Col2]*2	=$B3*2
30	300	=[@Col2]*2	=$B4*2
40	400	=[@Col2]*2	=$B5*2
50	500	=[@Col2]*2	=[@Col2]*2

If you then add data to Excel Test 2_Code 2.xlsx with something like:

# Code 3
$xlfile = "C:\Test\Excel Test 2_Code 2.xlsx"
$xlsheet = "testsheet"
$Data = @([PSCustomObject]@{
    "Col1" = 60
    "Col2" = 600
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 70
    "Col2" = 700
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 80
    "Col2" = 800
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
},[PSCustomObject]@{
    "Col1" = 90
    "Col2" = 900
    "Col3" = '=[Col2]*2'
    "Col4" = '=[Col2]*2'
})
ForEach ($Save in $data) {
    $Save | Export-Excel $xlFile -WorksheetName $xlSheet -Append
}

You get this output, see Excel Test 2_Code 2_Append.xlsx

Output from Excel Test 2_Code 2_Append.xlsx

Col1	Col2	Col3	        Col4
10	100	=[@Col2]*2	=$B2*2
20	200	=[@Col2]*2	=$B3*2
30	300	=[@Col2]*2	=$B4*2
40	400	=[@Col2]*2	=$B5*2
50	500	=[@Col2]*2	=$B6*2
60	600	=[@Col2]*2	=$B7*2
70	700	=[@Col2]*2	=$B8*2
80	800	=[@Col2]*2	=$B9*2
90	900	=[@Col2]*2	=[@Col2]*2

Note the formula in Row 6 Col4: it changed from =[@Col2]*2 in Excel Test 2_Code 2.xlsx to =$B6*2 in Excel Test 2_Code 2_Append.xlsx

[stahler]

As @oliwex stated, a better way to do this is to generate the file, then adjust.

$data = ConvertFrom-Csv @"
COL1,COL2
20,200
30,300
40,400
50,500
"@

$path = 'C:\TEMP\example1.xlsx'
Remove-Item -Path $path -ErrorAction SilentlyContinue

$excel = $data | Export-Excel -Path $path -PassThru
$sheet = $excel.Workbook.Worksheets["Sheet1"]

Set-ExcelColumn -Worksheet $sheet -Heading "COL3" -Value {"=B$row*2"}
Close-ExcelPackage -ExcelPackage $excel -Show

[oliwex]

@dfinke I think the code from @stahler is good for docs for that module as: Get-Help Set-ExcelColumn.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.