Public/Add-ExcelWorkSheetData.ps1

function Add-ExcelWorksheetData {
    [CmdletBinding()]
    Param(
        [alias('ExcelWorkbook')][OfficeOpenXml.ExcelPackage] $ExcelDocument,
        $ExcelWorksheet, # [OfficeOpenXml.ExcelWorksheet]
        [Parameter(ValueFromPipeline = $true)][Object] $DataTable,
        [ValidateSet("Replace", "Skip", "Rename")][string] $Option = 'Replace',
        [int]$StartRow = 1,
        [int]$StartColumn = 1,
        [alias("Autosize")][switch] $AutoFit,
        [switch] $AutoFilter,
        [Switch] $FreezeTopRow,
        [Switch] $FreezeFirstColumn,
        [Switch] $FreezeTopRowFirstColumn,
        [int[]]$FreezePane,
        [alias('Name', 'WorksheetName')][string] $ExcelWorksheetName,
        [alias('Rotate', 'RotateData', 'TransposeColumnsRows', 'TransposeData')][switch] $Transpose,
        [ValidateSet("ASC", "DESC", "NONE")][string] $TransposeSort = 'NONE',
        [switch] $PreScanHeaders, # this feature scans properties of an object for all objects it contains to make sure all headers are there
        [bool] $Supress
    )
    Begin {
        $FirstRun = $True
        $RowNr = if ($StartRow -ne $null -and $StartRow -ne 0) { $StartRow } else { 1 }
        $ColumnNr = if ($StartColumn -ne $null -and $StartColumn -ne 0 ) { $StartColumn } else { 1 }
        if ($ExcelWorksheet -ne $null) {
            Write-Verbose "Add-ExcelWorkSheetData - ExcelWorksheet given. Continuing..."
        } else {
            if ($ExcelDocument) {
                $ExcelWorkSheet = Add-ExcelWorkSheet -ExcelDocument $ExcelDocument -Name $ExcelWorksheetName -Option $Option
            } else {
                Write-Warning 'Add-ExcelWorksheetData - ExcelDocument and ExcelWorksheet not given. No data will be added...'
                # throw 'Add-ExcelWorksheetData - ExcelDocument and ExcelWorksheet not given. Terminating.'
            }
        }
        Write-Verbose "Add-ExcelWorksheetData - Excel Row: $RowNr Column: $ColumnNr"
    }
    Process {
        if ((Get-ObjectCount -Object $DataTable) -ne 0) {
            if ($FirstRun) {
                $FirstRun = $false
                #Write-Verbose "Add-ExcelWorksheetData - FirstRun - RowsToProcess: $($DataTable.Count) - Transpose: $Transpose AutoFit: $Autofit Autofilter: $Autofilter"
                if ($Transpose) { $DataTable = Format-TransposeTable -Object $DataTable -Sort $TransposeSort }
                $Data = Format-PSTable -Object $DataTable -ExcludeProperty $ExcludeProperty -NoAliasOrScriptProperties:$NoAliasOrScriptProperties -DisplayPropertySet:$DisplayPropertySet -PreScanHeaders:$PreScanHeaders # -SkipTitle:$NoHeader
                $WorksheetHeaders = $Data[0] # Saving Header information for later use
                #Write-Verbose "Add-ExcelWorksheetData - Headers: $($WorksheetHeaders -join ', ') - Data Count: $($Data.Count)"
                if ($NoHeader) {
                    $Data.RemoveAt(0);
                    #Write-Verbose "Removed header from ArrayList - Data Count: $($Data.Count)"
                }
                $ArrRowNr = 0
                foreach ($RowData in $Data) {
                    $ArrColumnNr = 0
                    $ColumnNr = $StartColumn
                    foreach ($Value in $RowData) {
                        #Write-Verbose "Row: $RowNr / $ArrRowNr Column: $ColumnNr / $ArrColumnNr Data: $Value Title: $($WorksheetHeaders[$ArrColumnNr])"
                        Add-ExcelWorkSheetCell -ExcelWorksheet $ExcelWorksheet -CellRow $RowNr -CellColumn $ColumnNr -CellValue $Value -Supress $True
                        $ColumnNr++
                        $ArrColumnNr++
                    }
                    $ArrRowNr++
                    $RowNr++

                }
            } else {
                #Write-Verbose "Add-ExcelWorksheetData - NextRun - RowsToProcess: $($DataTable.Count) - Transpose: $Transpose AutoFit: $Autofit Autofilter: $Autofilter"
                if ($Transpose) { $DataTable = Format-TransposeTable -Object $DataTable -Sort $TransposeSort }
                $Data = Format-PSTable -Object $DataTable -SkipTitle -ExcludeProperty $ExcludeProperty -NoAliasOrScriptProperties:$NoAliasOrScriptProperties -DisplayPropertySet:$DisplayPropertySet -OverwriteHeaders $WorksheetHeaders -PreScanHeaders:$PreScanHeaders
                $ArrRowNr = 0
                foreach ($RowData in $Data) {
                    $ArrColumnNr = 0
                    $ColumnNr = $StartColumn
                    foreach ($Value in $RowData) {
                        #Write-Verbose "Row: $RowNr / $ArrRowNr Column: $ColumnNr / $ArrColumnNr Data: $Value Title: $($WorksheetHeaders[$ArrColumnNr])"
                        Add-ExcelWorkSheetCell -ExcelWorksheet $ExcelWorksheet -CellRow $RowNr -CellColumn $ColumnNr -CellValue $Value -Supress $True
                        $ColumnNr++; $ArrColumnNr++
                    }
                    $RowNr++; $ArrRowNr++
                }
            }
        }
    }
    End {
        if ($AutoFit) { Set-ExcelWorksheetAutoFit -ExcelWorksheet $ExcelWorksheet }
        if ($AutoFilter) { Set-ExcelWorksheetAutoFilter -ExcelWorksheet $ExcelWorksheet -DataRange $ExcelWorksheet.Dimension -AutoFilter $AutoFilter }
        if ($FreezeTopRow -or $FreezeFirstColumn -or $FreezeTopRowFirstColumn -or $FreezePane) {
            Set-ExcelWorkSheetFreezePane -ExcelWorksheet $ExcelWorksheet `
                -FreezeTopRow:$FreezeTopRow `
                -FreezeFirstColumn:$FreezeFirstColumn `
                -FreezeTopRowFirstColumn:$FreezeTopRowFirstColumn `
                -FreezePane $FreezePane
        }
        if ($Supress) { return } else { return $ExcelWorkSheet }
    }

}