如何使用 Openpyxl 从 Excel 工作表创建嵌套字典 [英] How to create nested dictionaries from excel sheet using Openpyxl

问题描述

我是编程新手，正在尝试执行以下操作(到目前为止未成功):

我有一个包含 product code、sellers 和 prices 的 excel 电子表格，并且我正在尝试更新 prices 在我的电子表格中来自批发商发送的两个不同电子表格.

我想要一个程序正确

• 搜索一个 Excel 电子表格并
• 将相应的 price 从那里复制到上面的正确位置.

所以我想，例如:

• 下载批发商A销售的产品代码 '92526'的price.

我想保持制造商price不变.

<块引用>

我有一个正确执行此操作的程序，
除了我告诉它对电子表格中的每一行进行迭代并且它清除了制造商的现有价格.

我想要以下格式的字典:

{92526: {'price': 5.5, 'seller': '批发商 A'},97056:{'价格':19，'卖家':'批发商A'}，...}

<小时>

我尝试添加包含示例数据的电子表格的屏幕截图，但无法添加，所以这里是:

产品代码卖家价格92526 批发商 A 5.597056 批发商 A 1997055 批发商 B 1597054 批发商 B 4.5925AAT 制造商 3.99925AAF 制造商 6.75

列不代表我的电子表格中的实际列.

我的代码是这样的(再次，初学者):

导入 openpyxl导入打印数据 = {}files = {'My_main_file':'my_file.xlsx','File_WholesalerA':'FileA.xlsx','File_WholesalerB':'FileB.xlsx'}wb1 = openpyxl.load_workbook(files['My_main_file'])wb2 = openpyxl.load_workbook(files['File_WholesalerA'])wb3 = openpyxl.load_workbook(files['File_WholesalerB'])sheet1 = wb1.get_sheet_by_name('主数据库')sheet2 = wb2.get_sheet_by_name('sheetA')sheet3 = wb3.get_sheet_by_name('sheetB')# 收集我的数据库电子表格中的所有产品代码，并将它们作为关键字添加到空字典中对于范围(2，sheet1.max_row + 1)中的行:代码 = sheet1['E' + str(row)].value数据[代码] = 代码# 获取批发商 A 的价格并将其添加到数据字典中对于范围(2，sheet2.max_row + 1)中的行:代码 = sheet2['A' + str(row)].value如果数据中的代码:数据[代码]['价格'] = sheet2['J' + str(row)].value数据[代码]['卖家'] = '批发商A'# 获取批发商 B 的价格并将其添加到价格字典中对于范围内的行(2，sheet3.max_row + 1):代码 = sheet3['A' + str(row)].value如果数据中的代码:数据[代码]['价格'] = sheet3['K' + str(row)].value数据[代码]['卖家'] = '批发商B'#将收集到字典中的价格粘贴到我的excel表中，为每个#对应的产品代码对于范围(2，sheet1.max_row + 1)中的行:代码 = sheet1['E' + str(row)].value如果数据中的代码:# 这里我尽量确保代码只更新价格# 对应的卖家，不会覆盖价格#制造商.if sheet1['C' + str(row)].value == data[code]['seller']:sheet1['K' + str(row)].value = data[code]['price']# 用数据保存另一个版本的电子表格wb1.save('My_main_file v2.xlsx')pprint.pprint(数据)

预期结果是程序扫描批发商电子表格的 (10k +) 行，找到与我的产品代码对应的价格并将其粘贴到我的工作表中，覆盖旧价格但不删除任何其他价格.

我的初始程序设法收集价格并将它们粘贴到相应的产品代码中，但它删除了我拥有的制造商价格.由于某种原因，这个新程序甚至不会填充字典.

感谢任何帮助.

解决方案

此作业无法完成

data[code]['price'] = sheet2['J' + str(row)].value数据[代码]['卖家'] = '批发商A'

如果对你有帮助，你可以试试这个结构

data[code] = {'price': sheet2['J' + str(row)].value,'seller': '批发商 A'}

我上面提到的作业可以完成，但是你的字典需要知道它里面有嵌套字典，正如 Tomerikoo 在我上面评论的那样，您可以初始化行 data[code] = {} 然后它也可以工作.现在你会得到一个错误，说:类型错误:'str'/'int'/etc 对象不支持项目分配

