Formatting specific excel sheets in a loop (multiple files) using continental nodes

Hello @pawanmtm,

to my understanding you should format your Excel file (sheet) inside a loop. So in each iteration between 2 Excel Sheet Appender nodes and second Appender and Merge Variables node you need to use Continental nodes. Wrap them up into Metanode to make it clean :wink:

Regarding XLS Control Table Generator seems it usually receives either Excel data or manually defined tags…

Br,
Ivan

I am not 100% sure if I understand you.

When you say “connect with the main one” do you mean 3rd step (in your image)?

My immediate thought was that you use an Excel Reader node and point it to the result file from the 3rd step, then proceed with XLS Control Table Generator node you already have as a first node.

Or did I get you wrong?

1 Like

Hi Ivan,

Yes, even i had the same thought of formatting the file within loop.

@kowisoft: Yeah, its a nice thought, let me give a try and see how does it shape. Should i be connecting the 4th step result to the 3rd step result again for the format to take place?

Regards,
Pavan

yes, I think so. From my understanding the XLS Formatter nodes extract the base data and each of the coordinates (like row number, col number). Then they give each of these cells that are now uniquely identifiable a tag (a label so to say) and then - using the coordinates and these tags - apply the formatting to the desired output file.

I tried to visualize this (have done this with some of my colleagues as well):

This is just my interpretation (and pls note, I’m in no way affiliated with Conti :wink: just like their nodes)…

1 Like

Hello @pawanmtm & @kowisoft,

but considering workflow is outputting multiple files inside loop formatting process should take place inside it as well. Or start another loop that will go over each file/sheet and apply formatting but first seems more reasonable to me…

Br,
Ivan

Hi @pawanmtm, @ipazin & @kowisoft,

Isn’t it easier to use a separate formatting process for each sheet? Like this, for example?

Best Regards
Andrew

1 Like

Hi @Andrew_Steel,

that is something I had on my mind (inside a loop). And to wrap each formatting process inside a Metanode :wink:

Adding @Continental_KNIME_Extension_Developer in case of any comments/suggestions.

Br,
Ivan

Hi @pawanmtm,

That’s an interesting workflow you are designing!

@Andrew_Steel 's suggestion is very good on how to connect the formatting nodes. The XLS Control Table Generators should be connected exactly like he illustrated. But you cannot have two XLS Formatter (apply) nodes. You rather connect each of the duplicated formatting instructions with a Sheet Selector, then merge them via XLS Format Merger and then apply only once (ensuring with flow variable connection that this is done after the last Sheet Appender). The entire thing can then be embedded in your existing loop structure for multiple files:

Best regards

Arne

2 Likes

Hi All,

I feel this is getting better and better with all your inputs.

Coming back to the requirement, there are 2 things required, format column header and conditional format Exception Aging column. So i have added 3 more nodes based on the below links in the forum (sorry for making it more complicated… :slightly_smiling_face:)

Coloring cells with the Conditional Formatter

Also i would like to explain 1 parameter, i.e., most of the times any 1 sheet either T&E Queries or P2P Queries will have data the other will be blank[only header] (only in some cases both will have data). There is no issue for column header, but for conditional format, if the data is blank, i am not sure how 7, 8 and 9th nodes will handle this.

User Wise Report test.knwf (619.2 KB)

Any suggestions on this.

Regards,
Pavan

Hi Pavan,

Did you already try whether your solution is generic enough to handle missing data? The XLS Background Colorizer node will not fail if the input XLS Control Table doesn’t have any cell with the specified tag. So I don’t see any problem.

Best regards

Arne

Hi Arbe,

Sorry for the late reply. I think you are right, i received only warning.

To my surprise i am facing problem where we have data. The 8th step in the flow may be causing the next node forcing to error as highlighted below. I have checked the configuration which seems to be correct. Is there anything i missing here?





Regards,
Pavan

Hi All,

just want to check if any has got any luck on the above issue.

Regards,
Pavan

Hi All,

Has any got a chance to look into the above issue and suggest…

Regards,
Pavan

Hi @pawanmtm,

Sorry for the late reply, I didn’t step by the forum these days.
What exactly do the warning in node 8 and error in node 9 say?

The screenshot of the table result of node 7 doesn’t look like it is a proper table to be converted to an XLS Control Table with node 8. This way, your rule engine is only affecting cell C1 (1 row, 3rd column). But maybe that is what you are trying to achieve. What about The node 9 configuration? Why not have the “applies to tag” configured to “Red” as defined in the rule engine?

Best regards

Arne

Hi @arbe,

Below is the warning comments for your quick reference.

WARN XLS Control Table Generator 0:87
Cells of a XLS Formatter Control Table shall contain comma-separated lists of tags. Tags are typically user chosen and speaking names, e.g. ‘header’ or ‘totals’. Valid tags do not contain any of the letters ';|+&?!’. This warning can be ignored for some special features, e.g. the ‘applies to all tags’ option of the XLS Border Formatter node.*

WARN XLS Control Table Generator 0:87
Warning: The generated table is not a fully valid XLS Formatter Control Table as it contains invalid characters in tags. See log for details.

Here, i am trying to do the conditional formatting for the column Exception Aging.
In that iteration, it has populated only 1 record, hence you see only 1 row, hope my configuration for node 7 is correct. I could not understand completely the tag selection, hence it was like that.

I have attached the workflow also for your reference to have clear understanding.

User Wise Report test.knwf (619.2 KB)

Thanks in advance for your time.

Regards,
Pavan

Hi Pavan,

The root cause seems to be that you don’t craft a proper XLS control table. The attached workflow shows how that can be done in your case: Use XLS Control Table Generator in unpivoted mode, apply Rule Engine now defining a tag only for the column to analyze and your condition (missing for all other cells), pivot back to a wide layout via re-applying XLS Control Table Generator.

User Wise Report test2.knwf (402.2 KB)

Hope this helps.

Best regards

Arne

Hi Arne,

Yes, it certainly helped in moving to next step in a proper manner. I have applied the configurations and respective nodes as suggested, it is processed.

Now we have 2 tag’s 1 for header and 2nd for column formatting, how do i use these in configuring the XLS Sheet Properties node as it allows only 1 tag or is there any different procedure to be followed, could you please suggest on this.

Regards,
Pavan

Hi Pavan,

I’m not sure what exactly you want to achieve with XLS Sheet Properties. Is it to freeze a window? Then I would suggest you define a 3rd tag via Rule Engine on the cell you want to freeze at. You could label this tag “freeze” and enter that value in the XLS Sheet Properties.

Best regards

Arne

Hi Arbe,

I need to have the formatting settings to be applied to sheet 2 & 3, hence the use of XLS Sheet node, does that make sense? If not could you guide how it can be achieved?

Regards.
Pavan

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.