在处理excel表格数据中编写的一个可以自动处理课表信息的脚本。

python">import os
import openpyxl
import re# 检查文件是否存在
file_name = "course_info.xlsx"
if os.path.exists(file_name):# 如果文件存在，先删除os.remove(file_name)# 创建一个新的 Excel 工作簿
workbook = openpyxl.Workbook()
sheet = workbook.active
# 写入表头
sheet.append(["序号", "课程名称", "周次", "星期", "节次", "上课地点", "任课老师"])
# 保存 Excel 文件
workbook.save(file_name)def append_data_to_excel(excel, new_data):# 打开已存在的 Excel 文件workbook = openpyxl.load_workbook(excel)# 选择要添加数据的工作表，这里假设工作表的名字是 "Sheet"sheet = workbook["Sheet"]# 准备要添加的新数据，例如一个列表# new_data = [num, CourseName, week, day, lesson, classroom, teacher]# 在工作表中添加新数据sheet.append(new_data)# 保存修改后的 Excel 文件workbook.save(excel)# workbook = xlrd.open_workbook('./文献检测情况汇总(持续更新).xlsx')
workbook = openpyxl.load_workbook('./课表数据.xlsx')
work_sheet = workbook['data']num = 0  # 初始化序号for row in work_sheet.iter_rows(values_only=True, min_row=2,):CourseName = row[2];CoureseTimeAddress = row[25];# print(CourseName,CoureseTimeAddress)try:message = CoureseTimeAddress.split(';')# print(message)except:# print("no message")continuefor m in message:if m == '':continuem = m.split()# print(m)week = m[0]day = m[1][:3]# 使用正则表达式提取方括号内的内容pattern = r'\[(.*?)\]'  # 匹配方括号内的任意字符，非贪婪模式matches = re.findall(pattern, m[1])lesson = matches[0]# print(lesson)# 使用正则表达式提取 "1-204" 模式pattern = r'\b\d+-\d+\b'  # 匹配数字-数字模式的单词边界matches = re.findall(pattern, m[1])classroom = matches[0]# print(classroom)teacher = m[2]# num = num + 1# print(num, CourseName, week, day, lesson, classroom, teacher)week = week.split(",")# print(week)for w in week:flag = 0if '单' in w:flag = 1if '周' not in w:w = w[:-1]else:w = w[:-2]elif '双' in w:flag = 2if '周' not in w:w = w[:-1]else:w = w[:-2]else:flag = 0if '周' in w:w = w[:-1]# print(w)if '-' in w:a, b = w.split('-')try:a, b = int(a), int(b)except:print("error")print(a,b)for i in range(a,b+1):if flag==1 and i%2==0:continueif flag==2 and i%2!=0:continuenum = num + 1new_data = [num, CourseName, i, day, lesson, classroom, teacher]print(num, CourseName, i, day, lesson, classroom, teacher)# 将数据存入excel表格append_data_to_excel("course_info.xlsx", new_data)else:num = num + 1w = int(w)new_data = [num, CourseName, w, day, lesson, classroom, teacher]print(num, CourseName, w, day, lesson, classroom, teacher)# 将数据存入excel表格append_data_to_excel("course_info.xlsx", new_data)