BUG: read_excel blows the memory when using openpyxl engine

[liyucheng09]

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

# Your code here
Python 3.9.1 | packaged by conda-forge | (default, Dec  9 2020, 01:07:47) 
[Clang 11.0.0 ] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import pandas as pd
>>> pd.__version__
'1.2.0'
>>> df=pd.read_excel('full_data.xlsx')

Problem description

I am not quite sure how to describe the bug, the code just got stuck when I run pd.read_excel('full_data.xlsx'). I found this line cost a significant amount of memories (almost 14G but the .xlsx file is just 9MB).

I speculate it is result from read_excel now leverage openpyxl as default engine in python3.9. Loading this file in python3.8 works fine.

>>> from openpyxl import load_workbook
>>> wb=load_workbook('full_data.xlsx')
>>> df=pd.DataFrame(wb['Sheet1'].values)

The above codes also leads to the same issue.

[nmay231]

@liyucheng09 Please provide the output of pd.show_versions().

It shows other helpful information besides just the version of pandas 👍

[liyucheng09]

@nmay231 thanks for your reply. the outputs of show_versions() are as follows.

INSTALLED VERSIONS
------------------
commit           : 3e89b4c4b1580aa890023fc550774e63d499da25
python           : 3.9.1.final.0
python-bits      : 64
OS               : Darwin
OS-release       : 20.2.0
Version          : Darwin Kernel Version 20.2.0: Wed Dec  2 20:40:21 PST 2020; root:xnu-7195.60.75~1/RELEASE_ARM64_T8101
machine          : arm64
processor        : arm
byteorder        : little
LC_ALL           : None
LANG             : zh_HK.UTF-8
LOCALE           : zh_HK.UTF-8

pandas           : 1.2.0
numpy            : 1.20.1
pytz             : 2020.5
dateutil         : 2.8.1
pip              : 20.3.3
setuptools       : 49.6.0.post20201009
Cython           : None
pytest           : None
hypothesis       : None
sphinx           : None
blosc            : None
feather          : None
xlsxwriter       : None
lxml.etree       : None
html5lib         : None
pymysql          : None
psycopg2         : None
jinja2           : 2.11.3
IPython          : 7.21.0
pandas_datareader: None
bs4              : None
bottleneck       : None
fsspec           : 0.8.7
fastparquet      : None
gcsfs            : None
matplotlib       : None
numexpr          : None
odfpy            : None
openpyxl         : 3.0.7
pandas_gbq       : None
pyarrow          : 3.0.0
pyxlsb           : None
s3fs             : None
scipy            : None
sqlalchemy       : None
tables           : None
tabulate         : None
xarray           : None
xlrd             : None
xlwt             : None
numba            : None

[ahawryluk]

I wonder if this is has been fixed by #39547. Spreadsheets with a blank cell on the last row or column of the worksheet take vastly longer to load in 1.2.x than in 1.1.x due to the trailing rows of NaNs.

[liyucheng09]

@ahawryluk Thanks for the reply. After upgrading pandas, it works well, so I will close it.