Failure importing openpyxl in Spyder 5.1.5

Question

So I am trying to open a.xlsc file in Spyder using pandas. After importing pandas as pd, when using pd.read_excel() command in the console, it showed the error:

ImportError: Missing optional dependency 'openpyxl'. Use pip or.....

Then I installed the module with:

pip install openpyxl

successfully in CMD, but again Spyder shows the same error. I tried to restart the kernel, close and reopen Spyder, and even restarted my machine to no avail. Openpyxl is listed in both help ("modules") in python shell as well as in pip list in CMD.

When I tried to import the openpyxl itself in Spyder (import openpyxl)it says "Module not found error: No module named "openpyxl"." I have only python 3 (3.9) not both 2 & 3 and Spyder is the only IDE I use with python. I apologize if this question sounds stupid, I'm fairly new to Python. I would appreciate any help regarding this. Thank you.

Answer 1

Spyder has its own Python interpreter 3.7.9 so it loads only the modules installed for that Python.

Since I had a separate Python 3.9.7 installation, whenever I used pip via CMD it installed all the modules for the 3.9.7 Python, that's why my Python terminal & CMD were able to list openpyxl but Spyder said it isn't installed.

After ransacking through the internet I switched the Spyder interpreter to the 3.9.7 stand-alone installation by Tools > Preferences > Python interpreter > Use the following interpreter > navigate through your directory and select the stand-alone python 3.7.9 .exe. Afterwards, Spyder said "Your Python environment or installation doesn't have the spyder?kernels module or the right version of it installed. Use pip install spyder-kernels==2.1. to install..."*. So back to CMD, I installed the spyder-kernels and now everything's fine.

Spyder IPython console shows Python 3.9.7 (Used to be 3.7.9, Spyder's own interpreter). Now I'm able to import openpyxl without any issues.

Answer 2

I had the same issue as Anoban, but the answer provided by Anoban didn't work for me. I was able to resolve it in two ways

1. Changing my Python environment

This is the better, quicker answer IMO. My Spyder environment was somehow using a custom Python environment as its default (see picture).

Once I changed the Python environment (Tools > Preferences> Python interpreter) from "Default" to the following path (it should have been the default Python interpreter in the first place IMO): "C:\Users\username\miniconda3\python.exe"

...it worked (see image below to what it should look like in the IDE).

I still had to do an install of spyder-kernels=2.3 (see the next point in my post if you have trouble with that)

2. Creating a separate Python environment

I got this way to work before I figured out the method described in #1 above, so I am posting it too, since it may help someone. I used the help provided on the Spyder website, followed the directions provided on their site exactly, and it worked for me.

FYI, my installation is slightly different than Anoban's - I didn't use pip. I installed miniconda and a stand-alone version of Spyder on my Windows machine. I used the Anaconda prompt as the terminal window, not the Windows Command Prompt.

Hope this helps someone.

update: when I changed the Python interpreter to the new environment, the Python console gave me the following error

The Python environment or installation whose interpreter is located at
    C:\Users\rebecca\miniconda3\python.exe
doesn't have the spyder?kernels module or the right version of it installed (>= 2.3.0 and < 2.4.0). Without this module is not possible for Spyder to create a console for you.

You can install it by activating your environment (if necessary) and then running in a system terminal:
    conda install spyder?kernels=2.3

But it couldn't find the correct channels for installation so I needed to use

conda -c conda-forge spyder-kernels=2.3

then it worked.

Answer 3

@rpinto73

I know it's tad late. I'm wondering how my answer differs from yours lol.
We faced the same issue and came up with the same solutions. The difference is you set the Python interpreter in a conda environment as default in Spyder whereas I set my standalone Python installation as default. Thus we both assigned an external Python interpreter to be used inside Spyder. Since the external interpreters we assigned (a global Python interpreter in my case (that's why I used pip) and a Python interpreter inside a conda virtual environment in your case (so you had to use conda, you could use pip inside a conda virtual environment as well but not a good practice since it could break dependencies) didn't have the spyder-kernels module we had to install it manually.

The major issue with Spyder is it is an IDE WRITTEN in Python. Thus it needs a Python interpreter to load & function; which is the reason for it being painfully slow during loading. Spyder packs some mundane data science libraries like pandas, numpy, scipy with itself, which you make use of when using Spyder's default Python interpreter. However Spyder does not allow users to manually install packages for its default interpreter (as far as I know). Which makes sense since any conflicts in dependencies introduced by users manually installing/upgrading packages might break the functionality of Spyder itself, making it dysfunctional. Thus the best solution is to leave the Spyder's interpreter to Spyder itself and use a custom Python interpreter where we can install and upgrade modules as we please!.

And a free advice, it's okay to use Spyder when you are starting out with Python as beginners, but it is useless when you have to do heavy computations. You will mostly find yourself facing memory errors. It is lightweight IDE best for learners & beginners. It's best to switch to a more general yet capable development environments like Visual Studio Code, Jupyter Lab.. or a full-fledged Python IDE like PyCharm (there is a free community edition) if you want the in-IDE variables pane & plots pane. But be warned PyCharm (generally any IDE from JetBrains) is notorious for having massive memory footprints.