使用Python更快地根据用户输入从Excel表中提取信息

Question

我会尽我所能解释这件事，但我想我得再说几次。

所以我有一个有特定规格的图表列表。我在Excel中构建了一个表格，其中包含了各种图表及其规范。我使用PySimpleGui构建了一个用户界面，供用户进行交互。有一些下拉框，其中包含规范，它们将用作代码的输入，用于搜索这些图表。下面是GUI的代码

#To create the GUI, first the layout of the window must be set up.
#Set the theme
psg.theme('LightBlue')

#Create a list that will be used for the listbox
diag_num = []
Torq = []

#Set up the layout
left_col = [[psg.Text('Choose the Type: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','CSIR','CSCR', 'PSC', 'Poly', 'SP'], font='Helvetica',
    default_value='Select Option', key='type')],
    [psg.Text('Choose the TORQ Switch: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','282070101', '282070111', '282070117', '282070118', '282070125', '282070201','282070205','282070251',
    '282070261', '282070303', '282070304', '282070352', '282070403', '282070451', '282070600', '282070601', '282070602', '282070701', '282070702', '282070901', '282070902', '282070903', '282080101', 'None'],
     font='Helvetica', default_value='Select Option', key='switch')],
    [psg.Text('Is there a Terminal Board: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option', 'Yes', 'No'], font ='Helvetica', default_value='Select Option',
    key='board')],
    [psg.Text('Choose the Voltage: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Single','Double'], font='Helvetica',
    default_value='Select Option', key='volt')],
    [psg.Text('Choose the Rotation: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Non-Reversible','Reversible'], font='Helvetica',
    default_value='Select Option', key='rot')],
    [psg.Text('Choose the Start: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Total','Half'], font='Helvetica', default_value='Select Option',
    key='start')],
    [psg.Text('Choose the Overload: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','Disc','Lace-On','None'], font='Helvetica',
    default_value='Select Option', key='overl')],
    [psg.Text('Choose the Assembly Voltage: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option', 'HI', 'LO', 'FX', 'None'], font='Helvetica',
    default_value='Select Option', key='assymv')],
    [psg.Text('Choose the Lead End: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option','CW','CCW', 'REV', 'None'], font='Helvetica', default_value='Select Option',
    key='lead')],
    [psg.Text('Choose an Auxiliary Switch: ', size=(25,2), font='Helvetica', justification='left'), psg.Combo(['Select Option', 'Air', 'Line', 'Mech', 'On/Off', 'Pull Chain',
    'Relay', 'Rocker', 'SIMPAC', 'SSS', 'TRIAC', 'Volt', 'Multiple', 'None'], font='Helvetica', default_value='Select Option', key='auxsw')],
    [psg.Button('Search', font=('Helvetica')), psg.Button('Reset', font=('Helvetica')), psg.Exit(font=('Helvetica'))]]

right_col = [[psg.Listbox(values = diag_num, enable_events=True, font='Helvetica', select_mode = 'LISTBOX_SELECT_MODE_SINGLE', key='ConnDiag', size=(20,22))],
    [psg.Text('', size=(25,0))]]

layout = [[psg.Column(left_col, element_justification='c'), psg.Column(right_col, element_justification='c')]]
#Define the window
win = psg.Window('Connection Diagram Picker', layout, resizable=True)

然后，该程序将这些输入转换为1s和0，以便处理用户是否将下拉框保留为简单的“选择选项”而不是提供输入。这样，他们就可以搜索图表，而不需要精确地搜索。以下是这方面的代码：

if event == 'Search': #Searches based on combo values
        
        '''
        This event happens when the user clicks on the Search button in the GUI.
        When this happens, the program assigns the user inputs a Boolean value based on 1s and 0s.
        It then uses these values to perform basic logical tests to search through the Excel documents,
        to find the Connection diagram that matches that criteria input by the user.
        '''
        win['ConnDiag'].update([''])

        if v['switch'] != 'None' and v['switch'] != 'Select Option':
            switch = int(v['switch'])
        else:
            switch = v['switch']

        # Convert the inputs into Boolean logic assigned 1s and 0s
        if v['type'] == 'Select Option': typeBool = 0
        else: typeBool = 1
        
        if v['switch'] == 'Select Option': switchBool = 0
        else: switchBool = 1

        if v['board'] == 'Select Option': boardBool = 0
        else: boardBool = 1
        
        if v['volt'] == 'Select Option': voltBool = 0
        else: voltBool = 1

        if v['rot'] == 'Select Option': rotBool = 0
        else: rotBool = 1

        if v['start'] == 'Select Option': startBool = 0
        else: startBool = 1

        if v['overl'] == 'Select Option': overlBool = 0
        else: overlBool = 1
        
        if v['assymv'] == 'Select Option': assymvBool = 0
        else: assymvBool = 1

        if v['lead'] == 'Select Option': leadBool = 0
        else: leadBool = 1
        
        if v['auxsw'] == 'Select Option': auxswBool = 0
        else:auxswBool = 1

