xls formatting 'logic'

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

What am I doing wrong…?

Thanks a lot and best regards
Martin

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.

4 Likes

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.

Thanks a lot in advance.

0_End of workflow

1_Config_ControlTableGenerator 2_Config_CellFormatter 3_Config_xlsFormatter_apply 3_Config_xlsFormatter_apply_InputFile 3_Config_xlsFormatter_apply_OutputFile

Hi @tsv_ml_f21,

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

image

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

image

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!

Hopefully that will then resolve your problem.

Hello
Thank you for all the explanations.
It has worked for column F :grinning:.

image

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

Thanks and best regards

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.

1 Like

Good morning takbb
Thanks a lot for all the helpful explanations! It is working :grinning:.
Have a nice day.
Best regards

2 Likes

Hello again
I have thought I have understood the ‘procedure’, but with another workflow ‘the formatting process’ does not work.

The table in “Column Filter” contains 52 columns, in some of the columns there are ‘missing values’, but no entries is correct…

What ‘invalid characters in tags’?

‘Where’ can I see “log for details”?

Configuration of:

image

Error in:

image

Configuration of:

image

Thank you very much for your help.

Hi @tsv_ml_f21 , this is a strange one!

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.

Hopefully then you’ll be good to go.

Example of the issue…

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:

So I think the resolution for this issue is to add a similar node to your workflow. :slight_smile:

1 Like

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

Hi it’s Excel Formatting – KNIME Hub

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

I have download the hub workflow and attached it here

Excel Formatting demo workflow from hub.knwf (76.1 KB)

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.

1 Like

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 ? :wink:

What version are you on?

1 Like

Hello @tsv_ml_f21,

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?

From a quick look seems you can replace Column Expressions with String Manipulation (Multi Column) node.

Br,
Ivan

1 Like