PSExcel

1.0.2

Save-Excel.ps1

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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
 function Save-Excel {
    <#
    .SYNOPSIS
        Save an OfficeOpenXml ExcelPackage
 
    .DESCRIPTION
        Save an OfficeOpenXml ExcelPackage
 
    .PARAMETER Excel
        An ExcelPackage object to close
 
    .PARAMETER Path
        If specified, save as this path
 
    .PARAMETER Close
        If specified, close after saving
 
    .PARAMETER Passthru
        If specified, re-create and return the Excel object
 
    .EXAMPLE
        Save-Excel -Excel $Excel
 
        #Save $Excel
 
    .EXAMPLE
        Save-Excel -Excel $Excel -Close
 
        #Save $Excel, close
 
    .EXAMPLE
        Save-Excel -Excel $Excel -Path C:\new.xlsx
 
        #Save $Excel as C:\new.xlsx
 
    .EXAMPLE
        $Excel = $Excel | Save-Excel -Passthru
 
        #Save $Excel, re-open it to continue working with it.
 
    .NOTES
        Thanks to Doug Finke for his example:
            https://github.com/dfinke/ImportExcel/blob/master/ImportExcel.psm1
 
        Thanks to Philip Thompson for an expansive set of examples on working with EPPlus in PowerShell:
            https://excelpslib.codeplex.com/
 
    .LINK
        https://github.com/RamblingCookieMonster/PSExcel
 
    .FUNCTIONALITY
        Excel
    #>
    [OutputType([OfficeOpenXml.ExcelPackage])]
    [cmdletbinding()]
    param(
        [parameter( Mandatory=$true,
                    ValueFromPipeline=$true,
                    ValueFromPipelineByPropertyName=$true)]
        [OfficeOpenXml.ExcelPackage]$Excel,

        [parameter( Mandatory=$false,
                    ValueFromPipeline=$false,
                    ValueFromPipelineByPropertyName=$false)]
        [validatescript({
            $Parent = Split-Path $_ -Parent -ErrorAction SilentlyContinue
            if( -not (Test-Path -Path $Parent -PathType Container -ErrorAction SilentlyContinue) )
            {
                Throw "Specify a valid path. Parent '$Parent' does not exist: $_"
            }
            $True
        })]        
        [string]$Path,

        [switch]$Close,

        [switch]$Passthru
    )
    Process
    {
        foreach($xl in $Excel)
        {            
            Try
            {
                if($Path)
                {
                    Try
                    {
                        #Resolve relative paths... Thanks Oisin! http://stackoverflow.com/a/3040982/3067642
                        $Path = $ExecutionContext.SessionState.Path.GetUnresolvedProviderPathFromPSPath($Path)
                    }
                    Catch
                    {
                        Write-Error "Could not resolve path for '$Path': $_"
                        continue
                    }
                    
                    write-verbose "Saving $($xl.File) as $($Path)"

                    $xl.saveas($Path)
                }
                else
                {
                    write-verbose "Saving $($xl.File)"

                    $xl.save()
                }

                if($Passthru)
                {
                    $OpenPath = $xl.File
                    $xl.Dispose()
                    $xl = $Null
                    New-Excel -Path $OpenPath
                }
            }
            Catch
            {
                Write-Error "Error saving file. $_"
                Continue
            }
            
            if($Close)
            {
                Try
                {
                    write-verbose "Closing $($xl.File)"

                    $xl.Dispose()
                    $xl = $null
                }
                Catch
                {
                    Write-Error $_
                    Continue
                }
            }
        }    
    }
}