Very new to python here. Need some help with using openpyxl, please! This is for a personal project on my self-learning journey. I’m looking for a resource to show me how to do the following thing in pip3 openpyxl:
I have a class that has a variable which acts as a unique identifier. This is the first column in my excel workbook. What I want to do:
I want to add new IDs to the spreadsheet. This means, first, I need to iterate over only the first column and make sure the new unique identifier isn’t already present. I believe the following code does this iteration successfully:
for row in sheet.iter_rows('A{}:A{}'.format(sheet.min_row,sheet.max_row)):
Then, if the unique identifier is not present, meaning the ID is not already contained within the dataset, I want to add that ID and its associated class values to the dataset in a new row. I think the following line does that, inserting a new row right under the header row:
sheet.insert_rows(idx=2)
Then I write code below that to fill the rest of the cells in that row with the appropriate values.
Else if the unique identifier is already present, I want to use some of the data from the row containing that identifier and the associated class values, do some recalculations on those cells, and then update some of the other values/cells in the row, overwriting the old values. This is what I specifically need help with.
Based on something I saw elsewhere, this is my idea, putting everything together:
for row in sheet.iter_rows('A{}:A{}'.format(sheet.min_row,sheet.max_row)):
if fin.fin != row:
fin is the unique ID which is the name given to each case and also an attribute stored in the class. I’m also wondering if != row will work here or if I need to index like row[0] or if row.value should be used?
sheet.insert_rows(idx=2)
sheet["A2"] = fin.fin #setting cell A2 = unique id attribute of my class instance
sheet["B2"] = fin.kgs #setting cell B2 = kgs attribute of my class instance
sheet["F2"] = fin.kgs_to_lbs() #setting cell F2 to module output converting kgs to lbs
else:
sheet['B{}'.format(row)] = fin.kgs.format(row) #if the id is not unique, change value in column B of that row
sheet['F{}'].format(row) = fin.kgs_to_lbs.format(row) #if the id is not unique, change value in column F of that row
Hopefully that’s clear. Let me know if anything isn’t. I’m looking either for some sample code or a reference to a resource that would be helpful, please!
Advertisement
Answer
Some issues with your code there. Ath the momnet your code is iterating over the cells in column A and checking if a cell object is equal to your attribute fin.fin which I assume is either a string or an integer (?). Since they arent it will always be FALSE thus creating a new row above row 2.
So, the question you are trying to answer is in actuallity – Does fin.fin already exists in column A?. For that you can just take the list of values in column A and check if fin.fin is in it, we will use a list comprehension to build it:
known_identifiers = [cell.value for cell in ws['A']] # Creating a list of all known identifiers from column A
if fin.fin is not in known_identifiers:
# Special identifier is not in the Excel file add it and data
sheet.insert_rows(idx=2)
sheet["A2"] = fin.fin # setting cell A2 = unique id attribute of my class instance
sheet["B2"] = fin.kgs # setting cell B2 = kgs attribute of my class instance
sheet["F2"] = fin.kgs_to_lbs() #setting cell F2 to module output converting kgs to lbs
else:
# Identifier found, Change data in specific columns.
row = known_identifiers.index(fin.fin)+1 # Row is the same as the item list index+1 (As lists start from 0 and rows from 1)
sheet[f"B{row}"] = fin.kgs.format(row) # Not sure why you need `format` here but it's your class
sheet[f"F{row}"] = fin.kgs_to_lbs.format(row) # Same
Do notice that int(identifier) != str(identifier) so be sure you’re checking variables of the same type, if needed convert one or the other. Also, I have used f-strings in my code as I think it generates a nicer cleaner code, you can read about those – Here.