How do you save dictionary data to the matching excel row but different column using openpyxl

Question

I am using openpyxl to read a column (A) from an excel spreadsheet. I then iterate through a dictionary to find the matching information and then I want to write this data back to column (C) of the same Excel spreadsheet.

I have tried to figure out how to append data back to the corresponding row but without luck.

CODE

from openpyxl import load_workbook

my_dict = {
    'Agriculture': 'ET_SS_Agriculture',
    'Dance': 'ET_FA_Dance',
    'Music': 'ET_FA_Music'
}


wb = load_workbook("/Users/administrator/Downloads/Book2.xlsx")  # Work Book
ws = wb['Sheet1']  # Work Sheet
column = ws['A']  # Column
write_column = ws['C']


column_list = [column[x].value for x in range(len(column))]
for k, v in my_dict.items():
    for l in column_list:
        if k in l:
            print(f'The dict for {l} is {v}')
            # append v to row of cell index of column_list

So, if my excel spreadsheet looks like this:

I would like Column C to look like this after I have matched the data dictionary.

score 0 · Answer 1 · answered Nov 22 '21 at 23:59

In order to do this with your method you need the index (ie: row) to assign the values to column C, you can get this with enumerate when running over your column_list

    for i, l in enumerate(column_list):
        if k in l:
            print(f'The dict for {l} is {v}')
            # append v to row of cell index of column_list
            write_column[i].value = v

After writing all the values you will need to run

wb.save("/Users/administrator/Downloads/Book2.xlsx")

To save your changes

That said, you do a lot of unnecessary iterations of the data in the spreadsheet, and also make things a little bit difficult for yourself by dealing with this data in columns rather than rows. You already have your dict with the values in column A, so you can just do direct lookups using split.

You are adding to each row, so it makes sense to loop over rows instead, in my opinion.

my_dict = {
    'Agriculture': 'ET_SS_Agriculture',
    'Dance': 'ET_FA_Dance',
    'Music': 'ET_FA_Music'
}


wb = load_workbook("/Users/administrator/Downloads/Book2.xlsx")  # Work Book
ws = wb['Sheet1']  # Work Sheet

for row in ws:
    try:
        # row[0] = column A
        v = my_dict[row[0].value.split("-")[0]] # get the value from the dict using column A
    except KeyError:
        # leave rows which aren't in my_dict alone
        continue

    # row[2] = column C
    row[2].value = v

wb.save("/Users/administrator/Downloads/Book2.xlsx")

Thank you so much for your in-depth answer. This does make sense to clean up my processing. The only think I don't know and I am researching now is why it is not writing those results `row[2].value` back to the spreadsheet. Nothing is updated on the spreadsheet. — user3324136, Nov 23 '21 at 17:10
Using another link: https://stackoverflow.com/questions/33920108/writing-to-row-using-openpyxl, I tried writing to the cell with the hardcode `ws.cell(row=2, column=3).value = 'hello world'`, but this didn't work either. — user3324136, Nov 23 '21 at 17:16
I have tried multiple methods to write to a spreadsheet. Could this be a Mac issue? I have tried using this method as well. https://stackoverflow.com/questions/31395058/how-to-write-to-a-new-cell-in-python-using-openpyxl#:~:text=import%20openpyxl%0Awb%20%3D%20load_workbook(filename%3D%27xxxx.xlsx%27)%0Aws%20%3D%20wb.worksheets%5B0%5D%0Aws%5B%27A1%27%5D%20%3D%201%0Aws.cell(row%3D2%2C%20column%3D2).value%20%3D%202 — user3324136, Nov 23 '21 at 17:27

How do you save dictionary data to the matching excel row but different column using openpyxl

1 Answers1