Searching one row in excel to receive data from a corelating row using Python

Question

I wanted to create a simple dictionary for my work where I would create a communication between Python and excel. In one column there is the abbreviation and in another column is the full name. I have created a window with a popup where the user has to input the abbreviation and in return he will receive the popup with the full name. My problem is now to write the piece of code where it will take the data from excel and return with the answer. I am quite new to programming and still learning, and I need Your help.

from tkinter import *
from xlrd import open_workbook


window = Tk()

window.title("Hapag-Lloyd Dictionary")

window.geometry('350x200')

lbl = Label(window, text="Insert the abbreviation")

lbl.grid(column=0, row=0)

txt = Entry(window, width=10)

txt.grid(column=1, row=0)


def clicked():
messagebox.showinfo('Message title', 'Message content')

btn = Button(window, text="Click Me", command=clicked)

btn.grid(column=2, row=0)

window.mainloop()

John T · Answer 1 · 2020-01-15T16:24:54.563

Looks like you need to add a few things.

1) a variable for your text entry to pass to your function

2) the function to be able to accept a passed parameter and button since its now passing a parameter should utilize lambda. help understanding lambda here.

Why are Python lambdas useful?

3) routine to look up value in excel file within your function, I use openpyxl or xlwings typically and have included a working code for openpyxl, I also included what I think xlrd's should be, but don't have that on the cpu i'm at currently on to verify, here's a couple links to do nearly what you want in xlrd module.

Iterating all columns with xlrd

Iterating rows and columns using XLRD in Python

import tkinter as tk
from tkinter.messagebox import showinfo
import openpyxl as opxl

def popupmsg(msg):
    #output to user 
    title = "Full name"
    msg = msg
    showinfo(title, msg)

#3)lookup function
def lookUp(a):
    abbreviation = str(a)
    wb = opxl.load_workbook('example.xlsx')
    #if excel is not inside same folder as this routine include path to it
    sheet= wb['main']
    #named the sheet 'main' in case I need more
    for col_cells in sheet.iter_cols(min_col = 1 , max_col =1):
        for cell in col_cells:
            if (cell.value == abbreviation):
                rowN = cell.row
                fullName = str(sheet.cell(row = rowN, column = 2).value)
                popupmsg(fullName) #this will output every match 
                #with input Initials
            else:
                pass

    # #3)here's what I think should be the xlrd equivalent
    # import xlrd # replace opxl at top, comment out opxl routine
    # file_location = "example.xlsx"
    # workbook = xlrd.open_workbook(file_location)
    # sheet = workbook.sheet_by_index(0)
    # for cell in sheet.col(0)):
    #     if (cell.value == abbreviation):
    #         rowN = cell.row
    #         fullName = sheet.cell_value(rowN,1)
    #     else:
    #         pass
    popupmsg(fullName) #this will output the last output with input Initials


root=tk.Tk()

#txtvar 1)
abb = tk.StringVar()

abbLabel = tk.Label(root, text= "Insert the abbreviation")
abbEntry = tk.Entry(root, textvariable = abb)
#lambda 2)
lookupBtn = tk.Button(root, text= "Look Up Name", command = lambda: 
lookUp(abbEntry.get()))

abbLabel.pack()
abbEntry.pack(padx = 20)
lookupBtn.pack()
#more complicated forms may want to use grid

root.mainloop()

I also highly recommend adopting the object oriented structure, especially as you add complexity, outlined here.

Best way to structure a tkinter application?

Searching one row in excel to receive data from a corelating row using Python

1 Answers1