Openpyxl不会以只读模式关闭Excel工作簿

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.

我希望能够在Python中读取Excel文件，在读取完成后让Python脚本继续执行其他操作，同时能够在另一个进程中编辑Excel文件。我用的是python 2.7和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.

在我运行该函数之后，Excel文件仍然被锁定，以便从其他进程访问(它给出了当前正在使用的“文件名”错误。即使我不想再用Python来读它了。

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.

如何从脚本中关闭文件?我尝试过OESwb.close()，但它给出了错误的“'Workbook'对象没有'close'属性”。我找到了这篇文章，但似乎没有帮助。

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.

编辑:它会显示OESwb.save('filename.xlsx')工作，但仅当read_only=False时。但是，最好能够关闭文件并且仍然处于只读模式。这似乎是openpyxl的一个错误，因为它应该在load_workbook完成之后关闭文件。

8 个解决方案

#1

wb._archive.close()

Works with use_iterator too.

适用于use_iterator。

#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:

我已经尝试了所有这些以只读模式关闭xlsx文件的解决方案，但似乎没有一个解决方案。最后，我使用了一个in-mem文件:

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, * 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.

被接受的wb._archive.close()答案对我不起作用。可能是因为我使用的是只读模式。在“正常”模式下可以正常工作。

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.

正如他所说，与只使用只读相比，使用open()加载时速度更快。

My working code based on bmiller's answer:

我的工作代码基于bmiller的回答:

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

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.

要释放资源，并在需要时在相同或其他变量中重新加载它。

#7

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.

使用OESwb._archive.close()这将关闭在'read_only=True'模式下保持为open的附加ZipFile文件句柄。请注意，关闭后您无法从OESwb读取更多数据。还要注意，这是一个解决方案，_archive可以在将来的版本中删除。

#8

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

关于您的最新信息，openpyxl 2.4.4+提供了Workbook.close()方法。以下是引用。

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

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

#1

wb._archive.close()