在那里，代码与Excel表交互使用Pandas。它将获取下拉框中的值，并在Excel表中执行搜索，查找用户输入这些规范的图表。然后，它将拉出这些图表的列表，并在列表框中显示它们。

# ---------------------------------------------------------------- All True ---------------------------------------------------------------- #

        if typeBool == 1 and switchBool == 1 and boardBool == 1 and voltBool == 1 and rotBool == 1 and startBool == 1 and overlBool == 1 and assymvBool == 1 and leadBool == 1 and auxswBool == 1:

            #Declare looping variables
            i = 0

            diag_num.clear()
            Torq.clear()

            while i < len(workbook):
                
                if workbook['Type:'][i] == v['type'] and workbook['TORQ:'][i] == switch and workbook['Voltage:'][i] == v['volt'] and workbook['Rotation:'][i] == v['rot'] and workbook['Start:'][i] == v['start'] and workbook['Overload:'][i] == v['overl'] and workbook['Assembly Voltage:'][i] == v['assymv'] and workbook['Lead:'][i] == v['lead'] and workbook['Aux Switch:'][i] == v['auxsw']:

                    #Read through the Excel file to find the matching diagrams and append the list.
                    diag_num.append(workbook['Connection Diagram:'][i])
                    Torq.append(workbook['TORQ:'][i])
                
                i += 1

            diagNum, torq = DelDupl(diag_num,Torq)

            if len(diagNum) == 0:
                psg.popup('ERROR!', 'No connection diagram found!')

            win['ConnDiag'].update(values = diagNum)

            if v['ConnDiag']:

                UserChoice = v['ConnDiag'][0]
                UserChoice = BuildFile(UserChoice)
                readFile(UserChoice)

        # ---------------------------------------------------------------- One True ---------------------------------------------------------------- #

        if typeBool == 1 and switchBool == 0 and boardBool == 0 and voltBool == 0 and rotBool == 0 and startBool == 0 and overlBool == 0 and assymvBool == 0 and leadBool == 0 and auxswBool == 0:

            #Declare looping variables
            i = 0

            diag_num.clear()
            Torq.clear()

            while i < len(workbook):

                if workbook['Type:'][i] == v['type']:

                    #Read through the Excel file to find the matching diagrams and append the list.
                    diag_num.append(workbook['Connection Diagram:'][i])
                    Torq.append(workbook['TORQ:'][i])
                
                i += 1

            diagNum, torq = DelDupl(diag_num,Torq)

            win['ConnDiag'].update(values = diagNum)

            if v['ConnDiag']:

                UserChoice = v['ConnDiag'][0]
                UserChoice = BuildFile(UserChoice)
                readFile(UserChoice)

这只是代码的一个片段。它继续前进，在1和0之间交替进行，基本上创建了一个庞大的真值表，根据列表框是否有用户的输入而发生事情。然后，代码检查这些输入是什么，然后通过Excel表查找具有与用户输入匹配的规范的图表。这中的函数应该不会有太大的区别，因为它们只是删除重复项并搜索图的pdfs。

所以，问题是，我的代码起作用了。它没有问题工作，所以它不需要修理。但是，我遇到的情况是，由于我拥有的连接图的数量以及它们的规范和我都希望确保将来我可以轻松地添加或减去更多的图表，而不需要太多的代码，我有超过1000个不同的组合，这些1s和0s可以使用。这里的每个块大约需要30行代码，所以当我完成时，我将有将近40,000行代码。我大约有11,000条线深，这只是太长的路要做。代码运行良好，而且还不慢。但是我想找到一种压缩代码的方法，这样我就不会疯狂地复制粘贴和编辑这么多代码。我知道一定有办法，但我似乎不知道该怎么做。

Answer 1

最后我弄明白了我需要做什么。最后，我使用列表并根据用户输入值将信息转储到它们中。然后从那里，代码检查每个列表中是否存在所有的项，然后返回这些项。

