尝试使用openpyxl在Excel中使用不一致的数据更新价格 [英] Trying to update prices with inconsistent data in excel using openpyxl

问题描述

我正在尝试更新我的Excel表格中的价格.这些价格应在供应商发送的excel电子表格中找到，但是其excel表格中的数据不一致.我通常会导入单个项目的价格，但是某些项目仅具有一个案例的价格(每案例x个项目).我有商品数量，正在尝试创建一个程序，该程序可以自动正确更新我的价格.

I am trying to update prices in an excel sheet of mine. These prices should be found in excel spreadsheets sent by suppliers, however their excel sheet has inconsistent data. I generally import the price of a single item but some items only have a price for a case (consisting of x items per case). I have the item quantities and am trying to create a program that can correctly update my prices automatically.

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字典时，由于某种原因，它又变成空白.我仍然不知道为什么.

When I print the missing_prices dictionary it comes back blank for some reason. I still couldn't figure out why.

感谢您的帮助.另外，如果有人能想到一种更有效的方法，我很想知道如何做.我是编程新手，想学习如何提高代码效率.

Any help is appreciated. Also, if anyone can think of a more efficient way of doing this I would be curious to see how. I am new to programming and want to lear how to be more efficient with my code.

推荐答案

如果Excel文件中的单元格为空，则openpyxl给出的值为None.因此您的测试应如下所示:

If a cell is empty in the Excel file, the value given by openpyxl is None. So your test should look like:

if single_price == 0 or single_price is None:

这篇关于尝试使用openpyxl在Excel中使用不一致的数据更新价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！