Set-Row.ps1
|
Function Set-Row { <# .Synopsis Fills values into a [new] row in an Excel spreadsheet. To format a row without setting values, use Set-Format. .Description Set-Row accepts either a Worksheet object or an Excel package object returned by Export-Excel and the name of a sheet, and inserts the chosen contents into a row of the sheet. The contents can be a constant "42" , a formula or a script block which is converted into a constant or formula. The first cell of the row can optional be given a heading. .Example Set-row -Worksheet $ws -Heading Total -Value {"=sum($columnName`2:$columnName$endrow)" } $Ws contains a worksheet object, and no Row number is specified so Set-Row will select the next row after the end of the data in the sheet The first cell will contain "Total", and each other cell will contain =Sum(xx2:xx99) - where xx is the column name, and 99 is the last row of data. Note the use of `2 to Prevent 2 becoming part of the variable "ColumnName" The script block can use $row, $column, $ColumnName, $startRow/Column $endRow/Column #> [cmdletbinding()] Param ( #An Excel package object - e.g. from Export-Excel -passthru - requires a sheet name [Parameter(ParameterSetName="Package",Mandatory=$true)] [OfficeOpenXml.ExcelPackage]$ExcelPackage, #the name to update in the package [Parameter(ParameterSetName="Package")] $Worksheetname = "Sheet1", #A worksheet object [Parameter(ParameterSetName="sheet",Mandatory=$true)] [OfficeOpenXml.Excelworksheet] $Worksheet, #Row to fill right - first row is 1. 0 will be interpreted as first unused row $Row = 0 , #Position in the row to start from [Int]$StartColumn, #value, formula or script block for to fill in. Script block can use $worksheet, $row, $column [number], $ColumnName [letter(s)], $startRow, $startColumn, $endRow, $endColumn [parameter(Mandatory=$true)] $Value, #Optional Row heading $Heading , #Number format to apply to cells e.g. "dd/MM/yyyy HH:mm", "£#,##0.00;[Red]-£#,##0.00", "0.00%" , "##/##" , "0.0E+0" etc [Alias("NFormat")] $NumberFormat, #Style of border to draw around the row [OfficeOpenXml.Style.ExcelBorderStyle]$BorderAround, #Colour for the text - if none specified it will be left as it it is [System.Drawing.Color]$FontColor, #Make text bold; use -Bold:$false to remove bold [switch]$Bold, #Make text italic; use -Italic:$false to remove italic [switch]$Italic, #Underline the text using the underline style in -underline type; use -Underline:$false to remove underlining [switch]$Underline, #Should Underline use single or double, normal or accounting mode : default is single normal [OfficeOpenXml.Style.ExcelUnderLineType]$UnderLineType = [OfficeOpenXml.Style.ExcelUnderLineType]::Single, #Strike through text; use -Strikethru:$false to remove Strike through [switch]$StrikeThru, #Subscript or superscript (or none) [OfficeOpenXml.Style.ExcelVerticalAlignmentFont]$FontShift, [String]$FontName, #Point size for the text [float]$FontSize, #Change background colour [System.Drawing.Color]$BackgroundColor, #Background pattern - solid by default [OfficeOpenXml.Style.ExcelFillStyle]$BackgroundPattern = [OfficeOpenXml.Style.ExcelFillStyle]::Solid , #Secondary colour for background pattern [Alias("PatternColour")] [System.Drawing.Color]$PatternColor, #Turn on text wrapping; use -WrapText:$false to turn off word wrapping [switch]$WrapText, #Position cell contents to left, right, center etc. default is 'General' [OfficeOpenXml.Style.ExcelHorizontalAlignment]$HorizontalAlignment, #Position cell contents to top bottom or centre [OfficeOpenXml.Style.ExcelVerticalAlignment]$VerticalAlignment, #Degrees to rotate text. Up to +90 for anti-clockwise ("upwards"), or to -90 for clockwise [ValidateRange(-90, 90)] [int]$TextRotation , #Set cells to a fixed hieght [float]$Height, #If Sepecified returns the range of cells which affected [switch]$ReturnRange, #If Specified, return a row object to allow further work to be done [switch]$PassThru ) #if we were passed a package object and a worksheet name , get the worksheet. if ($ExcelPackage) {$Worksheet = $ExcelPackage.Workbook.worksheets[$Worksheetname] } #In a script block to build a formula, we may want any of corners or the columnname, #if row and start column aren't specified assume first unused row, and first column if (-not $StartColumn) {$StartColumn = $Worksheet.Dimension.Start.Column } $startRow = $Worksheet.Dimension.Start.Row + 1 $endColumn = $Worksheet.Dimension.End.Column $endRow = $Worksheet.Dimension.End.Row if ($Row -lt 2 ) {$Row = $endRow + 1 } Write-Verbose -Message "Updating Row $Row" #Add a row label if ($Heading) { $Worksheet.Cells[$Row, $StartColumn].Value = $Heading $StartColumn ++ } #Fill in the data if ($PSBoundParameters.ContainsKey('Value')) {foreach ($column in ($StartColumn..$EndColumn)) { #We might want the column name in a script block $ColumnName = [OfficeOpenXml.ExcelCellAddress]::new(1,$column).Address -replace "1","" if ($Value -is [scriptblock] ) { #re-create the script block otherwise variables from this function are out of scope. $cellData = & ([scriptblock]::create( $Value )) Write-Verbose -Message $cellData } else{$cellData = $Value} if ($cellData -match "^=") { $Worksheet.Cells[$Row, $column].Formula = $cellData } 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.Color.SetColor([System.Drawing.Color]::Blue) $Worksheet.Cells[$Row, $Column].Style.Font.UnderLine = $true } 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. }} #region Apply formatting $params = @{} foreach ($p in @('Underline','Bold','Italic','StrikeThru','FontSize', 'FontShift','NumberFormat','TextRotation', 'WrapText', 'HorizontalAlignment','VerticalAlignment', 'Height', 'FontColor' 'BorderAround', 'BackgroundColor', 'BackgroundPattern', 'PatternColor')) { if ($PSBoundParameters.ContainsKey($p)) {$params[$p] = $PSBoundParameters[$p]} } $theRange = [OfficeOpenXml.ExcelAddress]::TranslateFromR1C1("R[$Row]C[$StartColumn]:R[$Row]C[$EndColumn]",0,0) if ($params.Count) { Set-Format -WorkSheet $Worksheet -Range $theRange @params } #endregion #return the new data if -passthru was specified. if ($passThru) {$Worksheet.Row($Row)} elseif ($ReturnRange) {$theRange} } |