ImportExcel

7.2.2

Public/Set-WorksheetProtection.ps1

function Set-WorksheetProtection {
    [CmdletBinding()]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingfunctions', '',Justification='Does not change system state')]
    param (
        [Parameter(Mandatory=$true)]
        [OfficeOpenXml.ExcelWorksheet]$Worksheet ,
        [switch]$IsProtected,
        [switch]$AllowAll,
        [switch]$BlockSelectLockedCells,
        [switch]$BlockSelectUnlockedCells,
        [switch]$AllowFormatCells,
        [switch]$AllowFormatColumns,
        [switch]$AllowFormatRows,
        [switch]$AllowInsertColumns,
        [switch]$AllowInsertRows,
        [switch]$AllowInsertHyperlinks,
        [switch]$AllowDeleteColumns,
        [switch]$AllowDeleteRows,
        [switch]$AllowSort,
        [switch]$AllowAutoFilter,
        [switch]$AllowPivotTables,
        [switch]$BlockEditObject,
        [switch]$BlockEditScenarios,
        [string]$LockAddress,
        [string]$UnLockAddress
    )

    if     ($PSBoundParameters.ContainsKey('isprotected') -and  $IsProtected -eq $false) {$worksheet.Protection.IsProtected  = $false}
    elseif ($IsProtected) {
        $worksheet.Protection.IsProtected  = $true
        foreach ($ParName in @('AllowFormatCells',
          'AllowFormatColumns', 'AllowFormatRows',
          'AllowInsertColumns', 'AllowInsertRows', 'AllowInsertHyperlinks',
          'AllowDeleteColumns', 'AllowDeleteRows',
          'AllowSort'         , 'AllowAutoFilter', 'AllowPivotTables')) {
               if ($AllowAll -and -not $PSBoundParameters.ContainsKey($Parname)) {$worksheet.Protection.$ParName = $true}
               elseif ($PSBoundParameters[$ParName] -eq $true )                      {$worksheet.Protection.$ParName = $true}
        }
        if ($BlockSelectLockedCells)   {$worksheet.Protection.AllowSelectLockedCells   = $false }
        if ($BlockSelectUnlockedCells) {$worksheet.Protection.AllowSelectUnLockedCells = $false }
        if ($BlockEditObject)          {$worksheet.Protection.AllowEditObject          = $false }
        if ($BlockEditScenarios)       {$worksheet.Protection.AllowEditScenarios       = $false }
    }
    Else {Write-Warning -Message "You haven't said if you want to turn protection off, or on." }

    if ($LockAddress) {
        Set-ExcelRange     -Range $Worksheet.cells[$LockAddress] -Locked
    }
    elseif ($IsProtected) {
        Set-ExcelRange     -Range $Worksheet.Cells -Locked
    }
    if ($UnlockAddress) {
        Set-ExcelRange     -Range $Worksheet.cells[$UnlockAddress] -Locked:$false
    }
}