How should you modify existing documents?

[ninmonkey]

If you want to modify an existing book, sometimes incrementally -- what commands should you use? Export/ExcelPackage/something else? It seems like Export-Excel doesn't automatically write until you close the package.

This code works. How do you write it idiomatically?

# the typing is there for autocomplete.
Copy-Item -Path $Path.Source $path.Dest

[OfficeOpenXml.ExcelPackage]$pkg2 = Open-ExcelPackage $Path.Dest
[OfficeOpenXml.Table.ExcelTable]$table1 = $pkg2.Sheet1.Tables[0]
$rowFunc = [OfficeOpenXml.Table.RowFunctions]

$table1.Columns[0].TotalsRowFunction = $rowFunc::Count
$table1.Columns[1].TotalsRowFunction = $rowFunc::Count
if($true) { 
    $table1.Columns[2].TotalsRowFunction = $rowFunc::Average # $rowFunc::Average
    $table1.Columns[3].TotalsRowFunction = $rowFunc::Max # $rowFunc::Average # $rowFunc::StdDev
}
Close-ExcelPackage $pkg2 -Show

maybe there's something like ?

$table1.TotalRowFunction =  @{Region = 'Count' ; State = 'Count' }

This regenerates the initial test state

Import-Module ImportExcel
. {
    $PSDefaultParameterValues['ImportExcel*:verbose'] = $true
    $data = @'
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
'@ | ConvertFrom-Csv

    $Path = @{ Source = 'g:\temp\input.xlsx' ; Dest = 'g:\temp\output.xlsx' }
    Remove-Item -Path $Path.Source -ErrorAction SilentlyContinue
    Remove-Item -Path $Path.Dest -ErrorAction SilentlyContinue

    $pkgSrc = $data | Export-Excel -Path $Path.Source -PassThru
    $addExcelTableSplat = @{
        Range         = $pkgSrc.Sheet1.Cells['A1:D5']
        TableStyle    = 'Medium6'
        TotalSettings = @{Region = 'Count' }
    }
    Add-ExcelTable @addExcelTableSplat
    Close-ExcelPackage $pkgSrc -Show
}

I'm conditionally generating and then modifying tables, like how you might build a hashtable for splatting.
I don't

[dfinke]

Don't know. First time seeing $table1.Columns[0].TotalsRowFunction = $rowFunc::Count. Need to investigate.