Write KNIME table to Excel as table (proposal)

[izaychik63]

Will it be possible to write table from KNIME to Excel as Excel table. Say I have some references to the table on Excel sheet. After I replace it from KNIME and convert range to the table those references are destroyed. I assumes, if KNIME will write data to Excel as a table it may save existing references.

[Martyna]

Could you share an example what you exactly mean with references? I very often read KNIME tables, manipulate them and then save as an Excel table using the Excel writer node.

Have you checked the XLS Formatter nodes from Continental?
https://www.knime.com/community/continental-nodes-for-knime

[izaychik63]

Thank you for the link, Martyna.
I mean references like this =VLOOKUP(A3,Table1[#All],1,FALSE)

[mlauber71]

I only see these options as of now:

• have the formulas on a different sheet than the one you write to from KNIME. Then the formulas should be OK
• after saving the data add Excel Formulas to a sheet via Python package openpyxl
• populate the whole sheet with Python functions so you could decide which content would be written as data and which as formula. Also formatting is possible

[izaychik63]

Possibly I’m not clear. Table1 is an Excel table object. Every time I save data from KNIME I need to recreate table object. So, why not save data from KNIME as Excel table object?

[mlauber71]

Maybe you could give us a small example of what you want to do/change. It is quite possible that I do not fully grasp what you are pointing out.

[izaychik63]

This is a table from KNIME

I need to do Insert Table in Excel to get this

[Martyna]

Adding a filter and highlighting right?
Or do I miss something?

[izaychik63]

It is Excel table object. Please, see below

Overview of Excel tables

[mlauber71]

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.

After the ‘raw’ KNIME export:

After openpyxl got to work:

(cf. https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html)

kn_example_python_excel_table_object_filter.knwf (43.5 KB)

[izaychik63]

Thank you, Mlauber71. I think it would be nice extension to KNIME be able to do the same without number of steps and Pyton.