Get-ExcelSpreadSheetTables.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
<#
 
    .SYNOPSIS
    Returns the cell content from an Excel spreadsheet.
  
    .DESCRIPTION
    Returns the cell content from an Excel spreadsheet.
 
    .NOTES
    File Name : Get-ExcelCellContent.ps1
    Author : Pascal Rimark
    Requires : PowerShell Version 3.0
     
    .LINK
    To provide feedback or for further assistance email:
    pascal@rimark.de
 
    .PARAMETER File
    Specify the file location of the excel file to import
    String
 
    .PARAMETER Address
    Specify the address of the cell (e.g A1 or B21).
    String
 
    .EXAMPLE
    Get-ExcelCellContent .\MyExcel.xlsx -Address "B21"
 
#>


function Get-ExcelSpreadSheetTables() {
    param(

        [Parameter(Mandatory=$True)]
        [string]$File = "C:\users\primark\desktop\UICS-O365-Pre-RoleoutV2.xlsx"

    )

    $stopwatch =  [system.diagnostics.stopwatch]::StartNew()

    Write-Verbose "ScriptRoot: $PSScriptRoot"

    try {
        $epplus = [System.Reflection.Assembly]::LoadFile("$PSScriptRoot\EPPlus.dll");
        #$epplus = [System.Reflection.Assembly]::LoadFile("C:\Users\primark\Desktop\Powershell\impexc\EPPlus.dll");
        Write-Verbose "Assembly loaded"
    } catch {
        throw "FAILED_LOADING_ASSEMBLY_FILE - $($_.Exception.Message)"
    }

    try {
        $stream = New-Object -TypeName System.IO.FileStream -ArgumentList $File, 'Open', 'Read', 'ReadWrite'
        Write-Verbose "Stream opened ($($stream.Name))"
    } catch {
        throw "FAILED_CREATING_FILESTREAM - $($_.Exception.Message)"
    }

    try {
        $xlspck = New-Object OfficeOpenXml.ExcelPackage
        $xlspck.Load($stream)
        Write-Verbose "Package loaded - Loaded from stream"
    } catch {
        $stream.Dispose()
        $xlspck.Stream.Close()
        $xlspck.Dispose()
        throw "FAILED_CREATING EXCELPACKAGE - $($_.Exception.Message)"
    }

    try {
        if([string]::IsNullOrEmpty($WorkSheetName)) {
            $Worksheet = $xlspck.Workbook.Worksheets[1]
        } else {
            $Worksheet = $xlspck.Workbook.Worksheets["$WorkSheetName"]
        }
    } catch {
        $stream.Dispose()
        $xlspck.Stream.Close()
        $xlspck.Dispose()
        throw "FAILED_OPENING_WORKSHEET($WorkSheetName) - $($_.Exception.Message)"
    }

    return $Worksheet.Tables.Name
}