Excel writer with named ranges

[david_abekasis]

Excel writer is doing great job in writing data to Excel, either by appending data to end or by selecting sheets to write to.
When sharing Excel worksheet with audience it is sometimes required to provide interaction with Pivot Table, or some existing formulas within the workbook, in order to benefit from the KNIME data injection to Excel, we need the ability to name the range of the data, so later use it to refresh Pivot table, or activate formula using this range.

[mlauber71]

@david_abekasis you could use Python’s openpyxl package to provide Excel with a named area:

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

[david_abekasis]

@Write KNIME table to Excel as table (proposal)

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

Yes, I actually found this option before, and asked it as a possible new feature.
Actually your suggestion of creating Excel table is a wider option, that includes table filter, sorting and designing and assign a name to the range, while named range is only the name for that range of cells.
see whats-the-difference-between-a-table-and-a-named-range-in-excel-2007, but that is already off-topic.

[tobias.koetter]

Hello @david_abekasis ,
have a look at the new Excel Cell Updater node that we have released with KNIME Analytics Platform 4.5. You can specify the cell coordinates you want to write.

Excel Cell Updater

Bye
Tobias

[david_abekasis]

I have “met” this new great tool, but it does not answer my request,
as cell updater requires the “original” named range, to not change,
while we’re talking about writing arbitrary data table, and to name its range accordingly.

[mlauber71]

For what it is worth I brought the example to the new python environment so the use of openpyxl might serve as an example

KNIME and Python's openpyxl to define and name a Data area in Excel – mlauber71

@david_abekasis the Excel functions in KNIME are expanding but for a lot of things you would have to use python or R packages like openpyxl.

Although it does not answer your question you are aware of the Continental Excel nodes I assume:

Continental Nodes for KNIME — XLS Formatter Nodes | KNIME

[david_abekasis]

This is great, thank you for taking the step further, and having examples on the new Python integration, and the required implementation of named ranges or Table as you did.
And yes we are already using continental nodes, Thanks.