时间戳

首页 > 解决方案 > Openpyxl 将字典写入电子表格

python - Openpyxl 将字典写入电子表格

问题描述

我正在开发一个蛋白质跟踪器来跟踪我今天吃的蛋白质量，但我在将值输入电子表格时遇到了麻烦。我已将食物及其相关蛋白质值保存在字典（total_protein_dictionary）中，当我在代码中包含打印语句时，它表示正在将值输入到工作表中，但是当我在 excel 中检查时，那里什么都没有。我尝试了几种不同的方法。

import openpyxl
def write_values_to_spreadsheet(file, total_protein_dictionary):
"""Takes the keys and values of the total protein dictionary and puts them in the spreasheet"""
wb = openpyxl.load_workbook(file)
sheet_names = wb.sheetnames
sheet = wb.get_sheet_by_name(sheet_names[0])
print(len(total_protein_dictionary))

for next_row in range(1, len(total_protein_dictionary)+1):
    food, protein_grams = total_protein_dictionary.popitem()
    sheet.cell(column=1 , row=next_row, value=food)
    sheet.cell(column=2 , row=next_row, value=protein_grams)

    wb.save(file)

变体 2：

def write_values_to_spreadsheet(file, total_protein_dictionary):
"""Takes the keys and values of the total protein dictionary and puts them in the spreasheet"""
wb = openpyxl.load_workbook(file)

sheet_names = wb.sheetnames
sheet = wb.get_sheet_by_name(sheet_names[0])
print(len(total_protein_dictionary))


for key in range(1, len(total_protein_dictionary)+1):
    food, protein_grams = total_protein_dictionary.popitem()
    print(food, protein_grams)
    index = str(key)

    sheet['A' + index] = food
    sheet['B' + index] = protein_grams
    wb.save(file)

为什么它不会全部显示在我的 Excel 工作表中？另外，使用之间有什么区别

sheet_names = wb.sheetnames
sheet = wb.get_sheet_by_name(sheet_names[0])

和

wb.active

如果我想在第一张纸上工作？

标签： pythondictionaryopenpyxl

解决方案

好的，所以该功能本身运行良好，问题出在我的“运行”功能中，我实际上能够修复它。这是我更新后的 write_values_to_spreadsheet 和“运行”函数的代码。

def write_values_to_spreadsheet(wb, total_protein_dictionary):
"""Takes the keys and values of the total protein dictionary and puts them in the spreasheet"""
    sheet = wb.active

    print(len(total_protein_dictionary))

    for next_row in range(1, len(total_protein_dictionary)+1):

        food, protein_grams = total_protein_dictionary.popitem()
        sheet.cell(column=1 , row=next_row, value=food)
        sheet.cell(column=2 , row=next_row, value=protein_grams)

def run(file):
"""Runs the whole program"""
    wb = openpyxl.load_workbook(file) #opens the workbook at the beginning
    checks_sheet_title(wb)
    #food_protein(x) function start
    eaten_today = str(input("Did you eat any food today?\n")).lower()
    total_protein_dictionary = {} #creates dictionary that can be updated, taken as a     paramter in food_protein

    if eaten_today == "yes":
        foods_documented = False #answers the question: has all food eaten today been accounted for?
        total_protein_dictionary.update(food_protein(total_protein_dictionary)) #updates with new food
        print(total_protein_dictionary)

        while foods_documented == False:
            more_food = str(input("Did you have any more food?\n"))
            if more_food == "yes":
                total_protein_dictionary.update(food_protein(total_protein_dictionary))
                print(total_protein_dictionary)
            elif more_food == "no":
                foods_documented = True #escapes

    print("Today you've had " + str(sum(total_protein_dictionary.values())) + " grams of protein so far today.")
    write_values_to_spreadsheet(wb, total_protein_dictionary)
    wb.save(file)

'run' 的第一位提示用户制作字典，然后在第 35 行调用 write_values_to_spreadsheet。对我来说解决问题的是我将 write_values_to_spreadsheet 更改为将 wb 作为参数而不是文件，在此过程中我删除了 wb = openpyxl.load_workbook(file) 和 wb.save(file) 因为“运行”已经有了这些。我猜他们是不必要的并导致了问题。

无论如何，它现在正在工作！

推荐阅读