个人最近使用openpyxl操作excel,碰到的两个费时问题总结:
-
PermissionError: [Errno 13] Permission denied: 'test.xlsx'
此问题如果确定了没动过权限,就可能是因为打开了此文件没有关闭
(之前一直以为是权限问题,查了半天文件的权限,结果发现是打开检查结果没关...)
-
给单元格赋值了,并save()了,打开表格却没有数据
想打开excel文件根据第一列得到数据后输出到第二三列并保存回此excel文件,错误的代码:
def start_excel():
excel_name = 'test.xlsx'
wb = openpyxl.load_workbook(excel_name)
ws = openpyxl.load_workbook(excel_name).active
for r in range(2, ws.max_row + 1):
if ws.cell(r, 1).value:
value1 = ws.cell(r, 1).value
ws.cell(r, 2).value = value1 + '2'
ws.cell(r, 3).value = value1 + '3'
wb.save(excel_name)
print('finish')
原因在于:
wb = openpyxl.load_workbook(excel_name)
ws = openpyxl.load_workbook(excel_name).active
应该为:
wb = openpyxl.load_workbook(excel_name)
ws = wb.active
不然最后的 wb.save(excel_name),保存的并不是改好的excel(第一次使用贪方便复制粘贴着写引起的错误,应该不会有人也犯吧...)
附上正确的代码:
def start_excel():
excel_name = 'test.xlsx'
wb = openpyxl.load_workbook(excel_name)
ws = wb.active
for r in range(2, ws.max_row + 1):
if ws.cell(r, 1).value:
value1 = ws.cell(r, 1).value
ws.cell(r, 2).value = value1 + '2'
ws.cell(r, 3).value = value1 + '3'
wb.save(excel_name)
print('finish')