I have a workflow which produces 10 different tables as results.
I wish to write these 10 tables into excel where each table will be a separate sheet.
However, sometimes I do not with to export all 10 of the report. I wish to have a choice on what report I write to excel. maybe I want 1 4 5 6 th report.
Here is the configuration list where we can select based on our choices:
Hi @KNIMEuser23421,
This is possible by using a loop. Collect all your 10 reports in a single table and add an additional column called “Report” with the values from 1 to 10 indicating which report a row belongs to. Then filter the table based on the user selection (e.g. using a Reference Row Filter). Then use a Group Loop Start to start a loop with one iteration for every value in the report column (the one with the values from 1 to 10). In the loop, filter out the report column because you do not want it in your Excel sheet, then pass the table to the Excel Writer and set it to append if the file already exists. You will also need to calculate the sheet name dynamically and pass that value on via flow variable. Let me know if you need help with that. In the end, terminate the loop using a Variable Loop End that is connected to the top-right of the Excel Writer and you are done. The loop appends new sheets one after the other based on your filtered table.
Kind regards,
Alexander
Thanks for your detailed help suggestion. This would work if all 10 tables are similar. However, my 10 reports are very different in both table shapes and type of data. Brutally concatenating them together … will result in a monster table … I am just wondering if there is a loop where loop takes 10 tables as inputs separately and loops thought each of them. I would like to use Python or some other beautiful method to achieve this…
Hi @KNIMEuser23421,
Not sure if there is an elegant solution using Python, but you can also write the individual tables into temporary files (use Create Temp Folder) and then you use a List Files/Folders to get a table of all the files. Then you can filter the table based on the user selection and then you loop through the remaining files using a Table Row to Variable loop, read the file, and then write the Excel file. Does that help?
Kind regards,
Alexander
yes it can work… I will first export the tables into excel into the temp folder and reimport back using loop… Thank you it does solve the problem… but not elegant yet… I was wondering if we can add the export sheet true or false flag within the excel writer node and we can feed Boolean table to control which sheet get exported or not… this should be fairly easy to add…
but really appreciate your suggestions. I will try to see if I can implement this…
