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) { #Write-Verbose "Add-ExcelWorkSheet - Name: $WorksheetName already exists" if ($Option -eq 'Skip') { #Write-Verbose "Add-ExcelWorkSheet - Name: $WorksheetName - skipping" 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 { #Write-Verbose "Future use..." } } 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 { param( [OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [int] $CellRow, [int] $CellColumn, [Object] $CellValue, [bool] $Supress ) if ($ExcelWorksheet) { $Type = Get-ObjectType $CellValue Switch ($CellValue) { { $_ -and $Type.ObjectTypeName -eq 'PSCustomObject' } { $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue break } { $_ -and $Type.ObjectTypeName -eq 'Object[]' } { $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue -join [System.Environment]::NewLine $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.WrapText = $true break } { $_ -is [DateTime]} { $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Numberformat.Format = 'm/d/yy h:mm' break } { $_ -is [TimeSpan]} { $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Numberformat.Format = 'hh:mm:ss' break } { $_ -is [Int64]} { $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Numberformat.Format = '#' } Default { $Data = $ExcelWorksheet.Cells[$CellRow, $CellColumn].Value = $CellValue } } } if ($Supress) { return } else { $Data } } function Add-ExcelWorksheetData { [CmdletBinding()] Param( [alias('ExcelWorkbook')][OfficeOpenXml.ExcelPackage] $ExcelDocument, $ExcelWorksheet, # [OfficeOpenXml.ExcelWorksheet] [Parameter(ValueFromPipeline = $true)][Object] $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, # this feature scans properties of an object for all objects it contains to make sure all headers are there [alias('TableStyles')][nullable[OfficeOpenXml.Table.TableStyles]] $TableStyle, [string] $TableName, [bool] $Supress ) Begin { $FirstRun = $True $RowNr = if ($StartRow -ne $null -and $StartRow -ne 0) { $StartRow } else { 1 } $ColumnNr = if ($StartColumn -ne $null -and $StartColumn -ne 0 ) { $StartColumn } else { 1 } if ($ExcelWorksheet -ne $null) { 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...' # throw 'Add-ExcelWorksheetData - ExcelDocument and ExcelWorksheet not given. Terminating.' } } if ($AutoFilter -and $TableStyle) { Write-Warning 'Add-ExcelWorksheetData - Using AutoFilter and TableStyle is not supported at same time. TableStyle will be skipped.' } #Write-Verbose "Add-ExcelWorksheetData - Excel Row: $RowNr Column: $ColumnNr" } Process { if ((Get-ObjectCount -Object $DataTable) -ne 0) { if ($FirstRun) { $FirstRun = $false #Write-Verbose "Add-ExcelWorksheetData - FirstRun - RowsToProcess: $($DataTable.Count) - Transpose: $Transpose AutoFit: $Autofit Autofilter: $Autofilter" if ($Transpose) { $DataTable = Format-TransposeTable -Object $DataTable -Sort $TransposeSort } $Data = Format-PSTable -Object $DataTable -ExcludeProperty $ExcludeProperty -NoAliasOrScriptProperties:$NoAliasOrScriptProperties -DisplayPropertySet:$DisplayPropertySet -PreScanHeaders:$PreScanHeaders # -SkipTitle:$NoHeader $WorksheetHeaders = $Data[0] # Saving Header information for later use #Write-Verbose "Add-ExcelWorksheetData - Headers: $($WorksheetHeaders -join ', ') - Data Count: $($Data.Count)" if ($NoHeader) { $Data.RemoveAt(0); #Write-Verbose "Removed header from ArrayList - Data Count: $($Data.Count)" } $ArrRowNr = 0 foreach ($RowData in $Data) { $ArrColumnNr = 0 $ColumnNr = $StartColumn foreach ($Value in $RowData) { #Write-Verbose "Row: $RowNr / $ArrRowNr Column: $ColumnNr / $ArrColumnNr Data: $Value Title: $($WorksheetHeaders[$ArrColumnNr])" Add-ExcelWorkSheetCell -ExcelWorksheet $ExcelWorksheet -CellRow $RowNr -CellColumn $ColumnNr -CellValue $Value -Supress $True $ColumnNr++ $ArrColumnNr++ } $ArrRowNr++ $RowNr++ } } else { #Write-Verbose "Add-ExcelWorksheetData - NextRun - RowsToProcess: $($DataTable.Count) - Transpose: $Transpose AutoFit: $Autofit Autofilter: $Autofilter" 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) { #Write-Verbose "Row: $RowNr / $ArrRowNr Column: $ColumnNr / $ArrColumnNr Data: $Value Title: $($WorksheetHeaders[$ArrColumnNr])" Add-ExcelWorkSheetCell -ExcelWorksheet $ExcelWorksheet -CellRow $RowNr -CellColumn $ColumnNr -CellValue $Value -Supress $True $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 ) Begin { $Fail = $false $Data = @() $FirstRun = $true 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 ($Excel -eq $null) { Write-Verbose "ConvertTo-Excel - Excel is null, creating new Excel" $Excel = New-ExcelDocument #-Verbose } } Process { if ($Fail) { return } $Data += $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 Save-ExcelDocument -ExcelDocument $Excel -FilePath $FilePath -OpenWorkBook:$OpenWorkBook } } [int] $Script:SaveCounter = 0 function Find-ExcelDocumentText { 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 #$i = 1 foreach ($WorkSheet in $ExcelWorksheets) { #Write-Color 'Worksheet ', $i -Color White, Red $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++) { #Write-Color -Text 'Row: ', $Row, ' Column: ', $Column -Color White, Green, White, Green $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 } } } #$i++ } if ($Replace) { Save-ExcelDocument -ExcelDocument $Excel -FilePath $FilePathTarget -OpenWorkBook:$OpenWorkBook } if ($Supress) { return } else { return $Addresses } } } function Get-ExcelDocument { 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 { 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 = @() # 1st row 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 #$ValueContent $ColumnName = $Headers[$CellColumn - 1] # Write-Verbose "CellRow: $CellRow CellColumn: $CellColumn ColumnName: $ColumnName ValueContent: $ValueContent" Add-Member -InputObject $ExcelData -MemberType NoteProperty -Name $ColumnName -Value $ValueContent $ExcelData.$ColumnName = $ValueContent } $ExcelDataArray += $ExcelData } return $ExcelDataArray } function New-ExcelDocument { 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 { 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 #[hashtable] $CustomProperty, #[hashtable] $ExtendedProperty ) 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 } #foreach ($Key in $Custom.Keys) { # $ExcelDocument.Workbook.Properties.SetCustomPropertyValue($Key, $Custom.$Key) #} #foreach ($Key in $ExtendedProperty.Keys) { # $ExcelDocument.Workbook.Properties.SetExtendedPropertyValue($Key, $ExtendedProperty.$Key) #} } function Set-ExcelWorksheetAutoFilter { [CmdletBinding()] param ( [OfficeOpenXml.ExcelWorksheet] $ExcelWorksheet, [string] $DataRange, [bool] $AutoFilter ) if ($ExcelWorksheet) { if (-not $DataRange) { # if $DateRange was not provided try to get one from worksheet dimensions $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, # [underlineType] $UnderLineType, $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 ($UnderLine) { # $ExcelWorksheet.Cells[$CellRow, $CellColumn].Style.Font.UnderLine = $UnderLine # } 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) { # check for row or column not being 0 if ($FreezePane[1] -gt 1) { # check for column greater then 1 $ExcelWorksheet.View.FreezePanes($FreezePane[0], $FreezePane[1]) } } } } } #else { # Write-Verbose 'Set-ExcelWorkSheetFreezePane - ExcelWorkSheet is null' #} } 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) { # AutoFilter doesn't work with Styles return } if (-not $DataRange) { # if $DateRange was not provided try to get one from worksheet dimensions $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') |