Excel Writer - Variable Sheets

[stevelp]

I’m trying to create a workflow to write to various sheets in an Excel file, based on a user selection of databases to query.

For example, we have 10 database tables that a user might want to query data from. Let’s just call them Info1, Info2, Info3, etc. A user might want Info2, Info4, and Info7 for account 33388. I’d like them to just enter 33388 into a string configuration node, and then check the boxes next to Info2, Info4, and Info7, and it would generate an Excel file with three tabs, named for each of these datasets.

As far as I can tell, I can’t make the number of sheets generated via an Excel writer node dynamic, so here’s how I’ve thought I could do it. I would just create 10 sheet input ports on the Excel Writer, and then I could use variables to specify what query the user wants to select. Then I would need to split my data into multiple output ports. Is it possible to split data into multiple output ports using one node? I think I might just need to make a chain of row splitters, right?

Is this the best way to accomplish what I’m trying to do? Any more elegant suggestions?

[bruno29a]

Hi @stevelp , you most definitely can generate a dynamic number of sheets using the Excel Writer.

Here’s a very quick simple demo:

You can define the sheets however you want (interactive via String configuration, etc), in my case, I defined the sheets here:

Very basic data, which will be written in all of the sheet, but in your case, you can retrieve the data relevant to each sheet:

I also defined the file name and path as variables - again, you can probably do this within the loop depending on the account:

And Excel Writer is configured as follows to use these variables:

The workflow generates an Excel file in the data folder:

And the Excel has the 3 sheets:

Here’s the workflow: Excel Writer - Variable Sheets.knwf (18.0 KB)