时间戳

首页 > 解决方案 > 尝试使用openpyxl在excel中使用不一致的数据更新价格

python - 尝试使用openpyxl在excel中使用不一致的数据更新价格

问题描述

我正在尝试更新我的 Excel 表中的价格。这些价格应该在供应商发送的 excel 电子表格中找到，但是他们的 excel 表格中的数据不一致。我通常会导入单个项目的价格，但有些项目只有一个案例的价格（每个案例包含 x 个项目）。我有商品数量，并且正在尝试创建一个可以自动正确更新我的价格的程序。

Product Code    Case Price         Unit Price
92526               19                5.5
97056               250               19
97055               145               
97054               200               
925AAT              45.50    
925AAF              40                6.75


import openpyxl
import pprint

# Set up an empty dictionary which will take key, value pairs = product codes and prices respectively
data = {}

# Set up list of product codes with missing prices in Wholesaler A file.
missing_prices = {}

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

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

# 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
    price = sheet1['K' + str(row)].value
    data[code] = price

# Get Wholesaler A prices and add them to prices dictionary, overriding the old price. If single price is missing, use
# case price for the time being.
for row in range(2, sheet2.max_row + 1):
    code = sheet2['A' + str(row)].value
    if code in data:
        single_price = sheet2['J' + str(row)].value
        if single_price == 0 or single_price == '':
            missing_prices[code] = 0  # Append code of missing price as key to missing price dict and assign value of 0
            case_price = sheet2['I' + str(row)].value
            data[code] = case_price
        else:
            data[code] = single_price

# Correct Wholesaler A prices due to using case prices because of missing single prices (I have the number of units per case in my excel file)
for code in missing_prices.keys():
    for row in range(2, sheet1.max_row + 1):
        if sheet1['E' + str(row)].value == code:
            missing_prices[code] = sheet1['I' + str(row)].value

    data[code] = data[code] / missing_prices[code]

# 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:
        sheet1['K' + str(row)].value = data[code]

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

pprint.pprint(missing_prices)
pprint.pprint(data)

当我打印 missing_prices 字典时，由于某种原因它返回空白。我还是不明白为什么。

任何帮助表示赞赏。另外，如果有人能想到一种更有效的方法，我会很想知道如何做。我是编程新手，想了解如何更高效地使用我的代码。

标签： pythonopenpyxl

解决方案

如果 Excel 文件中的单元格为空，则给出的openpyxl值为None。所以你的测试应该是这样的：

if single_price == 0 or single_price is None:

推荐阅读