Python Openpyxl 不会以只读模式关闭 Excel 工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31416842/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Openpyxl does not close Excel workbook in read only mode
提问by wordsforthewise
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.
运行该函数后，即使我不想再用 Python 读取 Excel 文件，它仍然被锁定以供其他进程访问（它给出错误“'文件名'正在使用中。稍后再试”）。
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 postbut 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 完成后关闭文件。
采纳答案by user5859387
wb._archive.close()
Works with use_iterator too.
也适用于 use_iterator。
回答by jharrison12
To close, I believe you need to save the file:
要关闭，我相信您需要保存文件：
OESwb.save('filename.xlsx')
Hope this helps.
希望这可以帮助。
回答by Jeffrey Magedanz
I also found this to be a problem, and think it is strange that workbooks have no close method.
我也发现这是一个问题，并且认为工作簿没有 close 方法很奇怪。
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
回答by MASR
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.
释放资源，并在您再次需要它时在相同或其他变量中再次加载它。
回答by stovfl
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 _archivecould be removed in a future Version.
使用OESwb._archive.close()这将关闭在'read_only=True'Mode中保持打开的附加 ZipFile 文件句柄。请注意，关闭后您无法从OESwb. 另请注意，这是一种解决方法，_archive可以在未来版本中删除。
回答by bmiller
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.
甚至可能加载速度更快，无需担心关闭任何东西。
回答by Patrick Conwell
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.
由于某些严酷的原因，stackoverflow 将允许我发布答案，但我没有足够的“代表”来评论或投票——所以我们来了。
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 isfaster when loading with open() versus only using read-only.
正如他所说，这是更快的加载时开放（）与只使用只读。
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)
回答by Leonard2
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?highlight=close#id86
https://bitbucket.org/openpyxl/openpyxl/issues/673
