1+ import openpyxl
2+
3+ inv_file = openpyxl .load_workbook ("inventory.xlsx" )
4+ product_list = inv_file ["Sheet1" ]
5+
6+ products_per_supplier = {}
7+ total_value_per_supplier = {}
8+ products_under_10_inv = {}
9+
10+ for product_row in range (2 , product_list .max_row + 1 ):
11+ supplier_name = product_list .cell (product_row , 4 ).value
12+ inventory = product_list .cell (product_row , 2 ).value
13+ price = product_list .cell (product_row , 3 ).value
14+ product_num = product_list .cell (product_row , 1 ).value
15+ inventory_price = product_list .cell (product_row , 5 )
16+
17+ # calculation number of products per supplier
18+ if supplier_name in products_per_supplier :
19+ current_num_products = products_per_supplier .get (supplier_name )
20+ products_per_supplier [supplier_name ] = current_num_products + 1
21+ else :
22+ products_per_supplier [supplier_name ] = 1
23+
24+ # calculation total value of inventory per supplier
25+ if supplier_name in total_value_per_supplier :
26+ current_total_value = total_value_per_supplier .get (supplier_name )
27+ total_value_per_supplier [supplier_name ] = current_total_value + inventory * price
28+ else :
29+ total_value_per_supplier [supplier_name ] = inventory * price
30+
31+ # logic products with inventory less than 10
32+ if inventory < 10 :
33+ products_under_10_inv [int (product_num )] = int (inventory )
34+
35+ # add value for total inventory price
36+ inventory_price .value = inventory * price
37+
38+
39+ print (products_per_supplier )
40+ print (total_value_per_supplier )
41+ print (products_under_10_inv )
42+
43+ inv_file .save ("inventory_with_total_value.xlsx" )
0 commit comments