CODEWITHSUNDEEP
excelpowershell
Ben Adams
Ben Adams

Reputation: 129

How to add rows to excel document with formulas using powershell

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.

Upvotes: 1

Views: 5661

Answers (2)

Chris Boden
Chris Boden

Reputation: 219

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'

Upvotes: 2

TheMadTechnician
TheMadTechnician

Reputation: 36277

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()

Upvotes: 1

Related Questions