Pass objects to an Excel worksheet

Export-ExcelWorksheet.ps1

#requires -Version 3.0

<#
.SYNOPSIS
    Export an object's properties to a new Excel Worksheet.

.DESCRIPTION
    Using COM will create a new visible instance of Excel and insert an object's NoteProperties
    row by row. You can optionally exclude the property Names from the first row and also choose
    on what row to start inserting cell values.

.NOTES
    Author   : Lido Paglia <[email protected]>
    Date     : 02/21/2014 09:54:48
    Tags     : Excel, COM, Object, 
    Comments : An example of launching Excel and pasting in object values as suggested by Doug Finke 
               in response to my previous gist on pasting object data from PowerShell to Excel:
               <https://gist.github.com/lpaglia/9073991>. I find this method, while automated, to be 
               rather slow. Perhaps there is a better approach. I also had difficulty with how to 
               provide an estimated completion time to write-progress while still supporting pipeline 
               input. Also need to address an issue with errors writing cell data for object properties 
               that appear to be empty collections. The ServicesDependedOn property of a ServiceController 
               object is an example of where this issue surfaces.
    
.PARAMETER InputObject
    The InputObject to export to Excel.

.PARAMETER ExcludeHeaders
    Optional parameter that will exclude writing the proeprty names of the object 

.PARAMETER StartingRow
    Optional integer value for the Excel row to start inserting object data. The starting row
    must be a positive integer greater than 0.

.PARAMETER PassThru
    Optional parameter to pass objects through the pipeline.

.INPUTS
    [System.Object] You can pipe objects to Export-ExcelWorksheet.

.OUTPUTS
    [System.Object] Export-ExcelWorksheet optionally returns the input object to the pipeline using
    the PassThru parameter.

.EXAMPLE
    Export-ExcelWorksheet -InputObject (Get-Service)

.EXAMPLE
    dir | select Name,FullName | Export-ExcelWorksheet

.EXAMPLE
    Get-ADComputer -Filter * | Export-ExcelWorksheet

.EXAMPLE
    Export-ExcelWorksheet -inputobject $computers[0..1] -ExcludeHeaders -StartingRow 3 -PassThru

.LINK
    https://twitter.com/dfinke/status/435825544148844544

#>

function Export-ExcelWorksheet
{
    [cmdletbinding()]

    Param(
        [Parameter(Position=0,Mandatory,ValueFromPipeline)]
        [System.Object]$InputObject,

        [switch]$ExcludeHeaders,

        [ValidateScript({if($_ -gt 0){$true}else{
            throw "StartingRow must be greater than 0."}})]
        [int]$StartingRow = 1,

        [switch]$PassThru
    )
    begin
    {
        try
        {    
            $Excel = New-Object -ComObject Excel.Application -ErrorAction Stop
            $Excel.visible = $true
            $Workbook=$excel.Workbooks.add()
        }
        catch
        {
            throw $_.Exception.Message
        }
    }
    process
    {
        Write-Progress -Activity "Exporting object to Excel" -Status "Pasting to Excel."

        foreach($Object in $InputObject)
        {
            if(-Not$ExcludeHeaders)
            {
                Write-Progress -Activity "Exporting object to Excel" -Status "Pasting property names."
                $Properties = $Object | Get-Member -MemberType NoteProperty,Property
                
                for ($i = 0; $i -lt $Properties.count; $i++)
                {
                    $Workbook.ActiveSheet.Cells.Item(1, $i+1).Value2 = $Properties[$i].Name
                }

                $ExcludeHeaders = $true
                $StartingRow++
            }
        
            for ($i = 0; $i -lt $Properties.count; $i++)
            {
                Write-Progress -Activity "Exporting object to Excel" -Status "Pasting object Record at Row: $StartingRow"

                $CellValue = $Object.($Properties[$i].Name)
                
                if(($CellValue).Count -gt 1)
                {
                    $CellValue = $CellValue -join ', '
                }

                if($CellValue -ne $null)
                {
                    try
                    {
                        $Workbook.ActiveSheet.Cells.Item($StartingRow, $i+1).Value2 = $CellValue
                    }
                    catch
                    {
                        Write-Error "Could not export data to Excel (Cell: $StartingRow, $($i+1)) for Object Property: $($Properties[$i].Name)"
                    }
                }
            }

            if($PassThru)
            {
                $Object
            }

            $StartingRow++
        }
    }
    end
    {
        Write-Progress -Activity "Exporting object to Excel" -Completed -Status "Finished."
    }
}