Exports PowerShell objects from the pipeline to an Excel spreadsheet by saving it to an HTML table, launching Excel using the COM object model, and applying formats and hidden columns as necessary.

Export-Excel.ps1

#.SYNOPSIS
# Exports objects to an Excel spreadsheet by writing them to a temporary
# CSV file and using Excel automation model to import it into a workbook.
# This allows formatting to be applied to columns which would not otherwise
# be possible in a plain CSV export.
function Export-Excel {

    [CmdletBinding()]
    param(

        # The path to save the Excel spreadsheet to.
        [Parameter(Position=1)]
        [String]$Path,

        # An object or array of objects to write out to an Excel spreadsheet.
        # This parameter typically comes from pipeline input.
        [Parameter(ValueFromPipeline=$true)]
        [Object[]]$InputObject,

        # A hashtable that contains column headers as keys and
        # valid Excel format strings as values. The formats will
        # be applied to the columns after the worksheet is generated.
        [Parameter()]
        [Hashtable]$Format,

        # An array of column names to hide.
        # You should probably exclude them from the output by using the
        # Select-Object command earlier in the pipeline, but if you want them
        # to be in the spreadsheet, but simply hidden, use this parameter.
        [Parameter()]
        [String[]]$Hide,

        # Turns off column wrapping on all cells.
        [Parameter()]
        [Switch]$NoWrap,

        # Quits Excel after the spreadsheet is generated.
        # This parameter should be used with caution since Excel may already be
        # open prior to the command.
        [Parameter()]
        [Switch]$Quit

    )

    begin {

        if ($Path) {
        
            # If they supplied an output path, we will actually save the temp file
            # to the specified path in the format indicated by the extension. Since
            # the path will be given to Excel, we need to resolve the native path
            # in case a PowerShell path (like ~\Desktop\blah.xls) was used.
            
            $Path = $PSCmdlet.SessionState.Path.GetUnresolvedProviderPathFromPSPath($Path)

            # If the output file already exists, nuke it.

            if (Test-Path $Path -PathType Leaf) {
                Remove-Item $Path -Force
            }

        }

        # Excel VBA objects

        $Excel = $Null
        $Workbook = $Null
        $Sheet = $Null
        
        # Use a temporary file in the system temp directory to write the
        # results to. If it already exists, delete it.

        $ScratchName  = "Out-Excel.html"
        $ScratchPath  = "$ENV:TEMP\$ScratchName"

        Write-Verbose "Writing output to $ScratchPath"

        Remove-Item $ScratchPath -Force -ErrorAction 0


        # Create a wrapped pipeline that we can pass each input
        # object to as if it were piped directly to ConvertTo-Html.
        # We're using ConvertTo-Html because it produces a decent
        # table that Excel can open without worrying about newlines
        # in a CSV file.

        $ScriptBlock = { ConvertTo-Html -As Table -Title "Out-Excel" | Set-Content $ScratchPath -Encoding UTF8 -Force }
        $Pipeline = $ScriptBlock.GetSteppablePipeline($MyInvocation.CommandOrigin)

        $Pipeline.Begin($PSCmdlet)

        # Define some helper functions for modifying the worksheet
        # using the named column headers

        $Headers = @{}

        # Sets the display format string on columns with a given name
        function SetColumnFormat($Header, $Format) {
            if ($Headers[$Header]) {
                $Range = $Sheet.Cells.Item(1, $Headers[$Header]).EntireColumn
                try {
                    $Range.NumberFormat = $Format
                }
                catch {
                    Write-Warning "Column $Header has invalid format string: $Format ($_)"
                }
            }
        }


        # Hides columns with a given name
        function SetColumnHidden($Header) {
            if ($Headers[$Header]) {
                $Range = $Sheet.Cells.Item(1, $Headers[$Header]).EntireColumn
                try {
                    $Range.Hidden = $True
                }
                catch {
                    Write-Warning "Could not hide column $Header ($_)"
                }
            }
        }

    }

    process {

        # Not much to do here except pass the input object to the
        # wrapped pipeline which sends it to the output file.

        foreach ($o in $InputObject) {
            $Pipeline.Process($o)
        }

    }

    end {

        $Pipeline.End()
        $Pipeline.Dispose()

        # Figure out column headings and store them in a hashtable.
        # This makes it easier to refer to a column range by name.
        $i = 1
        foreach ($Match in [Regex]::Matches($(Get-Content $ScratchPath), '(?is)<TH>([^<]+)</TH>')) {
            $Headers[$Match.Groups[1].Value] = $i++
        }

        # Excel Automation

        try { 
            $Excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application')
        }
        catch [System.Management.Automation.MethodInvocationException] {
            $Excel = New-Object -ComObject 'Excel.Application'
        }

        $Workbook = $Excel.Workbooks.Open($ScratchPath)
        
        $Sheet = $Workbook.Worksheets.Item(1)
        
        # Turn off cell wrapping
        if ($NoWrap) {
            $Sheet.UsedRange.WrapText = $False
        }

        # Set column formats
        foreach ($Key in $Format.Keys) {
            SetColumnFormat $Key $Format[$Key]
        }

        # Hide certain columns
        foreach ($Key in $Hide) {
            SetColumnHidden $Key
        }

        $Workbook.Activate()

        $Excel.ActiveWindow.DisplayGridlines = $true

        # Save As?

        if ($Path) {

            $FileFormat = 51
            switch ([IO.Path]::GetExtension($Path)) {
                '.xlsb' { $FileFormat = 50 } # Excel 12 Binary
                '.xlsx' { $FileFormat = 51 } # Excel 12 XML (No Macro)
                '.xlsm' { $FileFormat = 52 } # Excel 12 (With Macro)
                '.xls'  { $FileFormat = 56 } # Excel Classic
            }

            $Workbook.SaveAs($Path, $FileFormat)

        }

        if ($Quit) {
            $Excel.Quit()
        }
        else {
            $Excel.Visible = $true
            $Excel.ActiveWindow.Activate()
        }

        $Excel = $null

    }

}