基于Python实现用户输入查询Excel接线图的代码精简方案咨询

云上老周

2026-9-27

接线图检索工具代码精简实现思路

我会尽可能清晰地说明问题，若表述不清后续会再调整措辞。
我目前有一批附带特定规格参数的接线图，已将所有接线图及其对应参数整理存入Excel表格，同时使用PySimpleGui搭建了供用户交互的GUI界面，界面内设置了对应参数的下拉选择框，用户选定参数作为输入后，代码将基于输入检索匹配的接线图。

现有实现代码

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)

输入参数布尔转换逻辑

后续程序会将用户输入转换为1和0的布尔值，用于识别用户是否保留了下拉框默认的“Select Option”未输入具体参数，从而支持用户非精确搜索接线图，这部分实现代码如下：

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

检索匹配逻辑

之后代码通过Pandas与Excel表格交互，基于用户输入的下拉框值检索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的参数组合搭建了庞大的真值表，针对用户是否输入对应参数的不同场景执行查询逻辑，匹配符合条件的接线图。代码中涉及的其他函数仅用于去重、检索接线图PDF文件，不影响核心逻辑。

当前问题

当前代码可正常运行无功能问题，但参数组合多达1000余种，每个组合对应约30行代码，完整代码量将接近40000行，目前已完成11000行代码开发，开发效率极低。同时为了后续方便增删接线图数据，需要找到精简代码的方案，避免大量重复的复制粘贴修改工作。

可行实现方案

核心思路是放弃枚举所有参数组合的写法，通过统一配置参数映射+动态拼接Pandas查询条件的方式，几十行代码即可覆盖所有查询场景：

首先统一维护参数配置，将GUI控件key、Excel对应列名、参数预处理逻辑整合为一个配置列表，避免重复写判断逻辑：

# 配置项格式：(GUI控件key, Excel对应列名, 参数值预处理函数)
PARAM_CONFIG = [
    ("type", "Type:", lambda x: x),
    ("switch", "TORQ:", lambda x: int(x) if x not in ("Select Option", "None") else x),
    ("board", "Terminal Board:", lambda x: x),
    ("volt", "Voltage:", lambda x: x),
    ("rot", "Rotation:", lambda x: x),
    ("start", "Start:", lambda x: x),
    ("overl", "Overload:", lambda x: x),
    ("assymv", "Assembly Voltage:", lambda x: x),
    ("lead", "Lead:", lambda x: x),
    ("auxsw", "Aux Switch:", lambda x: x),
]

搜索逻辑直接动态生成查询掩码，不需要逐个判断参数布尔值：

if event == 'Search':
    win['ConnDiag'].update([''])
    # 初始化全量匹配的掩码
    mask = pd.Series([True]*len(workbook), index=workbook.index)
    # 遍历配置动态拼接查询条件
    for gui_key, col_name, val_processor in PARAM_CONFIG:
        user_input = v[gui_key]
        # 用户没选该参数则跳过该条件
        if user_input == "Select Option":
            continue
        processed_val = val_processor(user_input)
        mask &= (workbook[col_name] == processed_val)
    # 直接过滤结果去重
    diagNum = workbook.loc[mask, "Connection Diagram:"].unique().tolist()
    # 后续逻辑和原有逻辑保持一致
    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)

额外优化点：

后续新增参数仅需在PARAM_CONFIG中新增一行配置即可，不需要修改查询逻辑
Excel列名调整、参数预处理逻辑修改也仅需调整对应配置项，维护成本极低
用Pandas向量化查询代替原来的逐行循环，查询速度会有明显提升

内容的提问来源于stack exchange，提问作者bingcs04

基于Python实现用户输入查询Excel接线图的代码精简方案咨询

现有实现代码 #

GUI界面实现 #

输入参数布尔转换逻辑 #

检索匹配逻辑 #

当前问题 #