-
Notifications
You must be signed in to change notification settings - Fork 401
/
GetExcelTable.ps1
102 lines (81 loc) · 2.64 KB
/
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
}
Import-Module .\ImportExcel.psd1 -Force
#Get-ExcelTableName .\testTable.xlsx | Get-ExcelTable .\testTable.xlsx
Get-ExcelTable .\testTable.xlsx Table3