Moving from epplus.dll to import-excel

filling in an existing spreadsheet.ps1

# this sample is a situation where i have a template file provided by a customer. this file is copied and the copy is then filled up with data. these examples assume epplus is in the working dir so it can be loaded and i am not including the import-module line for importexcel

function old($tfile,$file,$data){

    Copy-Item $tfile $file

    if($data.count -eq 0){
        return
    }

    # make sure epplus is here
    if (-Not (Test-Path "epplus.dll")) {throw "epplus.dll not found"}

    # load epplus, get our document
    $null = [Reflection.Assembly]::LoadFile((Get-Item "epplus.dll").FullName)
    $thefile = (Get-ChildItem $file).FullName
    $pkg = New-Object OfficeOpenXml.ExcelPackage $thefile
    $wks = $pkg.Workbook.Worksheets[1]
    $c = $wks.cells
    $i = 4
    
    foreach($f in $data){
        $c[$i,1].value = $f."val1"
        $c[$i,2].value = $f."val2"
        $c[$i,3].value = $f."val3"
        $c[$i,4].value = $f."val4"
        $c[$i,5].value = $f."val5"
        $i++
    }

    $null = $pkg.Save()
    $null = $pkg.Dispose()

}
function new($tfile,$file,$data){

    Copy-Item $tfile $file

    if($data.count -eq 0){
        return
    }

    $pkg = Open-ExcelPackage -Path (Get-ChildItem $file).FullName
    $wks = $pkg.Workbook.Worksheets[1]
    $c = $wks.cells
    $i = 4
    
    foreach($f in $data){
        $c[$i,1].value = $f."val1"
        $c[$i,2].value = $f."val2"
        $c[$i,3].value = $f."val3"
        $c[$i,4].value = $f."val4"
        $c[$i,5].value = $f."val5"
        $i++
    }

    Close-ExcelPackage -ExcelPackage $pkg 

}