Python Schedule Parsing Tool with GUI for Generating Class and Teacher Timetables

The script demonstrates how to transform a raw class schedule Excel sheet into organized timetables for each class and each teacher using Python libraries such as pandas , openpyxl , and PySimpleGUI . It first reads the source file, melts and cleans the data, then pivots it to produce the desired views.

Two main functions, get_class and get_teacher , build the Excel workbooks: they create worksheets, fill them with the processed data, merge cells for titles, apply bold fonts and center alignment, and finally save the files to a user‑specified directory.

A minimal graphical user interface is provided for authentication (hard‑coded admin/admin) and for selecting the input Excel file and output folder. After successful login, the user clicks “Start Processing”, the program validates the paths, calls the data‑processing functions, and prints progress messages in the GUI output area.

Below is the core implementation (kept intact inside ... tags):

import PySimpleGUI as sg
import sys
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
import os
import warnings
warnings.filterwarnings('ignore')

# Read original schedule data
def get_data(fileName):
    df = pd.read_excel(fileName, header=[1,2,3])
    data = df.melt(id_vars=df.columns[:1].to_list())
    data.columns = ['班级','星期','上下午','第N节课','课程_老师']
    data['课程'] = data['课程_老师'].apply(lambda s: s if '上' in s else s[:2])
    data['老师'] = data['课程_老师'].apply(lambda s: '' if '上' in s else s[2:]).str.strip().replace('', '无')
    data['星期'] = data['星期'].str.strip()
    return data

# Generate class timetables
def get_class(data, path):
    wb = Workbook()
    ws = wb.active
    for i in data['班级'].unique():
        result = data.query(f'班级=={i}').pivot(index=['第N节课'], columns='星期', values='课程')
        result = result[['星期一','星期二','星期三','星期四','星期五']]
        result.index = result.index.map(lambda x: f'第{x}节')
        result.reset_index(inplace=True)
        result = result.append(pd.Series({'': '第8节','星期一':'班/安'}), ignore_index=True)
        filename = r'按班级课程表.xlsx'
        name = f'{i}班'
        filepath = f"{path}/{filename}"
        title = f'课程表  {name}'
        value_list = [[title]]
        value_list.append(result.columns.to_list())
        temp = result.values.tolist()
        temp.insert(4, [])
        value_list.extend(temp)
        for row in value_list:
            ws.append(row)
        ws.merge_cells(f'A{(i-1)*11+1}:F{(i-1)*11+1}')
        cell = ws[f'A{(i-1)*11+1}']
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
        print(f'{name} 的课表已生成')
    wb.save(filepath)

# Generate teacher timetables (similar logic omitted for brevity)
# ...

# GUI for login
layout_login = [[sg.Text('账号：'), sg.Input(key='account_id')],
               [sg.Text('密码：'), sg.Input(password_char='*', key='password')],
               [sg.Button('      确定      '), sg.Button('      关闭      ')]]
window = sg.Window('请输入账号密码', layout=layout_login)
while True:
    event, values = window.read()
    if event in (None, '      关闭      '):
        sys.exit('程序关闭')
    elif values['account_id'] == 'admin' and values['password'] == 'admin':
        break
    else:
        sg.popup('账号密码不正确')
window.close()

# Main processing GUI
layout = [[sg.Text('选择课程表原始文件')],
          [sg.Input(key='fileName', enable_events=True), sg.FileBrowse('打开', file_types=(('Text Files', '*.xls*'),))],
          [sg.Text('选择解析结果存储地址')],
          [sg.Input(key='path', enable_events=True), sg.FolderBrowse('浏览')],
          [sg.Text('程序操作记录:', justification='center')],
          [sg.Output(size=(50,8))],
          [sg.Button('   开始处理   '), sg.Button('      关闭      ')]]
window = sg.Window('课程表解析工具', layout)
while True:
    event, values = window.read()
    if event in (None, '      关闭      '):
        break
    if event == '   开始处理   ':
        fileName = values['fileName']
        path = values['path']
        if os.path.exists(fileName) and os.path.exists(path):
            data = get_data(fileName)
            get_class(data, path)
            print('按班级课程表已经保存完毕..')
            # get_teacher(data, path)  # omitted for brevity
            print('按教师课程表已经保存完毕..')
        else:
            sg.popup('请选择正确的待处理文件及保存路径')
window.close()

By following the provided code and GUI workflow, users can quickly generate well‑formatted Excel timetables for both classes and teachers, and the script can be further extended or optimized as needed.