Add-XLTable.ps1

# internal helper functions
function Get-Value($Datum, $ColumnDefinition) {
   $value = $Datum | ForEach-Object -Process $ColumnDefinition.Expression;
   if ($value -eq $null -and $ColumnDefinition.ContainsKey('Default')) {
        $value = $ColumnDefinition.Default;
   }
   $value;
}

function Get-Columns($Datum, $ColumnDefinitions) {
    # normalize columns
    if ($ColumnDefinitions -eq $null) {
        $ColumnDefinitions = Get-Member -InputObject $Datum -MemberType Properties | 
            ForEach-Object -Process { @{Name = $_.Name; Property = $_.Name; } }
    }

    foreach ($col in $ColumnDefinitions) {
        if (-not $col.ContainsKey('Expression')) {
            $propertyName = $col.Property;
            $col.Expression = { $_.$propertyName }.GetNewClosure();
        }

        if (-not $col.ContainsKey('Default')) {
            $col.Default = $null;
        }

        if (-not $col.ContainsKey('Type')) {
            $value = Get-Value -Datum $Datum -Column $col;
            if ($value -ne $null) {
                $col.Type = $value.GetType();
            } else {
                $col.Type = $null;
            }
        }

        if ($col['NumberFormat'] -ne $null) {
            $col.NumberFormat = [XLNumberFormat]$col.NumberFormat;
        } else {
            $col.NumberFormat = switch ($col.Type) {
                    {$_ -eq [String]}        {[XLNumberFormat]::Text}
                    {$_ -eq [DateTime]}      {[XLNumberFormat]::DateTime}
                    default                  {[XLNumberFormat]::General}
                };
        }
        if ($col['Totals'] -eq $null) {
            $col.Totals = $null;
        }
    }

    $ColumnDefinitions;
}

