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

  1. as System.IO.Stream
  2. as Base64 encoded byte array
  3. 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:

  1. -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.
  2. -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.

  1. The file can be specified as Base64 encoded string or
  2. as System.IO.Sream object or
  3. 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 or ZZ55.
  • an Hashtable of this kind: @{Row="4"; Col="2"} (The Excel address of the cell would be B4.)

[-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 position A1 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                         !                                                                                                                                                                                                                                                                            

Discussion