Named range and tables in Excel

[zioludo]

Unless I did not see it at the moment Excel Reader and Writer Nodes do not manage named ranges (or even better tables) in Excel. It could be extremely convenient to do so… Power Query has this feature and it does take away all the pain of selecting in which columns/rows to fish the data…

[iCFO]

If you use the write to excel template approach then you can create the named range on the excel side and update it with KNIME. Works great.

Write To Excel Template

I agree that having the option to read and write via named ranges would be convenient. It would be a nice addition.

[iCFO]

You can use this in the mean time as well.

XLS Named Range Reader

[mlauber71]

@zioludo there is no dedicated node but you can use the bundled Python version of KNIME and OpenPyxl to handle tables and named ranges with KNIME. Here are examples:

---

KNIME and Python’s openpyxl to extract named tables (ranges) from Excel file (using the bundled KNIME Python version)

Read from Excel Table

@Experteece actually you can use Python and openpyxl with KNIME to do that. Identify named tables and import them (cf. this thread). [image] You van extract the tables directly or read all the ranges from your ‘named’ table (areas) and then use KNIME to import them. [image]

KNIME and Python's openpyxl to extract named tables (ranges) from Excel file...

---

KNIME and Python’s openpyxl to define and name a Data area in Excel (using the bundled Python version)

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

KNIME and Python's openpyxl to define and name a Data area in Excel (using...

[iCFO]

@mlauber71

Haven’t seen these. Great work!

How does this python approach to naming excel ranges work with existing names? Can it handle subsequent workflow executions, or sending updates to the range values of an existing name without corruption?