Openpyxl does not close Excel workbook in read only mode

Question

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.

Solution 1

wb._archive.close()

Works with use_iterator too.

Solution 2

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)

Solution 3

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.

Solution 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

Solution 5

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

View more solutions