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

[pawanmtm]

Dear Knimer’s,

I have multiple excel files and each file has 3 sheets, i would like to format the heading and conditional format 1 column for last 2 sheets using continental nodes. Example as shown below:

I have searched in the forum and found some useful info and was able to build workflow to some extent.

I am somehow missing from which node output should i be connecting to the continental nodes for formatting and how do i write it back to the excel. All this process with in the loop, so that formatting happens along side of file creation. Attached are the workflow and sample data for quick reference.

User Wise Report test.knwf (464.2 KB)
dady@dady.com.xlsx (22.8 KB)

Any thoughts on this?

Regards,
Pavan

[pawanmtm]

Hi,

Just wondering if anyone got a chance to look into the issue.

Regards,
Pavan

[ipazin]

Hello @pawanmtm,

have you checked examples they provide? (Not extension user so can’t suggest much more…)

Continental_KNIME_Extension_Developer/Examples – 02_XLS_Formatter

Br,
Ivan

[pawanmtm]

Hi Ivan,

Yes, based on the examples i was able to build the below flow for header formatting only highlighted in blue However i am missing on how i should get connect with the main one.

Thanks for your time.

Anyone experienced with these extension nodes could suggest on how to proceed, in the mean while let me see if i can get a break through.

Regards,
Pavan

[ipazin]

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

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

Br,
Ivan

[kowisoft]

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?

[pawanmtm]

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

[kowisoft]

@pawanmtm

@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?

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 just like their nodes)…

[ipazin]

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

[Andrew_Steel]

Hi @pawanmtm, @ipazin & @kowisoft,

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

Best Regards
Andrew

[ipazin]

Hi @Andrew_Steel,

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

Adding @Continental_KNIME_Extension_Developer in case of any comments/suggestions.

Br,
Ivan

[arbe]

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

[pawanmtm]

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… )

Coloring cells with the Conditional Formatter

Example Colorizer with rules – ana_ved

07_XLS_ConditionalFormatter – Continental_KNIME_Extension_Developer

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

[arbe]

@pawanmtm

but for conditional format, if the data is blank, i am not sure how 7, 8 and 9th nodes will handle this.

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

[pawanmtm]

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

[pawanmtm]

Hi All,

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

Regards,
Pavan

[pawanmtm]

Hi All,

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

Regards,
Pavan

[arbe]

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

[pawanmtm]

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

[arbe]

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