Get-ExcelSheetInfo.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
Function Get-ExcelSheetInfo {
    <#
      .SYNOPSIS
        Get worksheet names and their indices of an Excel workbook.
      .DESCRIPTION
        The Get-ExcelSheetInfo cmdlet gets worksheet names and their indices of an Excel workbook.
      .PARAMETER Path
        Specifies the path to the Excel file. This parameter is required.
      .EXAMPLE
        Get-ExcelSheetInfo .\Test.xlsx
 
      .NOTES
        CHANGELOG
        2016/01/07 Added Created by Johan Akerstrom (https://github.com/CosmosKey)
 
      .LINK
        https://github.com/dfinke/ImportExcel
    #>


    [CmdletBinding()]
    param(
        [Alias('FullName')]
        [Parameter(ValueFromPipelineByPropertyName=$true, ValueFromPipeline=$true, Mandatory=$true)]
        $Path
    )
    process {
        $Path = (Resolve-Path $Path).ProviderPath

        $stream = New-Object -TypeName System.IO.FileStream -ArgumentList $Path,'Open','Read','ReadWrite'
        $xl = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $stream
        $workbook  = $xl.Workbook
        
        if ($workbook -and $workbook.Worksheets) {
            $workbook.Worksheets |
                Select-Object -Property name,index,hidden,@{
                    Label = 'Path'
                    Expression = {$Path}
                }
        }

        $stream.Close()
        $stream.Dispose()
        $xl.Dispose()
        $xl = $null
    }
}