i = 0
j = 0

   while i < len(workbook):

        l_conn.append(workbook['Connection Diagram:'][i])
        l_TORQ.append(workbook['TORQ:'][i])

        if v['type'] == 'Select Option':
            l_type.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Type:'][i] == v['type']:
                l_type.append(workbook['Connection Diagram:'][i])

        if v['switch'] == 'Select Option':
            l_torq.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['TORQ:'][i] == int(v['switch']):
                l_torq.append(workbook['Connection Diagram:'][i])

        if v['board'] == 'Select Option':
            l_board.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Board:'][i] == v['board']:
                l_board.append(workbook['Connection Diagram:'][i])

        if v['volt'] == 'Select Option':
            l_volt.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Voltage:'][i] == v['volt']:
                l_volt.append(workbook['Connection Diagram:'][i])

        if v['rot'] == 'Select Option':
            l_rot.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Rotation:'][i] == v['rot']:
                l_rot.append(workbook['Connection Diagram:'][i])

        if v['start'] == 'Select Option':
            l_start.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Start:'][i] == v['start']:
                l_start.append(workbook['Connection Diagram:'][i])

        if v['overl'] == 'Select Option':
            l_overl.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Overload:'][i] == v['overl']:
                l_overl.append(workbook['Connection Diagram:'][i])

        if v['assymv'] == 'Select Option':
            l_assymv.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Assembly Voltage:'][i] == v['assymv']:
                l_assymv.append(workbook['Connection Diagram:'][i])

        if v['lead'] == 'Select Option':
            l_lead.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Lead:'][i] == v['lead']:
                l_lead.append(workbook['Connection Diagram:'][i])

        if v['auxsw'] == 'Select Option':
            l_auxsw.append(workbook['Connection Diagram:'][i])
        else:
            if workbook['Aux Switch:'][i] == v['auxsw']:
                l_auxsw.append(workbook['Connection Diagram:'][i])

        i += 1

    while j < len(l_conn):

        if l_conn[j] in l_type and l_conn[j] in l_torq and l_conn[j] in l_board and l_conn[j] in l_volt and l_conn[j] in l_rot and l_conn[j] in l_start and l_conn[j] in l_overl and l_conn[j] in l_assymv and l_conn[j] in l_lead and l_conn[j] in l_auxsw:

            diag_num.append(l_conn[j])
            Torq.append(l_TORQ[j])

        j += 1

Answer 2

不确定问题是什么.但这可能有帮助。

几乎所有相同的选项，很多行与文本和组合体，因此下面的布局方式可能更好。

import PySimpleGUI as psg

item_dict = {
    "type"   :  ('Choose the Type: ',
                ['Select Option', 'CSIR', 'CSCR', 'PSC', 'Poly', 'SP']),
    "switch" :  ('Choose the TORQ Switch: ',
                ['Select Option', '282070101', '282070111', '282070117', '282070118',
                                  '282070125', '282070201', '282070205', '282070251',
                                  '282070261', '282070303', '282070304', '282070352',
                                  '282070403', '282070451', '282070600', '282070601',
                                  '282070602', '282070701', '282070702', '282070901',
                                  '282070902', '282070903', '282080101', 'None']),
    "board"  :  ('Is there a Terminal Board: ',
                ['Select Option', 'Yes', 'No']),
    "volt"   :  ('Choose the Voltage: ',
                ['Select Option', 'Single', 'Double']),
    "rot"    :  ('Choose the Rotation: ',
                ['Select Option', 'Non-Reversible', 'Reversible']),
    "start"  :  ('Choose the Start: ',
                ['Select Option', 'Total', 'Half']),
    "overl"  :  ('Choose the Overload: ',
                ['Select Option', 'Disc', 'Lace-On', 'None']),
    "assymv" :  ('Choose the Assembly Voltage: ',
                ['Select Option', 'HI', 'LO', 'FX', 'None']),
    "lead"   :  ('Choose the Lead End: ',
                ['Select Option', 'CW', 'CCW', 'REV', 'None']),
    "auxsw"  :  ('Choose an Auxiliary Switch: ',
                ['Select Option', 'Air', 'Line', 'Mech', 'On/Off', 'Pull Chain',
                                  'Relay', 'Rocker', 'SIMPAC', 'SSS', 'TRIAC',
                                  'Volt', 'Multiple', 'None']),
}

item_list = ["type", "switch", "board", "volt", "rot", "start", "overl", "assymv",
    "lead", "auxsw" ]

