Functions/Excel/Export-ExcelPattern.ps1
|
Function Export-ExcelPattern { [cmdletbinding()] Param ( # Input Object [Parameter(Mandatory=$true,ValueFromPipeline=$true)] [psobject] $InputObject, # Filesystem Path to output location [Parameter(Mandatory=$true,Position=0)] [string] $Path, # Filesystem Path to Pattern location [Parameter(Mandatory=$true,Position=1)] [string] $PatternPath, # Number of worksheet in pattern [Parameter(Mandatory=$false,Position=1)] [int] $PatternWS = 1, # Y (row) position of origin cell [Parameter(Mandatory=$false,Position=2)] [int] $PatternY = 1, # X (Column) position of origin cell [Parameter(Mandatory=$false,Position=3)] [int] $PatternX = 1, # Notify [Parameter(Mandatory=$false,Position=4)] [boolean] $Notify = $False ) Begin { # Instantiate Excel $Excel = New-Object -comobject Excel.Application $Excel.visible = $false $Excel.DisplayAlerts = $false $xlsx = $Excel.Workbooks.Open($PatternPath) $Worksheet = $xlsx.Worksheets.Item($PatternWS) # Instantiate Data Collection $DATA = [system.collections.arraylist]@() } Process {$ADD = $DATA.add($InputObject)} End { # Send Data into Worksheet $DATA | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | clip $Pasted = $false while(!$Pasted) { $Paste = try{$Worksheet.Rows($PatternY).cells($PatternX).PasteSpecial()} catch {$null} if ($Paste){$Pasted = $true} } # Save Output to Path $xlsx.SaveAs($Path) if($Notify){write-host "`"$Path`" file created!" -ForegroundColor Green} $xlsx.Close() # Exit Excel Com App $Excel.quit() Start-Sleep 2 if($EXCEL.Hwnd -and (Get-ProcessByHandle -Handle $EXCEL.hwnd).hasexited -eq $False) {Stop-ProcessByHandle -Handle $Excel.Hwnd -Quiet} } } |