Trying to read excel cells through openpyxl using PySimpleGUI in Python

Question

I'm trying to get PySimpleGUI to read specific excel cells. While the code in itself works, it doesn't return the correct cell value once it's used with PySimpleGUI. It only returns the headers, but not the other rows. Any ideas on how to fix it will be much appreciated!

'''

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()

'''

"It only returns the headers, but not the other rows" because the first thing you do in your loop is check to see if row[2].value == 'ID', which it will for the HEADER (i.e. the first row), and then you break. As suggested, it's got nothing to do with PySimpleGUI. You break from your loop on the very first row and thus will never see any other rows. — Mike from PSG, Nov 01 '20 at 19:28

Gabriel Genellina · Answer 1 · 2020-11-01T23:02:16.080

0

(This should be a comment) Remove all things related to PySimpleGUI, it just masks the problem. Focus on the for loop, make it work. Currently, it only processes the header line because of the if row[2].value == 'ID': line

(edited) Try this alone:

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

edited Nov 01 '20 at 23:02

answered Nov 01 '20 at 06:07

Gabriel Genellina

31
3

But the thing is that if I remove all things PySimpleGUI, it works seamlessly. It returns the exact cells I request based on the ID. That's what I don't understand. It works as a standalone openpyxl and python code, but it gets weird when adding PySimpleGUI. I tried everything but can't figure out what I'm missing. – Robin Sage Nov 01 '20 at 06:11
@RobinSage Test again, the published code does not perform what you say it does. The 'for' loop executes only once, and breaks after printing the header (because of the 'break' statement). Even if you remove that 'break', it does nothing with the remaining rows. – Gabriel Genellina Nov 01 '20 at 07:53
I've tried everything, which is why I resorted to asking here... if you could show me, I'd appreciate it. – Robin Sage Nov 01 '20 at 08:12
Try this alone: ``` roster = openpyxl.load_workbook('sample.xlsx') sheet = roster.active for row in sheet.rows: print('{} {}'.format(row[0].value, row[1].value) ``` – Gabriel Genellina Nov 01 '20 at 22:59
the print() function is not the problem. My code prints the cell values correctly. Now, if I try to use --> window['name'].print(row[2].value) <-- instead of print(), that's when I get the title cell values only. I only returns "First Name" instead of the actual name. – Robin Sage Nov 02 '20 at 06:54
If you replace your original for-loop with my version, I'm pretty sure you should get first name and last name from all rows, including heading. If print(...) works fine, then Multiline.print(...) should work fine too. – Gabriel Genellina Nov 02 '20 at 07:44
I think your Multiline element may be too short and you don't notice the additional content. Try size=(15,4) – Gabriel Genellina Nov 02 '20 at 07:51

score 0 · Accepted Answer · answered Nov 09 '20 at 07:03

Alright, I figured it out. The code in itself is fine. The reason why it doesn't read the cells it's supposed to read is that PySimpleGUI is needs to know those Excel cells are integers. So the only thing I did was to add int(values['ID'])

I made another mod in the code. Since I'm already importing the excel file into the code using openpyxl.load_workbook, there is no need to browse for it in the window. I did away with that part. Here is the complete functional code:

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()

Trying to read excel cells through openpyxl using PySimpleGUI in Python

2 Answers2