font = ('Helvetica', 11)
psg.theme('LightBlue')
psg.set_options(font=font)


left_col = []
for item in item_list:
    item_text, item_list = item_dict[item]
    left_col.append([
        psg.Text(item_text, size=(25, 2)),
        psg.Combo(item_list, default_value=item_list[0], key=item),
    ])
left_col.append([psg.Button(text) for text in ('Search', 'Reset', 'Exit')])

right_col = [
    [psg.Listbox(values=diag_num, enable_events=True, key='ConnDiag', size=(20,22))],
    [psg.Text('', size=(25, 0))],
]

layout = [
    [psg.Column(left_col,  element_justification='c'),
     psg.Column(right_col, element_justification='c')],
]

win = psg.Window('Connection Diagram Picker', layout, resizable=True)

对于许多要检查是否为true的情况，请尝试使用内置函数all (针对您的情况)。

"""
# Replace code here by only one `all` statement
# Convert the inputs into Boolean logic assigned 1s and 0s
if v['type'] == 'Select Option': typeBool = 0
else: typeBool = 1

if v['switch'] == 'Select Option': switchBool = 0
else: switchBool = 1

...

if v['auxsw'] == 'Select Option': auxswBool = 0
else:auxswBool = 1

# ---------------------------------------------------------------- All True ---------------------------------------------------------------- #

if typeBool == 1 and switchBool == 1 and boardBool == 1 and voltBool == 1 and rotBool == 1 and startBool == 1 and overlBool == 1 and assymvBool == 1 and leadBool == 1 and auxswBool == 1:
"""
if all(map(lambda x:x != 'Select Option', item_list)):

同样，如果您在item_list中有很好的工作簿和项之间的映射设置，函数all仍然适用于您，可能如下所示

if all(map(lambda x:workbook['Type:'][i] == v[x], item_list)):

Answer 3

我认为你是在“艰难的道路上”。每当您需要枚举(手动)一个巨大的案例时，可能有一个更好的方法。

因此，下面假设您有一个很好的数据框架，其中包含了所有的信息，我认为您这样做是为了匹配选择。

这里的关键(如果您正在使用pandas...there，如果您在字典中有您的项目描述，则是其他方式)。使用pandas查询来匹配所选内容。您可以在组合框中手动创建此查询(如我在下面所做的)，将自动省略未选定的查询，而不是编写案例。

请注意，我只是在这里列出链接到数据的按钮列表，您可以手动制作按钮，这很好。正如我在注释中所指出的，如果窗口值中有与搜索条件无关的其他项，则需要在构造查询之前从值中省略它们。

下面的工作和做与我的机器上的玩具数据包括预期。回覆问题！:)

# gui search
import pandas as pd
import PySimpleGUI as psg

# some data

data = {    'Type':     ['CSIR', 'CSIR', 'Poly', 'Poly'],
            'Voltage':  ['Single', 'Single', 'Single', 'Double'],
            'Rotation': ['Reversible', 'Reversible', 'Non-Rev', 'Non-Rev'],
            'Diag':     [45, 63, 2001, 897]}

df = pd.DataFrame(data).set_index('Diag')
print(df)

# Some mock-up buttons....  use the df values...
psg.theme=('LightBlue')

buttons = {}
for c in df.columns:
    default='Select One'
    values = list(set(df[c]))
    b = psg.Combo(values=values, key=c, default_value=default)
    buttons[c] = b

# add the buttons from the list into your window...  This could be done several ways
# you could reference these buttons in your layout list individually, etc...
layout = []
for c in df.columns:
    layout.append([buttons[c],])
layout.append([psg.Button('Search'),])

win = psg.Window('example', layout)
e, v = win.read()
win.close()
print(v)

# make a query string for use with pandas dataframe query...
query = ""
# drop the ones that were not selected...
# note if you have other value objects in v besides the search items,
# you will need to remove them somehow
v = {k:v for k,v in v.items() if v != default}
for k, v in v.items():
    query += f'{k} == "{v}" and '
query += "True"  # cheap way of satisfying the last "AND"

print("\nmatches:")
result = df.query(query)
print(result)

print("\n\nget diagrams...:")
for diag in result.index.to_list():
    print('  ' + str(diag))

输出

(仅从电压组合体中选择“单一”)：

matches:
      Type Voltage    Rotation
Diag                          
45    CSIR  Single  Reversible
63    CSIR  Single  Reversible
2001  Poly  Single     Non-Rev


get diagrams...:
  45
  63
  2001