24建筑工程学院学习部旷课汇总程序
点击下方下载
Setup.exe
源码入下
import tkinter as tk
from tkinter import filedialog, messagebox
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import FormulaRule
import os
def read_excel_file(file_path, is_first_file):
try:
excel_file = pd.ExcelFile(file_path)
sheet_names = excel_file.sheet_names
csv_files = []
for sheet_name in sheet_names:
sheet = excel_file.parse(sheet_name)
csv_file = f"{sheet_name}-1.csv" if is_first_file else f"{sheet_name}-2.csv"
sheet.to_csv(csv_file, index=False, encoding="utf-8-sig")
csv_files.append(sheet_name)
return csv_files
except FileNotFoundError:
messagebox.showerror("错误", "未找到文件！")
except ValueError:
messagebox.showerror("错误", "只能读取 xlsx 类型文件！")
except ImportError:
messagebox.showerror("错误", "模块出错")
except Exception as e:
messagebox.showerror("错误", f"文件获取错误: {e}")
return []
def fix(m):
sortls = []
classes = ["土木工程（1）班", "土木工程（2）班",
"土木工程（3）班", "土木工程（4）班",
"城乡规划", "城市地下空间工程",
"工程管理（1）班", "工程管理（2）班",
"建筑学（1）班", "建筑学（2）班", "建筑学（3）班"]
for Class in classes:
sortls.append("".join([m[:-1], Class]))
csv1_path = f"{m}-1.csv"
data1 = []
if os.path.exists(csv1_path):
with open(csv1_path, "r", encoding="utf-8-sig") as f:
data1 = f.readlines()[1:]
csv2_path = f"{m}-2.csv"
data2 = []
if os.path.exists(csv2_path):
with open(csv2_path, "r", encoding="utf-8-sig") as fi:
data2 = fi.readlines()[1:]
dc = {}
for line in data1:
ls = line.strip("\n").replace(" ", "").split(",")
if ls[0].strip()[:-1] in sortls:
ls[0] = ls[0].strip()[:-1]
if "".join([ls[0].strip(), "班"]) in sortls:
ls[0] = "".join([ls[0], "班"])
dc[ls[0]] = dc.get(ls[0], []) + [ls[1:]]
for line in data2:
ls = line.strip("\n").replace(" ", "").split(",")
if ls[0][:-1].strip() in sortls:
ls[0] = ls[0][:-1].strip()
if "".join([ls[0].strip(), "班"]) in sortls:
ls[0] = "".join([ls[0].strip(), "班"])
dc[ls[0]] = dc.get(ls[0], []) + [ls[1:]]
df = {}
de = {}
for k, v in dc.items():
for i in v:
df[i[0]] = df.get(i[0], 0) + int(i[1])
lt = list(df.items())
lt.sort(key=lambda x: x[1], reverse=True)
de[k] = lt
df.clear()
dw = {}
for key in sortls:
try:
dw[key] = dw.get(key, []) + de[key]
except KeyError:
continue
with open(f"{m}.csv", "w", encoding="utf-8-sig") as f:
header = []
if data1:
with open(csv1_path, "r", encoding="utf-8-sig") as f_header:
header = f_header.readline().strip()
elif data2:
with open(csv2_path, "r", encoding="utf-8-sig") as f_header:
header = f_header.readline().strip()
if header:
f.write(f"{header}\n")
for k, v in dw.items():
for i in v:
f.write(f"{k},{i[0]},{i[1]}\n")
def merge_csv_to_excel(fix_need, end_name):
try:
with pd.ExcelWriter(end_name, engine="openpyxl") as writer:
for m in fix_need:
csv_path = f"{m}.csv"
if os.path.exists(csv_path):
sheet = pd.read_csv(csv_path, encoding="utf-8-sig")
sheet.to_excel(writer, sheet_name=m, index=False)
os.remove(csv_path)
except Exception as e:
messagebox.showerror("错误", f"写入错误: {e}")
def format_excel_file(end_name):
column_need = {"A": 20, "B": 16, "C": 11.6}
try:
wb = load_workbook(end_name)
thin_side = Side(style="thin", color="000000")
thin_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
for sheet in wb.worksheets:
for row in sheet.iter_rows():
for cell in row:
cell.alignment = Alignment(
horizontal="center",
vertical="center",
wrap_text=False
)
for column, width in column_need.items():
sheet.column_dimensions[column].width = width
max_row = sheet.max_row
max_column = sheet.max_column
for row in sheet.iter_rows(min_row=1, max_row=max_row, min_col=1, max_col=max_column):
for cell in row:
cell.border = thin_border
fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
rule = FormulaRule(formula=["$C2>=10"], stopIfTrue=True, fill=fill)
sheet.conditional_formatting.add(f"A2:C{max_row}", rule)
wb.save(end_name)
messagebox.showinfo("成功", "excel 表格自动处理成功")
except Exception as e:
messagebox.showerror("错误", f"excel 表格自动处理失败: {e}")
def select_first_file():
global first_file_path
first_file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
if first_file_path:
first_file_label.config(text=f"第一个文件: {first_file_path}")
def select_second_file():
global second_file_path
second_file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
if second_file_path:
second_file_label.config(text=f"第二个文件: {second_file_path}")
def process_files():
end_name = end_name_entry.get()
if not end_name.endswith(".xlsx"):
messagebox.showerror("错误", "请输入以 xlsx 为后缀的文件名")
return
if not first_file_path or not second_file_path:
messagebox.showerror("错误", "请选择两个 Excel 文件")
return
sheets1 = read_excel_file(first_file_path, True)
sheets2 = read_excel_file(second_file_path, False)
all_sheets = list(set(sheets1 + sheets2))
all_sheets.sort(key=lambda x:int(x[:-1]))
try:
for m in all_sheets:
fix(m)
csv1 = f"{m}-1.csv"
csv2 = f"{m}-2.csv"
if os.path.exists(csv1):
os.remove(csv1)
if os.path.exists(csv2):
os.remove(csv2)
except Exception as e:
messagebox.showerror("错误", f"读取错误: {e}")
return
merge_csv_to_excel(all_sheets, end_name)
format_excel_file(end_name)
root = tk.Tk()
root.title("旷课信息合并汇总")
root.geometry("400x300")
first_file_path = ""
second_file_path = ""
first_file_button = tk.Button(root, text="选择第一个文件", command=select_first_file)
first_file_button.pack(pady=10)
first_file_label = tk.Label(root, text="第一个文件: 未选择")
first_file_label.pack(pady=5)
second_file_button = tk.Button(root, text="选择第二个文件", command=select_second_file)
second_file_button.pack(pady=10)
second_file_label = tk.Label(root, text="第二个文件: 未选择")
second_file_label.pack(pady=5)
end_name_label = tk.Label(root, text="合并后的文件名:")
end_name_label.pack(pady=5)
end_name_entry = tk.Entry(root)
end_name_entry.pack(pady=5)
process_button = tk.Button(root, text="处理文件", command=process_files)
process_button.pack(pady=20)
root.mainloop()