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 AddRowToExcelTable() { [cmdletbinding()] 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 AddRowToExcelTable |