Public/ImportExcel/Export-PoshExcel.ps1

function Export-PoshExcel {
    <#
    .SYNOPSIS
    Create an excel from a csv, directory of csv's, or recursively through a directory and subdirectories (csv's)
    Each tab will be named the name of the csv
    This wraps Doug Finks Export-Excel
 
    .DESCRIPTION
    Create an excel from a csv, directory of csv's, or recursively through a directory and subdirectories (csv's)
    Each tab will be named the name of the csv
    This wraps Doug Finks Export-Excel
 
    .PARAMETER InputDirectory
    The directory that contains the csv's
 
    .PARAMETER ObjectInput
    This is used to pipe to an Excel file any object the same way you would traditionally pipe to Export-Csv
 
    .PARAMETER OutputDirectory
    The directory where the Excel file will be created
 
    .PARAMETER ExcelFilename
    The name of the Excel file you would like to create
 
    .PARAMETER Recurse
    Use this switch to create the excel from all csv's in a directory and also its subdirectories and so on.
    "Recursive"
 
    .PARAMETER Color
    Options are Grey, Blue, Orange, LtGrey, Gold, LtBlue, or Green
 
    .EXAMPLE
    Export-PoshExcel -InputDirectory C:\Users\Kevin\Desktop\temp -OutputDirectory C:\Scripts\ -ExcelFilename Test.xlsx -Recurse
 
    .EXAMPLE
    Get-Process | Export-PoshExcel -OutputDirectory C:\Scripts\ -ExcelFilename Process.xlsx -Color Green
 
    .EXAMPLE
    Import-Csv c:\scripts\allusers.csv | Export-PoshExcel -OutputDirectory C:\Scripts\ -ExcelFilename Process.xlsx -Color Green
 
    .NOTES
    The Excel file, by default, will have these features
     - Freeze Top Row
     - Freeze First Column
     - AutoSize each column
     - The top row will be bold
     - If the sheet is already there it will clear it before writing to it
     - If the sheet is not already there but the excel file is, it will add the sheet
 
    #>


    [CmdletBinding()]
    param (

        [Parameter(Mandatory, ParameterSetName = 'DirectoryInput')]
        [ValidateScript( { Test-Path -PathType Container -Path $_ })]
        [string]
        $InputDirectory,

        [Parameter(Mandatory, ParameterSetName = 'ObjectInput', ValueFromPipeline)]
        [Object[]]
        $ObjectInput,

        [Parameter(Mandatory, ParameterSetName = 'DirectoryInput')]
        [Parameter(Mandatory, ParameterSetName = 'ObjectInput')]
        [ValidateScript( { Test-Path -PathType Container -Path $_ })]
        [string]
        $OutputDirectory,

        [Parameter(Mandatory, ParameterSetName = 'DirectoryInput')]
        [Parameter(Mandatory, ParameterSetName = 'ObjectInput')]
        [ValidateNotNull()]
        [string]
        $ExcelFilename,

        [Parameter(ParameterSetName = 'DirectoryInput')]
        [switch]
        $Recurse,

        [Parameter(ParameterSetName = 'DirectoryInput')]
        [Parameter(ParameterSetName = 'ObjectInput')]
        [ValidateSet('Grey', 'Blue', 'Orange', 'LtGrey', 'Gold', 'LtBlue', 'Green')]
        [string]
        $Color = 'Blue'
    )
    begin {
        $PipelineObject = [System.Collections.Generic.List[PSObject]]::New()
        $ColorHash = @{
            Grey   = 'Medium1'
            Blue   = 'Medium2'
            Orange = 'Medium3'
            LtGrey = 'Medium4'
            Gold   = 'Medium5'
            LtBlue = 'Medium6'
            Green  = 'Medium7'
        }
        $EA = $ErrorActionPreference
        $ErrorActionPreference = "SilentlyContinue"
        $ExcelSplat = @{
            Path                    = (Join-Path $OutputDirectory $ExcelFilename)
            TableStyle              = $ColorHash[$Color]
            FreezeTopRowFirstColumn = $true
            AutoSize                = $true
            BoldTopRow              = $false
            ErrorAction             = 'SilentlyContinue'
        }
    }
    process {
        switch ($PSCmdlet.ParameterSetName) {
            'ObjectInput' {
                foreach ($Object in $ObjectInput) {
                    $PipelineObject.Add($Object)
                }
            }
            'DirectoryInput' {
                $GciSplat = @{
                    Path    = $InputDirectory
                    Filter  = '*.csv'
                    Recurse = $Recurse
                }
                Get-ChildItem @GciSplat | Sort-Object BaseName -Descending | ForEach-Object {
                    Import-Csv $_.fullname | Export-Excel @ExcelSplat -WorksheetName $_.basename
                }
            }
        }
    }
    end {
        switch ($PSCmdlet.ParameterSetName) {
            'ObjectInput' {
                $PipelineObject | Export-Excel @ExcelSplat
            }
        }
        $ErrorActionPreference = $EA
    }
}