How to add rows to excel document with formulas using powershell

Question

I have a small Excel template, the first row consists of cells with formulas like

=SUBTOTAL(9,C3:C15000)

and such. The second row is headers.

I have a PowerShell process gathering data to fill the Excel daily using Import-Excel, but when I merge the two files (importing the template Excel into a variable and adding rows to it), those top cells with the formulas lose their underlying formula values and get read as 0 (since there is no data in the template).

Is there a way to preserve those formula cells so that when the file is exported, they will contain dynamic values? I am using the Import-Excel module now but I'm not opposed to doing it a different way, including other programs. Thank you!

$report = $report | Sort thing_1, thing_2 | Select $reportcolumns

$template = Import-Excel -Path C:\Temp\Template.xlsx -NoHeader

foreach ($row in $report) {

    $template += $row

}

$template | Export-Excel -Path C:\Temp\Filled_Template.xlsx

$Report is a collection of customPS objects with the same columns as the template. Some stuff happens before this but it's specific, and not really important to this part of the script anyway.

Can you show the code you currently have? There may be an issue in your logic resulting in you overwriting the formula in the cell with a value. — Chris Boden
– Chris Boden, Commented Aug 22, 2019 at 20:04

Chris Boden · Accepted Answer · 2019-08-23 17:38:05Z

Looking at the code for Import-Excel, it only imports data, not formulas. Basically, what you're attempting to do will not work the way you are trying to do it. As you mentioned, the cells with formulas show 0 because that was the value in that cell at the time it was read by Import-Excel, because there was no data. Taking a quick glance at the code for the Import-Excel library, and what documentation is there, it only supports formulas going out (e.g. using Export-Excel) and has no support anywhere for formulas coming in (If someone with more experience with this module knows otherwise, please let me know).

As TheMadTechnician suggested, using the COM object if Excel is available on the system is a preferable course of action. If the COM object is unavailable, you could add the formulas in programatically before passing the data to Export-Excel. This is done by assigning the formula as the value to the cell.

$template = Import-Excel -Path 'C:\Temp\Template.xlsx' -NoHeader

# do whatever here that is needed to ensure that formulas end up where they need to be
$template[0].P1 = '=SUBTOTAL(9,C3:C15000)'

foreach ($row in $report) {

    $template += $row

}

$template | Export-Excel -Path 'C:\Temp\Filled_Template.xlsx'

TheMadTechnician · Accepted Answer · 2019-08-22 23:28:38Z

1

Without using Import-Excel and Export-Excel, I would use the ComObject (assuming Excel is installed on the system) to just paste your data directly into the spreadsheet.

$XL = New-Object -ComObject Excel.Application
$WB = $XL.Workbooks.Open('C:\Temp\Template.xlsx')
# Convert $report to a tab delimeted CSV, skip the header, and copy to the clipboard
$report | ConvertTo-Csv -Delimeter "`t" -NoTypeInfo | Select -Skip 1 | Clip
# Paste to row 3 column 1, pipe to Out-Null to avoid the TRUE that it responds with
$WB.ActiveSheet.Cells.Item(3,1).PasteSpecial() | Out-Null
# Save the workbook, and close it
$WB.SaveAs('C:\Temp\Filled_Template.xlsx')
$WB.Close() | Out-Null
$XL.Quit()

answered Aug 22, 2019 at 23:28

TheMadTechnician

36.5k3 gold badges48 silver badges63 bronze badges

Collectives™ on Stack Overflow

How to add rows to excel document with formulas using powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related