Functions/Add-RowToExcelSheet.ps1

<#
    .SYNOPSIS
    Add a new row to an Excel table
    Related to: General
    .DESCRIPTION
    Executed tasks:
        - Open the Excel file
        - Open the Excel sheet
        - Check if table exists
        - Increment row identifier (if enabled)
        - Insert a new row based on the ExcelColumnValuesInRow
        - Save and close Excel file
        - Display all actions (if DebugExcel is enabled)
    Preconditions:
        - File path has to be retreived
        - Excel sheet name has to be retreived
        - Excel column values in row has to be specified
#>

function Add-RowToExcelTable() {
    [cmdletbinding()]
    [Alias('AddRowToExcelTable')]
    Param
    (
        [parameter(Mandatory = $true)]
        [string] $FilePath,
        [parameter(Mandatory = $true)]
        [string] $ExcelSheetName,
        [parameter(Mandatory = $true)]
        [string[]] $ExcelColumnValuesInRow,
        [parameter(Mandatory = $false)]
        [boolean] $IncrementRowIdentifier = $true,
        [parameter(Mandatory = $false)]
        [boolean] $DebugExcel = $false
    )
    PROCESS {

        [Microsoft.Office.Interop.Excel.ApplicationClass]$Excel = New-Object -ComObject "Excel.Application"
        $Excel.Visible = $DebugExcel
        $Excel.ScreenUpdating = $DebugExcel
        $Excel.displayAlerts = $DebugExcel
 

        Write-Verbose "NOTE: Opening Excell worksheet"
        $ExcelWorkBook = $Excel.Workbooks.Open("$($FilePath)", 0, $false)
        
        $ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item($ExcelSheetName) 
        $ExcelWorkSheet.activate()

        $LastRowInTableNumber = [int]$ExcelWorkSheet.UsedRange.rows.count;
        $NewRowInTableNumber = [int]$ExcelWorkSheet.UsedRange.rows.count + 1

        if (!($LastRowInTableNumber -gt 1)) {
            Write-verbose 'ERROR: Please make sure to add an table header.'
            break;
        }

        if ($IncrementRowIdentifier) {
        
            if (!$ExcelColumnValuesInRow -is [int]) {
                Write-Verbose 'Please make sure that the row identifier is of type integer.'
                break;
            }
        
            $LastRowIdentifier = $ExcelWorkSheet.Cells.Item($LastRowInTableNumber, 1).Text
            $NewRowIdentifier = [int]$LastRowIdentifier + 1;
            $ExcelColumnValuesInRow[0] = $NewRowIdentifier
        }
        
        for ($i = 0; $i -lt $ExcelColumnValuesInRow.Count; $i++) {
            $ExcelWorkSheet.Cells.Item($NewRowInTableNumber, $i + 1) = $ExcelColumnValuesInRow[$i]
            Write-Progress -Activity "Inserting request column in Excell" -Status "$i% Complete:" -PercentComplete $i;
        }
        Write-Verbose 'NOTE: Inserted new row in Excel sheet'

        $ExcelWorkBook.SaveAs($FilePath) 
        $Excel.Workbooks.Close()
        $Excel.Quit() 
        [GC]::Collect()
        Write-Verbose "NOTE: Excell worksheet saved and closed"     

        return $ExcelColumnValuesInRow
    }
}
Export-ModuleMember -Function Add-RowToExcelTable -Alias AddRowToExcelTable