Import-KFExcelFile
Syntax
Import-KFExcelFile
-InFile <excel-file>
-SheetName <string>
[-Address <address>]
[-Fields <list-of-string>]
[-AfterReadingCell <scriptblock>]
[-AfterReadingHeaderCell <scriptblock>]
[-ThrowErrors[:$true]]
Support
>= kenaflow 4.0.11
Return
System.Collections.Hashtable
object or $null
Description
This cmdlet can be used to import an Microsoft Excel file. It enables the workflow developer to consume data from an Excel file source.
The result is returned as Hashtable
object. The keys are taken from the first line of the imported Excel worksheet -SheetName
, starting with the given -Address
or cell A1
.
The input Excel file is given as
- as System.IO.Stream
- as Base64 encoded byte array
- or as file on disk, relative to the workflow folder.
With -Fields
the imported fields can be filtered. If this parameter is present it's values must match the string values in the cells of the first line of the imported worksheet starting at position A1
or starting at the specified position in -Address
.
There are two script blocks that can be used to post-process the imported data before writing it to the result hashtable object:
-AfterReadingHeaderCell
is executed after reading every header cell in the first line of the imported worksheet. It's is used to modify the hashtable keys. There is an example below.-AfterReadingCell
is executed after reading every data cell from the imported worksheet. It can be used to modify data before it is written to the result hashtable. There is an example below.
Parameters
-InFile <template-excel-file>
An Excel file to be imported.
- The file can be specified as Base64 encoded string or
- as System.IO.Sream object or
- as a full or relative path to an existing file on disk. It's relative to the current workflow folder.
-SheetName <string>
The name of the Excel worksheet.
[-Address <address>]
This parameter can be used to specify the worksheet region for the data import.
<address>
can be
- an Excel style cell addres, such as
A1
orZZ55
.- an Hashtable of this kind:
@{Row="4"; Col="2"}
(The Excel address of the cell would beB4
.)
[-Fields <list-of-strings>]
With
-Fields
the imported fields can be filtered. If this parameter is present it's values must match the string values in the cells of the first line of the imported worksheet starting at positionA1
or starting at the specified position in-Address
.
[-AfterReadingHeaderCell <scriptblock>]
This scriptblock is executed after reading every header cell in the first line of the imported worksheet. It's is used to modify the hashtable keys.
This is an example of who the script show look like, especially to get the corret parameters. The script MUST return a value!
-AfterReadingHeaderCell { param($cell, $row, $col, $currentFieldName) return $currentFieldName }
>
`[-AfterReadingCell <scriptblock>]`
> This scriptblock is executed after reading every data cell from the imported worksheet. It can be used to modify data before it is written to the result hashtable.
>
> This is an example of who the script show look like, especially to get the corret parameters. The script MUST return a value!
> ```PowerShell
-AfterReadingCell {
param($cell, $row, $col, $currentValue)
return $currentValue
}
[-ThrowErrors[:$true]]
This is a default parameter for all kenaflow cmdlets. If set to
$true
(default!) the engine will pass exception within the cmdlet to the script for further handling.
Example
1.
This example show how to read an Excel file from disk. It additionaly show how to manipulate the imported data.
$ht2 = Import-KFExcelFile -InFile "$PSScriptRoot\inp3.xlsx" -SheetName "Tabelle1" -Address "A1" -AfterReadingHeaderCell {
param($cell, $row, $col, $currentFieldName)
write-host $currentFieldName
return "Field $currentFieldName"
} -AfterReadingCell {
param($cell, $row, $col, $currentValue)
write-host $currentValue
return "x $currentValue"
}
$ht2 | % {
write-host "----Hashtable start"
$_
}
Input file:
Output:
----Hashtable start
Field A x This
----Hashtable start
Field B x is
----Hashtable start
Field C x a
----Hashtable start
Field C x kenaflow
Field B x of
Field A x test
2.
This script combines Import-KFExcelFile
with New-KFExcelFile
. The Excel file is created in memory. The DateTime
values in Excel can be converted to regular System.DateTime values using [DateTime]::FromOADate
.
$i = New-KFExcelFile -input @(
@{FieldA = "kenaflow"; FieldB = [DateTime]::Now; FieldC = 10.101}
@{FieldA = "is"; FieldB = [DateTime]::Now.AddDays(1); FieldC = 10.101}
@{FieldA = "cool"; FieldB = [DateTime]::Now.AddMonths(1); FieldC = 10.101}
@{FieldA = "!"; FieldB = [DateTime]::Now.AddYears(1); FieldC = 10.101}
) -asbase64 -SheetName "test1"
$ht = Import-KFExcelFile -InFile $i -SheetName "test1" -Address @{Col=1;Row=1}
$ht = Import-KFExcelFile -InFile $i -SheetName "test1" -Address "A1"
$ht
Output:
Name Value
---- -----
FieldB 44193,4891746759
FieldC 10,101
FieldA kenaflow
FieldB 44194,4891746759
FieldC 10,101
FieldA is
FieldB 44224,4891746759
FieldC 10,101
FieldA cool
FieldB 44558,4891746759
FieldC 10,101
FieldA !