如何通过从下拉列表中选择一个因子将Excel中的特定单元格值导入到tkinter标签框中?

问题描述

from tkinter import *
from tkinter import ttk
import openpyxl
from openpyxl import load_workbook
win=Tk()
BLOCK_NAME=StringVar()
VILLAGE_NA=StringVar()

wb = load_workbook(filename=r"C:\Users\Hp\Desktop\Test data.xlsx")
ws = wb['Sheet1']
xlsx_range = ws['B2':'B10']

elements = []
for cell in xlsx_range:
    for x in cell:
        y = x.value
        elements.append(y)
        print(y)

def lookupdata(event):
     print(combodata.get())

def lookupvillage():
    if combodata.get()==(Say B2):
        BLOCK_NAME.set([sheet["C2"].value])
    elif combodata.get()==(Say B3):
        BLOCK_NAME.set([sheet["C3"].value])
    elif combodata.get()==(Say B4):
        BLOCK_NAME.set([sheet["C4"].value])

Label(win, text="Select data").grid(row=1, column=1, padx=10, pady=10)
combodata=ttk.Combobox(win, textvariable=clicked, width=30)
combodata['values']=elements
combodata.grid(row=1, column=2,padx=50, pady=50)
combodata.bind("<<ComboboxSelected>>", lookupdata)

lbl3=Label(win, text="Block", bg="crimson", fg="white", font=("times new roman",15,"bold")).grid(row=6, column=0, padx=20, pady=5, sticky='w')
ent3=Entry(win, textvariable=BLOCK_NAME, width=20, state='readonly')
ent3.grid(row=6, column=1, padx=20, pady=5)

btn = Button(win, text = 'Calculate Value', command=lookupvillage, bd = '5', width=15, height=2)
btn.grid(row=7, column=2, padx=20, pady=10)

win.mainloop()

我准备了这段代码，该代码有助于从Tkinter中的excel值创建下拉列表.但是我希望当从下拉列表中选择一个特定值时，应该选择excel同一行中的数据并将其设置在Tkinter的标签框中.就像所选下拉列表中的值在B2单元格中一样，我希望标签中的C2值自动出现.我在下拉列表中有1000个以上的值，因此使用if-elif语句将很困难，在此方面也给我提供了指导(For循环可能会有所帮助).预先感谢.

I have prepared this code which helps in creating a dropdown from excel values in Tkinter. But I want that when a particular value is selected from the dropdown, then data in the same row from excel should be chosen and set in the label boxes in Tkinter. Like if the value in the dropdown selected is in the B2 cell, I want the C2 value in the label automatically. I have more than 1000 values in the dropdown so it would be difficult with if-elif statements, guide me in this also (For loop might be helpful). Thanks in advance.

推荐答案

前言:由于您没有提供任何示例数据，因此我使用以下功能制作了自己的数据:

Preface: since you didn't provide any sample data, I made my own, using this function:

from openpyxl import Workbook

def create_sample_workbook():
    wb = Workbook()
    ws = wb.create_sheet("Sheet1")
    columns = {
        "B": ["alpha", "bravo", "charlie", "delta",    "echo",  "foxtrot", "golf",   "hotel", "india", "juliet"],
        "C": ["kilo",  "lima",  "mike",    "november", "oscar", "papa",    "quebec", "romeo", "sierra", "tango"]
    }
    for column_letter, col in columns.items():
        for i, value in enumerate(col,1):
            ws[f"{column_letter}{i}"] = value
    wb.save("Test data.xlsx")

create_sample_workbook()

由于我们的数据不同，您的结果可能看起来与我的结果不同.

Your results may look different from mine since our data is different.

我还必须将您的 combodata = ttk.Combobox(win，textvariable = clicked，width = 30)更改为 combodata = ttk.Combobox(win，width = 30)为了修复您的代码中的NameError.我认为这与您的问题没有直接关系.

I also had to change your combodata=ttk.Combobox(win, textvariable=clicked, width=30) to combodata=ttk.Combobox(win, width=30) In order to fix a NameError in your code. I don't think this is directly related to your problem.

那是一种有效的方法，是的.在 lookupvillage 中，遍历您感兴趣的行，并查看组合框是否与该行的B列匹配.

That's one valid approach, yes. within lookupvillage, iterate over the rows you're interested in, and see if the combobox matches the row's B column.

def lookupvillage():
    value = combodata.get()
    for i in range(2, 11):
        if value == ws.cell(i, 2).value:
            BLOCK_NAME.set(ws.cell(i, 3).value)
            return
    #couldn't find a matching value. Maybe the blank item is still selected?
    BLOCK_NAME.set("unknown")

结果:

您还可以通过预先准备将B列值映射到其对应的C和D值的字典来节省一些查找时间.将其放在 ws = wb ['Sheet1'] 下:

You can also save a little lookup time by pre-preparing a dictionary that maps B column values to their corresponding C and D values. Put this just below ws = wb['Sheet1']:

data = {} #todo: pick a more descriptive name that explains what kind of data is in B and C and D
for i in range(2,11):
    data[ws.cell(i,2).value] = {"C": ws.cell(i,3).value, "D": ws.cell(i,4).value}

现在，您可以快速找到任何B值的C和D值，而无需循环:

Now you can quickly find C and D values for any B value, no loop required:

def lookupvillage():
    value = combodata.get()
    if value in data:
        BLOCK_NAME.set(data[value]["C"])
    else:
        BLOCK_NAME.set("unknown")

这篇关于如何通过从下拉列表中选择一个因子将Excel中的特定单元格值导入到tkinter标签框中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！