Public/Export-Excel.ps1

function Export-Excel {
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    [OutputType([OfficeOpenXml.ExcelPackage])]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingPlainTextForPassword", "")]
    param(
        [Parameter(ParameterSetName = 'Default', Position = 0)]
        [String]$Path,
        [Parameter(Mandatory = $true, ParameterSetName = "Package")]
        [OfficeOpenXml.ExcelPackage]$ExcelPackage,
        [Parameter(ValueFromPipeline = $true)]
        [Alias('TargetData')]
        $InputObject,
        [Switch]$Calculate,
        [Switch]$Show,
        [String]$WorksheetName = 'Sheet1',
        [Alias("PW")]
        [String]$Password,
        [switch]$ClearSheet,
        [switch]$Append,
        [String]$Title,
        [OfficeOpenXml.Style.ExcelFillStyle]$TitleFillPattern = 'Solid',
        [Switch]$TitleBold,
        [Int]$TitleSize = 22,
        $TitleBackgroundColor,
        [parameter(DontShow=$true)]
        [Switch]$IncludePivotTable,
        [String]$PivotTableName,
        [String[]]$PivotRows,
        [String[]]$PivotColumns,
        $PivotData,
        [String[]]$PivotFilter,
        [Switch]$PivotDataToColumn,
        [Hashtable]$PivotTableDefinition,
        [Switch]$IncludePivotChart,
        [Alias('ChartType')]
        [OfficeOpenXml.Drawing.Chart.eChartType]$PivotChartType = 'Pie',
        [Switch]$NoLegend,
        [Switch]$ShowCategory,
        [Switch]$ShowPercent,
        [Switch]$AutoSize,
        $MaxAutoSizeRows = 1000,
        [Switch]$NoClobber,
        [Switch]$FreezeTopRow,
        [Switch]$FreezeFirstColumn,
        [Switch]$FreezeTopRowFirstColumn,
        [Int[]]$FreezePane,
        [Switch]$AutoFilter,
        [Switch]$BoldTopRow,
        [Switch]$NoHeader,
        [ValidateScript( {
                if (-not $_) {  throw 'RangeName is null or empty.'  }
                elseif ($_[0] -notmatch '[a-z]') { throw 'RangeName starts with an invalid character.'  }
                else { $true }
        })]
        [String]$RangeName,
        [Alias('Table')]
        $TableName,
        [OfficeOpenXml.Table.TableStyles]$TableStyle =  [OfficeOpenXml.Table.TableStyles]::Medium6,
        [Switch]$Barchart,
        [Switch]$PieChart,
        [Switch]$LineChart ,
        [Switch]$ColumnChart ,
        [Object[]]$ExcelChartDefinition,
        [String[]]$HideSheet,
        [String[]]$UnHideSheet,
        [Switch]$MoveToStart,
        [Switch]$MoveToEnd,
        $MoveBefore ,
        $MoveAfter ,
        [Switch]$KillExcel,
        [Switch]$AutoNameRange,
        [Int]$StartRow = 1,
        [Int]$StartColumn = 1,
        [alias('PT')]
        [Switch]$PassThru,
        [String]$Numberformat = 'General',
        [string[]]$ExcludeProperty,
        [Switch]$NoAliasOrScriptPropeties,
        [Switch]$DisplayPropertySet,
        [String[]]$NoNumberConversion,
        [Object[]]$ConditionalFormat,
        [Object[]]$ConditionalText,
        [Object[]]$Style,
        [ScriptBlock]$CellStyleSB,
        #If there is already content in the workbook the sheet with the PivotTable will not be active UNLESS Activate is specified
        [switch]$Activate,
        [Parameter(ParameterSetName = 'Default')]
        [Switch]$Now,
        [Switch]$ReturnRange,
        #By default PivotTables have Totals for each Row (on the right) and for each column at the bottom. This allows just one or neither to be selected.
        [ValidateSet("Both","Columns","Rows","None")]
        [String]$PivotTotals = "Both",
        #Included for compatibility - equivalent to -PivotTotals "None"
        [Switch]$NoTotalsInPivot,
        [Switch]$ReZip
    )

    begin {
        $numberRegex = [Regex]'\d'
        $isDataTypeValueType = $false
        if ($NoClobber) {Write-Warning -Message "-NoClobber parameter is no longer used" }
        #Open the file, get the worksheet, and decide where in the sheet we are writing, and if there is a number format to apply.
        try   {
            $script:Header = $null
            if ($Append -and $ClearSheet) {throw "You can't use -Append AND -ClearSheet." ; return}
            #To force -Now not to format as a table, allow $false in -TableName to be "No table"
            $TableName = if ($null -eq $TableName -or ($TableName -is [bool] -and $false -eq $TableName)) { $null } else {[String]$TableName}
            if ($Now -or (-not $Path -and -not $ExcelPackage) ) {
                if (-not $PSBoundParameters.ContainsKey("Path")) { $Path = [System.IO.Path]::GetTempFileName() -replace '\.tmp', '.xlsx' }
                if (-not $PSBoundParameters.ContainsKey("Show")) { $Show = $true }
                if (-not $PSBoundParameters.ContainsKey("AutoSize")) { $AutoSize = $true }
                #"Now" option will create a table, unless something passed in TableName/Table Style. False in TableName will block autocreation
                if (-not $PSBoundParameters.ContainsKey("TableName") -and
                    -not $PSBoundParameters.ContainsKey("TableStyle") -and
                    -not $AutoFilter) {
                    $TableName = '' # later rely on distinction between NULL and ""
                }
            }
            if ($ExcelPackage) {
                $pkg = $ExcelPackage
                $Path = $pkg.File
            }
            Else { $pkg = Open-ExcelPackage -Path $Path -Create -KillExcel:$KillExcel -Password:$Password}
        }
        catch {throw "Could not open Excel Package $path"}
        try   {
            $params = @{WorksheetName=$WorksheetName}
            foreach ($p in @("ClearSheet", "MoveToStart", "MoveToEnd", "MoveBefore", "MoveAfter", "Activate")) {if ($PSBoundParameters[$p]) {$params[$p] = $PSBoundParameters[$p]}}
            $ws = $pkg | Add-Worksheet @params
            if ($ws.Name -ne $WorksheetName) {
                Write-Warning -Message "The Worksheet name has been changed from $WorksheetName to $($ws.Name), this may cause errors later."
                $WorksheetName = $ws.Name
            }
        }
        catch {throw "Could not get worksheet $worksheetname"}
        try   {
            if ($Append -and $ws.Dimension) {
                #if there is a title or anything else above the header row, append needs to be combined wih a suitable startrow parameter
                $headerRange = $ws.Dimension.Address -replace "\d+$", $StartRow
                #using a slightly odd syntax otherwise header ends up as a 2D array
                $ws.Cells[$headerRange].Value | ForEach-Object -Begin {$Script:header = @()} -Process {$Script:header += $_ }
                $NoHeader = $true
                #if we did not get AutoNameRange, but headers have ranges of the same name make autoNameRange True, otherwise make it false
                if (-not $AutoNameRange) {
                    $AutoNameRange  = $true ; foreach ($h in $header) {if ($ws.names.name -notcontains $h) {$AutoNameRange = $false} }
                }
                #if we did not get a Rangename but there is a Range which covers the active part of the sheet, set Rangename to that.
                if (-not $RangeName -and $ws.names.where({$_.name[0] -match '[a-z]'})) {
                    $theRange = $ws.names.where({
                         ($_.Name[0]   -match '[a-z]' )              -and
                         ($_.Start.Row    -eq $StartRow)             -and
                         ($_.Start.Column -eq $StartColumn)          -and
                         ($_.End.Row      -eq $ws.Dimension.End.Row) -and
                         ($_.End.Column   -eq $ws.Dimension.End.column) } , 'First', 1)
                    if ($theRange) {$rangename = $theRange.name}
                }

                #if we did not get a table name but there is a table which covers the active part of the sheet, set table name to that, and don't do anything with autofilter
                $existingTable = $ws.Tables.Where({$_.address.address -eq $ws.dimension.address},'First', 1)
                if ($null -eq $TableName -and $existingTable) {
                    $TableName  = $existingTable.Name
                    $TableStyle = $existingTable.StyleName -replace "^TableStyle",""
                    $AutoFilter = $false
                }
                #if we did not get $autofilter but a filter range is set and it covers the right area, set autofilter to true
                elseif (-not $AutoFilter -and $ws.Names['_xlnm._FilterDatabase']) {
                    if ( ($ws.Names['_xlnm._FilterDatabase'].Start.Row    -eq $StartRow)    -and
                         ($ws.Names['_xlnm._FilterDatabase'].Start.Column -eq $StartColumn) -and
                         ($ws.Names['_xlnm._FilterDatabase'].End.Row      -eq $ws.Dimension.End.Row) -and
                         ($ws.Names['_xlnm._FilterDatabase'].End.Column   -eq $ws.Dimension.End.Column) ) {$AutoFilter = $true}
                }

                $row = $ws.Dimension.End.Row
                Write-Debug -Message ("Appending: headers are " + ($script:Header -join ", ") + " Start row is $row")
                if ($Title) {Write-Warning -Message "-Title Parameter is ignored when appending."}
            }
            elseif ($Title) {
                #Can only add a title if not appending!
                $Row = $StartRow
                $ws.Cells[$Row, $StartColumn].Value = $Title
                $ws.Cells[$Row, $StartColumn].Style.Font.Size = $TitleSize

                if  ($PSBoundParameters.ContainsKey("TitleBold")) {
                    #Set title to Bold face font if -TitleBold was specified.
                    #Otherwise the default will be unbolded.
                    $ws.Cells[$Row, $StartColumn].Style.Font.Bold = [boolean]$TitleBold
                }
                if ($TitleBackgroundColor ) {
                    if ($TitleBackgroundColor -is [string])         {$TitleBackgroundColor = [System.Drawing.Color]::$TitleBackgroundColor }
                    $ws.Cells[$Row, $StartColumn].Style.Fill.PatternType = $TitleFillPattern
                    $ws.Cells[$Row, $StartColumn].Style.Fill.BackgroundColor.SetColor($TitleBackgroundColor)
                }
                $Row ++ ; $startRow ++
            }
            else {  $Row = $StartRow }
            $ColumnIndex = $StartColumn
            $Numberformat = Expand-NumberFormat -NumberFormat $Numberformat
            if ((-not $ws.Dimension) -and ($Numberformat -ne $ws.Cells.Style.Numberformat.Format)) {
                    $ws.Cells.Style.Numberformat.Format = $Numberformat
                    $setNumformat = $false
            }
            else {  $setNumformat = ($Numberformat -ne $ws.Cells.Style.Numberformat.Format) }
        }
        catch {throw "Failed preparing to export to worksheet '$WorksheetName' to '$Path': $_"}
        #region Special case -inputobject passed a dataTable object
        <# If inputObject was passed via the pipeline it won't be visible until the process block, we will only see it here if it was passed as a parameter
          if it is a data table don't do foreach on it (slow) - put the whole table in and set dates on date columns,
          set things up for the end block, and skip the process block #>

        if ($InputObject -is  [System.Data.DataTable])  {
            if ($Append -and $ws.dimension) {
                $row ++
                $null = $ws.Cells[$row,$StartColumn].LoadFromDataTable($InputObject, $false )
                if ($TableName -or  $PSBoundParameters.ContainsKey('TableStyle')) {
                    Add-ExcelTable -Range $ws.Cells[$ws.Dimension] -TableName $TableName -TableStyle $TableStyle
                }
            }
            else  {
                #Change TableName if $TableName is non-empty; don't leave caller with a renamed table!
                $orginalTableName = $InputObject.TableName
                if ($PSBoundParameters.ContainsKey("TableName")) {
                    $InputObject.TableName = $TableName
                }
                while ($InputObject.TableName -in $pkg.Workbook.Worksheets.Tables.name) {
                    Write-Warning "Table name $($InputObject.TableName) is not unique, adding '_' to it "
                    $InputObject.TableName += "_"
                }
                #Insert as a table, if Tablestyle didn't arrive as a default, or $TableName non-null - even if empty
                if ($null -ne $TableName -or $PSBoundParameters.ContainsKey("TableStyle")) {
                    $null = $ws.Cells[$row,$StartColumn].LoadFromDataTable($InputObject, (-not $noHeader),$TableStyle )
                    # Workaround for EPPlus not marking the empty row on an empty table as dummy row.
                    if ($InputObject.Rows.Count -eq 0) {
                        ($ws.Tables | Select-Object -Last 1).TableXml.table.SetAttribute('insertRow', 1)
                    }
                }
                else {
                    $null = $ws.Cells[$row,$StartColumn].LoadFromDataTable($InputObject, (-not $noHeader) )
                }
                $InputObject.TableName = $orginalTableName
            }
            foreach ($c in $InputObject.Columns.where({$_.datatype -eq [datetime]})) {
                Set-ExcelColumn -Worksheet $ws -Column ($c.Ordinal + $StartColumn) -NumberFormat 'Date-Time'
            }
            foreach ($c in $InputObject.Columns.where({$_.datatype -eq [timespan]})) {
                Set-ExcelColumn -Worksheet $ws -Column ($c.Ordinal + $StartColumn) -NumberFormat '[h]:mm:ss'
            }
            $ColumnIndex         += $InputObject.Columns.Count - 1
            if ($noHeader) {$row += $InputObject.Rows.Count -1 }
            else           {$row += $InputObject.Rows.Count    }
            $null = $PSBoundParameters.Remove('InputObject')
            $firstTimeThru = $false
        }
        #endregion
        else  {$firstTimeThru = $true}
    }

    process { if ($PSBoundParameters.ContainsKey("InputObject")) {
        try {
            if ($null -eq $InputObject) {$row += 1}
            foreach ($TargetData in $InputObject) {
                if ($firstTimeThru) {
                    $firstTimeThru = $false
                    $isDataTypeValueType = ($null -eq $TargetData) -or ($TargetData.GetType().name -match 'string|timespan|datetime|bool|byte|char|decimal|double|float|int|long|sbyte|short|uint|ulong|ushort|URI|ExcelHyperLink')
                    if ($isDataTypeValueType ) {
                        $script:Header = @(".")       # dummy value to make sure we go through the "for each name in $header"
                        if (-not $Append) {$row -= 1} # By default row will be 1, it is incremented before inserting values (so it ends pointing at final row.); si first data row is 2 - move back up 1 if there is no header .
                    }
                    if ($null -ne $TargetData) {Write-Debug "DataTypeName is '$($TargetData.GetType().name)' isDataTypeValueType '$isDataTypeValueType'" }
                }
                #region Add headers - if we are appending, or we have been through here once already we will have the headers
                if (-not $script:Header) {
                    if ($DisplayPropertySet -and $TargetData.psStandardmembers.DefaultDisplayPropertySet.ReferencedPropertyNames) {
                        $script:Header = $TargetData.psStandardmembers.DefaultDisplayPropertySet.ReferencedPropertyNames.Where( {$_ -notin $ExcludeProperty})
                    }
                    else {
                        if ($NoAliasOrScriptPropeties) {$propType = "Property"} else {$propType = "*"}
                        $script:Header = $TargetData.PSObject.Properties.where( {$_.MemberType -like $propType}).Name
                    }
                    foreach ($exclusion in $ExcludeProperty) {$script:Header = $script:Header -notlike $exclusion}
                    if ($NoHeader) {
                        # Don't push the headers to the spreadsheet
                        $Row -= 1
                    }
                    else {
                        $ColumnIndex = $StartColumn
                        foreach ($Name in $script:Header) {
                            $ws.Cells[$Row, $ColumnIndex].Value = $Name
                            Write-Verbose "Cell '$Row`:$ColumnIndex' add header '$Name'"
                            $ColumnIndex += 1
                        }
                    }
                }
                #endregion
                #region Add non header values
                $Row += 1
                $ColumnIndex = $StartColumn
                <#
                 For each item in the header OR for the Data item if this is a simple Type or data table :
                   If it is a date insert with one of Excel's built in formats - recognized as "Date and time to be localized"
                   if it is a timespan insert with a built in format for elapsed hours, minutes and seconds
                   if its any other numeric insert as is , setting format if need be.
                   Preserve URI, Insert a data table, convert non string objects to string.
                   For strings, check for fomula, URI or Number, before inserting as a string (ignore nulls) #>

                foreach ($Name in $script:Header) {
                    if   ($isDataTypeValueType) {$v = $TargetData}
                    else {$v = $TargetData.$Name}
                    try   {
                        if     ($v -is    [DateTime]) {
                            $ws.Cells[$Row, $ColumnIndex].Value = $v
                            $ws.Cells[$Row, $ColumnIndex].Style.Numberformat.Format = 'm/d/yy h:mm' # This is not a custom format, but a preset recognized as date and localized.
                        }
                        elseif ($v -is    [TimeSpan]) {
                            $ws.Cells[$Row, $ColumnIndex].Value = $v
                            $ws.Cells[$Row, $ColumnIndex].Style.Numberformat.Format = '[h]:mm:ss'
                        }
                        elseif ($v -is    [System.ValueType]) {
                            $ws.Cells[$Row, $ColumnIndex].Value = $v
                            if ($setNumformat) {$ws.Cells[$Row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
                        }
                        elseif ($v -is    [uri] ) {
                            $ws.Cells[$Row, $ColumnIndex].HyperLink = $v
                            $ws.Cells[$Row, $ColumnIndex].Style.Font.Color.SetColor([System.Drawing.Color]::Blue)
                            $ws.Cells[$Row, $ColumnIndex].Style.Font.UnderLine = $true
                        }
                        elseif ($v -isnot [String] ) { #Other objects or null.
                            if ($null -ne $v) { $ws.Cells[$Row, $ColumnIndex].Value = $v.toString()}
                        }
                        elseif ($v[0] -eq '=') {
                            $ws.Cells[$Row, $ColumnIndex].Formula = ($v -replace '^=','')
                            if ($setNumformat) {$ws.Cells[$Row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
                        }
                        elseif ( [System.Uri]::IsWellFormedUriString($v , [System.UriKind]::Absolute) ) {
                            if ($v -match "^xl://internal/") {
                                  $referenceAddress = $v -replace "^xl://internal/" , ""
                                  $display          = $referenceAddress -replace "!A1$"   , ""
                                  $h = New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList $referenceAddress , $display
                                  $ws.Cells[$Row, $ColumnIndex].HyperLink = $h
                            }
                            else {$ws.Cells[$Row, $ColumnIndex].HyperLink = $v }   #$ws.Cells[$Row, $ColumnIndex].Value = $v.AbsoluteUri
                            $ws.Cells[$Row, $ColumnIndex].Style.Font.Color.SetColor([System.Drawing.Color]::Blue)
                            $ws.Cells[$Row, $ColumnIndex].Style.Font.UnderLine = $true
                        }
                        else {
                            $number = $null
                            if ( $numberRegex.IsMatch($v)     -and  # if it contains digit(s) - this syntax is quicker than -match for many items and cuts out slow checks for non numbers
                                 $NoNumberConversion -ne '*'  -and  # and NoNumberConversion isn't specified
                                 $NoNumberConversion -notcontains $Name -and
                                 [Double]::TryParse($v, [System.Globalization.NumberStyles]::Any, [System.Globalization.NumberFormatInfo]::CurrentInfo, [Ref]$number)
                               ) {
                                 $ws.Cells[$Row, $ColumnIndex].Value = $number
                                 if ($setNumformat) {$ws.Cells[$Row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
                            }
                            else {
                                $ws.Cells[$Row, $ColumnIndex].Value  = $v
                            }
                        }
                    }
                    catch {Write-Warning -Message "Could not insert the '$Name' property at Row $Row, Column $ColumnIndex"}
                    $ColumnIndex += 1
                }
                $ColumnIndex -= 1 # column index will be the last column whether isDataTypeValueType was true or false
                #endregion
            }
        }
        catch {throw "Failed exporting data to worksheet '$WorksheetName' to '$Path': $_" }
    }}

    end {
        if ($firstTimeThru -and $ws.Dimension) {
              $LastRow        = $ws.Dimension.End.Row
              $LastCol        = $ws.Dimension.End.Column
              $endAddress     = $ws.Dimension.End.Address
        }
        else {
              $LastRow        = $Row
              $LastCol        = $ColumnIndex
              $endAddress     = [OfficeOpenXml.ExcelAddress]::GetAddress($LastRow , $LastCol)
        }
        $startAddress         = [OfficeOpenXml.ExcelAddress]::GetAddress($StartRow, $StartColumn)
        $dataRange            = "{0}:{1}" -f $startAddress, $endAddress

        Write-Debug "Data Range '$dataRange'"
        if ($AutoNameRange) {
            try {
                if (-not $script:header) {
                    # if there aren't any headers, use the the first row of data to name the ranges: this is the last point that headers will be used.
                    $headerRange = $ws.Dimension.Address -replace "\d+$", $StartRow
                    #using a slightly odd syntax otherwise header ends up as a 2D array
                    $ws.Cells[$headerRange].Value | ForEach-Object -Begin {$Script:header = @()} -Process {$Script:header += $_ }
                    if   ($PSBoundParameters.ContainsKey($TargetData)) {  #if Export was called with data that writes no header start the range at $startRow ($startRow is data)
                           $targetRow = $StartRow
                    }
                    else { $targetRow = $StartRow + 1 }                   #if Export was called without data to add names (assume $startRow is a header) or...
                }                                                         # ... called with data that writes a header, then start the range at $startRow + 1
                else {     $targetRow = $StartRow + 1 }

                #Dimension.start.row always seems to be one so we work out the target row
                #, but start.column is the first populated one and .Columns is the count of populated ones.
                # if we have 5 columns from 3 to 8, headers are numbered 0..4, so that is in the for loop and used for getting the name...
                # but we have to add the start column on when referencing positions
                foreach ($c in 0..($LastCol - $StartColumn)) {
                    $targetRangeName = @($script:Header)[$c]  #Let Add-ExcelName fix (and warn about) bad names
                    Add-ExcelName  -RangeName $targetRangeName -Range $ws.Cells[$targetRow, ($StartColumn + $c ), $LastRow, ($StartColumn + $c )]
                    try {#this test can throw with some names, surpress any error
                        if ([OfficeOpenXml.FormulaParsing.ExcelUtilities.ExcelAddressUtil]::IsValidAddress(($targetRangeName -replace '\W' , '_' ))) {
                            Write-Warning -Message "AutoNameRange: Property name '$targetRangeName' is also a valid Excel address and may cause issues. Consider renaming the property."
                        }
                    }
                    catch {
                        Write-Warning -Message "AutoNameRange: Testing '$targetRangeName' caused an error. This should be harmless, but a change of property name may be needed.."
                    }
                }
            }
            catch {Write-Warning -Message "Failed adding named ranges to worksheet '$WorksheetName': $_"  }
        }
        #Empty string is not allowed as a name for ranges or tables.
        if ($RangeName) { Add-ExcelName  -Range $ws.Cells[$dataRange] -RangeName $RangeName}

        #Allow table to be inserted by specifying Name, or Style or both; only process autoFilter if there is no table (they clash).
        if     ($null -ne $TableName -or $PSBoundParameters.ContainsKey('TableStyle')) {
            #Already inserted Excel table if input was a DataTable
            if ($InputObject -isnot [System.Data.DataTable]) {
                Add-ExcelTable -Range $ws.Cells[$dataRange] -TableName $TableName -TableStyle $TableStyle
            }
        }
        elseif ($AutoFilter) {
            try {
                $ws.Cells[$dataRange].AutoFilter = $true
                Write-Verbose -Message "Enabled autofilter. "
            }
            catch {Write-Warning -Message "Failed adding autofilter to worksheet '$WorksheetName': $_"}
        }

        if ($PivotTableDefinition) {
            foreach ($item in $PivotTableDefinition.GetEnumerator()) {
                $params = $item.value
                if ($Activate) {$params.Activate = $true   }
                if ($params.keys -notcontains 'SourceRange' -and
                   ($params.Keys -notcontains 'SourceWorkSheet'   -or  $params.SourceWorkSheet -eq $WorksheetName)) {$params.SourceRange = $dataRange}
                if ($params.Keys -notcontains 'SourceWorkSheet')      {$params.SourceWorkSheet = $ws }
                if ($params.Keys -notcontains 'NoTotalsInPivot'   -and $NoTotalsInPivot  ) {$params.PivotTotals       = 'None'}
                if ($params.Keys -notcontains 'PivotTotals'       -and $PivotTotals      ) {$params.PivotTotals       = $PivotTotals}
                if ($params.Keys -notcontains 'PivotDataToColumn' -and $PivotDataToColumn) {$params.PivotDataToColumn = $true}

                Add-PivotTable -ExcelPackage $pkg -PivotTableName $item.key @Params
            }
        }
        if ($IncludePivotTable -or $IncludePivotChart -or $PivotData) {
            $params = @{
                'SourceRange' = $dataRange
            }
            if ($PivotTableName -and ($pkg.workbook.worksheets.tables.name -contains $PivotTableName)) {
                Write-Warning -Message "The selected PivotTable name '$PivotTableName' is already used as a table name. Adding a suffix of 'Pivot'."
                $PivotTableName += 'Pivot'
            }

            if   ($PivotTableName)  {$params.PivotTableName    = $PivotTableName}
            else                    {$params.PivotTableName    = $WorksheetName + 'PivotTable'}
            if          ($Activate) {$params.Activate          = $true   }
            if       ($PivotFilter) {$params.PivotFilter       = $PivotFilter}
            if         ($PivotRows) {$params.PivotRows         = $PivotRows}
            if      ($PivotColumns) {$Params.PivotColumns      = $PivotColumns}
            if         ($PivotData) {$Params.PivotData         = $PivotData}
            if   ($NoTotalsInPivot) {$params.PivotTotals       = "None"    }
            Elseif   ($PivotTotals) {$params.PivotTotals       = $PivotTotals}
            if ($PivotDataToColumn) {$params.PivotDataToColumn = $true}
            if ($IncludePivotChart -or
                $PSBoundParameters.ContainsKey('PivotChartType')) {
                                     $params.IncludePivotChart = $true
                                     $Params.ChartType         = $PivotChartType
                if ($ShowCategory)  {$params.ShowCategory      = $true}
                if ($ShowPercent)   {$params.ShowPercent       = $true}
                if ($NoLegend)      {$params.NoLegend          = $true}
            }
            Add-PivotTable -ExcelPackage $pkg -SourceWorkSheet $ws   @params
        }

        try {
            #Allow single switch or two seperate ones.
            if ($FreezeTopRowFirstColumn -or ($FreezeTopRow -and $FreezeFirstColumn)) {
                if ($Title) {
                    $ws.View.FreezePanes(3, 2)
                    Write-Verbose -Message "Froze title and header rows and first column"
                }
                else {
                    $ws.View.FreezePanes(2, 2)
                    Write-Verbose -Message "Froze top row and first column"
                }
            }
            elseif ($FreezeTopRow) {
                if ($Title) {
                    $ws.View.FreezePanes(2, 1)
                    Write-Verbose -Message "Froze title and header rows"
                }
                else {
                    $ws.View.FreezePanes(2, 1)
                    Write-Verbose -Message "Froze top row"
                }
            }
            elseif ($FreezeFirstColumn) {
                $ws.View.FreezePanes(1, 2)
                Write-Verbose -Message "Froze first column"
            }
            #Must be 1..maxrows or and array of 1..maxRows,1..MaxCols
            if ($FreezePane) {
                $freezeRow, $freezeColumn = $FreezePane
                if (-not $freezeColumn -or $freezeColumn -eq 0) {
                    $freezeColumn = 1
                }

                if ($freezeRow -ge 1) {
                    $ws.View.FreezePanes($freezeRow, $freezeColumn)
                    Write-Verbose -Message "Froze panes at row $freezeRow and column $FreezeColumn"
                }
            }
        }
        catch {Write-Warning -Message "Failed adding Freezing the panes in worksheet '$WorksheetName': $_"}

        if ($PSBoundParameters.ContainsKey("BoldTopRow")) { #it sets bold as far as there are populated cells: for whole row could do $ws.row($x).style.font.bold = $true
            try {
                if ($Title) {
                    $range = $ws.Dimension.Address -replace '\d+', ($StartRow + 1)
                }
                else {
                    $range = $ws.Dimension.Address -replace '\d+', $StartRow
                }
                $ws.Cells[$range].Style.Font.Bold = [boolean]$BoldTopRow
                Write-Verbose -Message "Set $range font style to bold."
            }
            catch {Write-Warning -Message "Failed setting the top row to bold in worksheet '$WorksheetName': $_"}
        }
        if ($AutoSize -and -not $env:NoAutoSize) {
            try {
                #Don't fit the all the columns in the sheet; if we are adding cells beside things with hidden columns, that unhides them
                if ($MaxAutoSizeRows -and $MaxAutoSizeRows -lt $LastRow ) {
                    $AutosizeRange = [OfficeOpenXml.ExcelAddress]::GetAddress($startRow,$StartColumn,   $MaxAutoSizeRows , $LastCol)
                    $ws.Cells[$AutosizeRange].AutoFitColumns()
                }
                else {$ws.Cells[$dataRange].AutoFitColumns()  }
                Write-Verbose -Message "Auto-sized columns"
            }
            catch {  Write-Warning -Message "Failed autosizing columns of worksheet '$WorksheetName': $_"}
        }
        elseif ($AutoSize) {Write-Warning -Message "Auto-fitting columns is not available with this OS configuration." }

        foreach ($Sheet in $HideSheet) {
            try {
                $pkg.Workbook.WorkSheets.Where({$_.Name -like $sheet}) | ForEach-Object {
                    $_.Hidden = 'Hidden'
                    Write-verbose -Message "Sheet '$($_.Name)' Hidden."
                }
            }
            catch {Write-Warning -Message  "Failed hiding worksheet '$sheet': $_"}
        }
        foreach ($Sheet in $UnHideSheet) {
            try {
                $pkg.Workbook.WorkSheets.Where({$_.Name -like $sheet}) | ForEach-Object {
                    $_.Hidden = 'Visible'
                    Write-verbose -Message "Sheet '$($_.Name)' shown"
                }
            }
            catch {Write-Warning -Message  "Failed showing worksheet '$sheet': $_"}
        }
        if (-not $pkg.Workbook.Worksheets.Where({$_.Hidden -eq 'visible'})) {
            Write-Verbose -Message "No Sheets were left visible, making $WorksheetName visible"
            $ws.Hidden = 'Visible'
        }

        foreach ($chartDef in $ExcelChartDefinition) {
            if ($chartDef -is [System.Management.Automation.PSCustomObject]) {
                $params = @{}
                $chartDef.PSObject.Properties | ForEach-Object {if ( $null -ne $_.value) {$params[$_.name] = $_.value}}
                Add-ExcelChart -Worksheet $ws @params
            }
            elseif ($chartDef -is [hashtable] -or  $chartDef -is[System.Collections.Specialized.OrderedDictionary]) {
                Add-ExcelChart -Worksheet $ws @chartDef
            }
        }

        if ($Calculate) {
            try   { [OfficeOpenXml.CalculationExtension]::Calculate($ws) }
            catch { Write-Warning "One or more errors occured while calculating, save will continue, but there may be errors in the workbook. $_"}
        }

        if ($Barchart -or $PieChart -or $LineChart -or $ColumnChart) {
            if ($NoHeader) {$FirstDataRow = $startRow}
            else           {$FirstDataRow = $startRow + 1 }
            $range = [OfficeOpenXml.ExcelAddress]::GetAddress($FirstDataRow, $startColumn, $FirstDataRow, $lastCol )
            $xCol  = $ws.cells[$range] | Where-Object {$_.value -is [string]    } | ForEach-Object {$_.start.column} | Sort-Object | Select-Object -first 1
            if (-not $xcol) {
                $xcol  = $StartColumn
                $range = [OfficeOpenXml.ExcelAddress]::GetAddress($FirstDataRow, ($startColumn +1), $FirstDataRow, $lastCol )
            }
            $yCol  = $ws.cells[$range] | Where-Object {$_.value -is [valueType] -or $_.Formula } | ForEach-Object {$_.start.column} | Sort-Object | Select-Object -first 1
            if (-not ($xCol -and $ycol)) { Write-Warning -Message "Can't identify a string column and a number column to use as chart labels and data. "}
            else {
                $params = @{
                XRange = [OfficeOpenXml.ExcelAddress]::GetAddress($FirstDataRow, $xcol , $lastrow, $xcol)
                YRange = [OfficeOpenXml.ExcelAddress]::GetAddress($FirstDataRow, $ycol , $lastrow, $ycol)
                Title  =  ''
                Column = ($lastCol +1)
                Width  = 800
                }
                if   ($ShowPercent) {$params["ShowPercent"]  = $true}
                if  ($ShowCategory) {$params["ShowCategory"] = $true}
                if      ($NoLegend) {$params["NoLegend"]     = $true}
                if (-not $NoHeader) {$params["SeriesHeader"] = $ws.Cells[$startRow, $YCol].Value}
                if   ($ColumnChart) {$Params["chartType"]    = "ColumnStacked" }
                elseif  ($Barchart) {$Params["chartType"]    = "BarStacked"    }
                elseif  ($PieChart) {$Params["chartType"]    = "PieExploded3D" }
                elseif ($LineChart) {$Params["chartType"]    = "Line"          }

                Add-ExcelChart -Worksheet $ws @params
            }
        }

        # It now doesn't matter if the conditional formating rules are passed in $conditionalText or $conditional format.
        # Just one with an alias for compatiblity it will break things for people who are using both at once
        foreach ($c in  (@() + $ConditionalText  +  $ConditionalFormat) ) {
            try {
                #we can take an object with a .ConditionalType property made by New-ConditionalText or with a .Formatter Property made by New-ConditionalFormattingIconSet or a hash table
                if ($c.ConditionalType) {
                    $cfParams = @{RuleType = $c.ConditionalType;    ConditionValue = $c.Text ;
                           BackgroundColor = $c.BackgroundColor; BackgroundPattern = $c.PatternType  ;
                           ForeGroundColor = $c.ConditionalTextColor}
                    if ($c.Range) {$cfParams.Range = $c.Range}
                    else          {$cfParams.Range = $ws.Dimension.Address }
                    Add-ConditionalFormatting -Worksheet $ws @cfParams
                    Write-Verbose -Message "Added conditional formatting to range $($c.range)"
                }
                elseif ($c.formatter)  {
                    switch ($c.formatter) {
                        "ThreeIconSet" {Add-ConditionalFormatting -Worksheet $ws -ThreeIconsSet $c.IconType -range $c.range -reverse:$c.reverse  }
                        "FourIconSet"  {Add-ConditionalFormatting -Worksheet $ws  -FourIconsSet $c.IconType -range $c.range -reverse:$c.reverse  }
                        "FiveIconSet"  {Add-ConditionalFormatting -Worksheet $ws  -FiveIconsSet $c.IconType -range $c.range -reverse:$c.reverse  }
                    }
                    Write-Verbose -Message "Added conditional formatting to range $($c.range)"
                }
                elseif ($c -is [hashtable] -or  $c -is[System.Collections.Specialized.OrderedDictionary]) {
                    if (-not $c.Range -or $c.Address) {$c.Address = $ws.Dimension.Address }
                    Add-ConditionalFormatting -Worksheet $ws @c
                }
            }
            catch {throw "Error applying conditional formatting to worksheet $_"}
        }
        foreach ($s in $Style) {
            if (-not $s.Range) {$s["Range"] = $ws.Dimension.Address }
            Set-ExcelRange -Worksheet $ws @s
        }
        if ($CellStyleSB) {
            try {
                $TotalRows = $ws.Dimension.Rows
                $LastColumn = $ws.Dimension.Address -replace "^.*:(\w*)\d+$" , '$1'
                & $CellStyleSB $ws $TotalRows $LastColumn
            }
            catch {Write-Warning -Message "Failed processing CellStyleSB in worksheet '$WorksheetName': $_"}
        }

        #Can only add password, may want to support -password $Null removing password.
        if ($Password) {
            try {
                $ws.Protection.SetPassword($Password)
                Write-Verbose -Message 'Set password on workbook'
            }
            catch {throw "Failed setting password for worksheet '$WorksheetName': $_"}
        }

        if ($PassThru) {       $pkg   }
        else {
            if ($ReturnRange) {$dataRange }

            if ($Password) {   $pkg.Save($Password) }
            else           {   $pkg.Save() }
            Write-Verbose -Message "Saved workbook $($pkg.File)"
            if ($ReZip) {
                Write-Verbose -Message "Re-Zipping $($pkg.file) using .NET ZIP library"
                try {
                    Add-Type -AssemblyName 'System.IO.Compression.Filesystem' -ErrorAction stop
                }
                catch {
                    Write-Error "The -ReZip parameter requires .NET Framework 4.5 or later to be installed. Recommend to install Powershell v4+"
                    continue
                }
                try {
                    $TempZipPath = Join-Path -Path ([System.IO.Path]::GetTempPath()) -ChildPath ([System.IO.Path]::GetRandomFileName())
                    $null = [io.compression.zipfile]::ExtractToDirectory($pkg.File, $TempZipPath)
                    Remove-Item $pkg.File -Force
                    $null = [io.compression.zipfile]::CreateFromDirectory($TempZipPath, $pkg.File)
                }
                catch {throw "Error resizipping $path : $_"}
            }

            $pkg.Dispose()

            if ($Show) { Invoke-Item $Path }
        }
    }
}