This pack is for reading and writing to Excel Spreadsheets as a information store.
This can come in handy when you want to grab data for workflows and/or store results from workflows in a format that is easily editable and formatable.
Copy and edit the excel.yaml.example into the /opt/stackstorm/configs directory.
Note : When modifying the configuration in /opt/stackstorm/configs/ please
remember to tell StackStorm to load these new values by running
st2ctl reload --register-configs
If the excel file does not exist, a new spreadsheet will be created with a single sheet named 'Sheet'
The key_column and variable_name_row are normally 1, but can be altered if you have an existing spreadsheet that includes information above and to the left of the data.
The end of keys and variables is signified by an empty row or column, so keys and variables (columns) must be continuous. If you plan on adding variables and/or keys, ensure there is no additional information to the right or below the data.
There are five actions:
- get_sheets
- get_keys_for_columns
- get_keys_for_rows
- get_variables
- set_variables
- delete_row
####get_sheets
Returns an array of all the sheet names in the excel file
####get_keys_for_columns
Returns all the column keys (aka variable names) in the specified sheet in the excel file
####get_keys_for_rows
Returns all the row keys in the specified sheet in the excel file
####get_variables
For a key (row name) in a given Excel Sheet, returns the variables in JSON format. Variable names are the column headers. An optional list of variables names (in array format) will retrieve only those variables that match the array.
Examples:
# Gets all variables for key '123'
st2 run excel.get_variables key='123' sheet='Accounts'
# Gets only the last_name and first_name variables for key '123'
st2 run excel.get_variables key='123' sheet='Accounts' \
variables='[last_name,first_name]'
####set_variables
For a key (row name) in a given Excel Sheet, set the variables provided. If the key does not exist, a new row is created. If the sheet or variables do not exist, a new sheet or column will be created unless "strict" mode is enable.
When calling this action, the excel file will be locked during the process. If another instance has locked the file, then it will retry by default three times waiting a second between attempts. You can adjust these values in the configuration.
Example:
# Sets values for key '123'
st2 run excel.set_variables sheet='Accounts' key='123' \
variables='{"last_name":"Braly","first_name":"Tim"}' \
strict=False
####delete_row
For a key (row name) in a given Excel Sheet, delete the row. If the sheet or key does not exist, no error is reported unless "strict" mode is enabled.
When calling this action, the excel file will be locked during the process. If another instance has locked the file, then it will retry by default three times waiting a second between attempts. You can adjust these values in the configuration.
Example:
# Delete row for key '123'
st2 run excel.delete_row sheet='Accounts' key='123' \
strict=False