Excelimo.psm1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
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 @()