ConvertFromExcelData.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
function ConvertFrom-ExcelData {
    <#
    .SYNOPSIS
    Reads data from a sheet, and for each row, calls a custom scriptblock with a list of property names and the row of data.
 
     
    .EXAMPLE
    ConvertFrom-ExcelData .\testSQLGen.xlsx {
        param($propertyNames, $record)
 
        $reportRecord = @()
        foreach ($pn in $propertyNames) {
            $reportRecord += "{0}: {1}" -f $pn, $record.$pn
        }
        $reportRecord +=""
        $reportRecord -join "`r`n"
}
 
First: John
Last: Doe
The Zip: 12345
....
    #>

    param(
        [Alias("FullName")]
        [Parameter(ValueFromPipelineByPropertyName = $true, ValueFromPipeline = $true, Mandatory = $true)]
        [ValidateScript( { Test-Path $_ -PathType Leaf })]
        $Path,
        [ScriptBlock]$scriptBlock,
        [Alias("Sheet")]
        $WorkSheetname = 1,
        [int]$HeaderRow = 1,
        [string[]]$Header,
        [switch]$NoHeader,
        [switch]$DataOnly
    )

    $null = $PSBoundParameters.Remove('scriptBlock')
    $params = @{} + $PSBoundParameters

    $data = Import-Excel @params

    $PropertyNames = $data[0].psobject.Properties |
        Where-Object {$_.membertype -match 'property'} |
        Select-Object -ExpandProperty name

    foreach ($record in $data) {
        & $scriptBlock $PropertyNames $record
    }
}