时间戳

首页 > 解决方案 > 循环期间保存 Excel 时权限被拒绝错误

python-3.x - 循环期间保存 Excel 时权限被拒绝错误

问题描述

我正在为一个项目构建一个小型 Google Geocode API 应用程序。我读入一个长/纬度组合的 excel 文件，然后循环调用 geocode 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:\Users\johnsonm\.vscode\extensions\ms-python.python-2019.9.34911\pythonFiles\ptvsd_launcher.py", line 43, in <module>
    main(ptvsdArgs)
  File "c:\Users\johnsonm\.vscode\extensions\ms-python.python-2019.9.34911\pythonFiles\lib\python\ptvsd\__main__.py", line 432, in main
    run()
  File "c:\Users\johnsonm\.vscode\extensions\ms-python.python-2019.9.34911\pythonFiles\lib\python\ptvsd\__main__.py", line 316, in run_file
    runpy.run_path(target, run_name='__main__')
  File "C:\Users\johnsonm\AppData\Local\Programs\Python\Python37-32\lib\runpy.py", line 263, in run_path
    pkg_name=pkg_name, script_name=fname)
  File "C:\Users\johnsonm\AppData\Local\Programs\Python\Python37-32\lib\runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "C:\Users\johnsonm\AppData\Local\Programs\Python\Python37-32\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "c:\Users\johnsonm\OneDrive - XXX\Documents\Python Learning\API Projects\Test.py", line 44, in <module>
    wb.save("testxl.xlsx")
  File "C:\Users\johnsonm\AppData\Local\Programs\Python\Python37-32\lib\site-packages\openpyxl\workbook\workbook.py", line 409, in save
    save_workbook(self, filename)
  File "C:\Users\johnsonm\AppData\Local\Programs\Python\Python37-32\lib\site-packages\openpyxl\writer\excel.py", line 292, in save_workbook
    archive = ZipFile(filename, 'w', ZIP_DEFLATED, allowZip64=True)
  File "C:\Users\johnsonm\AppData\Local\Programs\Python\Python37-32\lib\zipfile.py", line 1204, in __init__
    self.fp = io.open(file, filemode)
PermissionError: [Errno 13] Permission denied: 'testxl.xlsx'

标签： python-3.xopenpyxl

解决方案

首先尝试运行一个最小的示例来解决问题所在。尝试运行这个：

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)

推荐阅读