function Add-XLTable {
[CmdletBinding()]
param(
    [Parameter(Mandatory = $true, Position = 0, ValueFromPipeline=$true)]
    [XLSheet]$Sheet,
    [Parameter(Mandatory = $true)]
    [string]$Name,
    [Parameter(Mandatory = $true)]
    [object]$Data,
    [object[]]$Columns = $null,
    [int]$Row = 0,
    [int]$Column = 0,
    [switch]$AutoSize = $false,
    [Switch]$Transpose = $false,
    [switch]$PassThru = $false
)  
begin{
    #validate some input
    if ($Columns -ne $null) {
        $Columns = $Columns | ForEach-Object -Process {
                switch ($_) {
                    {$_ -is [string]} { @{Name = $_; Property = $_}; break; }
                    {$_ -is [System.Collections.IDictionary]} { 
                        if ($_['Name'] -eq $null) {
                            if ($_['Property'] -ne $null) {
                                $_.Name = $_.Property;        
                            } else {
                                throw "Name or Property is required for column definition";
                            }
                        } elseif ($_['Property'] -eq $null -and $_['Expression'] -eq $null) {
                            $_.Property = $_.Name;
                        }

                        if ($_['Property'] -eq $null -and $_['Expression'] -eq $null) {
                            throw "Property or Expression is requierd for column definitions";
                        }

                        if ($_['Type'] -ne $null) {
                            if ($_.Type -isnot [Type]) {
                                if ($_.Type -is [string]) {
                                    Write-Verbose -Message "Coercing string '${_.Type}' to [Type]"
                                    $_.Type = [Type]$_.Type;
                                } else {
                                    throw "Type must be either String or Type";
                                }
                            }
                        }
                        $_;
                        break;
                    }
                    default {throw "Invalid column definition: " + $_;}
                }
            };
        }


    # extract tabular data
    $rows = [System.Collections.Generic.List[object[]]]::new(); 

    if ($Data -is [System.Collections.IDictionary]) {
        foreach ($kvp in $Data.GetEnumerator()) {
            $rows.Add(@($kvp.Name, $kvp.Value))
        }
    } else {
        [bool]$firstIteration = $true;
        foreach ($datum in @($Data)) {
            if ($firstIteration) {
                $Columns = Get-Columns -Datum $datum -ColumnDefinitions $Columns
                # add header row
                $rows.Add($Columns.Name);
                $firstIteration = $false;
            }

            $rows.Add(@($Columns | ForEach-Object -Process { Get-Value -Datum $datum -Column $_ }));
        }
    }

}
process{

    # find empty location in sheet that can accomodate data
    [int]$tableHeight = $rows.Count;
    [int]$tableWidth = $rows[0].Count;

    $worksheet = $Sheet.Worksheet;
    if ($worksheet.Dimension -ne $null) {
        $lastRow = $worksheet.Dimension.End.Row;
        $lastCol = $worksheet.Dimension.End.Column;
        # for some reason $Sheet.Dimension.End doens't include tables Total rows
        if ($worksheet.Tables.Count -gt 0) {
            $lastTableRow = ($worksheet.Tables.Address.End.Row | Measure-Object -Maximum).Maximum;
        } else {
            $lastTableRow = 1;
        }
        $lastRow = [Math]::Max($lastRow, $lastTableRow);
    } else {
        $lastRow = 0;
        $lastCol = 0;
    }

    if ($Row -eq 0 -and $Column -eq 0) {
        $Row = $lastRow + 2;
        $Column = 2;
    } elseif ($Row -eq 0) {
        $Row = $lastRow + 2;
    } elseif ($Column -eq 0) {
        $Column = $lastCol + 2;
    }

    # write data into sheet
    [int]$currentRow = $Row;
    foreach ($dataRow in $rows) {
        [int]$currentColumn = $Column;
        foreach ($value in $dataRow) {
            if ($Transpose.IsPresent) {
                $cell = $worksheet.Cells[$currentColumn, $currentRow];
            } else {
                $cell = $worksheet.Cells[$currentRow, $currentColumn];
            }
            
            $colDef = $Columns[$currentColumn - $Column]; 
            $colType = $colDef.Type;
            $colValue = $null;
            # TODO maybe exclude the header from the $rows data

            # don't convert the header
            if ($currentRow -gt $Row -and $colType -ne $null -and $value -isnot $colType) {
                $result = $null;
                if ($value -eq $null -or $value -eq '' -or ($value -is [Single] -and [Single]::IsNaN($value)) -or ($value -is [Double] -and [Double]::IsNaN($value))) {
                    $colValue = $colDef.Default;
                } else {
                    try {
                        Invoke-Expression "`$result = [$colType]`$value"
                        if ($result -eq $null) {
                            Write-Warning -Message "Failed to convert value '$value' to type '$colType'";
                            $colValue = $colDef.Default;
                        } else {
                            $colValue = $result;
                        }                        
                    } catch {
                        Write-Warning -Message "Failed to convert value '$value' to type '$colType'";
                        $colValue = $colDef.Default;
                    }
                }
            } else {
                $colValue = if ($value -ne $null) {$value} else {$colDef.Default};
            }

            if ($colValue -ne $null) {
                $cell.Value = $colValue;
                $cellFmt = $cell.Style.Numberformat;
                # this could very well be wrong
                switch ([XLNumberFormat]$colDef.NumberFormat) {
                    "Text" { $cellFmt.Format = "Text" }
                    "Date" { $cellFmt.Format = "yyyy-mm-dd" }
                    "General" { $cellFmt.Format = "General" }
                    "Percent" { $cellFmt.Format = "0.0%"; }
                    "DateTime" { $cellFmt.Format = "yyyy-mm-dd h:mm.ss" }
                    "Time" { $cellFmt.Format = "h:mm.ss" }
                }
            } elseif ($colValue -eq $null) {
                $cell.Formula = '=NA()'
            }
            $currentColumn++;
        }
        $currentRow++;
    }

    # create table
    if ($Transpose.IsPresent) {
        $tableRange = [OfficeOpenXml.ExcelRange]::GetAddress($Row, $Column, $Row + $tableWidth - 1, $Column + $tableHeight - 1);
    } else {
        $tableRange = [OfficeOpenXml.ExcelRange]::GetAddress($Row, $Column, $Row + $tableHeight - 1, $Column + $tableWidth - 1);
    }
    $table = $worksheet.Tables.Add($tableRange, $Name)

    if ($AutoSize) {
        $worksheet.Cells[$tableRange].AutoFitColumns();
    }
    if ($Transpose.IsPresent) {
        $table.ShowHeader = $false;
    } else {
        if ($Columns | Where-Object -Property Totals -NE -Value $null) {
            
            $totalsColumn = 0;
            # TODO replace with for loop
            foreach ($col in $Columns) {
                if ($col.Totals -ne $null) {
                    
                    try {
                        $xlTotalsFunction = [XLTotalsFunction]$col.Totals;
                    } catch {
                        $xlTotalsFunction = $null;
                    }

                    $totalsFormula = $null;
                    if ($xlTotalsFunction -ne $null) {                        
                        $totalsFormula = '=SUBTOTAL({0},{1}[{2}])' -f [int]$xlTotalsFunction,$table.Name,$col.Name;
                    } elseif ($col.Totals -is [string] -and $col.Totals[0] -eq '=') {
                        $totalsForumla = $col.Totals;
                    } else {
                        throw "Invalid 'Total' function/formula: " + $col.Totals
                    }
                    $table.Columns[$totalsColumn].TotalsRowFormula = $totalsFormula;
                }
                $totalsColumn++;   
            }

            $table.ShowTotal= $true;

        }
    }
    if ($PassThru.IsPresent) {
        Write-Output -InputObject $Sheet
    }
}
end{}
}