Continental XLS Formatter

I can’t seem to get the XLS Control Table Generator working, I always receive the “Warning: The generated table is not yet a valid XLS Formatter Control Table as it contains invalid characters in some tags.”

Has anyone found a solution to this?

Hi there,

I would say you have some character in your table that is invalid. I know I haven’t said much but for example if I have “&” character in a cell I’m getting this warning message. Does it affects your result in any way?

Br,
Ivan

1 Like

No “&” symbol, I wish there was a list of characters that we can’t use or the offending character in the error message to help solve the problem.

Hi,

maybe @arbe can help on this one.

Br,
Ivan

So far both “!” and “&” seem to cause this error. Though these are common in datasets and I’m not sure of a workaround.

Any thoughts on this one @arbe?

Hi @KunalB86 and @ipazin,

The concept of XLS Control Tables is based on tags in the meaning of speaking format identifiers. Usually, you don’t transfer your original data to a control table, but define new tag values based on your original data. However, there is an exception: Some of the XLS formatting nodes accept other things than tags (e.g. background color values, row or column heights, or the all tags option of Border Formatter and Cell Merger). Hence, only a warning is given, not an error.

To your question: The characters invalid in tags are: ;*/|+&?!,
This is mostly due to the idea to prevent users from trying combinations of tags (e.g. a&b) in user interface elements where single tags are expected only.

Without knowing your specific problem (if there is any, since you were initially writing about a warning, not an error), I would suggest to define valid XLS Control Tables for most use cases (Generator in unpivot mode -> Rule Engine to overwrite the Value column with speaking tags -> Pivot back and RowID to get a valid Control Table again). For the special cases mentioned above (e.g. all tags option of Border Formatter), you can most likely just ignore the warning.

Does that help? If not, it would be good to understand what exactly you are trying to achieve, best via an example workflow.

Best regards

Arne

2 Likes

Thank you for your detailed response, I’m not familiar with the concept of “tags” or the difference between a original data table and a control table. I need to research a little more about defining new tag values based on my original data, but your response is very helpful. I’ll try your solution to see if I can get it going in the meantime. Thank you again.

2 Likes

Hi,

tnx for explanation @arbe.

@KunalB86 here is example from KNIME Hub that maybe helps you (and me!) in better understanding :smiley:
https://kni.me/w/ngcuj3N_qZ9u_OqY

Br,
Ivan

3 Likes

Thank you so much for this, this clears up so many questions I had around this extension. I understand now how it works and how to use it. A great help!

2 Likes

Does this extension support writing to specific sheets, or multiple sheets via loops?

Hi there,

not yet if I understand what are you trying to accomplish. See here:

Br,
Ivan

2 Likes

Thanks @ipazin for pointing out this example workflow. By the way: we have an entire section on the example server accessible from within KNIME (99_Community | 05 …) where you find not only node-by-node tutorials but also tutorial use csaes based on real application cases.
Best regards
Arne

3 Likes

Dear @KunalB86,
with the update to version 1.1.0 of the extension, the behaviour regarding error information should have improved. On the console, you now find the reason why it’s not a control table AND the list of invalid characters.
Best regards
Arne

3 Likes

Perfect, thank you for these nodes and letting me know of the updates! Very much appreciated!

1 Like

I was used to “the old way” (pivoting, RowID) of using the XLS nodes. As I need to explain this to a colleague, I love that it’s now even easier.

The good things even get better, thank you @arbe and @ipazin

2 Likes

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