将图片批量导入excel的B列,A列为图片名,请问为什么给A列单元格赋值,会报错:[WinError 32] 另一个程序正在使用此文件,进程无法访问

#从excel导出图片
#使用openpyxl操作图片,需要提前安装pillow
import openpyxl
import os

#要操作的文件地址
file_path = 'C:\\Users\\fang\\Desktop\\中国车\\合并后的文件.xlsx'
#要操作的图片地址图片目录
picture_path = "C:\\Users\\fang\\Desktop\\1\\"



#图片路径列表,目标文件夹里所有图片的路径
all_pic_path = []

#获取目标文件里所有图片的路径
#历遍目标文件,dir=路径,dirnames=子文件夹,filenames=该路径的所有文件名
for dirpath, dirnames, filenames in os.walk(picture_path):

    #历遍所有文件名
    for filename in filenames:

        #组合路径和文件名,将其添加到图片路径列表里
        all_pic_path.append(os.path.join(dirpath, filename))

#加载工作簿
opened_excel = openpyxl.load_workbook(file_path)

#打开工作簿的工作表(sheet1),sheetnames代表该工作簿已存在的所有工作表的名称列表
excel_data = opened_excel[opened_excel.sheetnames[0]]



x = 1

#将图片路径列表里的所有图片添加到excel里
#循环每一个图片路径
for a in all_pic_path:
    try :
        #分离路径和文件名,拿到文件名+文件名后缀
        b = os.path.basename(a)

        #分离文件名和文件名后缀,拿到文件名
        pic_name = os.path.splitext(b)[0]

        #在A列导入对应的图片名称
        excel_data['A' + str(x)] = str(pic_name)
        #excel_data.cell(row= x , column = 1 , value = str(pic_name))
        #print(excel_data.cell(row= x , column = 1 ).value)

        # 将图片路径变为excel能使用的图片
        img = openpyxl.drawing.image.Image(a)

        # 将图片插入excel
        excel_data.add_image(img, "B"+str(x))


        # 设置图片单元格那行的行高
        excel_data.row_dimensions[str(x)].height = 1280 / 10 * 0.8
        # 设置图片单元格那列的列宽
        excel_data.column_dimensions["B"].width = 720 / 10 * 0.8

        # 打印导入了哪些图片
        print("已导入" +str(x)+"项,"+ pic_name)
        x += 1

    except :
        excel_data['A' + str(x)] = str(pic_name+"导入失败")
        x += 1
        continue


#存储excel
opened_excel.save(file_path+'运行结果.xlsx')
print("已完成")

报错为

如果我把(excel_data[‘A’ + str(x)] = str(pic_name))这行删掉或者故意把单元格的位置写错,让程序运行except的代码,程序都可以正常运行,有大佬知道是为什么吗,感谢^_^

Jason990420
最佳答案
File "E:\Python\lib\site-packages\openpyxl\worksheet\_writer.py", line 177, in rows
    return sorted(rows.items())
TypeError: '<' not supported between instance of 'str' and 'int'

Check the values of rows in _writer.py, something like this, or add statements to _writer.py to check.

from collections import defaultdict
import openpyxl

wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet 1')

for i in range(5):
    ws[f'A{i+1}'] = i+1
    ws.row_dimensions[str(i+1)].height = 20

# code from method 'rows' in '_writer.py'
rows = defaultdict(list)
for (row, col), cell in sorted(ws._cells.items()):
    rows[row].append(cell)

for row in ws.row_dimensions.keys() - rows.keys():
    rows[row] = []
# Check ths value of 'rows'
for key, value in rows.items():
    print(f'{repr(key)} : {value}')
1 : [<Cell 'Sheet 1'.A1>]
2 : [<Cell 'Sheet 1'.A2>]
3 : [<Cell 'Sheet 1'.A3>]
4 : [<Cell 'Sheet 1'.A4>]
5 : [<Cell 'Sheet 1'.A5>]
'2' : []
'3' : []
'4' : []
'1' : []
'5' : []

Found two kinds of keys used, str and int

That’s why you got exception which happened when return sorted(rows.items())

So, I got that you used wrong row number in some statements, then it is excel_data.row_dimensions[str(x)].

Revise this statement from

excel_data.row_dimensions[str(x)].height = 1280 / 10 * 0.8

to

excel_data.row_dimensions[x].height = 1280 / 10 * 0.8
3周前 评论
Jason990420 (作者) 3周前
hg946902208 (楼主) 3周前
hg946902208 (楼主) 3周前
讨论数量: 4
Jason990420
File "E:\Python\lib\site-packages\openpyxl\worksheet\_writer.py", line 177, in rows
    return sorted(rows.items())
TypeError: '<' not supported between instance of 'str' and 'int'

Check the values of rows in _writer.py, something like this, or add statements to _writer.py to check.

from collections import defaultdict
import openpyxl

wb = openpyxl.Workbook()
ws = wb.create_sheet('Sheet 1')

for i in range(5):
    ws[f'A{i+1}'] = i+1
    ws.row_dimensions[str(i+1)].height = 20

# code from method 'rows' in '_writer.py'
rows = defaultdict(list)
for (row, col), cell in sorted(ws._cells.items()):
    rows[row].append(cell)

for row in ws.row_dimensions.keys() - rows.keys():
    rows[row] = []
# Check ths value of 'rows'
for key, value in rows.items():
    print(f'{repr(key)} : {value}')
1 : [<Cell 'Sheet 1'.A1>]
2 : [<Cell 'Sheet 1'.A2>]
3 : [<Cell 'Sheet 1'.A3>]
4 : [<Cell 'Sheet 1'.A4>]
5 : [<Cell 'Sheet 1'.A5>]
'2' : []
'3' : []
'4' : []
'1' : []
'5' : []

Found two kinds of keys used, str and int

That’s why you got exception which happened when return sorted(rows.items())

So, I got that you used wrong row number in some statements, then it is excel_data.row_dimensions[str(x)].

Revise this statement from

excel_data.row_dimensions[str(x)].height = 1280 / 10 * 0.8

to

excel_data.row_dimensions[x].height = 1280 / 10 * 0.8
3周前 评论
Jason990420 (作者) 3周前
hg946902208 (楼主) 3周前
hg946902208 (楼主) 3周前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!