Private/Export-SPMExcel.ps1
|
$script:SPMPermStyle = @{ FC = @{ Font = 'B91C1C'; Fill = 'FDECEC'; Pale = 'E8B4B4' } W = @{ Font = '1D4ED8'; Fill = 'EAF1FD'; Pale = 'AFC6EE' } C = @{ Font = '6D28D9'; Fill = 'F1EBFB'; Pale = 'CDBBE7' } R = @{ Font = '15803D'; Fill = 'EAF5EE'; Pale = 'AFD4BB' } X = @{ Font = '475569'; Fill = 'EEF1F4'; Pale = 'BFC7D1' } } $script:SPMTypeOrder = @{ SharePointGroup = 0; EntraGroup = 1; M365Group = 1; SecurityGroup = 1; User = 2; Everyone = 3; EveryoneExceptExternal = 3; Other = 3; SharingLink = 4 } function ConvertTo-SPMColor { [CmdletBinding()] param([Parameter(Mandatory)][string]$Hex) $r = [Convert]::ToInt32($Hex.Substring(0, 2), 16) $g = [Convert]::ToInt32($Hex.Substring(2, 2), 16) $b = [Convert]::ToInt32($Hex.Substring(4, 2), 16) return [System.Drawing.Color]::FromArgb(255, $r, $g, $b) } function Set-SPMPermCell { <# .SYNOPSIS Writes a permission code into a worksheet cell with the report's color scheme (hue = level, intensity = inheritance). #> [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Worksheet, [Parameter(Mandatory)][int]$Row, [Parameter(Mandatory)][int]$Column, [Parameter(Mandatory)][string]$Permission, [Parameter(Mandatory)][bool]$Unique ) $styleKey = if ($script:SPMPermStyle.ContainsKey($Permission)) { $Permission } else { 'X' } $style = $script:SPMPermStyle[$styleKey] $cell = $Worksheet.Cells[$Row, $Column] $cell.Value = $Permission $cell.Style.HorizontalAlignment = [OfficeOpenXml.Style.ExcelHorizontalAlignment]::Center $cell.Style.Font.Size = 9 if ($Unique) { $cell.Style.Font.Bold = $true $cell.Style.Font.Color.SetColor((ConvertTo-SPMColor $style.Font)) $cell.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $cell.Style.Fill.BackgroundColor.SetColor((ConvertTo-SPMColor $style.Fill)) } else { $cell.Style.Font.Color.SetColor((ConvertTo-SPMColor $style.Pale)) } } function Get-SPMWorksheetName { [CmdletBinding()] param( [Parameter(Mandatory)][string]$Title, [Parameter(Mandatory)][string]$Suffix, [Parameter(Mandatory)][AllowEmptyCollection()][System.Collections.Generic.HashSet[string]]$UsedNames ) $clean = ($Title -replace '[\[\]\*\?/\\:]', ' ').Trim() $max = 31 - $Suffix.Length - 1 if ($clean.Length -gt $max) { $clean = $clean.Substring(0, $max).Trim() } $name = "$clean $Suffix" $i = 2 while (-not $UsedNames.Add($name)) { $name = "$($clean.Substring(0, [Math]::Min($clean.Length, $max - 2))) $Suffix$i" $i++ } return $name } function Add-SPMMatrixSheet { <# .SYNOPSIS Writes one matrix worksheet (rows = hierarchy, columns = the given column set). .DESCRIPTION $Columns is an array of @{ Header = <string>; CellFor = <scriptblock node -> @{Permission;Unique} or $null> }. #> [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Package, [Parameter(Mandatory)][string]$SheetName, [Parameter(Mandatory)]$Site, [Parameter(Mandatory)][object[]]$Columns ) $ws = Add-Worksheet -ExcelPackage $Package -WorksheetName $SheetName $ws.Cells[1, 1].Value = 'Hierarchy (site > library > folder)' $ws.Cells[1, 1].Style.Font.Bold = $true $ws.Cells[1, 1].Style.VerticalAlignment = [OfficeOpenXml.Style.ExcelVerticalAlignment]::Bottom for ($c = 0; $c -lt $Columns.Count; $c++) { $cell = $ws.Cells[1, ($c + 2)] $cell.Value = [string]$Columns[$c].Header $cell.Style.TextRotation = 90 $cell.Style.Font.Size = 9 $cell.Style.VerticalAlignment = [OfficeOpenXml.Style.ExcelVerticalAlignment]::Bottom $cell.Style.HorizontalAlignment = [OfficeOpenXml.Style.ExcelHorizontalAlignment]::Center $ws.Column($c + 2).Width = 4.5 } $ws.Row(1).Height = 115 $ws.Column(1).Width = 58 $uniqueFill = ConvertTo-SPMColor 'FFF7ED' $row = 2 foreach ($n in @($Site.nodes)) { $cell = $ws.Cells[$row, 1] $cell.Value = (' ' * (2 * [int]$n.level)) + [string]$n.title $cell.Style.Font.Size = 10 if ($n.hasUniquePermissions -and $n.level -gt 0) { $cell.Style.Font.Bold = $true $cell.Style.Fill.PatternType = [OfficeOpenXml.Style.ExcelFillStyle]::Solid $cell.Style.Fill.BackgroundColor.SetColor($uniqueFill) } for ($c = 0; $c -lt $Columns.Count; $c++) { $v = & $Columns[$c].CellFor $n if ($v) { Set-SPMPermCell -Worksheet $ws -Row $row -Column ($c + 2) -Permission $v.Permission -Unique ([bool]$n.hasUniquePermissions) } } $row++ } $row++ $ws.Cells[$row, 1].Value = 'FC = Full Control, W = Write (Edit), C = Contribute, R = Read, X* = custom levels. Bold/tinted = assigned here (unique), pale = inherited. Highlighted hierarchy rows = inheritance broken.' $ws.Cells[$row, 1].Style.Font.Size = 8 $ws.Cells[$row, 1].Style.Font.Italic = $true if ($Site.customRoles) { foreach ($prop in $Site.customRoles.PSObject.Properties) { $row++ $ws.Cells[$row, 1].Value = "$($prop.Name) = $($prop.Value)" $ws.Cells[$row, 1].Style.Font.Size = 8 } } $ws.View.FreezePanes(2, 2) return $ws } function Add-SPMGroupSheet { [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Package, [Parameter(Mandatory)]$Site, [Parameter(Mandatory)][AllowEmptyCollection()][System.Collections.Generic.HashSet[string]]$UsedNames, [switch]$IncludeSharingLinks ) $principals = @($Site.principals | Where-Object { $IncludeSharingLinks -or $_.type -ne 'SharingLink' } | Sort-Object { $script:SPMTypeOrder[[string]$_.type] ?? 3 }, name) $cellMaps = @{} foreach ($n in @($Site.nodes)) { $m = @{} foreach ($a in @($n.assignments)) { $m[[string]$a.principalId] = $a } $cellMaps[[string]$n.id] = $m } $columns = @(foreach ($p in $principals) { $principalId = [string]$p.id @{ Header = [string]$p.name CellFor = { param($n) $a = $cellMaps[[string]$n.id][$principalId]; if ($a) { @{ Permission = [string]$a.permission } } }.GetNewClosure() } }) $name = Get-SPMWorksheetName -Title ([string]$Site.title) -Suffix 'G' -UsedNames $UsedNames $null = Add-SPMMatrixSheet -Package $Package -SheetName $name -Site $Site -Columns $columns } function Add-SPMPeopleSheet { [CmdletBinding(SupportsShouldProcess = $false)] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')] param( [Parameter(Mandatory)]$Package, [Parameter(Mandatory)]$Site, [Parameter(Mandatory)][AllowEmptyCollection()][System.Collections.Generic.HashSet[string]]$UsedNames, [switch]$IncludeSharingLinks ) $personMap = Get-SPMPersonMap -Site $Site $principalById = @{} foreach ($p in @($Site.principals)) { $principalById[[string]$p.id] = $p } # effective best permission per node/person $cells = @{} $personHasAny = [System.Collections.Generic.HashSet[string]]::new() foreach ($n in @($Site.nodes)) { $nodeCells = @{} foreach ($a in @($n.assignments)) { $p = $principalById[[string]$a.principalId] if (-not $p) { continue } if (-not $IncludeSharingLinks -and $p.type -eq 'SharingLink') { continue } $keys = @() if ($p.type -eq 'User') { $k = ([string]($p.upn ?? $p.name)).Trim().ToLowerInvariant() if ($k) { $keys = @($k) } } elseif ($personMap.PerPrincipal.ContainsKey([string]$p.id)) { $keys = @($personMap.PerPrincipal[[string]$p.id]) } foreach ($k in $keys) { [void]$personHasAny.Add($k) $existing = $nodeCells[$k] if (-not $existing -or (Get-SPMPermRank $a.permission) -lt (Get-SPMPermRank $existing)) { $nodeCells[$k] = [string]$a.permission } } } $cells[[string]$n.id] = $nodeCells } $persons = @($personMap.Persons | Where-Object { $personHasAny.Contains($_.key) }) $specials = @($Site.principals | Where-Object { $_.type -in 'Everyone', 'EveryoneExceptExternal' }) $columns = @() $columns += @(foreach ($person in $persons) { $key = $person.key @{ Header = [string]$person.name CellFor = { param($n) $v = $cells[[string]$n.id][$key]; if ($v) { @{ Permission = $v } } }.GetNewClosure() } }) $columns += @(foreach ($p in $specials) { $principalId = [string]$p.id @{ Header = [string]$p.name CellFor = { param($n) $a = @($n.assignments) | Where-Object { [string]$_.principalId -eq $principalId } | Select-Object -First 1; if ($a) { @{ Permission = [string]$a.permission } } }.GetNewClosure() } }) $name = Get-SPMWorksheetName -Title ([string]$Site.title) -Suffix 'P' -UsedNames $UsedNames $null = Add-SPMMatrixSheet -Package $Package -SheetName $name -Site $Site -Columns $columns } |