Openpyxl does not close Excel workbook in read only mode

wordsforthewise picture wordsforthewise · Jul 14, 2015 · Viewed 32.7k times · Source

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.

Answer

Patrick Conwell picture Patrick Conwell · Jul 29, 2017

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)