尝试在Python中使用PySimpleGUI通过openpyxl读取excel单元格

Question

我试图让PySimpleGUI读取特定的excel单元格。虽然代码本身可以工作，但一旦与PySimpleGUI一起使用，它就不会返回正确的单元格值。它只返回标题，而不返回其他行。任何关于如何修复它的想法都将不胜感激

'''

import PySimpleGUI as sg
import openpyxl

sg.theme('DefaultNoMoreNagging')
layout =  [[sg.Text("Roster", pad=(30,30), font=("Arial", 30, 'bold'), size=(40,1), justification='c')],
          [sg.Text("Upload Roster", font=("Arial", 20, 'bold'), pad=(50,0), size=(17,1)), sg.Button("Browse", font='Arial 20'), sg.Text("Student ID", font=("Arial", 20, 'bold'), pad=(30,0), size=(10,1)), sg.InputText(key='ID', font="Arial 20", background_color="#F7F9F9", size=(10,1))],
          [sg.Text("Student Name", font=("Arial", 20, 'bold'), pad=(50,30), size=(17,1)), sg.Multiline(key='name', font="Arial 20", pad=(0,30), background_color="#F7F9F9", size=(15,1))]]
          
window = sg.Window("Roster", layout)

name = ''

while True:
    event,values = window.read()
    name = values['name']
    if event == "Cancel" or event == sg.WIN_CLOSED:
        break
    elif event == "Browse":

        sg.theme('DefaultNoMoreNagging')
        layout = [[sg.T('Source File', font=('Arial', 20,'bold'))],
                 [sg.In(key='input', size=(25,1), background_color="#F7F9F9", font='Arial 20'), sg.FilesBrowse(target='input',font='Arial 15')],
                 [sg.OK(font='Arial 15', key='OK'), sg.Exit(font='Arial 15')]]

        browse_window = sg.Window("File Browse", layout)
        

        while True:
            event,values = browse_window.read()
            roster = openpyxl.load_workbook('sample.xlsx')
            sheet = roster.active
            columns = sheet.columns
            
            if event == "Exit" or event == sg.WIN_CLOSED:
                break
            elif event == 'OK':
                                              
                if roster:
                    for row in sheet.rows:
                        if row[2].value == 'ID':
                            window['name'].print(row[1].value)
                            print("First Name: ".format(row[0].value))
                            break
                        else:
                            sg.popup_error("Record not found", font="Arial 20")
                            
            browse_window.close()
            
window.close()

'''

Answer 1

（这应该是一个评论） 删除所有与PySimpleGUI相关的内容，它只是掩盖了问题。 专注于for循环，让它工作。目前，由于if row[2].value == 'ID':行，它只处理头行

（已编辑）请单独尝试以下操作：

    roster = openpyxl.load_workbook('sample.xlsx')
    sheet = roster.active
    for row in sheet.rows:
        print('{} {}'.format(row[0].value, row[1].value))

Answer 2

好吧，我知道了。代码本身很好。它不读取它应该读取的单元格的原因是PySimpleGUI需要知道那些Excel单元格是整数。所以我唯一做的就是添加int（value['ID']）

我在代码中做了另一个mod。由于我已经使用openpyxl.load\u工作簿将excel文件导入到代码中，因此不需要在窗口中浏览它。我去掉了那部分。以下是完整的功能代码：

import PySimpleGUI as sg
import openpyxl

roster = openpyxl.load_workbook('/home/superuser/Desktop/sample.xlsx')
sheet = roster.active  

sg.theme('DefaultNoMoreNagging')
#sg.theme('TanBlue')
#sg.theme('Material2')
layout =  [[sg.Text(pad=(150,30), font=("Arial", 20, 'bold'), size=(15,1))],
          [sg.Text("Student ID:", font=("Arial", 15, 'bold'), pad=(20,0), size=(12,1)), sg.InputText(key='ID', font="Arial 20", background_color="#F7F9F9", size=(10,1)),sg.Button("Enter",font="Arial 10", pad=(10,0))],
          [sg.Text("Student Name:", font=("Arial", 15, 'bold'), pad=(20,30), size=(12,1)), sg.Multiline(key='name', font="Arial 20", pad=(0,5), background_color="#F7F9F9", size=(17,1), do_not_clear=False)]]

window = sg.Window("Working Code", layout)

while True:
    event,values = window.read()
    if event == "Cancel" or event == sg.WIN_CLOSED:
        break
    elif event == "Enter":        
        for row in sheet.rows:
            if  int(values['ID']) == row[2].value:
                print("Student Name:{} {}".format(row[0].value,row[1].value))        
                window['name'].update(f"{row[0].value} {row[1].value}")
                break
        else:
            sg.popup_error("Record not found", font="Arial 10")

window.close()