如何使用openpyxl将字典食材克数正确匹配填入Excel对应位置

问题原因 #

你当前的代码直接提取每餐对应的食材重量值列表追加到行中，没有和表头预设的食材顺序做匹配，不同餐包含的食材种类不同，所以重量会错位填到错误的列。另外你原本的表头没有预留第一列的餐次字段，和后续行的结构也不匹配。

实现方案 #

不需要额外写多层条件判断，直接按照表头的食材名称顺序，从每餐的食材字典中取值即可，不存在的食材对应填空值，逻辑简单清晰：

import openpyxl as xl
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

data = {
    "Meal 1": {
        "Eggs": "140g",
        "Cheese": "200g",
        "Salad": "200g",
        "Avocado": "150g"
    },
    "Meal 2": {
        "Snack": "50g",
        "Coffee": "250g",
    },
    "Meal 3": {
        "Rice": "300g",
        "Meat": "200g",
        "Vegetables": "150g",
    },
    "Meal 4": {
        "Fruit": "150g",
    },
    "Meal 5": {
        "Eggs": "140g",
        "Cheese": "200g",
        "Tuna": "250g",
        "Avocado": "150g"
    }
}

wb = Workbook()
ws = wb['Sheet']
ws.title = 'Ordinary Meal Day'
# 表头第一列增加餐次字段，和后续行结构对应
headings = ["餐次","Eggs","Cheese","Salad","Avocado","Snack","Coffee","Rice","Meat","Vegetables","Fruit","Tuna"]
ws.append(headings)
for meal_name, meal_ingredients in data.items():
    row_data = [meal_name]
    # 按食材表头顺序遍历取值，没有对应食材就填空
    for ingredient in headings[1:]:
        row_data.append(meal_ingredients.get(ingredient, ""))
    ws.append(row_data)

wb.save('Meals.xlsx')

注：你原代码里的Snake是Snack（零食）的笔误，Vagtables是Vegetables（蔬菜）的拼写错误，上述示例代码已经做了修正。

内容的提问来源于stack exchange，提问作者Or Zemer Tov