Dear Community
I started with KNIME this May. I would like to format the excel saved with “excel writer”, but I am not able to apply the concept since there is no change realized in the excel I intend to format.
Can anyone help me on this?
Coding/situation:
A sorter node contains the final data table before writing the data with “Excel Writer”.
From this sorter node I connect an “XLS Control Table Generator”, in the configuration I select ‘write comun header to first row’ and apply/OK.
From this I connect an “XLS Cell formatter” and input in the tag selection the tag “WE Total”; for data type and format I would like to have 1000 separator without decimals => apply/OK.
From this I connect an “XLS Formatter (apply)” and do a ‘red connection’ to “Excel Writer”.
Running all the nodes I can not realize any change in the column “WE Total”…
Hi @tsv_ml_f21, and welcome to the forum. It’s difficult to know where exactly you are going wrong although it sounds from your description like you are writing using the Excel Writer last, which actually isn’t how it works. What you need to do is write your excel file first and then the other nodes apply the formatting to it.
Have a look at the following demo workflow and hopefully this will become clearer, but if still having problems if you can post a small sample workflow, with some dummy data we can give you further pointers to assist you.
Hello
Thanks a lot for your quick response.
Referring to the demo workflow I think my workflow has already been identical, but after some other tries today it has not written 1000 separator to the ‘test’ column F.
By the way I do not exactly understand what it means that the excel should be written before formatting since I can only ‘draw an arrow’ from ‘sorter’ to ‘excel writer’ and ‘at the same time’ to ‘xls control table generator’… In other words: I can not control that excel writer is done first and after that the formatting process starts…
I have attached the end of the process and the details respectively:
… end of workflow
Config Excel writer with file name
Config ControlTableGenerator
Output-table CTG
Config CellFormatter
Output CellFormatter
Config xlsFormatter apply
Config xlsFormatter apply InputFile
Config xlsFormatter apply OutputFile
Hope that you can give me the crucial issue to make the formatting run.
In KNIME, the order of processing is defined, as you say by the connecting lines, but a node may only begin processing once EVERY node connected to it either via a black data port or a red variable flow port has completed.
So in your diagram, if you have the variable flow connection between the Excel Writer and the XLS Formatter (as you have in the picture) then this WILL ensure that the XLS Formatter cannot execute until the Excel Writer has completed its operation. If that red connection were not present, then there would be no guarantee about the sequence of operations. I hope that makes sense. So variable flows don’t just pass variables, they also help to orchestrate the sequence in which nodes execute.
(It may be that you already had that correct, but I wasn’t sure from your original description of the nodes, without being able to see the workflow)
Ok, I’ve looked at the example workflow referenced earlier and compared with yours, and I think you are missing the XLS Control Table from Cell Range node, which is where you need to define the “tag” referenced by the XLS Cell Formatter node so that it knows the range over which the formatting should occur
From testing this out, you cannot specify entire columns as ranges (i.e. you cannot just say “F:F”), so you have to give it an actual range that you are happy covers your output
I suspect this formatting has to be applied to every individual cell, so the larger the range, the slower it will be, so make it big enough to cover what you are likely to require, but don’t overdo it!
Hello
Thank you for all the explanations.
It has worked for column F .
So do I need the node ‘xls control table from cell range’ for EVERY column E to J = 6x the node ‘xls control table from cell range’ or is it possible to select the range from F2:J500…?
Hi @tsv_ml_f21, you should be able to do it by specifying a single tag with a range like you have suggested. The limitation is that you have to give an actual cell range so you can use something like F2:J500 but you can’t just say F:J.
Incidentally, the name that you give your tag is independent of your actual column headings in the spreadsheet, so you can call your tag anything you like. It is just used to identify ranges used by the formatter nodes, so if you have different formats to be applied in different places, you would create a series of xls control tables from cell range nodes which each create a tag naming a different cell range, and then add formatter nodes describing the formats for each of those tags.
Firstly, the “log”, I think refers to the Console window (via View - Console) menu. That’s the only place I’m seeing messages for these nodes unless its writing to a file somewhere. The messages aren’t always obvious (to me at least) about what they mean.
For your specific issue, I’d first written here to say that I had also managed to hit the problem, and I saw the following error messages in the log:
WARN XLS Control Table Generator 0:108 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:108 Warning: The generated table is not a fully valid XLS Formatter Control Table as it contains invalid characters in tags. See log for details.
INFO XLS Control Table Generator 0:108 XLS Control Table Generator 0:108 End execute (0 secs)
INFO XLS Control Table from Cell Range 0:136 XLS Control Table from Cell Range 0:136 End execute (0 secs)
WARN XLS Formatter (apply) 0:109 The output file already exists and will be overwritten.
WARN XLS Font Formatter 0:140 Xls Formatting Control Table check failed at rowID "10", column "D": Comma-separated tag list contains invalid character(s), i.e. ;*|+&?!
ERROR XLS Font Formatter 0:140 Execute failed: The provided input table is not a valid XLS control table. See log for details.
Originally I hadn’t been able to work out what was going on because the error appeared to “clear” and I couldn’t make it return. It turns out that I had during my investigation accidentally removed a column that was causing the issue. The column contained certain characters that the XLS formatter doesn’t like
Basically, the XLS formatting nodes don’t appear to like it when there are any cells presented to them that contain any of the following characters:
;*|+&?!
When you pass your table to the XLS Control Table Generator, it must not have those values. That’s fine though because you don’t need to pass your actual data to these nodes, you simply need to pass “place-holder” values to it.
So prior to the call to the XLS Control Table Generator, put in a node that replaces any (or all) of your data, but specifically data that might cause problems, with some other value.
In the “Excel Formatting” demo workflow that I linked to in my first reply, the “Column Expressions” node (indicated below) replaces the actual data with “placeholder” data. I hadn’t really noted why that was required before.
If you open that workflow, and remove that node, you will find that you then see the error that you’ve been getting because the fourth column “Molecule” contains data that the XLS nodes can’t handle:
Hello takbb
Thanks a lot for your efforts!
I will check this additional node asap, it sounds reasonable.
What is the link to this example workflow?
Best regards
Martin
Hello takbb
I am so excited - but can’t drag and drop the workflow since I have an older version installed.
How can I look for Upgrades in KNIME? I am on my employers’ computer and do not want to have conflicts with compliance issues…
Thanks and best regards, Martin
Sorry, it is me again:
Or would it help if you would screenshot the ‘important’ codings in the column expressions node since I am able to apply this node in general.
Thanks and best regards
The Column Expressions node is simply replacing the values in each column with a literal. I may be wrong, but I don’t think it matters what the actual value is.
I’ve not worked with previous versions of KNIME so unfortunately don’t know the answer to this part. It’s probably a question for one of the KNIME team… @ipazin ?
if you can not update KNIME then why look for upgrades or I’m missing something? Anyways you can get latest version here or depending on your current version you can update from within KNIME (File --> Update KNIME...). Or are you saying that Drag&Drop functionality from Hub is not working for you?
Hello takbb
I have to say sorry, I have been busy with my ‘normal’ tasks… I have tried it after your last input and finally I have succeded .
So thanks a lot for all your valuable inputs, ‘case’ can be ‘closed’.
Best regards
.
.