applies to version: 8.2.x; author: Marcin Wiktor
New BPS Actions concerning .docx and .xlsx files in BPS 8.2
BPS already offered Actions that generate .docx documents (both global and “on step”) in previous versions.
Version 8.2 introduces several upgrades to this Action, as well as a totally new set of Actions for generating and reading data from Excel files. These new additions will be broken down in the following article.
1. Generating .docx files – changes in 8.2
Apart from some ergonomic changes, there is a new mode setting, and a new way for template selection – from attachment.
New Mode: Update existing file – Instead of creating a new attachment for the Workflow element, this BPS Action will now update an existing attachment.
The file (or files) we want to update can be defined by any of the three methods found under “Attachments to be processed” (Category, Regular expression or SQL query options).
The other novelty is templates stored as attachment files. The ‘Template’ drop-down menu allows you to select whether to use an attachment or an element from the document library as the template.
2. Generating .xlsx files – BPS Action
Generate an Excel file BPS Action may be declared for the following Action types:
- On browser opening,
- On path,
- On timeout,
- On exit,
- as a Menu button.
Furthermore, it may be defined as a global (Workflow-wide) Action:
- On browser opening,
- as a Menu button.
The configuration screen for this Excel Action is similar in many aspects to that of ”Generate a Word file”.
 – Settings
Save mode – Here we define if the generated .xlsx file will be added as a new attachment, or if it will overwrite existing attachment(s) that match the settings of .
File selection – Here we define the source of the template used for file generation.
 – Template file from SharePoint library
If “SharePoint library” was selected in the “File selection” menu, this field becomes active, and must link directly to the desired template.
 – Attachments to be processed
If ”Update of existing attachment” was selected as the mode, or ”Existing attachment” as file selection, this section will become active. Here we define which files will be modified/location of the template.
 – New attachment configuration
If ”New attachment” was selected as the ”Save mode” this section will become active.
Both the file name and description may be generated dynamically with the use of tags located in the tree on the right (Double click or drag’n’drop).
We can also define the category to which the newly generated files will be assigned.
It should be noted that for this action, if a second file is generated with the same it will not overwrite the old file. Both files will be accessible form the attachments menu.
 – Configuration of form field mapping
Similarly to .docx, we must create a template for generating files. Unlike Word files, dynamic tags are not used. Instead it is necessary to define which Form fields will load their values into individual cells.
Form field – Name of the form field which will be assigned to a specific cell(s) on an Excel spreadsheet.
Excel Address – Location of the cell which will obtain a value from the specified form field. They must be entered in the following format: <Worksheet name>!<Cell address or table name>
To correctly address cell A1, enter:
To correctly address a table named Table1, enter:
To better demonstrate the functionality provided by this BPS Action, we present the following examples:
3. Example 1 – Creating a new attachment
For demonstrative purposes, we will create a straightforward Workflow registering information about newly hired employees. We create a ”Generate an Excel file” action under ”On step” which will generate an .xlsx file containing data from the Form, and add it as an attachment to the Workflow element.
Next we must create a template, on which generated files will be based on.
Cells which will carry specific data should be properly formatted so that they display their contents correctly. Cell formatting is relevant, especially when you consider Form fields like “Date and time” and ”Floating-point number” fields with various amounts of decimal places. If we leave the default cell format settings, they may not display their values correctly, or in a bizarre and impractical way.
In our example, we must change the format of the “Employment date:” to “Date”. Furthermore, for aesthetic purposes we will enable the “Wrap text” option for the “Comment” cell since it is a multi-row text field.
The third and final step is to correctly complete the remaining configuration for the “Generate an Excel file” BPS Action:
Traversing through the Workflow path should trigger the Action and give us the desired end effect – a newly created .xlxs attachment.
4. Example 2 – Update of existing attachment and Item lists.
In this second example, we will design a simple Inventory Workflow, in which users may request office supplies by adding and filling out rows of an Items list.
On step exit, an .xlsx file is generated and saved in the “Requirement” category.
Next, the Workflow element passes to the supervisor, who will be able to edit the Quantity and Price columns, they will also have access to an additional Comment column. After the supervisor completes his step the “Generate an Excel file” Action is launched once more, updating the contents of the previous attachment.
To properly generate an Items list, we have to include a table in our template. Currently it is impossible to map individual columns – they will all be mapped by default.
We leave the default table name as “Table1”.
Remember to change the format of the “Registration date” cell to “Date”. For added clarity, we can change the format of “Price” to “Currency”, and for “Superior’s comment” we can activate “Text wrap” to collapse the cell neatly.
After loading in the template, we may proceed to set the other configuration options.
We don’t want a new attachment to be created when the supervisor changes data in the Workflow element, an update to the existing document is sufficient. To this end, we must configure the “Generate an Excel file” Action on the “Superior approval” Step to use an existing attachment as a template (the one that was generated on the previous step) by setting: File selection: Existing attachment. This will update values in the existing Excel Spreadsheet and then save the changes by overwriting the attachment.
Form fields which are not going to be changed (the supervisor cannot edit them anyway) do not have to be mapped.
After completing all steps of the workflow, we should have exactly one .xlsx attachment that looks something like this:
5. Reading data from an Excel file
BPS 8.2 also introduces the complementary action to .xlsx generation – reading/loading data from Excel Spreadsheets.
The “Read…” Action may be assigned the same types as the “Generate…” one (On browser opening, On path, On timeout, On exit, Menu button).
Action configuration is comparable, albeit slightly simpler since it is not necessary to define save and template settings.
When implementing this functionality into a Workflow, keep in mind the each time this action is executed, Form fields and Items lists defined in the “Attributes mapping configuration” section are overwritten.