PSWriteExcel.psm1
|
function Add-ExcelWorkSheet { [cmdletBinding()] param ([OfficeOpenXml.ExcelPackage] $ExcelDocument, [alias('Name')][string] $WorksheetName, [ValidateSet("Replace", "Skip", "Rename")][string] $Option = 'Skip', [bool] $Supress) $WorksheetName = $WorksheetName.Trim() if ($WorksheetName.Length -eq 0) { $WorksheetName = Get-RandomStringName -Size 31 Write-Warning "Add-ExcelWorkSheet - Name is empty. Generated random name: $WorksheetName" } elseif ($WorksheetName.Length -gt 31) {$WorksheetName = $WorksheetName.Substring(0, 31)} $PreviousWorksheet = Get-ExcelWorkSheet -ExcelDocument $ExcelDocument -Name $WorksheetName if ($PreviousWorksheet) { if ($Option -eq 'Skip') { Write-Warning "Add-ExcelWorkSheet - Worksheet $WorksheetName already exists. Skipping creation of new worksheet." Write-Warning "Add-ExcelWorkSheet - You can overwrite this setting with one of the Options: Replace, Skip, Rename" $Data = $PreviousWorksheet } elseif ($Option -eq 'Replace') { Write-Verbose "Add-ExcelWorkSheet - WorksheetName: $WorksheetName - exists. Replacing worksheet with empty worksheet." Remove-ExcelWorksheet -ExcelDocument $ExcelDocument -ExcelWorksheet $PreviousWorksheet $Data = Add-ExcelWorkSheet -ExcelDocument $ExcelDocument -WorksheetName $WorksheetName -Option $Option -Supress $False } elseif ($Option -eq 'Rename') { Write-Verbose "Add-ExcelWorkSheet - Worksheet: $WorksheetName already exists. Renaming worksheet to random value." $WorksheetName = Get-RandomStringName -Size 31 Write-Verbose "Add-ExcelWorkSheet - New worksheet name $WorksheetName" } else {} } else { Write-Verbose "Add-ExcelWorkSheet - WorksheetName: $WorksheetName doesn't exists in Workbook. Continuing..." $Data = $ExcelDocument.Workbook.Worksheets.Add($WorksheetName) if ($Data.Name -ne $WorksheetName) { Write-Warning "Add-ExcelWorkSheet - WorksheetName was changed from:'$WorksheetName' to new name: '$($Data.Name)'." Write-Warning "Add-ExcelWorkSheet - Maximum amount of chars is 31 for worksheet name" } } if ($Supress) {return} else {return $Data} } function Add-ExcelWorkSheetCell { [CmdletBinding()] param([OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [int] $CellRow, [int] $CellColumn, [Object] $CellValue) if ($ExcelWorksheet) { Switch ($CellValue) { {$_ -is [PSCustomObject]} { $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue break } {$_ -is [Array]} { $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue -join [System.Environment]::NewLine $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.WrapText = $true break } {$_ -is [DateTime]} { $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Numberformat.Format = 'm/d/yy h:mm' break } {$_ -is [TimeSpan]} { $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Numberformat.Format = 'hh:mm:ss' break } {$_ -is [Int64]} { $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Numberformat.Format = '#' break } Default {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue} } } } function Add-ExcelWorksheetData { [CmdletBinding()] Param([alias('ExcelWorkbook')][OfficeOpenXml.ExcelPackage] $ExcelDocument, $ExcelWorksheet, [Parameter(ValueFromPipeline = $true)][Array] $DataTable, [ValidateSet("Replace", "Skip", "Rename")][string] $Option = 'Replace', [int]$StartRow = 1, [int]$StartColumn = 1, [alias("Autosize")][switch] $AutoFit, [switch] $AutoFilter, [Switch] $FreezeTopRow, [Switch] $FreezeFirstColumn, [Switch] $FreezeTopRowFirstColumn, [int[]]$FreezePane, [alias('Name', 'WorksheetName')][string] $ExcelWorksheetName, [alias('Rotate', 'RotateData', 'TransposeColumnsRows', 'TransposeData')][switch] $Transpose, [ValidateSet("ASC", "DESC", "NONE")][string] $TransposeSort = 'NONE', [switch] $PreScanHeaders, [alias('TableStyles')][nullable[OfficeOpenXml.Table.TableStyles]] $TableStyle, [string] $TableName, [bool] $Supress) Begin { $FirstRun = $True $RowNr = if ($null -ne $StartRow -and $StartRow -ne 0) {$StartRow} else {1} $ColumnNr = if ($null -ne $StartColumn -and $StartColumn -ne 0) {$StartColumn} else {1} if ($null -ne $ExcelWorksheet) {Write-Verbose "Add-ExcelWorkSheetData - ExcelWorksheet given. Continuing..."} else {if ($ExcelDocument) {$ExcelWorkSheet = Add-ExcelWorkSheet -ExcelDocument $ExcelDocument -Name $ExcelWorksheetName -Option $Option} else {Write-Warning 'Add-ExcelWorksheetData - ExcelDocument and ExcelWorksheet not given. No data will be added...'}} if ($AutoFilter -and $TableStyle) {Write-Warning 'Add-ExcelWorksheetData - Using AutoFilter and TableStyle is not supported at same time. TableStyle will be skipped.'} } Process { if ($DataTable.Count -gt 0) { if ($FirstRun) { $FirstRun = $false if ($Transpose) {$DataTable = Format-TransposeTable -Object $DataTable -Sort $TransposeSort} $Data = Format-PSTable -Object $DataTable -ExcludeProperty $ExcludeProperty -NoAliasOrScriptProperties:$NoAliasOrScriptProperties -DisplayPropertySet:$DisplayPropertySet -PreScanHeaders:$PreScanHeaders $WorksheetHeaders = $Data[0] if ($NoHeader) {$Data.RemoveAt(0)} $ArrRowNr = 0 foreach ($RowData in $Data) { $ArrColumnNr = 0 $ColumnNr = $StartColumn foreach ($Value in $RowData) { Add-ExcelWorkSheetCell -ExcelWorksheet $ExcelWorksheet -CellRow $RowNr -CellColumn $ColumnNr -CellValue $Value $ColumnNr++ $ArrColumnNr++ } $ArrRowNr++ $RowNr++ } } else { if ($Transpose) {$DataTable = Format-TransposeTable -Object $DataTable -Sort $TransposeSort} $Data = Format-PSTable -Object $DataTable -SkipTitle -ExcludeProperty $ExcludeProperty -NoAliasOrScriptProperties:$NoAliasOrScriptProperties -DisplayPropertySet:$DisplayPropertySet -OverwriteHeaders $WorksheetHeaders -PreScanHeaders:$PreScanHeaders $ArrRowNr = 0 foreach ($RowData in $Data) { $ArrColumnNr = 0 $ColumnNr = $StartColumn foreach ($Value in $RowData) { Add-ExcelWorkSheetCell -ExcelWorksheet $ExcelWorksheet -CellRow $RowNr -CellColumn $ColumnNr -CellValue $Value $ColumnNr++; $ArrColumnNr++ } $RowNr++; $ArrRowNr++ } } } } End { if ($AutoFit) {Set-ExcelWorksheetAutoFit -ExcelWorksheet $ExcelWorksheet} if ($AutoFilter) {Set-ExcelWorksheetAutoFilter -ExcelWorksheet $ExcelWorksheet -DataRange $ExcelWorksheet.Dimension -AutoFilter $AutoFilter} if ($FreezeTopRow -or $FreezeFirstColumn -or $FreezeTopRowFirstColumn -or $FreezePane) {Set-ExcelWorkSheetFreezePane -ExcelWorksheet $ExcelWorksheet -FreezeTopRow:$FreezeTopRow -FreezeFirstColumn:$FreezeFirstColumn -FreezeTopRowFirstColumn:$FreezeTopRowFirstColumn -FreezePane $FreezePane} if ($TableStyle) {Set-ExcelWorkSheetTableStyle -ExcelWorksheet $ExcelWorksheet -TableStyle $TableStyle -DataRange $ExcelWorksheet.Dimension -TableName $TableName} if ($Supress) {return} else {return $ExcelWorkSheet} } } function ConvertFrom-Excel { [CmdletBinding()] param([alias('Excel', 'Path')][string] $FilePath, [alias('WorksheetName', 'Name')][string] $ExcelWorksheetName) if (Test-Path $FilePath) { $ExcelDocument = Get-ExcelDocument -Path $FilePath if ($ExcelWorksheetName) { $ExcelWorksheet = Get-ExcelWorkSheet -ExcelDocument $ExcelDocument -Name $ExcelWorksheetName if ($ExcelWorksheet) { $Data = Get-ExcelWorkSheetData -ExcelDocument $ExcelDocument -ExcelWorkSheet $ExcelWorksheet return $Data } else {Write-Warning "ConvertFrom-Excel - Worksheet with name $ExcelWorksheetName doesn't exists. Conversion terminated."} } } else {Write-Warning "ConvertFrom-Excel - File $FilePath doesn't exists. Conversion terminated."} } function ConvertTo-Excel { [CmdletBinding()] param([alias("path")][string] $FilePath, [OfficeOpenXml.ExcelPackage] $Excel, [alias('Name', 'WorksheetName')][string] $ExcelWorkSheetName, [alias("TargetData")][Parameter(ValueFromPipeline = $true)][Object] $DataTable, [ValidateSet("Replace", "Skip", "Rename")][string] $Option = 'Replace', [switch] $AutoFilter, [alias("Autosize")][switch] $AutoFit, [Switch] $FreezeTopRow, [Switch] $FreezeFirstColumn, [Switch] $FreezeTopRowFirstColumn, [int[]]$FreezePane, [alias('Rotate', 'RotateData', 'TransposeColumnsRows', 'TransposeData')][switch] $Transpose, [ValidateSet("ASC", "DESC", "NONE")][string] $TransposeSort = 'NONE', [alias('TableStyles')][nullable[OfficeOpenXml.Table.TableStyles]] $TableStyle, [string] $TableName, [switch] $OpenWorkBook, [switch] $PreScanHeaders) Begin { $Fail = $false $Data = [System.Collections.Generic.List[Object]]::new() if ($FilePath -like '*.xlsx') { if (Test-Path $FilePath) { $Excel = Get-ExcelDocument -Path $FilePath Write-Verbose "ConvertTo-Excel - Excel exists, Excel is loaded from file" } } else { $Fail = $true Write-Warning "ConvertTo-Excel - Excel path not given or incorrect (no .xlsx file format)" return } if ($null -eq $Excel) { Write-Verbose "ConvertTo-Excel - Excel is null, creating new Excel" $Excel = New-ExcelDocument } } Process { if ($Fail) {return} $Data.Add($DataTable) } End { if ($Fail) {return} $Return = Add-ExcelWorksheetData -DataTable $Data -ExcelDocument $Excel -AutoFit:$AutoFit -AutoFilter:$AutoFilter -ExcelWorksheetName $ExcelWorkSheetName -FreezeTopRow:$FreezeTopRow -FreezeFirstColumn:$FreezeFirstColumn -FreezeTopRowFirstColumn:$FreezeTopRowFirstColumn -FreezePane $FreezePane -Transpose:$Transpose -TransposeSort $TransposeSort -Option $Option -TableStyle $TableStyle -TableName $TableName -PreScanHeaders:$PreScanHeaders Save-ExcelDocument -ExcelDocument $Excel -FilePath $FilePath -OpenWorkBook:$OpenWorkBook } } [int] $Script:SaveCounter = 0 function Find-ExcelDocumentText { [CmdletBinding()] param([string] $FilePath, [string] $FilePathTarget, [string] $Find, [switch] $Replace, [string] $ReplaceWith, [switch] $Regex, [switch] $OpenWorkBook, [bool] $Supress) $Excel = Get-ExcelDocument -Path $FilePath if ($Excel) { $Addresses = @() $ExcelWorksheets = $Excel.Workbook.Worksheets foreach ($WorkSheet in $ExcelWorksheets) { $StartRow = $WorkSheet.Dimension.Start.Row $StartColumn = $WorkSheet.Dimension.Start.Column $EndRow = $WorkSheet.Dimension.End.Row + 1 $EndColumn = $WorkSheet.Dimension.End.Column + 1 for ($Row = $StartRow; $Row -le $EndRow; $Row++) { for ($Column = $StartColumn; $Column -le $EndColumn; $Column++) { $Value = $Worksheet.Cells[$Column, $Row].Value if ($Value -like "*$Find*") { if ($Replace) {if ($Regex) {$Worksheet.Cells[$Column, $Row].Value = $Value -Replace $Find, $ReplaceWith} else {$Worksheet.Cells[$Column, $Row].Value = $Value.Replace($Find, $ReplaceWith)}} $Addresses += $WorkSheet.Cells[$Column, $Row].FullAddress } } } } if ($Replace) {Save-ExcelDocument -ExcelDocument $Excel -FilePath $FilePathTarget -OpenWorkBook:$OpenWorkBook} if ($Supress) {return} else {return $Addresses} } } function Get-ExcelDocument { [CmdletBinding()] param([alias("FilePath")][string] $Path) $Script:SaveCounter = 0 if (Test-Path $Path) { $Excel = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Path return $Excel } else {return} } function Get-ExcelProperties { [CmdletBinding()] param([OfficeOpenXml.ExcelPackage] $ExcelDocument) if ($ExcelDocument) {$Properties = [ordered] @{} foreach ($Key in $ExcelDocument.Workbook.Properties.PsObject.Properties.Name | Where {$_ -notlike '*Xml'}) {$Properties.$Key = $ExcelDocument.Workbook.Properties.$Key} return $Properties } } function Get-ExcelTranslateFromR1C1 { [alias('Set-ExcelTranslateFromR1C1')] [CmdletBinding()] param([int]$Row, [int]$Column = 1) $Range = [OfficeOpenXml.ExcelAddress]::TranslateFromR1C1("R[$Row]C[$Column]", 0, 0) return $Range } function Get-ExcelTranslateToR1C1 { [alias('Set-ExcelTranslateToR1C1')] [CmdletBinding()] param([string] $Value) if ($Value -eq '') {return} else { $Range = [OfficeOpenXml.ExcelAddress]::TranslateToR1C1($Value, 0, 0) return $Range } } function Get-ExcelWorkSheet { [OutputType([OfficeOpenXml.ExcelWorksheet])] [cmdletBinding()] param ([OfficeOpenXml.ExcelPackage] $ExcelDocument, [string] $Name, [nullable[int]] $Index, [switch] $All) if ($ExcelDocument) { if ($Name -and $Index) { Write-Warning 'Get-ExcelWorkSheet - Only $Name or $Index of Worksheet can be used.' return } if ($All) {$Data = $ExcelDocument.Workbook.Worksheets} elseif ($Name -or $Index -ne $null) { if ($Name) {$Data = $ExcelDocument.Workbook.Worksheets | Where {$_.Name -eq $Name}} if ($Index -ne $null) { if ($PSEdition -ne 'Core') {$Index = $Index + 1} Write-Verbose "Get-ExcelWorkSheet - Index: $Index" $Data = $ExcelDocument.Workbook.Worksheets[$Index] } } } return $Data } function Get-ExcelWorkSheetCell { [CmdletBinding()] param([OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [int] $CellRow, [int] $CellColumn, [bool] $Supress) if ($ExcelWorksheet) {$Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value} return $Data } function Get-ExcelWorkSheetData { [CmdletBinding()] param([OfficeOpenXml.ExcelPackage] $ExcelDocument, [OfficeOpenXml.ExcelWorksheet] $ExcelWorkSheet) $Dimensions = $ExcelWorkSheet.Dimension $CellRow = 1 $ExcelDataArray = @() $Headers = @() for ($CellColumn = 1; $CellColumn -lt $Dimensions.Columns + 1; $CellColumn++) { $Heading = $ExcelWorkSheet.Cells[$CellRow, $CellColumn].Value if ([string]::IsNullOrEmpty($Heading)) {$Heading = $ExcelWorkSheet.Cells[$CellRow, $CellColumn].Address} $Headers += $Heading } Write-Verbose "Get-ExcelWorkSheetData - Headers: $($Headers -join ',')" for ($CellRow = 2; $CellRow -lt $Dimensions.Rows + 1; $CellRow++) {$ExcelData = [PsCustomObject] @{} for ($CellColumn = 1; $CellColumn -lt $Dimensions.Columns + 1; $CellColumn++) { $ValueContent = $ExcelWorkSheet.Cells[$CellRow, $CellColumn].Value $ColumnName = $Headers[$CellColumn - 1] Add-Member -InputObject $ExcelData -MemberType NoteProperty -Name $ColumnName -Value $ValueContent $ExcelData.$ColumnName = $ValueContent } $ExcelDataArray += $ExcelData } return $ExcelDataArray } function New-ExcelDocument { [CmdletBinding()] param() $Script:SaveCounter = 0 $Excel = New-Object -TypeName OfficeOpenXml.ExcelPackage return $Excel } function Remove-ExcelWorksheet { [CmdletBinding()] param ([alias('ExcelWorkbook')][OfficeOpenXml.ExcelPackage] $ExcelDocument, [OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet) if ($ExcelDocument -and $ExcelWorksheet) {$ExcelDocument.Workbook.Worksheets.Delete($ExcelWorksheet)} } function Save-ExcelDocument { [CmdletBinding()] param ([parameter(Mandatory = $false, ValueFromPipeline = $true)][Alias('Document', 'Excel', 'Package')] $ExcelDocument, [string] $FilePath, [alias('Show', 'Open')][switch] $OpenWorkBook) if (-not $ExcelDocument -or $ExcelDocument.Workbook.Worksheets.Count -eq 0) { Write-Warning "Save-ExcelDocument - Saving workbook $FilePath was terminated. No worksheets/data exists." return } if ($Script:SaveCounter -gt 5) { Write-Warning "Save-ExcelDocument - Couldnt save Excel. Terminating.." return } try { Write-Verbose "Save-ExcelDocument - Saving workbook $FilePath" $ExcelDocument.SaveAs($FilePath) $Script:SaveCounter = 0 } catch { $Script:SaveCounter++ $ErrorMessage = $_.Exception.Message if ($ErrorMessage -like "*The process cannot access the file*because it is being used by another process.*" -or $ErrorMessage -like "*Error saving file*") { $FilePath = Get-FileName -Temporary -Extension 'xlsx' Write-Warning "Save-ExcelDocument - Couldn't save file as it was in use or otherwise. Trying different name $FilePath" $ExcelDocument.File = $FilePath Save-ExcelDocument -ExcelDocument $ExcelDocument -FilePath $FilePath -OpenWorkBook:$OpenWorkBook } else {Write-Warning "Save-ExcelDocument - Error: $ErrorMessage"} } if ($OpenWorkBook) {if (Test-Path $FilePath) {Invoke-Item -Path $FilePath} else {Write-Warning "Save-ExcelDocument - File $FilePath doesn't exists. Can't open Excel document."}} } function Set-ExcelProperties { [CmdletBinding()] param([OfficeOpenXml.ExcelPackage] $ExcelDocument, [string] $Title, [string] $Subject, [string] $Author, [string] $Comments, [string] $Keywords, [string] $LastModifiedBy, [string] $LastPrinted, [DateTime] $Created, [string] $Category, [string] $Status, [string] $Application, [string] $HyperlinkBase, [string] $AppVersion, [string] $Company, [string] $Manager, [nullable[DateTime]] $Modified, [nullable[bool]] $LinksUpToDate, [nullable[bool]] $HyperlinksChanged, [nullable[bool]] $ScaleCrop, [nullable[bool]] $SharedDoc) if ($Title) {$ExcelDocument.Workbook.Properties.Title = $Title} if ($Subject) {$ExcelDocument.Workbook.Properties.Subject = $Subject} if ($Author) {$ExcelDocument.Workbook.Properties.Author = $Author} if ($Comments) {$ExcelDocument.Workbook.Properties.Comments = $Comments} if ($Keywords) {$ExcelDocument.Workbook.Properties.Keywords = $Keywords} if ($LastModifiedBy) {$ExcelDocument.Workbook.Properties.LastModifiedBy = $LastModifiedBy} if ($LastPrinted) {$ExcelDocument.Workbook.Properties.LastPrinted = $LastPrinted} if ($Created) {$ExcelDocument.Workbook.Properties.Created = $Created} if ($Category) {$ExcelDocument.Workbook.Properties.Category = $Category} if ($Status) {$ExcelDocument.Workbook.Properties.Status = $Status} if ($Application) {$ExcelDocument.Workbook.Properties.Application = $Application} if ($HyperlinkBase) {if ($HyperlinkBase -like '*://*') {$ExcelDocument.Workbook.Properties.HyperlinkBase = $HyperlinkBase} else {Write-Warning "Set-ExcelProperties - Hyperlinkbase is not an URL (doesn't contain ://)"}} if ($AppVersion) {$ExcelDocument.Workbook.Properties.AppVersion = $AppVersion} if ($Company) {$ExcelDocument.Workbook.Properties.Company = $Company} if ($Manager) {$ExcelDocument.Workbook.Properties.Manager = $Manager} if ($Modified) {$ExcelDocument.Workbook.Properties.Modified = $Modified} if ($LinksUpToDate -ne $null) {$ExcelDocument.Workbook.Properties.LinksUpToDate = $LinksUpToDate} if ($HyperlinksChanged -ne $null) {$ExcelDocument.Workbook.Properties.HyperlinksChanged = $HyperlinksChanged} if ($ScaleCrop -ne $null) {$ExcelDocument.Workbook.Properties.ScaleCrop = $ScaleCrop} if ($SharedDoc -ne $null) {$ExcelDocument.Workbook.Properties.SharedDoc = $SharedDoc} } function Set-ExcelWorksheetAutoFilter { [CmdletBinding()] param ([OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [string] $DataRange, [bool] $AutoFilter) if ($ExcelWorksheet) { if (-not $DataRange) {$DataRange = $ExcelWorksheet.Dimension} try {$ExcelWorksheet.Cells[$DataRange].AutoFilter = $AutoFilter} catch { $ErrorMessage = $_.Exception.Message -replace "`n", " " -replace "`r", " " Write-Warning "Set-ExcelWorksheetAutoFilter - Failed AutoFilter with error message: $ErrorMessage" } } } function Set-ExcelWorksheetAutoFit { [CmdletBinding()] param ([OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet) if ($ExcelWorksheet) { Write-Verbose "Set-ExcelWorksheetAutoFit - Columns Count: $($ExcelWorksheet.Dimension.Columns)" if ($ExcelWorksheet.Dimension.Columns -gt 0) { try {$ExcelWorksheet.Cells.AutoFitColumns()} catch { $ErrorMessage = $_.Exception.Message -replace "`n", " " -replace "`r", " " Write-Warning "Set-ExcelWorksheetAutoFit - Failed AutoFit with error message: $ErrorMessage" } } } } function Set-ExcelWorkSheetCellStyleFont { [CmdletBinding()] param([OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [int] $CellRow, [int] $CellColumn, [nullable[bool]] $Bold, [nullable]$Color, $Family, $Italic, [string] $Name, $Scheme, [nullable[int]] $Size, $Strike, $UnderLine, $VerticalAlign) if (-not $ExcelWorksheet) {return} if ($Bold) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Bold = $Bold} if ($Color) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Color = $Color} if ($Family) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Family = $Family} if ($Italic) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Italic = $Italic} if ($Name) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Name = $Name} if ($Scheme) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Scheme = $Scheme} if ($Size) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Size = $Size} if ($Strike) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.Strike = $Strike} if ($UnderLineType) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.UnderLineType = $UnderLineType} if ($VerticalAlign) {$ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.VerticalAlign = $VerticalAlign} } function Set-ExcelWorkSheetFreezePane { [CmdletBinding()] param([OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [Switch] $FreezeTopRow, [Switch] $FreezeFirstColumn, [Switch] $FreezeTopRowFirstColumn, [int[]]$FreezePane) try { if ($ExcelWorksheet -ne $null) { if ($FreezeTopRowFirstColumn) { Write-Verbose 'Set-ExcelWorkSheetFreezePane - Processing freezing panes FreezeTopRowFirstColumn' $ExcelWorksheet.View.FreezePanes(2, 2) } elseif ($FreezeTopRow -and $FreezeFirstColumn) { Write-Verbose 'Set-ExcelWorkSheetFreezePane - Processing freezing panes FreezeTopRow and FreezeFirstColumn' $ExcelWorksheet.View.FreezePanes(2, 2) } elseif ($FreezeTopRow) { Write-Verbose 'Set-ExcelWorkSheetFreezePane - Processing freezing panes FreezeTopRow' $ExcelWorksheet.View.FreezePanes(2, 1) } elseif ($FreezeFirstColumn) { Write-Verbose 'Set-ExcelWorkSheetFreezePane - Processing freezing panes FreezeFirstColumn' $ExcelWorksheet.View.FreezePanes(1, 2) } if ($FreezePane) { Write-Verbose 'Set-ExcelWorkSheetFreezePane - Processing freezing panes FreezePane' if ($FreezePane.Count -eq 2) {if ($FreezePane -notcontains 0) {if ($FreezePane[1] -gt 1) {$ExcelWorksheet.View.FreezePanes($FreezePane[0], $FreezePane[1])}}} } } } catch { $ErrorMessage = $_.Exception.Message -replace "`n", " " -replace "`r", " " Write-Warning "Set-ExcelWorkSheetFreezePane - Worksheet: $($ExcelWorksheet.Name) error: $ErrorMessage" } } function Set-ExcelWorkSheetTableStyle { [CmdletBinding()] param([OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [string] $DataRange, [alias('TableStyles')][nullable[OfficeOpenXml.Table.TableStyles]] $TableStyle, [string] $TableName = $(Get-RandomStringName -LettersOnly -Size 5 -ToLower)) try { if ($null -ne $ExcelWorksheet) { if ($ExcelWorksheet.AutoFilterAddress) {return} if (-not $DataRange) {$DataRange = $ExcelWorksheet.Dimension} if ($null -ne $TableStyle) { Write-Verbose "Set-ExcelWorkSheetTableStyle - Setting style to $TableStyle" $ExcelWorkSheetTables = $ExcelWorksheet.Tables.Add($DataRange, $TableName) $ExcelWorkSheetTables.TableStyle = $TableStyle } } } catch { $ErrorMessage = $_.Exception.Message -replace "`n", " " -replace "`r", " " Write-Warning "Set-ExcelWorkSheetTableStyle - Worksheet: $($ExcelWorksheet.Name) error: $ErrorMessage" } } if ($PSEdition -eq 'Core') { Add-Type -Path $PSScriptRoot\Lib\Core\EPPlus.NetCORE.dll Add-Type -Path $PSScriptRoot\Lib\Core\Microsoft.Extensions.Configuration.Abstractions.dll Add-Type -Path $PSScriptRoot\Lib\Core\Microsoft.Extensions.Configuration.dll Add-Type -Path $PSScriptRoot\Lib\Core\Microsoft.Extensions.Configuration.FileExtensions.dll Add-Type -Path $PSScriptRoot\Lib\Core\Microsoft.Extensions.Configuration.Json.dll Add-Type -Path $PSScriptRoot\Lib\Core\Microsoft.Extensions.FileProviders.Abstractions.dll Add-Type -Path $PSScriptRoot\Lib\Core\Microsoft.Extensions.FileProviders.Physical.dll Add-Type -Path $PSScriptRoot\Lib\Core\Microsoft.Extensions.Primitives.dll } else {Add-Type -Path $PSScriptRoot\Lib\Default\EPPlus.Net40.dll} Export-ModuleMember -Function @('Add-ExcelWorkSheet', 'Add-ExcelWorkSheetCell', 'Add-ExcelWorksheetData', 'ConvertFrom-Excel', 'ConvertTo-Excel', 'Find-ExcelDocumentText', 'Get-ExcelDocument', 'Get-ExcelProperties', 'Get-ExcelTranslateFromR1C1', 'Get-ExcelTranslateToR1C1', 'Get-ExcelWorkSheet', 'Get-ExcelWorkSheetCell', 'Get-ExcelWorkSheetData', 'New-ExcelDocument', 'Remove-ExcelWorksheet', 'Save-ExcelDocument', 'Set-ExcelProperties', 'Set-ExcelWorksheetAutoFilter', 'Set-ExcelWorksheetAutoFit', 'Set-ExcelWorkSheetCellStyleFont', 'Set-ExcelWorkSheetFreezePane', 'Set-ExcelWorkSheetTableStyle') -Alias @('Set-ExcelTranslateFromR1C1', 'Set-ExcelTranslateToR1C1') |