在循环期间保存 Excel 时出现权限被拒绝错误

Question

我正在为一个项目构建一个小型的Google Geocode API应用程序。我读取了长/纬度组合的 excel 文件，然后循环调用地理编码 api 以从返回的 json 中获取特定值，然后将其保存到 excel 文件中。为了解释导致我的工作丢失的潜在故障，我在循环期间多次调用 .save(((来自 openpyxl(。

这个过程工作了一段时间，~20-200+ 循环，然后我会在我尝试保存到的文件上收到权限被拒绝错误。

这似乎与我见过的其他权限被拒绝问题不匹配，因为我知道我有权限，因为某些保存工作。在权限被拒绝错误后，我可以在 Excel 上验证失败前的每次保存是否成功。

我的怀疑是我不完全了解 .save(((也许它在调用频率方面有一些限制？(是如何操作的(但从文档中没有看到任何帮助我的东西(，并且可能有更好的方法来处理这个过程。感谢您的任何建议！

代码如下：

import urllib
import requests
import json
import openpyxl
from openpyxl import Workbook 
from openpyxl import load_workbook
import time
main_api = 'https://maps.googleapis.com/maps/api/geocode/json?'
api_key = 'plz no steal'
#Load workbook for long/lat
workbook_in = load_workbook("crimexlonglat.xlsx")
rowNum = 625
loopCnt = 1
# #Declare workbook
wb = load_workbook("testxl.xlsx")
#wb = Workbook()
#grab active worksheet. Not sure why this is necessary
ws = wb.active
while (rowNum < 1000):
print('Loop ' + str(loopCnt))
#check for invalid lat/long and skip
if workbook_in['Sheet1']['A'+str(rowNum)].value == '0.00000000,0.00000000' or workbook_in['Sheet1']['A'+str(rowNum)].value == '-1.00000000,-1.00000000':
ws['A'+str(rowNum)] = ''
rowNum = rowNum + 1
loopCnt = loopCnt + 1
wb.save("testxl.xlsx")
else:
address = workbook_in['Sheet1']['A'+str(rowNum)].value
url = main_api + urllib.parse.urlencode({'address': address, 'key': api_key})
json_data = requests.get(url).json()
#Dump and load seems unncessary. Need to understand this better.
outputJson = json.dumps(json_data) 
parsedJson = json.loads(outputJson)
ws['A'+str(rowNum)] = parsedJson['results'][0]['address_components'][2]['long_name']
rowNum = rowNum + 1
loopCnt = loopCnt + 1
wb.save("testxl.xlsx")
time.sleep(1)
wb.save("testxl.xlsx")

错误文本：

Traceback (most recent call last):
File "c:Usersjohnsonm.vscodeextensionsms-python.python-2019.9.34911pythonFilesptvsd_launcher.py", line 43, in <module>
main(ptvsdArgs)
File "c:Usersjohnsonm.vscodeextensionsms-python.python-2019.9.34911pythonFileslibpythonptvsd__main__.py", line 432, in main
run()
File "c:Usersjohnsonm.vscodeextensionsms-python.python-2019.9.34911pythonFileslibpythonptvsd__main__.py", line 316, in run_file
runpy.run_path(target, run_name='__main__')
File "C:UsersjohnsonmAppDataLocalProgramsPythonPython37-32librunpy.py", line 263, in run_path
pkg_name=pkg_name, script_name=fname)
File "C:UsersjohnsonmAppDataLocalProgramsPythonPython37-32librunpy.py", line 96, in _run_module_code
mod_name, mod_spec, pkg_name, script_name)
File "C:UsersjohnsonmAppDataLocalProgramsPythonPython37-32librunpy.py", line 85, in _run_code
exec(code, run_globals)
File "c:UsersjohnsonmOneDrive - XXXDocumentsPython LearningAPI ProjectsTest.py", line 44, in <module>
wb.save("testxl.xlsx")
File "C:UsersjohnsonmAppDataLocalProgramsPythonPython37-32libsite-packagesopenpyxlworkbookworkbook.py", line 409, in save
save_workbook(self, filename)
File "C:UsersjohnsonmAppDataLocalProgramsPythonPython37-32libsite-packagesopenpyxlwriterexcel.py", line 292, in save_workbook
archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
File "C:UsersjohnsonmAppDataLocalProgramsPythonPython37-32libzipfile.py", line 1204, in __init__
self.fp = io.open(file, filemode)
PermissionError: [Errno 13] Permission denied: 'testxl.xlsx'

Answer 1

首先尝试运行一个最小的示例来解决问题。 尝试运行以下命令：

import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook
import time
# #Declare workbook
wb = Workbook()
# grab active worksheet. Not sure why this is necessary
ws = wb.active
for rowNum in range(1000):
print('Loop ' + str(rowNum))
ws.append(['Some text', 42])
wb.save("testxl2.xlsx")
# time.sleep(1)