Public/Set-CellComment.ps1
|
[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '', Scope='Function', Target='Set*', Justification='Does not change system state')] param() function Set-CellComment { [CmdletBinding(DefaultParameterSetName = "Range")] param( [Parameter(Mandatory = $True, ParameterSetName = "ColumnLetter")] [Parameter(Mandatory = $True, ParameterSetName = "ColumnNumber")] [Parameter(Mandatory = $False, ParameterSetName = "Range")] [OfficeOpenXml.ExcelWorkSheet]$Worksheet, [Parameter(Mandatory = $True, ParameterSetName = "Range", ValueFromPipeline = $true,Position=0)] [Alias("Address")] $Range, [Parameter(Mandatory = $True, ParameterSetName = "ColumnLetter")] [Parameter(Mandatory = $True, ParameterSetName = "ColumnNumber")] [Int]$Row, [Parameter(Mandatory = $True, ParameterSetName = "ColumnLetter")] [String]$ColumnLetter, [Parameter(Mandatory = $True, ParameterSetName = "ColumnNumber")] [Int]$ColumnNumber, [Parameter(Mandatory = $True)] [String]$Text ) If ($PSCmdlet.ParameterSetName -eq "Range") { Write-Verbose "Using 'Range' Parameter Set" if ($Range -is [Array]) { $null = $PSBoundParameters.Remove("Range") $Range | Set-CellComment @PSBoundParameters } else { #We should accept, a worksheet and a name of a range or a cell address; a table; the address of a table; a named range; a row, a column or .Cells[ ] if ($Range -is [OfficeOpenXml.Table.ExcelTable]) {$Range = $Range.Address} elseif ($Worksheet -and $Range -is [string]) { # Convert range as string to OfficeOpenXml.ExcelAddress $Range = [OfficeOpenXml.ExcelAddress]::new($Range) } elseif ($Range -is [string]) {Write-Warning -Message "The range parameter you have specified also needs a worksheet parameter." ;return} #else we assume $Range is a OfficeOpenXml.ExcelAddress } } ElseIf ($PSCmdlet.ParameterSetName -eq "ColumnNumber") { $Range = [OfficeOpenXml.ExcelAddress]::new($Row, $ColumnNumber, $Row, $ColumnNumber) } ElseIf ($PSCmdlet.ParameterSetName -eq "ColumnLetter") { $Range = [OfficeOpenXml.ExcelAddress]::new(("{0}{1}" -f $ColumnLetter,$Row)) } If ($Range -isnot [Array]) { Foreach ($c in $Worksheet.Cells[$Range]) { write-verbose $c.address Try { If ($Null -eq $c.comment) { [Void]$c.AddComment($Text, "ImportExcel") } Else { $c.Comment.Text = $Text $c.Comment.Author = "ImportExcel" } $c.Comment.AutoFit = $True } Catch { "Could not add comment to cell {0}: {1}" -f $c.Address, $_.ToString() } } } } |