Home > Net >  In an excel sheet with multiple entries of the same value is there a way in python to return the lat
In an excel sheet with multiple entries of the same value is there a way in python to return the lat

Time:08-03

Ok so I'm trying to search and retrieve scores for the latest entries of names in a list. My program includes a tkinter gui to search up a name from an excel sheet to return the associated score for that name. Problem is this sheet is updated via a separate app with the same names with updated scores and so my program can only search the first matched value of the searched name so only returns the first score.

Here is a look at a sample of data from my excel file:

enter image description here

Here is my code:

import tkinter
from tkinter import *
import tkinter as tk
import openpyxl

main = Tk()
main.title("Score checker App")
main.geometry("600x200")
main.configure(bg='Teal')

excel_path = r".\Data.xlsx"


def exit():
    main.destroy()


def clear():
    Name.delete(0, 'end')
    Score.delete(0, 'end')


def submit():
    search_name = Name.get()

    Score.configure(state=tk.NORMAL)


    Score.delete(0, 'end')


    file = openpyxl.load_workbook(excel_path)

    sheet = file['Sheet1']


    for cell in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=15, 
                                values_only=True):


        if cell[0] == search_name:
            Score.insert(0, cell[1])


            break


Name = tkinter.Entry(main)
Name.place(x=250, y=30)
Score = tkinter.Entry(main)
Score.place(x=250, y=60)


label_1 = tkinter.Label(main, text="Name", width=20,bg='black', fg='white', font=("bold", 10))
label_1.place(x=50, y=30)
label_2 = tkinter.Label(main, text="Score", width=20,bg='black', fg='white', font=("bold", 10))
label_2.place(x=50, y=60)


tkinter.Button(main, text="Search Name", command=submit).place(x=380, y=30)
tkinter.Button(main, text="Exit", command=exit).place(x=50, y=100)
tkinter.Button(main, text="Clear", command=clear).place(x=150, y=100)

main.mainloop()

CodePudding user response:

If you want the last score of a person, you can save the found score to a variable instead of inserting to the entry box inside the for loop and remove the break line. Then insert the found score after the for loop.

def submit():
    ...
    latest_score = None
    for cell in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=15,
                                values_only=True):

        if cell[0] == search_name:
            latest_score = cell[1] # save the found score

    if latest_score:
        # insert the found score
        Score.insert(0, latest_score)
  • Related