Skip to content

Instantly share code, notes, and snippets.

@royashbrook
Created November 22, 2021 15:15
Show Gist options
  • Save royashbrook/fc7a61282a66bf4baab56b70401a3848 to your computer and use it in GitHub Desktop.
Save royashbrook/fc7a61282a66bf4baab56b70401a3848 to your computer and use it in GitHub Desktop.
Moving from epplus.dll to import-excel
# 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
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment