Excel Table Writer Node & Openpyxl

[qdmt]

I am trying to use the Excel Table Writer node, which I understand requires the openpyxl module.

In KNIME, I have confirmed selection of my environment “env”.

In Anaconda Shell Prompt, I have confirmed that “openpyxl” is correctly installed in that same environment.

Yet, when I run the node, the node errors out and says

ModuleNotFoundError: No module named 'openpyxl'

I have an excel model that uses INDEX/MATCH & XLOOKUP extensively to reference a sheet that contains the data that KNIME writes into. This data is not fixed and expected to grow.

If I don’t use the Excel Table Writer node, I am constrained to using entire A:A column references (as opposed to strictly the range or table object I need).

Excel Table Writer node seems like it would be a great solution otherwise.

[ScottF]

Hi @qdmt -

To me this seems like some sort of misconfiguration in the KNIME preferences about exactly which environment is being used. Are you certain you’re pointing to the Conda envrionment that you’re updating via the shell prompt, as opposed to KNIME’s bundled one?

Sorry if this question seems silly, just trying to narrow down what’s happening. I’ll also tag @tommy since it’s his component we’re discussing.

[mlauber71]

@qdmt openpyxl should be part of the bundled Python version in KNIME.

[qdmt]

@ScottF @mlauber71

I can confirm I’m pointing to Conda under KNIME > Python:

Under KNIME > Python Legacy, I can also confirm I’m using the matching environment for Python 3:

I can further confirm that openpyxl is visible to the Python Script node using the following script:

import sys
sys.path.append("path_to_conda_environment/lib/pythonX.X/site-packages")
import openpyxl
print(openpyxl.__version__)

[ScottF]

Two things to try:

1. Can you verify that openpyxl is present when imported in the Python Script (legacy) node, as opposed to the standard Python Script node shown in your screenshot?

2. Since as @mlauber71 pointed out, openpxyl is present in the bundled Python environment, what happens when you switch over to that in the KNIME settings?

[mlauber71]

@qdmt you might have to see how Python is configured in the component. You could also check this example that should just use the bundled 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...

[tommy]

hi @qdmt , @ScottF

I just checked the functionality of my component after establishing a new Python3 environment via KNIME preferences.

It generated the same error as stated in the original post here.
Thus, I installed the package via “pip install openpyxl” in a conda prompt.

That did the trick.

Hope that helps, greets
tommy

[qdmt]

@ScottF

1. Can you verify that openpyxl is present when imported in the Python Script (legacy) node, as opposed to the standard Python Script node shown in your screenshot?
2. Since as @mlauber71 pointed out, openpxyl is present in the bundled Python environment, what happens when you switch over to that in the KNIME settings?

With 1, yes, can confirm it is present with 3.1.2.
With 2., I had the same error either way.

@mlauber71
Interesting example - I was using the Excel Table Writer node, which uses openpyxl. That might be the way to go so I can impart a bit more control on the config.

@tommy pip install openpyxl was one of the first things I did.

I might throw in the towel on the Excel Table Writer node for now. My dataset seems to have outgrown the node’s ability to efficiently process the info. At only ~170k rows, it takes a bit too much time (10min+) to write the table, only to run into the openpyxl error.

I’ll revisit the issue later, but perhaps will help point others to @mlauber71’s reference example in the meantime (unless others disagree). Cheers

[mlauber71]

@tommy from what I see your component uses a legacy Python node and also it does not seem to have dynamic settings of the rows and columns (if I am not mistaken). Maybe you can update the component and use the bundled Python environment and also create a dynamic setting for the rows and columns.

[tommy]

hi @mlauber71

thanks for providing the hints.
I have to admit that I left this component untouched for some time.
Now, it has been updated and I have replaced the legacy Python node by a Python script and updated the code.
The number of rows and columns is calculated based on the input table, so the whole table get’s converted. In case there are some user requirements to filter some information, the user might use a row/column filter node in advance.
What do you mean by “dynamic setting”?

My test with the new setup was successful. Hopefully, it’s stable.

thanks, tommy

[ScottF]

@qdmt does the updated component work for you? If so we could mark tommy’s latest post as the solution.

[qdmt]

Will check report/update back asap

[qdmt]

@ScottF @tommy @mlauber71

Reporting back - the update worked. Thanks @tommy for the update.
It also works despite running 4.7.

Cheers