Set-ExcelRow.ps1

function  Set-ExcelRow {
    [CmdletBinding()]
    [Alias("Set-Row")]
    [OutputType([OfficeOpenXml.ExcelRow],[String])]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingfunctions', '',Justification='Does not change system state')]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification="Variables created for script block which may be passed as a parameter, but not used in the script")]
    param(
        [Parameter(ParameterSetName="Package",Mandatory=$true)]
        [OfficeOpenXml.ExcelPackage]$ExcelPackage,
        [Parameter(ParameterSetName="Package")]
        $WorksheetName = "Sheet1",
        [Parameter(ParameterSetName="Sheet",Mandatory=$true)]
        [OfficeOpenXml.Excelworksheet] $Worksheet,
        [Parameter(ValueFromPipeline = $true)]
        $Row = 0 ,
        [int]$StartColumn,
        $Value,
        $Heading ,
        [Switch]$HeadingBold,
        [Int]$HeadingSize ,
        [Alias("NFormat")]
        $NumberFormat,
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderAround,
        $BorderColor=[System.Drawing.Color]::Black,
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderBottom,
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderTop,
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderLeft,
        [OfficeOpenXml.Style.ExcelBorderStyle]$BorderRight,
        $FontColor,
        [Switch]$Bold,
        [Switch]$Italic,
        [Switch]$Underline,
        [OfficeOpenXml.Style.ExcelUnderLineType]$UnderLineType = [OfficeOpenXml.Style.ExcelUnderLineType]::Single,
        [Switch]$StrikeThru,
        [OfficeOpenXml.Style.ExcelVerticalAlignmentFont]$FontShift,
        [String]$FontName,
        [float]$FontSize,
        $BackgroundColor,
        [OfficeOpenXml.Style.ExcelFillStyle]$BackgroundPattern = [OfficeOpenXml.Style.ExcelFillStyle]::Solid ,
        [Alias("PatternColour")]
        $PatternColor,
        [Switch]$WrapText,
        [OfficeOpenXml.Style.ExcelHorizontalAlignment]$HorizontalAlignment,
        [OfficeOpenXml.Style.ExcelVerticalAlignment]$VerticalAlignment,
        [ValidateRange(-90, 90)]
        [int]$TextRotation ,
        [float]$Height,
        [Alias('Hidden')]
        [Switch]$Hide,
        [Switch]$ReturnRange,
        [Switch]$PassThru
    )
    begin {
        #if we were passed a package object and a worksheet name , get the worksheet.
        if ($ExcelPackage)  {
            if ($ExcelPackage.Workbook.Worksheets.Name -notcontains $WorksheetName) {
                throw "The Workbook does not contain a sheet named '$WorksheetName'"
            }
            else {$Worksheet   = $ExcelPackage.Workbook.Worksheets[$WorksheetName] }
        }
        #In a script block to build a formula, we may want any of corners or the columnname,
        #if row and start column aren't specified assume first unused row, and first column
        if (-not $StartColumn) {$StartColumn = $Worksheet.Dimension.Start.Column    }
        $startRow                            = $Worksheet.Dimension.Start.Row   + 1
        $endColumn                           = $Worksheet.Dimension.End.Column
        $endRow                              = $Worksheet.Dimension.End.Row
    }
    process {
        if ($null -eq $Worksheet.Dimension) {Write-Warning "Can't format an empty worksheet."; return}
        if      ($Row  -eq 0 ) {$Row         = $endRow + 1 }
        Write-Verbose -Message "Updating Row $Row"
        #Add a row label
        if      ($Heading)     {
            $Worksheet.Cells[$Row, $StartColumn].Value = $Heading
            if ($HeadingBold) {$Worksheet.Cells[$Row, $StartColumn].Style.Font.Bold = $true}
            if ($HeadingSize) {$Worksheet.Cells[$Row, $StartColumn].Style.Font.Size = $HeadingSize}
            $StartColumn ++
        }
        #Fill in the data
        if      ($PSBoundParameters.ContainsKey('Value')) {foreach ($column in ($StartColumn..$endColumn)) {
            #We might want the column name in a script block
            $columnName = (New-Object -TypeName OfficeOpenXml.ExcelCellAddress @(1,$column)).Address -replace "1",""
            if  ($Value -is [scriptblock] ) {
                #re-create the script block otherwise variables from this function are out of scope.
                $cellData = & ([scriptblock]::create( $Value ))
                if ($null -eq $cellData) {Write-Verbose -Message "Script block evaluates to null."}
                else                     {Write-Verbose -Message "Script block evaluates to '$cellData'"}
            }
            else{$cellData = $Value}
            if  ($cellData -match "^=")      { $Worksheet.Cells[$Row, $column].Formula                    = ($cellData -replace '^=','') } #EPPlus likes formulas with no = sign; Excel doesn't care
            elseif ( [System.Uri]::IsWellFormedUriString($cellData , [System.UriKind]::Absolute)) {
                # Save a hyperlink : internal links can be in the form xl://sheet!E419 (use A1 as goto sheet), or xl://RangeName
                if ($cellData -match "^xl://internal/") {
                    $referenceAddress = $cellData -replace "^xl://internal/" , ""
                    $display          = $referenceAddress -replace "!A1$"    , ""
                    $h = New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList $referenceAddress , $display
                    $Worksheet.Cells[$Row, $Column].HyperLink = $h
                }
                else {$Worksheet.Cells[$Row, $Column].HyperLink = $cellData }
                $Worksheet.Cells[$Row, $Column].Style.Font.Color.SetColor([System.Drawing.Color]::Blue)
                $Worksheet.Cells[$Row, $Column].Style.Font.UnderLine = $true
            }
            else                             { $Worksheet.Cells[$Row, $column].Value                      = $cellData                    }
            if  ($cellData -is [datetime])   { $Worksheet.Cells[$Row, $column].Style.Numberformat.Format  = 'm/d/yy h:mm'                } #This is not a custom format, but a preset recognized as date and localized.
            if  ($cellData -is [timespan])   { $Worksheet.Cells[$Row, $Column].Style.Numberformat.Format  = '[h]:mm:ss'                  }
        }}
        #region Apply formatting
        $params = @{}
        foreach ($p in @('Underline','Bold','Italic','StrikeThru', 'FontName', 'FontSize', 'FontShift','NumberFormat','TextRotation',
                        'WrapText', 'HorizontalAlignment','VerticalAlignment', 'Height', 'FontColor'
                        'BorderAround', 'BorderBottom', 'BorderTop', 'BorderLeft', 'BorderRight', 'BorderColor',
                        'BackgroundColor', 'BackgroundPattern', 'PatternColor')) {
            if ($PSBoundParameters.ContainsKey($p)) {$params[$p] = $PSBoundParameters[$p]}
        }
        if ($params.Count) {
            $theRange = New-Object -TypeName OfficeOpenXml.ExcelAddress @($Row, $StartColumn, $Row, $endColumn)
            Set-ExcelRange -Worksheet $Worksheet -Range $theRange @params
        }
        #endregion
        if ($PSBoundParameters.ContainsKey('Hide')) {$Worksheet.Row($Row).Hidden = [bool]$Hide}
        #return the new data if -passthru was specified.
        if     ($passThru)     {$Worksheet.Row($Row)}
        elseif ($ReturnRange)  {$theRange}
    }
}