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. Option: $Option" $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 $Data = Add-ExcelWorkSheet -ExcelDocument $ExcelDocument -WorksheetName $WorksheetName -Option $Option -Supress $False 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 ($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, [OfficeOpenXml.ExcelWorksheet] $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, [RGBColors] $TabColor = [RGBColors]::None, [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 Write-Verbose "Add-ExcelWorkSheetData - ExcelWorksheet $($ExcelWorkSheet.Name)" } 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 -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 -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 ($TabColor -ne [RGBColors]::None) { $ExcelWorksheet.TabColor = ConvertFrom-Color -Color $TabColor } 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 } 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 -Supress $true 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, [nullable[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') |