How to save file without overwrite excel file

Question

I am using EPPlus.dll in Powershell script to convert csv file to xlsx file, but I no have idea how to save file without overwrite existing data.

I have to generate new information from csv file to excel file, but I have others, manualy created tab which my script always overwrite.

Script looks like this:

$FileNameCSV = "$env:WORKSPACE/file.csv"
$FileNameExcel = "\\folder\file.xlsx "

$DLLPath = "D:\EPPlus.dll"
[Reflection.Assembly]::LoadFile($DLLPath) | Out-Null

$Format = New-object -TypeName OfficeOpenXml.ExcelTextFormat
$Format.Delimiter = ","

$Format.TextQualifier = '"'
$Format.Encoding = [System.Text.Encoding]::UTF8
$Format.SkipLinesBeginning = '0'
$Format.SkipLinesEnd = '1'

$TableStyle = [OfficeOpenXml.Table.TableStyles]::Medium1

$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage 
$Worksheet = $ExcelPackage.Workbook.Worksheets.Add("CM")

$null=$Worksheet.Cells.LoadFromText((Get-Item $FileNameCSV),$Format,$TableStyle,$true) 

$Worksheet.Cells[$Worksheet.Dimension.Address+1].AutoFitColumns()

$Worksheet.Column(3).Style.Numberformat.Format = "dd/mm/yyyy";

$ExcelPackage.SaveAs($FileNameExcel)

Write-Host "CSV File $FileNameCSV converted to Excel file $FileNameExcel"

Answer 1

You need to open the existing file named $FileNameExcel by passing the name to the constructor, probably like this:

  $ExcelPackage = New-Object OfficeOpenXml.ExcelPackage($FileNameExcel)

Then, add a new worksheet to this workbook, load the csv into this new worksheet the same way you do it currently, and save the workbook again.