ExcelTemplateHandler

Description

This actionhandler is available from v7.14 and allows you to create and use Excel document templates with values from process variables that usually come from a smartform.

Variables from VariablesHelper can also be used with this actionhandler.

In order to create document names based on variables, you can include desired variables in the document name in addition to the document content.

It is possible to insert pictures from the process instance into the Word document. Therefore, special variables are needed in the template. An individual picture can also be inserted with the exact name of the picture, for example: $img{bild.jpg}. It is also possible to insert all images attached to the instance: $img{addAllimages}.

The ExcelTemplateHandler can also be installed in the Smartform as a button to generate a template if required. More information is here.

Class

com.dooris.bpm.actionhandler.ExcelTemplateHandler

Parameter

Parameter	Default value	Description	Valid examples
`template` MANDATORY		This parameter is used to set the path to the template, so the ActionHandler can search for it in the resources folder. In order to rename the generated file, the name of the template itself may contain process variables. Given the template, parameter has the following value: `template`=`${SYS.PROCESSINSTANCE_NAME}_report.xlsx`. In the target folder, the file will be searched accordingly. The ExcelTemplateHandler literally searches for the template `${SYS.PROCESSINSTANCE_NAME}_report.xlsx`. If it is unable to find the respective document, the process variables `SYS.PROCESSINSTANCE_NAME` will be resolved in the document name. Only now, the ExcelTemplateHandler is looking for the template `8D-ProblemSolving_report.xlsx` (8D-ProblemSolving is the resolved value of `${SYS.PROCESSINSTANCE_NAME}`). In order to access ‘Resources’ section under the 'Administration' please see: `Roles`	`${processInstanceId}_8D-report.xlsx` The handler supports .xlsx and .xlsm file extensions
`overwriteExisting`	`false`	This parameter is used to decide if the documents should be overwritten if the process run by a loop. If the original template is needed, it should be `false`.	`true`
`documentPrefix`		The documentPrefix can be used so that created documents always have the specified value before the template name.	`mydocumentprefix`
`removeEmptyTableRows`	`false`	When the value is set to `true`, all table rows in the xlsx-template are cleared, if the variable in the first column is missing or empty. This can avoid unsightly blank rows in dynamic content (DOMRepeater).	`true`
`interpretNumbersAsBoolean`	`false`	It can be either true or false. If this value is set to true, process variables (which start with "bool") are changed like below: baa1 → true (In the document: true) boolbaa1 → true (In the document: yes) boolbaa1 → 1 (In the document: yes) bobbb1 → false (In the document: false) boolbbb1 → false (In the document: no) boolbbb1 → 0 (In the document: no)	`true`
`interpretBooleanAsHuman`	`false`	This parameter is used to decide if the boolean variables (true and false) should be replaced with ‘yes and no'. Setting this variable to `true` will change boolean variables as 'yes and no'.	`true`
`target`		This parameter is used to determine the document name in the destination folder. This procedure is useful if the name of the created Word document should be different from the template name. The target parameter can contain process variables. Suppose the user wanted to generate two reports in English and German. The Word template in the resource folder has the following name `${SYS.PROCESSINSTANCE_NAME}_report_${language}`. With the target parameter, the name of the Word document can now be changed to e.g. `target=XY-Report_DE`. Therefore the user can create Excel files with different names from the same Excel template. File extension is not needed within the target parameter.	`target=${processInstanceId}_${processInstanceName}_report`
`locale`	`en`	With this parameter all system supported languages can be given in short form(e.g. `en`, `de`). This leads to, that all system-generated words (e.g. boolean Yes/No) are translated.	`de`

Setup

In order to setup the action hander the below steps are required:

Setup the process
1. Have or upload a simple workflow
2. Import form
Upoad the template in tenant admin resources

After the setup you should be able to see:

Use case 1: Create a simple template

Select the process,
Upload the images in documents (upload at least an image called “im1.png” and another one )
Start a new instance with the below template

The generated file can be found in the documents section together with the images. The generated file should look like:

In the above example we have included:

Variable substitution,
Conditional formatting,
Adding single images by name, and all images
Using excel functions,
Document prefix,
Document target,
Interpreting number as boolean, boolean as human and translation

The variables must be the same as in the Excel template.

Calling the execution of ExcelTemplateHandler from a REST api call endpoint

The ExcelTemplateHandler can be called from rest api using the below endpoint

POST: [your server url here]/tim/api/templates

{
    // Mandatory params
    "processInstanceId": [YOUR PI ID HERE],
    "template": [YOUR TEMPLATE PATH HERE],

    //Optional params
    "target": "",
    "documentPrefix": "",
    "locale": "",
    "overwriteExisting": false,
    "interpretNumbersAsBoolean": false,
    "interpretBooleanAsHuman": false,
    "removeEmptyTableRows": false,
    "open": false
}

Integrating the ExcelTemplateHandler into a HTML-Smartform

The ExcelTemplateHandler can be integrated into a HTML-Smartform as a link, a button, or an image so that the participants can create a template at any time. For this, the following code must be integrated into the smartform:

<button type="button" onbuttonclick="generateFileFromTemplate([{handler:'ExcelTemplateHandler',template:'temp.xlsx',attach:false,open:true,documentPrefix:'Prefix'}])">Generate Excel template</button>

The "onbuttonclick" attribute defines which functions/parameters the handler should execute. See Parameters sections above.

Important when using HTML-Smartforms

Normally written text will be inherited, whereas variables will be overwritten if marked as ${Variable-ID}. To overwrite variables with content from the HTML form, you must use the variable's ID, such as ${Variable-ID}, within the text form fields. An exception to this rule applies to radio buttons, where you should use the variable name, like ${Variable-Name}, instead of the variable ID.