I am new to programming and am trying to do the following (so far unsuccessfully):

I have an excel spreadsheet with product codes, sellers and prices and am trying to update the prices in my spreadsheet from two different spreadsheets sent by wholesalers.

I want a program to correctly

• search an excel spreadsheet and
• copy the corresponding price from there to the correct location above.

So I want to, for example:

• download the price for product code '92526' sold by wholesaler A.

I want to leave the manufacturer prices untouched.

I had a program which was doing that correctly,
except that I was telling it to iterate for every line in my spreadsheet and it was erasing the existing prices for manufacturers.

I want a dictionary in the format:

{92526: {'price': 5.5, 'seller': 'Wholesaler A'}, 
 97056: {'price': 19, 'seller': 'Wholesaler A'}, 
 ...
} 

---

I tried adding a screenshot of a spreadsheet with sample data but couldn't, so here it goes:

Product Code    Seller         Price
92526         Wholesaler A    5.5
97056         Wholesaler A    19
97055         Wholesaler B    15
97054         Wholesaler B    4.5
925AAT        Manufacturer    3.99
925AAF        Manufacturer    6.75

Columns are not representative of the actual columns in my spreadsheet.

The code I have is this (again, beginner):

import openpyxl
import pprint

data = {}
files = {'My_main_file':'my_file.xlsx',
         'File_WholesalerA':'FileA.xlsx',
         'File_WholesalerB':'FileB.xlsx'
         }

wb1 = openpyxl.load_workbook(files['My_main_file'])                   
wb2 = openpyxl.load_workbook(files['File_WholesalerA'])                
wb3 = openpyxl.load_workbook(files['File_WholesalerB'])           
sheet1 = wb1.get_sheet_by_name('Master Database')
sheet2 = wb2.get_sheet_by_name('sheetA')
sheet3 = wb3.get_sheet_by_name('sheetB')

# Collect all product codes in my database spreadsheet and add them as keys to the empty dictionary
for row in range(2, sheet1.max_row + 1):
    code = sheet1['E' + str(row)].value
    data[code] = code

# Get Wholesaler A prices and add them to data dictionary
for row in range(2, sheet2.max_row + 1):
    code = sheet2['A' + str(row)].value
    if code in data:
        data[code]['price'] = sheet2['J' + str(row)].value
        data[code]['seller'] = 'Wholesaler A'

# Get Wholesaler B prices and add them to prices dictionary
for row in range(2, sheet3.max_row + 1):
    code = sheet3['A' + str(row)].value
    if code in data:
        data[code]['price'] = sheet3['K' + str(row)].value
        data[code]['seller'] = 'Wholesaler B'

# Paste the prices collected into the dictionary into my excel sheet for each #corresponding product code
for row in range(2, sheet1.max_row + 1):
    code = sheet1['E' + str(row)].value
    if code in data:
        # Here I try to ensure that the code only updates the prices for the 
        # corresponding sellers and doesn't overwrite the prices for 
        # manufacturers.
        if sheet1['C' + str(row)].value == data[code]['seller']:
            sheet1['K' + str(row)].value = data[code]['price']

# Save another version of the spreadsheet with the data
wb1.save('My_main_file v2.xlsx')

pprint.pprint(data)

The expected result is for the program to scan the (10k +) lines of the Wholesaler spreadsheets, find the price corresponding to my product code and paste it into my sheet overwriting the old price but not erasing any other price.

My initial program managed to collect the prices and paste them to the corresponding product codes, but it erased the manufacturer prices which I had. This new program wont even populate the dictionary for some reason.

Any help is appreciated.

解决方案

This assignment can't be done

data[code]['price'] = sheet2['J' + str(row)].value
data[code]['seller'] = 'Wholesaler A'

You can try this structure if it helps you out

data[code] = {'price': sheet2['J' + str(row)].value,
              'seller': 'Wholesaler A'}

Edit:

The assignment I mentioned above can be done, but your dictionary needs to know it has nested dictionary inside it as Tomerikoo commented above me, you could initial the line data[code] = {} and then it will work too. Right now you'll jsut get an error saying: TypeError: 'str'/'int'/etc object does not support item assignment

这篇关于如何使用 Openpyxl 从 Excel 工作表创建嵌套字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！