Openpyxl search in column and add if it's missing from dictionary-CodePudding

i know it sounds quite simple but can't figure how to make it work. I have a dictionary and an excel. All i need to perform is to iterate for each key on dictionary and see if it exists on excel col2 for example. If its missing then just have to add it at the end, not in the iteration cell so it won't overwrite the existing data. So far i have reached until this point and have stuck.


path="some path\\test.xlsx"
my_dict={'key1':'val1','key2':'val2','key3':'val3'}

wb = openpyxl.load_workbook(path)
ws=wb.active

for i in range(2,ws.max_row 1):
    cell_data = ws.cell(row=i, column=2)
    for key in list(my_dict.keys()):
        if cell_data.value!=key:
            #print('row' str(i)  ' cell2' ': ' cell_obj.value)
            # print('key: ' key)
            cell_data.value = key

wb.save(path)

Below i have attached the input and desired output, any guidance really appreciated.

CodePudding user response：

You are on good tracks with what you show.

This is what I propose can copy the keys of your dictionary into a set, then iterate over your worksheet, removing the keys you find from the set, and finally iterate over the set appending to the worksheet.

from openpyxl import load_workbook

path = "/tmp/75043437.xlsx"

wb = load_workbook(path)
ws = wb.active

my_dict = {"key1": "val1", "key2": "val2", "key3": "val3"}

my_keys = set(my_dict.keys())

for row in ws.iter_rows(min_row=2, values_only=True):
    try:
        my_keys.remove(row[1])
    except KeyError:
        pass

for k in my_keys:
    ws.append((None, k, None))

wb.save(path)