0

Hi I am in process of writing a python code to input search and edit data into excel.

my chosen format for the UI is PySimpleGUI and my xlsx package is openpyxl.

I have been coding for approximately 3 weeks.

I probably bit off more than I can chew but I won't give up.

I need to know why my for loop won't work so I can fix it.

here is the offending code:

import PySimpleGUI as sg
import pandas as pd
import openpyxl as op

def weekly_wage():

    filename = 'Payroll.xlsx'

    wb = op.load_workbook(filename, data_only=True)

    ws = wb.worksheets[0]

    ws_tables = []


    layout = [
        [sg.Text('Employee Name', size=(15,1), font=('ariel', 16)), sg.InputText(key='-Name-', font=('ariel',16))],
        [sg.Text('Week Ending', size=(15,1), font=('ariel',16)), sg.InputText(key='-Date-', font=('ariel',16))],
        [sg.Submit(font=('ariel', 16))],
        [sg.Text('The Weekly Wage is:', font=('ariel', 16))],
        [sg.Output(size=(10, 1))]
    ]

    window = sg.Window('Gross Wages Search', layout, size=(450,250))

    # Event Loop
            
    while True:
        event, values = window.read()
        if event == sg.WIN_CLOSED:
            break
        if event == 'Submit':
        if event == 'Submit':
            key1 = window['-Name-']      # this gives a callable object to the Input keys
            key2 = window['-Date-']
            rows = ws.iter_rows()

            name = []
            date = []
            gross = []
                                   
            for a,b,aw in rows:
                    name.append(a)
                    date.append(b)
                    gross.append(aw)
                    if a.value == key1 and b.value == key2:
                        print (aw.value)
                        break
                    else:
                        print('Try Again')
                        break


    window.close()

weekly_wage()

I have poored over the openpyxl docs for hours per night, and I have performed that many google searches I now have shares!!

Wesley Ryman
  • 1
  • 3
  • What "doesn't work"? – Charlie Clark Feb 01 '22 at 13:40
  • I am using it to print the value defined by the loop. and it will only print 'try again'. I just don't understand what I have done wrong. – Wesley Ryman Feb 01 '22 at 23:51
  • add a print in the line after the forloop `for row in ws.iter_rows():` `print(row[1].value, values['-Name-']` and same for date and key2. you might not be getting back what you think you are – colk84 Feb 02 '22 at 01:24
  • `print(value)` would return a `NameError` if it ever ran so the condition is obviously never fulfilled. – Charlie Clark Feb 02 '22 at 08:41
  • @Charlie Clark - good point I changed that line in the code to print(ws.cell(row=cell.row, column=49).value – Wesley Ryman Feb 02 '22 at 12:56
  • @colk84 - Thank you for that I now know I. was searching the wrong 2 columns!! chances are I am looking for a value in a blank cell too I will check – Wesley Ryman Feb 02 '22 at 12:58
  • Don't put edited code in comments, edit the original post. – Charlie Clark Feb 02 '22 at 14:22
  • @Charlie Clark - Thanks for the heads up over how to show what changes I have made. I have edited the question with my new loop. still got no more idea. now it flags an error on idle ValueError: too many values to unpack (expected 3) – Wesley Ryman Feb 02 '22 at 15:14
  • It's not clear to us which values should match. As previously suggested, adding some print statements for the keys and the cell values should help here. – Charlie Clark Feb 02 '22 at 16:15
  • ok.. so hopefully I can explain this better... I have an excel spreadsheet recording times and doing calculation on them. So I am trying to print the value from Column AW that corresponds (is on the same row as the value of both Key1 and Key 2). I imagined I should iterate through the rows to determine which one fits both criteria, and then print the value of the cell in column AW. Is that clears or can you give a newbie an idea of what you want me to add print statement for. :) I am trying hard – Wesley Ryman Feb 03 '22 at 03:37

0 Answers0