Public/Set-ExcelColumn.ps1
|
function Set-ExcelColumn { [CmdletBinding()] [Alias("Set-Column")] [OutputType([OfficeOpenXml.ExcelColumn],[String])] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingfunctions', '',Justification='Does not change system state')] [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification="Variables created for script block which may be passed as a parameter, but not used in the script")] param( [Parameter(ParameterSetName="Package",Mandatory=$true)] [OfficeOpenXml.ExcelPackage]$ExcelPackage, [Parameter(ParameterSetName="Package")] [String]$Worksheetname = "Sheet1", [Parameter(ParameterSetName="sheet",Mandatory=$true)] [OfficeOpenXml.ExcelWorksheet]$Worksheet, [Parameter(ValueFromPipeline=$true)] [ValidateRange(0,16384)] $Column = 0 , [ValidateRange(1,1048576)] [Int]$StartRow , $Value , $Heading , [Alias("NFormat")] $NumberFormat, [OfficeOpenXml.Style.ExcelBorderStyle]$BorderAround, $FontColor, [Switch]$Bold, [Switch]$Italic, [Switch]$Underline, [OfficeOpenXml.Style.ExcelUnderLineType]$UnderLineType = [OfficeOpenXml.Style.ExcelUnderLineType]::Single, [Switch]$StrikeThru, [OfficeOpenXml.Style.ExcelVerticalAlignmentFont]$FontShift, [String]$FontName, [float]$FontSize, $BackgroundColor, [OfficeOpenXml.Style.ExcelFillStyle]$BackgroundPattern = [OfficeOpenXml.Style.ExcelFillStyle]::Solid , [Alias("PatternColour")] $PatternColor, [Switch]$WrapText, [OfficeOpenXml.Style.ExcelHorizontalAlignment]$HorizontalAlignment, [OfficeOpenXml.Style.ExcelVerticalAlignment]$VerticalAlignment, [ValidateRange(-90, 90)] [int]$TextRotation , [Alias("AutoFit")] [Switch]$AutoSize, [float]$Width, [Switch]$AutoNameRange, [Alias('Hidden')] [Switch]$Hide, [Switch]$Specified, [Switch]$PassThru ) begin { #if we were passed a package object and a worksheet name , get the worksheet. if ($ExcelPackage) { if ($ExcelPackage.Workbook.Worksheets.Name -notcontains $Worksheetname) { throw "The Workbook does not contain a sheet named '$Worksheetname'" } else {$Worksheet = $ExcelPackage.Workbook.Worksheets[$Worksheetname] } } #In a script block to build a formula, we may want any of corners or the column name, #if Column and Startrow aren't specified, assume first unused column, and first row if (-not $StartRow) {$startRow = $Worksheet.Dimension.Start.Row } $startColumn = $Worksheet.Dimension.Start.Column $endColumn = $Worksheet.Dimension.End.Column $endRow = $Worksheet.Dimension.End.Row } process { if ($null -eq $workSheet.Dimension) {Write-Warning "Can't format an empty worksheet."; return} if ($Column -eq 0 ) {$Column = $endColumn + 1 } $columnName = (New-Object 'OfficeOpenXml.ExcelCellAddress' @(1, $column)).Address -replace "1","" Write-Verbose -Message "Updating Column $columnName" #If there is a heading, insert it and use it as the name for a range (if we're creating one) if ($PSBoundParameters.ContainsKey('Heading')) { $Worksheet.Cells[$StartRow, $Column].Value = $Heading $StartRow ++ if ($AutoNameRange) { Add-ExcelName -Range $Worksheet.Cells[$StartRow, $Column, $endRow, $Column] -RangeName $Heading } } elseif ($AutoNameRange) { Add-ExcelName -Range $Worksheet.Cells[($StartRow+1), $Column, $endRow, $Column] -RangeName $Worksheet.Cells[$StartRow, $Column].Value } #Fill in the data -it can be zero null or and empty string. if ($PSBoundParameters.ContainsKey('Value')) { foreach ($row in ($StartRow..$endRow)) { if ($Value -is [scriptblock]) { #re-create the script block otherwise variables from this function are out of scope. $cellData = & ([scriptblock]::create( $Value )) if ($null -eq $cellData) {Write-Verbose -Message "Script block evaluates to null."} else {Write-Verbose -Message "Script block evaluates to '$cellData'"} } else { $cellData = $Value} if ($cellData -match "^=") { $Worksheet.Cells[$Row, $Column].Formula = ($cellData -replace '^=','') } #EPPlus likes formulas with no = sign; Excel doesn't care elseif ( [System.Uri]::IsWellFormedUriString($cellData , [System.UriKind]::Absolute)) { # Save a hyperlink : internal links can be in the form xl://sheet!E419 (use A1 as goto sheet), or xl://RangeName if ($cellData -match "^xl://internal/") { $referenceAddress = $cellData -replace "^xl://internal/" , "" $display = $referenceAddress -replace "!A1$" , "" $h = New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList $referenceAddress , $display $Worksheet.Cells[$Row, $Column].HyperLink = $h } else {$Worksheet.Cells[$Row, $Column].HyperLink = $cellData } $Worksheet.Cells[$Row, $Column].Style.Font.UnderLine = $true $Worksheet.Cells[$Row, $Column].Style.Font.Color.SetColor([System.Drawing.Color]::Blue) } else { $Worksheet.Cells[$Row, $Column].Value = $cellData } if ($cellData -is [datetime]) { $Worksheet.Cells[$Row, $Column].Style.Numberformat.Format = 'm/d/yy h:mm' } # This is not a custom format, but a preset recognized as date and localized. if ($cellData -is [timespan]) { $Worksheet.Cells[$Row, $Column].Style.Numberformat.Format = '[h]:mm:ss' } }} #region Apply formatting $params = @{} foreach ($p in @('Underline','Bold','Italic','StrikeThru', 'FontName', 'FontSize','FontShift','NumberFormat','TextRotation', 'WrapText', 'HorizontalAlignment','VerticalAlignment', 'Autosize', 'Width', 'FontColor' 'BorderAround', 'BackgroundColor', 'BackgroundPattern', 'PatternColor')) { if ($PSBoundParameters.ContainsKey($p)) {$params[$p] = $PSBoundParameters[$p]} } if ($params.Count) { $theRange = "$columnName$StartRow`:$columnName$endRow" Set-ExcelRange -Worksheet $Worksheet -Range $theRange @params } #endregion if ($PSBoundParameters.ContainsKey('Hide')) {$workSheet.Column($Column).Hidden = [bool]$Hide} #return the new data if -passthru was specified. if ($PassThru) { $Worksheet.Column($Column)} elseif ($ReturnRange) { $theRange} } } |