Openpyxl does not close Excel workbook in read-only mode

Question

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

Currently it looks like this:

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 starting the function, the Excel file is still locked to access other processes (it gives the error "file name" is currently being used. Try again later "), even if I do not want to read it in Python anymore.

How to close a file from my script? I tried OESwb.close (), but it gives the error “The object“ Workbook ”does not have the attribute“ close. ”I found this post , but it does not seem to help.

EDIT: It seems 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. This seems to be a bug with openpyxl, as it should close the file after load_workbook has finished loading.

Answer 1

 wb._archive.close() 

Also works with use_iterator.

Answer 2

I tried all of these solutions to close the xlsx file in read-only mode, and none of them does the job. I finally finished using the 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) 

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

Answer 3

I also found that this is a problem, and I think it is strange that books do not have a close method.

The solution I came across was a context manager that “closes” the file for me, so I did not add meaningless saves to my code every time I read the 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 

Answer 4

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 wb._archive.close() did not work for me. Perhaps this is due to the fact that I'm using read-only mode. It can work normally in "normal" mode.

The bmiller answer is the only answer that worked for me too :

 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, this is faster when booting from open () and only using read-only.

My working code based on bmiller 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) 

Answer 5

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

 OESwb.save('filename.xlsx') 

Hope this helps.

Answer 6

You can try:

 wb = None 

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

Answer 7

Use OESwb._archive.close() This will close the optional file descriptor ZipFile, which was closed in 'read_only=True' mode. Remember that after closing you could not read more data from OESwb . Remember that this is a workaround, and _archive may be removed in a future version.

Answer 8

Openpyxl 2.4.4+ provides the Workbook.close() method for the latest information. Below are the links.

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