Can I get your help troubleshooting a file handle issue with the Python package openpyxl version 3.0.7? If the load_workbook 'read_only' parameter is set to False, this does not occur. It only occurs when set to True. If you call these load_workbook and close functions multiple times (of the same file), this will eventually happen. I believe I narrowed down the source code opening the file handle. The problem is that it isn't removed. The exception is thrown when calling shutil.move(source_file, target_file) after opening/closing the same workbook multiple times. I'm going to try and avoid it by opening and closing one time, but I'll need to build a data structure to store everything because the workbook has 23 worksheets. But this seems like an issue regardless. If I set to read_only=False, the performance is terrible! So it takes about an hour+ longer to run.
import openpyxl # openpyxl 3.0.7
# repeat open/close multiple times
wb_source = openpyxl.load_workbook(file_path, read_only=True)
ws_source = wb_source[worksheet_name]
for row in ws_source.rows:
for cell in # cells
# ...
wb_source.close()
shutil.move(file_path, file_path_archive)
Here is the exception:
Traceback (most recent call last):
File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.7_3.7.2544.0_x64__qbz5n2kfra8p0\lib\shutil.py", line 566, in move
os.rename(src, real_dst)
PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'C:\\Python\\...file.xlsx' -> 'C:\\Python\\...file.xlsx'
.\venv\Lib\site-packages\openpyxl\reader\excel.py
# Python stdlib imports
from zipfile import ZipFile, ZIP_DEFLATED, BadZipfile
from sys import exc_info
from io import BytesIO
import os.path
import warnings
# ...
if self.read_only:
ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target, self.shared_strings)
ws.sheet_state = sheet.state
self.wb._sheets.append(ws)
continue
else:
fh = self.archive.open(rel.target)
ws = self.wb.create_sheet(sheet.name)
ws._rels = rels
ws_parser = WorksheetReader(ws, fh, self.shared_strings, self.data_only)
ws_parser.bind_all()
.\venv\Lib\site-packages\openpyxl\packaging\manifest.py
mimetypes = MimeTypes()
C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.7_3.7.2544.0_x64__qbz5n2kfra8p0\Lib\mimetypes.py
class MimeTypes:
def init(files=None):
global suffix_map, types_map, encodings_map, common_types
global inited, _db
inited = True # so that MimeTypes.__init__() doesn't call us again
if files is None or _db is None:
db = MimeTypes()
if _winreg:
db.read_windows_registry()
if files is None:
files = knownfiles
else:
files = knownfiles + list(files)
else:
db = _db
for file in files:
if os.path.isfile(file):
db.read(file) # <-------------------------------------- read file
encodings_map = db.encodings_map
suffix_map = db.suffix_map
types_map = db.types_map[True]
common_types = db.types_map[False]
# Make the DB a global variable now that it is fully initialized
_db = db
def read(self, filename, strict=True):
"""
Read a single mime.types-format file, specified by pathname.
If strict is true, information will be added to
list of standard types, else to the list of non-standard
types.
"""
with open(filename, encoding='utf-8') as fp:
self.readfp(fp, strict)
def readfp(self, fp, strict=True):
"""
Read a single mime.types-format file.
If strict is true, information will be added to
list of standard types, else to the list of non-standard
types.
"""
while 1:
line = fp.readline()
if not line:
break
words = line.split()
for i in range(len(words)):
if words[i][0] == '#':
del words[i:]
break
if not words:
continue
type, suffixes = words[0], words[1:]
for suff in suffixes:
self.add_type(type, '.' + suff, strict)