Openpyxl does not close Excel workbook in read onl

问题:

I want to be able to read an Excel file in Python, keep the Python script running doing something else after the reading is finished, and be able to edit the Excel file in another process in the meantime. I'm using python 2.7 and openpyxl.

Currently it looks like:

from openpyxl import load_workbook

def get_excel_data():
    OESwb = load_workbook(filename = OESconfigFile, data_only=True, 
                          read_only=True)
    ws = OESwb.get_sheet_by_name('MC01')
    aValue = ws['A1'].value
    return aValue

val = get_excel_data()

After I run the function, the Excel file is still locked for access from other processes (it gives the error "'filename' is currently in use. Try again later") even when I do not want to read it in Python anymore.

How can I close the file from my script? I've tried OESwb.close() but it gives the error "'Workbook' object has no attribute 'close'". I found this post but it doesn't seem to be helping.

EDIT: It appears OESwb.save('filename.xlsx') works, but only if read_only=False. However, it would be ideal to be able to close the file and still be in readonly mode. It appears this is a bug with openpyxl since it should close the file after load_workbook is finished.

回答1:

wb._archive.close()

Works with use_iterator too.

回答2:

I've tried all these solutions for closing an xlsx file in read-only mode and none seem to do the job. I finally ended up using an in-mem file:

with open(xlsx_filename, "rb") as f:
    in_mem_file = io.BytesIO(f.read())

wb = load_workbook(in_mem_file, read_only=True)

Might even load faster and no need to worry about closing anything.

回答3:

For some draconian reason, stackoverflow will allow me to post an answer but I don't have enough 'rep' to comment or vote -- so here we are.

The accepted answer of wb._archive.close() did not work for me. Possibly this is because I am using read-only mode. It may work fine when in 'normal' mode.

bmiller's answer is the only answer that worked for me as well:

with open(xlsx_filename, "rb") as f:
    in_mem_file = io.BytesIO(f.read())

wb = load_workbook(in_mem_file, read_only=True)

And as he said, it is faster when loading with open() versus only using read-only.

My working code based on bmiller's answer:

import openpyxl
import io

xlsx_filename=r'C:/location/of/file.xlsx')
with open(xlsx_filename, "rb") as f:
    in_mem_file = io.BytesIO(f.read())

wb = openpyxl.load_workbook(in_mem_file, read_only=True)

回答4:

I also found this to be a problem, and think it is strange that workbooks have no close method.

The solution I came up with was a context manager which "closes" the file for me so that I don't have put meaningless saves in my code every time I read a spreadsheet.

@contextlib.contextmanager
def load_worksheet_with_close(filename, *args, **kwargs):
    '''
    Open an openpyxl worksheet and automatically close it when finished.
    '''
    wb = openpyxl.load_workbook(filename, *args, **kwargs)
    yield wb
    # Create path in temporary directory and write workbook there to force
    # it to close
    path = os.path.join(tempfile.gettempdir(), os.path.basename(filename))
    wb.save(path)
    os.remove(path)

To use it:

with load_worksheet_with_close('myworkbook.xlsx') as wb:
    # Do things with workbook

回答5:

To close, I believe you need to save the file:

OESwb.save('filename.xlsx')

Hope this helps.

回答6:

Use OESwb._archive.close() This will close the additional ZipFile filehandle which was hold open in 'read_only=True' Mode. Be aware, after close you could not read more Data from OESwb. Be also aware, this ist a workaround and _archive could be removed in a future Version.

回答7:

For your latest information, openpyxl 2.4.4+ provides Workbook.close() method. Below are references.

http://openpyxl.readthedocs.io/en/stable/changes.html?highlight=close#id86
https://bitbucket.org/openpyxl/openpyxl/issues/673

回答8:

You can try:

wb = None

to free the resources, and load it again as soon as you need it again, in the same or other variable.