Excelimo.psm1
|
function Excel { [CmdletBinding()] param([Parameter(Position = 0)][ValidateNotNull()][ScriptBlock] $Content = $(Throw "Excel requires opening and closing brace."), [string] $FilePath, [switch] $Open, [switch] $Parallel) $Time = Start-TimeLog $ExcelDocument = New-ExcelDocument $Script:Excel = @{} $Script:Excel.ExcelDocument = $ExcelDocument $Script:Excel.Runspaces = @{} $Script:Excel.Runspaces.Parallel = $Parallel.IsPresent $Script:Excel.Runspaces.RunspacesPool = New-RunSpace $Script:Excel.Runspaces.Runspaces = [System.Collections.Generic.List[PSCustomObject]]::new() [Array] $Output = ConvertFrom-ScriptBlock -ScriptBlock $Content $WorkbookProperties = ConvertTo-ScriptBlock -Code $Output -Include 'WorkbookProperties' $Everything = ConvertTo-ScriptBlock -Code $Output -Exclude 'WorkbookProperties' if ($Everything) { & $Everything $Script:Excel.Runspaces.End = Stop-Runspace -Runspaces $Script:Excel.Runspaces.Runspaces -FunctionName "Excel" -RunspacePool $Script:RunspacesPool -Verbose:$Verbose -ErrorAction SilentlyContinue -ErrorVariable +AllErrors -ExtendedOutput:$ExtendedOutputF } if ($WorkbookProperties) {& $WorkbookProperties} $EndTime = Stop-TimeLog -Time $Time -Option OneLiner Save-ExcelDocument -ExcelDocument $ExcelDocument -FilePath $FilePath -OpenWorkBook:$Open $Script:Excel = $null Write-Verbose "Excel - Time to create - $EndTime" } function WorkbookProperties { [CmdletBinding()] param([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) $ExcelProperties = @{HyperlinksChanged = $HyperlinksChanged ScaleCrop = $ScaleCrop HyperlinkBase = $HyperlinkBase Subject = $Subject LastModifiedBy = $LastModifiedBy Author = $Author LinksUpToDate = $LinksUpToDate Modified = $Modified LastPrinted = $LastPrinted Company = $Company Comments = $Comments Title = $Title SharedDoc = $SharedDoc Created = $Created Category = $Category ExcelDocument = $Script:Excel.ExcelDocument Status = $Status AppVersion = $AppVersion Keywords = $Keywords Application = $Application Manager = $Manager } Set-ExcelProperties @ExcelProperties } function Worksheet { [CmdletBinding()] param([Array] $DataTable, [string] $Name, [ValidateSet("Replace", "Skip", "Rename")][string] $Option = 'Replace', [RGBColors] $TabColor = [RGBColors]::None, [switch] $AutoFilter, [switch] $AutoFit) $ScriptBlock = {Param ($ExcelDocument, [Array] $DataTable, [string] $Name, [ValidateSet("Replace", "Skip", "Rename")][string] $Option = 'Replace', [RGBColors] $TabColor = [RGBColors]::None, [bool] $Supress, [switch] $AutoFilter, [switch] $AutoFit) $addExcelWorkSheetDataSplat = @{DataTable = $DataTable TabColor = $TabColor Supress = $Supress Option = $Option ExcelDocument = $ExcelDocument ExcelWorksheetName = $Name AutoFit = $AutoFit AutoFilter = $AutoFilter } Add-ExcelWorkSheetData @addExcelWorkSheetDataSplat -Verbose} $ExcelWorkSheetParameters = [ordered] @{DataTable = $DataTable TabColor = $TabColor Supress = $true Option = $Option ExcelDocument = $Script:Excel.ExcelDocument Name = $Name AutoFit = $AutoFit AutoFilter = $AutoFilter } if ($Script:Excel.Runspaces.Parallel) { $RunSpace = Start-Runspace -ScriptBlock $ScriptBlock -Parameters $ExcelWorkSheetParameters -RunspacePool $Script:Excel.Runspaces.RunspacesPool -Verbose:$Verbose $Script:Excel.Runspaces.Runspaces.Add($RunSpace) } else {& $ScriptBlock -Parameters @ExcelWorkSheetParameters} } Export-ModuleMember -Function @('Excel', 'WorkbookProperties', 'Worksheet') -Alias @() |