GetExcelTable.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
Function Get-ExcelTableName {
    Param (
        $Path,
        $WorksheetName
    )

    $Path = (Resolve-Path $Path).ProviderPath
    $Stream = New-Object -TypeName System.IO.FileStream -ArgumentList $Path, 'Open', 'Read', 'ReadWrite'

    $Excel = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Stream

    if ($WorksheetName) {
        $Worksheet = $Excel.Workbook.Worksheets[$WorkSheetName]
    } else {
        $Worksheet = $Excel.Workbook.Worksheets | Select-Object -First 1
    }

    foreach($TableName in $Worksheet.Tables.Name) {
        [PSCustomObject][Ordered]@{
            WorksheetName=$Worksheet.Name
            TableName=$TableName
        }
    }

    $Stream.Close()
    $Stream.Dispose()
    $Excel.Dispose()
    $Excel = $null
}

Function Get-ExcelTable {
    Param (
        $Path,
        $TableName,
        $WorksheetName
    )

    $Path = (Resolve-Path $Path).ProviderPath
    $Stream = New-Object -TypeName System.IO.FileStream -ArgumentList $Path, 'Open', 'Read', 'ReadWrite'

    $Excel = New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList $Stream

    if ($WorksheetName) {
        $Worksheet = $Excel.Workbook.Worksheets[$WorkSheetName]
    } else {
        $Worksheet = $Excel.Workbook.Worksheets | Select-Object -First 1
    }

    if($TableName) {
        $Table = $Worksheet.Tables[$TableName]
    } else {
        $Table = $Worksheet.Tables | Select-Object -First 1
    }

    $rowCount = $Table.Address.Rows
    $colCount = $Table.Address.Columns

    $digits = "0123456789".ToCharArray()

    $start, $end=$Table.Address.Address.Split(':')

    $pos=$start.IndexOfAny($digits)
    [int]$startCol=ConvertFrom-ExcelColumnName $start.Substring(0,$pos)
    [int]$startRow=$start.Substring($pos)

    $propertyNames = for($col=$startCol; $col -lt ($startCol+$colCount); $col+= 1) {
        $Worksheet.Cells[$startRow, $col].value
    }

    $startRow++
    for($row=$startRow; $row -lt ($startRow+$rowCount); $row += 1) {
        $nr=[ordered]@{}
        $c=0
        for($col=$startCol; $col -lt ($startCol+$colCount); $col+= 1) {
            $nr.($propertyNames[$c]) = $Worksheet.Cells[$row, $col].value
            $c++
        }
        [pscustomobject]$nr
    }

    $Stream.Close()
    $Stream.Dispose()
    $Excel.Dispose()
    $Excel = $null
}

function ConvertFrom-ExcelColumnName {
    param($columnName)

    $sum=0
    $columnName.ToCharArray() |
        ForEach-Object {
            $sum*=26
            $sum+=[char]$_.tostring().toupper()-[char]'A'+1
        }
    $sum
}

ipmo .\ImportExcel.psd1 -Force

#Get-ExcelTableName .\testTable.xlsx | Get-ExcelTable .\testTable.xlsx
Get-ExcelTable .\testTable.xlsx Table3