Update-KFExcelFile

Syntax

Update-KFExcelFile
  -InFile <excel-file>
  -SheetName <string>
  -AsStream
  [-StartAddress <address>]
  [-EndAddress <address>]
  [-EveryCellProcessor <scriptblock>]
  [-Values <list-of-hashtables>] 
  [-ThrowErrors[:$true]]

Update-KFExcelFile
  -InFile <excel-file>
  -SheetName <string>
  -AsBase64
  [-StartAddress <address>]
  [-EndAddress <address>]
  [-EveryCellProcessor <scriptblock>]
  [-Values <list-of-hashtables>] 
  [-ThrowErrors[:$true]]

Update-KFExcelFile
  -InFile <excel-file>
  -SheetName <string>
  -OutFile <path>
  [-StartAddress <address>]
  [-EndAddress <address>]
  [-EveryCellProcessor <scriptblock>]
  [-Values <list-of-hashtables>] 
  [-ThrowErrors[:$true]]

Support

>= kenaflow 4.0.11

Return

System.Collections.Hashtable object or $null

Description

This cmdlet can be used to modify Excel file cell values. Formats cannot be modified.

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.

There are three different output options:

  1. as System.IO.MemoryString by using -AsStream
  2. as Base64 encoded byte array using -AsBase64
  3. or as file on disk using -OutFile <path>

You can only use one output option.

With -StartAddress and -EndAddress the worksheet region can be specified. Default - if not specified - is "Every cell starting with A1", which is identical with -StartAddress "A1" -EndAddress $null

With -EveryCellProcessor can be used to process every cell value by using a scriptblock.

With -Values you can specify a list of hashtable objects with replacement values for specific cells.

If the modified cell is included in -Values and -EveryCellProcessor is specified too the value in -Values used when calling -EveryCellProcessor but the current cell value can be accessed with parameter $cell.CellValue in the scriptblock.

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.

-AsStream

The result is a System.IO.MemoryStream object

-AsBase64

The result is a Base64 encoded byte array that represents the Excel file.

-OutFile <path>

The result is written to a file on disk. The path can be relative to the current workflow folder.

[-StartAddress <address>]

This parameter can be used to specify the worksheet region for the data import. If not specified A1 is used

<address> can be

  • an Excel style cell addres, such as A1 or ZZ55.
  • an Hashtable of this kind: @{Row=4; Col=2} or @{Row="4"; Col="2"} (The Excel address of the cell would be B4.)

[-EndAddress <address>]

This parameter can be used to specify the worksheet region for the data import. If not specified the worksheet is processed completely (starting from -StartAddress). The format <address> is described above.

[-EveryCellProcessor <scriptblock>]

This scriptblock can be used to process and manipulate every cell in the specified range of the worksheet.

This is an example of who the script show look like, especially to get the corret parameters. If the script does not return a value the cell will not be modified! An empty string "" IS a valid result and will replace the cells value.

-EveryCellProcessor { param($cell, $row, $col, $currentVal) if( $row -eq 2 -and $col -eq 3 ) { return "Hello User!" } }

> 

`[-Values <list-of-hashtables>]` 
> This parameter can be used to specify a list of replacement values, referenced by a cell address for each value.
> 
> Such a `<list-of-hashtables>` looks like this:
> ```PowerShell
-Values @(
   @{Cell = "B2"; Value = "kenaflow"}
   @{Cell = @{Row="3"; Col=3}; Value = "is"}
   @{Cell = "D5"; Value = "cool!"}
)

[-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

Update-KFExcelFile -InFile "$PSScriptRoot\inp4.xlsx" -SheetName "Tabelle1" -StartAddress "A2" `
   -Values @(
        @{Cell = "B2"; Value = "kenaflow"}
        @{Cell = @{Row="3"; Col="3"}; Value = "is"}
        @{Cell = "D5"; Value = "cool!"}
   ) -EveryCellProcessor {
    param($cell, $row, $col, $currentVal) 
        if( $row -eq 2 -and $col -eq 3 ) {
            return "Hallo User!"
        }
   } -OutFile "$PSScriptRoot\test5.xlsx"

Input file:

Output:

Discussion