import os
import sys
from datetime import datetime
from tkinter import Tk, filedialog, Button
from tkinter import ttk
import fitz # PyMuPDF
from openpyxl import Workbook
def is_number_like(s):
try:
float(s)
return True
except ValueError:
return False
def remove_average_row(ws):
for row_num, row in enumerate(ws.iter_rows(min_row=1, max_col=1), start=1):
if row[0].value and '總分' in str(row[0].value):
for row_to_delete in range(row_num, len(ws['A']) + 1):
ws.delete_rows(row_num)
break
def start_conversion(root, start_button, progress_bar, progress_label):
# 禁用按鈕並顯示禁用效果
start_button.config(state="disabled", relief="sunken", text="轉檔中...")
# 啟用 tkinter 檔案選擇視窗
pdf_paths = filedialog.askopenfilenames(
title="選擇一個或多個 PDF 檔案",
filetypes=[("PDF Files", "*.pdf"), ("All Files", "*.*")]
)
if not pdf_paths:
print("❌ 沒有選擇任何 PDF，程序結束。")
start_button.config(state="normal", relief="raised", text="開始轉換") # 重新啟用按鈕
return
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
output_folder = filedialog.askdirectory(title="選擇儲存的資料夾") # 讓使用者選擇儲存位置
if not output_folder:
print("❌ 沒有選擇儲存資料夾，程序結束。")
start_button.config(state="normal", relief="raised", text="開始轉換") # 重新啟用按鈕
return
print("📄 開始擷取 PDF 文字並寫入 Excel...")
wb = Workbook()
sheet_names = []
subjects = []
use_tqdm = sys.stdout is not None # 判斷是否有 console
# 更新進度條最大值
progress_bar["maximum"] = len(pdf_paths)
for idx, pdf_path in enumerate(pdf_paths, start=1):
try:
doc = fitz.open(pdf_path)
sheet_name = f"a{idx}"
ws = wb.create_sheet(sheet_name)
row_num = 1
for page_num in range(len(doc)):
page = doc.load_page(page_num)
text = page.get_text().strip()
lines = text.split('\n')
for line in lines:
ws.cell(row=row_num, column=1, value=line)
row_num += 1
doc.close()
temp_text = ""
col_num = 1
row_num = 1
for row in ws.iter_rows(min_row=1, max_col=1):
cell_value = str(row[0].value).strip() if row[0].value is not None else ""
if is_number_like(cell_value):
ws.cell(row=row_num, column=col_num, value=cell_value)
col_num += 1
else:
if temp_text:
ws.cell(row=row_num, column=1, value=temp_text)
row_num += 1
temp_text = cell_value
col_num = 2
if temp_text:
ws.cell(row=row_num, column=1, value=temp_text)
remove_average_row(ws)
if ws['A11'].value:
new_sheet_name = str(ws['A11'].value).strip()
if new_sheet_name and len(new_sheet_name) <= 31:
ws.title = new_sheet_name
sheet_names.append(ws.title)
for row in ws.iter_rows(min_row=12, max_col=1):
cell_value = str(row[0].value).strip() if row[0].value is not None else ""
if cell_value and cell_value not in subjects:
subjects.append(cell_value)
except Exception as e:
print(f"⚠️ 錯誤：無法處理檔案 {pdf_path}，原因：{e}")
# 更新進度條並顯示進度
progress_bar["value"] = idx
progress_label.config(text=f"進度: {idx}/{len(pdf_paths)} 檔案處理中...")
root.update_idletasks()
if "Sheet" in wb.sheetnames:
del wb["Sheet"]
ws_summary = wb.create_sheet("Summary", 0)
for idx, sheet_name in enumerate(sheet_names, start=2):
ws_summary.cell(row=idx, column=1, value=sheet_name)
for idx, subject in enumerate(subjects, start=2):
ws_summary.cell(row=1, column=idx, value=subject)
ws_summary.cell(row=1, column=len(subjects) + 2, value="總分")
ws_summary.cell(row=1, column=len(subjects) + 3, value="平均")
for idx, sheet_name in enumerate(sheet_names, start=2):
student_ws = wb[sheet_name]
total_score = 0
total_weight = 0
for i, subject in enumerate(subjects, start=2):
subject_row = None
for row in student_ws.iter_rows(min_row=12, max_col=1):
if row[0].value == subject:
subject_row = row[0].row
break
if subject_row:
score = student_ws.cell(row=subject_row, column=2).value
weight = student_ws.cell(row=subject_row, column=3).value
if score is not None and weight is not None and is_number_like(str(score)) and is_number_like(str(weight)):
score = float(score)
weight = float(weight)
total_score += score * weight
total_weight += weight
ws_summary.cell(row=idx, column=i, value=score)
if total_weight > 0:
average_score = total_score / total_weight
ws_summary.cell(row=idx, column=len(subjects) + 2, value=total_score)
ws_summary.cell(row=idx, column=len(subjects) + 3, value=average_score)
final_excel_path = os.path.join(output_folder, f"滾球隊成績_{timestamp}.xlsx")
wb.save(final_excel_path)
print(f"✅ 所有 PDF 已轉成 Excel 並整理完畢，儲存於：{final_excel_path}")
# 轉檔完成後重新啟用按鈕
start_button.config(state="normal", relief="raised", text="開始轉換")
progress_label.config(text="轉檔完成！")
def create_gui():
# 設置 Tkinter 視窗
window = Tk()
window.title("PDF 轉 Excel 工具")
window.geometry("400x200")
# 設置按鈕來啟動轉換
start_button = Button(window, text="開始轉換", command=lambda: start_conversion(window, start_button, progress_bar, progress_label), height=2, width=20)
start_button.pack(pady=20)
# 設置進度條
progress_bar = ttk.Progressbar(window, length=300, mode="determinate")
progress_bar.pack(pady=10)
# 顯示進度的標籤
progress_label = ttk.Label(window, text="進度: 0/0 檔案處理中...")
progress_label.pack()
# 開始 Tkinter 介面的循環，直到視窗關閉
window.mainloop()
if __name__ == "__main__":
create_gui()