Public/Join-Worksheet.ps1
|
function Join-Worksheet { [CmdletBinding(DefaultParameterSetName = 'Default')] param ( [Parameter(ParameterSetName = "Default", Position = 0)] [Parameter(ParameterSetName = "Table" , Position = 0)] [String]$Path , [Parameter(Mandatory = $true, ParameterSetName = "PackageDefault")] [Parameter(Mandatory = $true, ParameterSetName = "PackageTable")] [OfficeOpenXml.ExcelPackage]$ExcelPackage, $WorksheetName = 'Combined', [switch]$Clearsheet, [switch]$NoHeader, [string]$FromLabel = "From" , [switch]$LabelBlocks, [Switch]$AutoSize, [Switch]$FreezeTopRow, [Switch]$FreezeFirstColumn, [Switch]$FreezeTopRowFirstColumn, [Int[]]$FreezePane, [Parameter(ParameterSetName = 'Default')] [Parameter(ParameterSetName = 'PackageDefault')] [Switch]$AutoFilter, [Switch]$BoldTopRow, [switch]$HideSource, [String]$Title, [OfficeOpenXml.Style.ExcelFillStyle]$TitleFillPattern = 'Solid', $TitleBackgroundColor, [Switch]$TitleBold, [Int]$TitleSize = 22, [Hashtable]$PivotTableDefinition, [Object[]]$ExcelChartDefinition, [Object[]]$ConditionalFormat, [Object[]]$ConditionalText, [switch]$AutoNameRange, [ValidateScript( { if (-not $_) { throw 'RangeName is null or empty.' } elseif ($_[0] -notmatch '[a-z]') { throw 'RangeName starts with an invalid character.' } else { $true } })] [String]$RangeName, [ValidateScript( { if (-not $_) { throw 'Tablename is null or empty.' } elseif ($_[0] -notmatch '[a-z]') { throw 'Tablename starts with an invalid character.' } else { $true } })] [Parameter(ParameterSetName = 'Table' , Mandatory = $true)] [Parameter(ParameterSetName = 'PackageTable' , Mandatory = $true)] [String]$TableName, [Parameter(ParameterSetName = 'Table')] [Parameter(ParameterSetName = 'PackageTable')] [OfficeOpenXml.Table.TableStyles]$TableStyle = 'Medium6', [switch]$ReturnRange, [switch]$Show, [switch]$PassThru ) #region get target worksheet, select it and move it to the end. if ($Path -and -not $ExcelPackage) {$ExcelPackage = Open-ExcelPackage -path $Path } $destinationSheet = Add-Worksheet -ExcelPackage $ExcelPackage -WorksheetName $WorksheetName -ClearSheet:$Clearsheet foreach ($w in $ExcelPackage.Workbook.Worksheets) {$w.view.TabSelected = $false} $destinationSheet.View.TabSelected = $true $ExcelPackage.Workbook.Worksheets.MoveToEnd($WorksheetName) #row to insert at will be 1 on a blank sheet and lastrow + 1 on populated one $row = (1 + $destinationSheet.Dimension.End.Row ) #endregion #region Setup title and header rows #Title parameters work as they do in Export-Excel . if ($row -eq 1 -and $Title) { $destinationSheet.Cells[1, 1].Value = $Title $destinationSheet.Cells[1, 1].Style.Font.Size = $TitleSize if ($TitleBold) {$destinationSheet.Cells[1, 1].Style.Font.Bold = $True } #Can only set TitleBackgroundColor if TitleFillPattern is something other than None. if ($TitleBackgroundColor -AND ($TitleFillPattern -ne 'None')) { if ($TitleBackgroundColor -is [string]) {$TitleBackgroundColor = [System.Drawing.Color]::$TitleBackgroundColor } $destinationSheet.Cells[1, 1].Style.Fill.PatternType = $TitleFillPattern $destinationSheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor($TitleBackgroundColor) } elseif ($TitleBackgroundColor) { Write-Warning "Title Background Color ignored. You must set the TitleFillPattern parameter to a value other than 'None'. Try 'Solid'." } $row = 2 } if (-not $noHeader) { #Assume every row has titles in row 1, copy row 1 from first sheet to new sheet. $destinationSheet.Select("A$row") $ExcelPackage.Workbook.Worksheets[1].cells["1:1"].Copy($destinationSheet.SelectedRange) #fromlabel can't be an empty string if ($FromLabel ) { #Add a column which says where the data comes from. $fromColumn = ($destinationSheet.Dimension.Columns + 1) $destinationSheet.Cells[$row, $fromColumn].Value = $FromLabel } $row += 1 } #endregion foreach ($i in 1..($ExcelPackage.Workbook.Worksheets.Count - 1) ) { $sourceWorksheet = $ExcelPackage.Workbook.Worksheets[$i] #Assume row one is titles, so data itself starts at A2. if ($NoHeader) {$sourceRange = $sourceWorksheet.Dimension.Address} else {$sourceRange = $sourceWorksheet.Dimension.Address -replace "A1:", "A2:"} #Position insertion point/ $destinationSheet.Select("A$row") if ($LabelBlocks) { $destinationSheet.Cells[$row, 1].value = $sourceWorksheet.Name $destinationSheet.Cells[$row, 1].Style.Font.Bold = $true $destinationSheet.Cells[$row, 1].Style.Font.Size += 2 $row += 1 } $destinationSheet.Select("A$row") #And finally we're ready to copy the data. $sourceWorksheet.Cells[$sourceRange].Copy($destinationSheet.SelectedRange) #Fill in column saying where data came from. if ($fromColumn) { $row..$destinationSheet.Dimension.Rows | ForEach-Object {$destinationSheet.Cells[$_, $fromColumn].Value = $sourceWorksheet.Name} } #Update where next insertion will go. $row = $destinationSheet.Dimension.Rows + 1 if ($HideSource) {$sourceWorksheet.Hidden = [OfficeOpenXml.eWorkSheetHidden]::Hidden} } #We accept a bunch of parameters work to pass on to Export-excel ( Autosize, Autofilter, boldtopRow Freeze ); if we have any of those call Export-excel otherwise close the package here. $params = @{} + $PSBoundParameters 'Path', 'Clearsheet', 'NoHeader', 'FromLabel', 'LabelBlocks', 'HideSource', 'Title', 'TitleFillPattern', 'TitleBackgroundColor', 'TitleBold', 'TitleSize' | ForEach-Object {$null = $params.Remove($_)} if ($params.Keys.Count) { if ($Title) { $params.StartRow = 2} $params.WorksheetName = $WorksheetName $params.ExcelPackage = $ExcelPackage Export-Excel @Params } else { Close-ExcelPackage -ExcelPackage $ExcelPackage $ExcelPackage.Dispose() $ExcelPackage = $null } } |