Public/Add-ExcelTable.ps1

function Add-ExcelTable {
    [CmdletBinding()]
    [OutputType([OfficeOpenXml.Table.ExcelTable])]
    param (
        [Parameter(Mandatory=$true)]
        [OfficeOpenXml.ExcelRange]$Range,
        [String]$TableName = "",
        [OfficeOpenXml.Table.TableStyles]$TableStyle = 'Medium6',
        [Switch]$ShowHeader ,
        [Switch]$ShowFilter,
        [Switch]$ShowTotal,
        [hashtable]$TotalSettings,
        [Switch]$ShowFirstColumn,
        [Switch]$ShowLastColumn,
        [Switch]$ShowRowStripes,
        [Switch]$ShowColumnStripes,
        [Switch]$PassThru
    )
    try {
        if ($TableName -eq "" -or $null -eq $TableName) {
            $tbl = $Range.Worksheet.Tables.Add($Range, "")
        }
        else {
            if ([OfficeOpenXml.FormulaParsing.ExcelUtilities.ExcelAddressUtil]::IsValidAddress($TableName)) {
                Write-Warning -Message "$TableName reads as an Excel address, and so is not allowed as a table name."
                return
            }
            if ($TableName -notMatch '^[A-Z]') {
                Write-Warning -Message "$TableName is not allowed as a table name because it does not begin with a letter."
                return
            }
            if ($TableName -match "\W") {
                Write-Warning -Message "At least one character in $TableName is illegal in a table name and will be replaced with '_' . "
                $TableName = $TableName -replace '\W', '_'
            }
            $ws = $Range.Worksheet
            #if the table exists in this worksheet, update it.
            if ($ws.Tables[$TableName]) {
                $tbl =$ws.Tables[$TableName]
                $tbl.TableXml.table.ref = $Range.Address
                Write-Verbose -Message "Re-defined table '$TableName', now at $($Range.Address)."
            }
            elseif ($ws.Workbook.Worksheets.Tables.Name -contains $TableName) {
                Write-Warning -Message "The Table name '$TableName' is already used on a different worksheet."
                return
            }
            else {
                $tbl = $ws.Tables.Add($Range, $TableName)
                Write-Verbose -Message "Defined table '$($tbl.Name)' at $($Range.Address)"
            }
        }
        #it seems that show total changes some of the others, so the sequence matters.
        if     ($PSBoundParameters.ContainsKey('ShowHeader'))        {$tbl.ShowHeader        = [bool]$ShowHeader}
        if     ($PSBoundParameters.ContainsKey('TotalSettings'))     {
            $tbl.ShowTotal = $true
            foreach ($k in $TotalSettings.keys) {
                if (-not $tbl.Columns[$k]) {Write-Warning -Message "Table does not have a Column '$k'."}
                elseif ($TotalSettings[$k] -notin @("Average", "Count", "CountNums", "Max", "Min", "None", "StdDev", "Sum", "Var") ) {
                    Write-Warning -Message "'$($TotalSettings[$k])' is not a valid total function."
                }
                else {$tbl.Columns[$k].TotalsRowFunction = $TotalSettings[$k]}
            }
        }
        elseif ($PSBoundParameters.ContainsKey('ShowTotal'))         {$tbl.ShowTotal         = [bool]$ShowTotal}
        if     ($PSBoundParameters.ContainsKey('ShowFilter'))        {$tbl.ShowFilter        = [bool]$ShowFilter}
        if     ($PSBoundParameters.ContainsKey('ShowFirstColumn'))   {$tbl.ShowFirstColumn   = [bool]$ShowFirstColumn}
        if     ($PSBoundParameters.ContainsKey('ShowLastColumn'))    {$tbl.ShowLastColumn    = [bool]$ShowLastColumn}
        if     ($PSBoundParameters.ContainsKey('ShowRowStripes'))    {$tbl.ShowRowStripes    = [bool]$ShowRowStripes}
        if     ($PSBoundParameters.ContainsKey('ShowColumnStripes')) {$tbl.ShowColumnStripes = [bool]$ShowColumnStripes}
        $tbl.TableStyle = $TableStyle

        if ($PassThru) {return $tbl}
    }
    catch {Write-Warning -Message "Failed adding table '$TableName' to worksheet '$WorksheetName': $_"}
}