ImportExcel

7.8.1

Add-Subtotals.ps1

Function Add-Subtotals {
    param(
        [Parameter(Mandatory=$true, Position=0)]
        $ChangeColumnName ,              # = "Location"

        [Parameter(Mandatory=$true, Position=1)]
        [hashtable]$AggregateColumn  ,  #= @{"Sales" = "SUM" }

        [Parameter(Position=2)]
        $ExcelPath           = ([System.IO.Path]::GetTempFileName() -replace "\.tmp", ".xlsx")         ,

        [Parameter(Position=3)]
        $WorksheetName      = "Sheet1",

        [Parameter(ValueFromPipeline=$true)]
        $InputObject,  #$DataToPivot | Sort location, product

        [switch]$HideSingleRows,
        [switch]$NoSort,
        [switch]$NoOutLine,
        [switch]$Show

    )
    begin {
        if (-not $PSBoundParameters.ContainsKey('ExcelPath')) {$Show = $true}
        $data               = @()
        $aggFunctions       = [ordered]@{
                "AVERAGE"   = 1; "COUNT"     = 2;  "COUNTA"    = 3  #(non empty cells) f
                "MAX"       = 4; "MIN"       = 5;  "PRODUCT"   = 6; "STDEV"     = 7 # (sample)
                "STDEVP"    = 8 # (whole population);
                "SUM"       = 9;  "VAR"       = 10 # (Variance sample)
                "VARP"      = 11 # (whole population) #add 100 to ignore hidden cells
        }
    }
    process {
        $data               += $InputObject
    }
    end {
        if (-not $NoSort)  {$data = $data | Sort-Object  $changeColumnName}
        $Header             = $data[0].PSObject.Properties.Name
        #region turn each entry in $AggregateColumn "=SUBTOTAL(a,x{0}}:x{1})" where a is the aggregate function number and x is the column letter
        $aggFormulas        = @{}
        foreach ($k in $AggregateColumn.Keys)  {
            $columnNo       = 0 ;
            while ($columnNo -lt $header.count  -and $header[$columnNo] -ne $k) {$columnNo ++}
            if    ($columnNo -eq $header.count) {
                    throw "'$k' isn't a property of the first row of data."; return
            }
            if ($AggregateColumn[$k] -is [string]) {
                $aggfn      = $aggFunctions[$AggregateColumn[$k]]
                if (-not $aggfn) {
                    throw   "$($AggregateColumn[$k]) is not a valid aggregation function - these are $($aggFunctions.keys -join ', ')" ; return
                }
            }
            else {$aggfn = $AggregateColumn[$k]}
            $aggFormulas[$k] =  "=SUBTOTAL({0},{1}{{0}}:{1}{{1}})"  -f $aggfn , (Get-ExcelColumnName ($columnNo+1) ).ColumnName
        }
        if ($aggformulas.count -lt 1) {throw "We didn't get any aggregation formulas"}
        $aggFormulas | out-string -Stream | Write-Verbose -Verbose
        #endregion
        $insertedRows       = @()
        $singleRows         = @()
        $previousValue      = $data[0].$changeColumnName
        $currentRow         = $lastChangeRow  = 2
        #region insert subtotals and send to excel:
        #each time there is a change in the column we're intetersted in.
        #either Add a row with the value and subtotal(s) function(s) if there is more than one row to total
        #or note the row if there was only one row with that value (we may hide it later.)
        $excel              = $data |
            ForEach-Object -process {
                if ($_.$changeColumnName -ne $previousValue) {
                    if ($lastChangeRow -lt ($currentrow - 1)) {
                        $NewObj = @{$changeColumnName = $previousValue}
                        foreach    ($k in $aggFormulas.Keys) {
                            $newobj[$k] = $aggformulas[$k] -f  $lastChangeRow,  ($currentRow - 1)
                        }
                        $insertedRows  += $currentRow
                        [pscustomobject]$newobj
                        $currentRow    += 1
                    }
                    else {$singleRows  += $currentRow  }
                    $lastChangeRow      = $currentRow
                    $previousValue      = $_.$changeColumnName
                }
                $_
                $currentRow += 1
                } -end { # the process block won't output the last row
                    if ($lastChangeRow -lt ($currentrow - 1)) {
                        $NewObj = @{$changeColumnName = $previousValue}
                        foreach    ($k in $aggFormulas.Keys) {
                            $newobj[$k] = $aggformulas[$k] -f  $lastChangeRow,  ($currentRow - 1)
                        }
                        $insertedRows  += $currentRow
                        [pscustomobject]$newobj
                    }
                    else {$singleRows  += $currentRow  }
            }  |   Export-Excel  -Path $ExcelPath  -PassThru  -AutoSize -AutoFilter -AutoNameRange -BoldTopRow -WorksheetName $WorksheetName -Activate -ClearSheet #-MaxAutoSizeRows 10000
        #endregion
        #Put the subtotal rows in bold optionally hide rows where only one has the value of interest.
        $ws                 = $excel.$WorksheetName
        #We kept lists of the total rows Since 1 rows won't get expand/collapse we can hide them.
        foreach ($r in $insertedrows)   {$ws.Row($r).style.font.bold = $true }
        if ($HideSingleRows)  {
            foreach ($r in $hideRows)   { $ws.Row($r).hidden = $true}
        }
        $range                 = $ws.Dimension.Address
        $ExcelPath             = $excel.File.FullName
        $SheetIndex            = $ws.index
        if ($NoOutline)  {
            Close-ExcelPackage $excel -show:$Show
            return
        }
        else {
            Close-ExcelPackage $excel

            try   { $excelApp       = New-Object -ComObject "Excel.Application" }
            catch { Write-Warning "Could not start Excel application - which usually means it is not installed."  ; return }

            try   { $excelWorkBook  = $excelApp.Workbooks.Open($ExcelPath) }
            catch { Write-Warning -Message "Could not Open $ExcelPath."  ; return }
            $ws   = $excelWorkBook.Worksheets.item($SheetIndex)
            $null = $ws.Range($range).Select()
            $null = $excelapp.ActiveCell.AutoOutline()
            $null = $ws.Outline.ShowLevels(1,$null)
            $excelWorkBook.Save()
            if ($show) {$excelApp.Visible = $true}
            else       {
                        [void]$excelWorkBook.close()
                        $excelapp.Quit()
            }
        }
    }
}