Openpyxl create_sheet() is always blank, will not append list when in for loop or while loop

Question

I'm trying to iterate through an excel file, create a list of my appointments, and append that list to a new sheet in excel. Creating a multi-dimensional list of my appointments was super easy, but python will not append my appointments to a new sheet and I don't know why.

Python will append to new sheet if I manually put in the text, i.e.

ws.append(['hello','world'])

It will append if I save the text manually as variables, i.e.

hello = "hello"
world = "world"
ws.append(hello, world)

It seems to get caught up on iterating through the list itself. It will even append if I manually enter a number for i, i.e.

ws.appendappt_array[0]

But for some reason, if i try to put it in a while loop or a for loop to iterate through, it just will not do it, and I can't for the life of me figure out why.

Here's an example of the multi-dimensional list output:

[['1 Thurs', 'St Louis', 'Missouri', 'thomas'], ['9 Fri', 'Chicago', 'Illinois', 'thomas'], ['16 Fri', 'St Louis', 'Missouri', 'thomas'], ['23 Fri', 'Milwaukee', 'Wisconsin', 'thomas'], ['29 Thurs', 'Chicago', 'Illinois', 'thomas']]

And finally here is the code. Sometimes people misspell things in the excel document, so I decided to make it as a function, and then feed every kind of name into it, just in case.

import openpyxl

from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter

def find_date_and_location(name, workbook_LOC):
    wb = load_workbook(workbook_LOC)
    
    new_save_file = workbook_LOC.replace(".xlsx", "_updated.xlsx")
    num_of_sheets = len(wb.sheetnames)
    wb.create_sheet('New_appointments')
    ws = wb['New_appointments']
    appt_array = []
    
    i = 0
    while i < num_of_sheets:
        sheet = wb.worksheets[i]
        state = wb.sheetnames[i].replace(".","")
        row_count = sheet.max_row
        column_count = sheet.max_column
        for row in range (1, row_count+1):
            for col in range(1, column_count+1):
                char = get_column_letter(col)
                cell_val = str(sheet[char+str(row)].value)
                cell_val_lower = cell_val.lower()
                if name in cell_val_lower:
                    date = sheet["A"+str(row)].value
                    location =sheet[char+"1"].value
                    new_appointment = [date, location, state, name]
                    appt_array.append(new_appointment)
        i += 1

    while i < len(appt_array):
        ws.append(appt_array[i])
        i += 1
    wb.save(new_save_file)

possible_names = ['tom', 'tomm', 'tommy', 'thomas', 'thommas', 'thompkin']
for name in possible_names:
    find_date_and_location(name, 'SEPT2022STAFFING.xlsx')

Answer 1

There seems to be a lot of unnecessary looping in the code at a guess.
For example; for every variation of the name in the possible_names list you are running the function find_date_and_location(), and you are checking every cell for the name. Do you know what column the name would be in?
At the very least you can loop through once and check the value in the cell against the possible_names list like;

cell_val = str(sheet[char+str(row)].value)
if cell_val in possible_names:
    # The cell has a variant of the name you want

Anyway I think your issue as far as appending to the new sheet 'New_appointments' is simply due to this part of the code that does the appending. You are using the variable 'i' which had already been used in the previous while loop.

while i < len(appt_array):
    ws.append(appt_array[i])
    i += 1

I don't know what value 'i' is by the time it reaches that line but from your example information

len(appt_array)

is equal to 5 and 'i' at this time is likely to be larger than that which means that loop is never run. Obviously you can confirm that by

1. reset i to 0
2. use a different variable name
3. Printing the i variable as the loop is cycled to confirm the append is executed. The print statement will likely not execute

... If you provide more detail about the file SEPT2022STAFFING.xlsx we can help you make the code more efficient I think.