Automatos.Common

1.0.0

Functions/Data/Export-Excel.ps1

Function Export-Excel
    {
    [cmdletbinding()]
    Param
        (
        # Input Object
        [Parameter(Mandatory=$true,ValueFromPipeline=$true)]
        [psobject]
        $InputObject,

        # Filesystem Path to output location
        [Parameter(Mandatory=$true,Position=0)]
        [string]
        $Path
        )

    Begin
        {
        # Instantiate Excel
        $Excel = new-object -comobject Excel.Application
        $Excel.visible = $false
        $Excel.DisplayAlerts = $false
        $Excel.SheetsInNewWorkbook = 1
        
        # Make Workbook
        $xlsx = $Excel.Workbooks.Add()

        # Make Worksheet
        $Worksheet = $xlsx.Worksheets.Item(1)

        # 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.cells.Item(1).PasteSpecial()}catch{$null}
            if ($Paste){$Pasted = $true}
            }

        # Save Output to Path
        $xlsx.SaveAs($Path)
        $xlsx.Close()

        # Exit Excel Com App
        $Excel.quit()
        }
    }