Export data to excel as table

[JinnyLe]

Hi all,
I would want to seek guidance on how to export data to excel file and the data is saved as Excel table.

As i’m going to do a loop to create multiple files, i would like to save data as table in KNIME instead of opening it and convert manually in Excel.

Thank you!

[ipazin]

Hi there @JinnyLe,

I’m not sure I understand and to avoid guessing can you explain what are you trying to do one more time?

Br,
Ivan

[JinnyLe]

hi Ivan,

After i use the Excel reader note at the end of my flow, i manage to export the data into Excel. However, the data is not in “table form” in excel. Usually if i need to convert the data into table form, i need to go open the Excel file -> choose the data range -> Ctrl + T to create Table.

So i’m asking if there is a way to convert data range into Table form in KNIME itself. Not sure if i’m clear enough? Thanks so much!

[mlauber71]

Well yes that is actually possible but only with the help of Python package OpenpyXL. You can find an example here:

Write KNIME table to Excel as table (proposal)

OK I think now I get what you want. Yes it is possible to first save a table as a ‘pure’ excel file and then define a range as a table object “All” which you can later use to refer to it. In my example the no of lines that the object would have is assigned dynamically while the columns are fixed but one might also be able to change that to a dynamic setting. [image] After the ‘raw’ KNIME export: [image] After openpyxl got to work: [image] (cf. https://openpyxl.readthedocs.io/en/stable/…

[JinnyLe]

hi Ivan,
This is great! I tried to run the above flow and this is the message i receive at the Python nodes. Not sure if you know the reason?

“ERROR Python Script (1⇒1) 8:8 Execute failed: No module named ‘openpyxl’
Traceback (most recent call last):
File “”, line 5, in
ModuleNotFoundError: No module named ‘openpyxl’”

[mlauber71]

You would have to install the module openpyxl in your Python environment

pip install openpyxl

or via Anaconda:

https://anaconda.org/anaconda/openpyxl

[JinnyLe]

hi Ivan,
Thanks for the guidance. I still run into the same error message even after i have openyxl installed. Do you think there could be another reason?

[mlauber71]

This comes to mind:

• check if you actually installed it into your py3_knime environment instead of the basic anaconda one. To activate it type
  source activate py3_knime
• if the error message is the same it might indicate there still is a problem
• You could clear the KNIME.log and try again to run your workflow and if it fails you could provide us with the log so we might have a look

Bringing KNIME and Python to work together is not so easy sometimes.

[JinnyLe]

Hi Ivan,
Thanks a ton for being life save. I managed to create the table based on your guidance.

I would want to have another question if there is a node to do conditional formatting for cell value.

For example, if the cell value is above 120 it will be in green color. If below 95 it will be in red. I’m looking at Xls formatting nodes but haven’t found out. Thanks!

I’m thinking of using python with something like- not sure if possible

worksheet.conditional_format(‘D5:E5’, {‘type’: ‘cell’,
‘criteria’: ‘greater than’,
‘value’: 5,
‘format’: red_format})
Regards,
Jinny

[mlauber71]

Glad that I could be of any help. Concerning conditional formatting with KNIME and Excel I see two paths:

• the one you mentioned continue using Python or
• try to use the Continental XLS nodes, especially the " XLS Conditional Formatter Node" that seems to do what you want, although I have not used it myself

https://www.knime.com/community/continental-nodes-for-knime-xls-formatter

As it is often the case with KNIME there are several ways to do it since KNIME is such an open platform and typically you start with something you feel comfortable with and then continue from there. And your gain experience that will be useful for your next project.

And there is the forum (of course) - and the KNIME hub with a lot of useful examples.

Excel format – KNIME Hub

[JinnyLe]

hi Ivan,
I managed to do the conditional color coding

I have last 2 questions regarding this, it’'s great if you could give me some suggestion

This is the data example i get after export to excel.

1. If i want to merge the headers, is Xls cell merger the right node to use?
   

2. Is it possible to extend the column size so that full headers are displayed?
   

   

Thanks so much!
Jinny

[ipazin]

Hi there @JinnyLe,

if @mlauber71 is not Ivan then you are thanking the wrong person

I only replied once on beginning and all the credits go to above mentioned user

Br,
Ivan

[mlauber71]

I do not have a specific expertise about cell mergers. You might have to try what the Python package has to offer or the Continental nodes.

Regarding the autosize of columns the standard Excel sheet appender of KNIME does have such a feature - but it used to take forever and sometimes crush the system so I rarely ever use it. Not sure if they fixed that in recent versions. You might give it a try but save your work first.