I am having trouble finding a way to use a variable to fill in the cell range for the XLS Control Table from Cell Range node. For each run of the workflow, the cell range for that node could be different. I need the cell range to go for the very first cell to the last in the row, but the length of the row will change for each run. I would like the cell range to change automatically to match the length of the data, so it does not need to be entered manually each time the workflow is run.
I am going from XLS Control Table Generator to cell range. Below is headers from the XLS control table generator.
The extract column header node can be left un-configured, and the column expressions node will need an expression such as the following “column(numberOfColumns()-1)” this outputs the last column and provides for dynamic output if the sheet range changes. I also outputted a new column named ‘Last Column’ of type String.
Extract Table Dimension can be left un-configured and number to string will take Dimensions as the variable to change. Table to Row node will again take Dimensions column to then turn into a variable.
The string manipulation node will then be used to take the inputs of the number of rows and first and last columns. For this I assumed A1 would be the starting point, and inputted the following “join(“A1:”, $Last Column$, $${SDimensions}$$)”. I appended a column called Excel Range.
Finally in Table Row to Variable I chose the column Excel Range to be converted to a variable. Then in XLS Control Table from Cell Range in the Flow Variables tab I chose the Excel Range Flow Variable.
I hope this helps and gives you enough information to maybe refine this or move on from this blocker!
another option you might want to consider is to use unpivoting option in the XLS Control Table Generator node and use then a Rule Engine node to define the different tag values based on the Row and Column number. (Instead of defining the tag value with the XLS Control Table from Cell Range node)
@rsrudd the Excel Range column reads A1:S273, but I need it to just be the first row so only the last column is changing (A1:S1). Additionally I need a separate variable that would be E2:E273 where the last row would vary.
