Skip to main content
 首页 » 编程设计

python之使用 openpyxl 将 python 字典列表写入 xlsx

2024年11月01日8zfyouxi

我尝试使用 openpyxlsx 将 dicts 的 list 写入 xlsx 文件中

products= [{'id':46329',  
            'discription':'AD BLeu',  
            'marque':'AZERT', 
            'category':'liquid', 
            'family': 'ADBLEU',  
            'photos':'D:\\hamzawi\\hamza\\image2py\\46329_1.png'}, 
            {dict2 ...}, 
            {dictn...} 
           ] 

 # creat a workbook 
 filena = "produitimage.xlsx" 
 workbook = Workbook() 
 sheet = workbook.active 
 #add headers 
 sheet.append(["Product ID", "Product Name", "Marque", 
          "Category", "Family", "Photos"]) 
 for product in products: 
    for item in product.items(): 
        for row, entry in enumerate(item, start=3): 
            sheet.cell(row=row, column=1, value=entry) 
 #add some images 
 images = [item['photos'] for item in products] 
 for image in images: 
     logo = Image(image) 
     #logo.height = 150 
     #logo.width = 150 
     sheet.add_image(logo) 
 workbook.save(filename=filena) 

我得到了只有标题没有数据的 xlsx 文件

请您参考如下方法:

Question: append list of dict

import openpyxl 
 
products = [{'id':46329,  
             'discription':'AD BLeu',  
             'marque':'AZERT', 
             'category':'liquid', 
             'family': 'ADBLEU',  
             'photos':'D:\\hamzawi\\hamza\\image2py\\46329_1.png'} 
            ] 
 
# Dictionarys are not in order by default 
# Define a `list` of `keys` in desired order 
fieldnames = ['id', 'discription', 'marque', 'category', 'family', 'photos'] 
 
# create a new workbook 
wb = openpyxl.Workbook() 
ws = wb.active 
 
# append headers 
ws.append(["Product ID", "Product Name", "Marque", "Category", "Family", "Photos"]) 
 
# append data 
# iterate `list` of `dict` 
for product in products: 
    # create a `generator` yield product `value` 
    # use the fieldnames in desired order as `key` 
    values = (product[k] for k in fieldnames) 
 
    # append the `generator values` 
    ws.append(values) 
 
# show Worksheet Values 
for row_values in ws.iter_rows(values_only=True): 
    for value in row_values: 
        print(value, end='\t') 
    print() 

Output:

Product ID  Product Name    Marque  Category    Family  Photos   
46329       AD BLeu         AZERT   liquid      ADBLEU  D:\hamzawi\hamza\image2py\46329_1.png    

如果您想要图像而不是图像文件路径,请更改以下内容:

# remove 'photos' from fieldnames 
fieldnames = \ 
['id', 'discription', 'marque', 'category', 'family'] 
 
# you need the Row index, add a `enumerate(..., 2)` 
for row, product in enumerate(products,2): 
    values = (product[k] for k in fieldnames) 
    sheet.append(values) 
 
    # after append the `values` add the image 
    # Here, Column 'F' 
    ws.add_image(Image(product['photos']), 'F{}'.format(row))