With the new Nintex Document Generation action in Nintex for Office 365, it is now possible to create an Excel Report from Sharepoint List items. With the output options of either its original format (i.e. Excel file in this case) or PDF in the defined destination of Sharepoint folder, you can then automate the generated report document for approval as usual in Nintex Workflow.
In the following diagram, I have my Expense home page consists of Expense Items (i.e. Customer List) and Create Expense Report query form. Create Expense Report Query form is a simple form one can use to specify report filter criterial (i.e. Title, Start Date and End Date, etc. of the items to be included in the excel report to be generated).
The Create Expense Report query form is nothing but just another custom list to trigger a Document Generation workflow (i.e. "Process Expense Report - Demo" workflow in this case) to export the filtered list items to an Excel document with the newly available Document Generation workflow action. Here is a screen capture of the created Expense Report Query custom list. The "Process Expense Report - Demo" workflow will be triggered when new item (i.e. "KK Expense Claim for Dec" item in this example) being created.
Once the "Process Expense Report - Demo" workflow is being executed, it will generate an Excel document and output the file to the defined destination folder (i.e. Expense Reports in my scenario) as shown in the diagram below
The output of the Excel file (i.e. "KK Expense Claim for Dec - (Demo)" is shown below.
The "Process Expense Report - Demo" workflow is basically a 7 steps workflow made up of 13 workflow actions as shown in the workflow diagram below
| Steps | Workflow Actions | What it does... |
|---|---|---|
| 1 | Action ① | Query List (i.e.Expense Items custom list) returned a list of items stored in list items collection |
| 2 | Action ② | Set the Variable idx to 0 |
| 3 | Action ③ | Loop through the list item collection to.. |
| 4 | Action ④-⑨ | Create dictionary pair of key (i.e. Field Name), and Value (i.e. Field Value). The dictionary has 7 pairs of 7 fields of expense list item to be built. |
| 5 | Action ⑩ | Build a dictionary (i.e. dicItem dictionary) of the Expense list item (i.e. itemNo, itemExpType, itemDate, itemCurrency, itemAmount, itemAmountInSG). |
| 6 | Action ⑪ | Add the list item dictionary (i.e. dicItem) to a new Expense collection (i.e. collExpenses) |
| 7 | Action ⑫ | With the Expense Collection (i.e. collExpenses) as input, the Doc Gen action will used the "Expense Form - DEMO.xlsx" document template to generate "KK Expense Claim - Dec (Demo).xlsx" excel documents |
Here is how I visualized the outcome of the built Collection "collExpenses" done by "Action 11" after completing the "Action 3" for-each loop.
Document Generation Action uses the "Expense Form - DEMO.xlsx" as template, and generate the Excel document which was then placed in the "Expense Reports" SharePoint Document Library. Diagram below highlights how the "Expense Form - Demo.xlsx" template is being "Tagged" in my example for Document Generation action to insert values into it.
The configuration of the Document Generation should be straight forward, here is the configuration of my example
The key challenges for my first time testing getting the Document Generation to work for creating the "Row Replication", would be surrounding the building of "collExpenses" collection and Tagging the excel template for the repeating row purposes. Thanks to @Dan Stoll's hint on how to create the collection to be used as variable by the Document Generation action (i.e. Dan's blog on that could be found here -> It's here... Nintex Document Generation . With this blog, I am hoping it will help those who encountered the same challenges as me.
