pip install pandas openpyxl customtkinter
pip install ctktable
pip install tksheet pandas openpyxl
pip install customtkinter tksheet pandas openpyxl
pip install customtkinter tksheet pandas openpyxl Pillow
V8
import json
import customtkinter as ctk
import pandas as pd
import string
import openpyxl
import os
import sys
import fitz # Thư viện PyMuPDF
from PIL import Image
from tkinter import filedialog, messagebox
from tksheet import Sheet
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import COLOR_INDEX
import ctypes
# Tên file lưu cấu hình
CONFIG_FILE = "app_config.json"
# --- THIẾT LẬP APP ID CHO TASKBAR ---
try:
myappid = 'nsoft.excelpro.osd.2.0'
ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID(myappid)
except:
pass
# =================================================================
# --- HỆ THỐNG TỪ ĐIỂN 15 NGÔN NGỮ ĐẦY ĐỦ ---
# =================================================================
LANG_DICT = {
"Tiếng Việt": {
"title": "📊 QUẢN LÝ EXCEL PRO", "coord": "📍 CẤU HÌNH VỊ TRÍ", "row_p": "Dòng (2)", "col_p": "Cột (A)",
"exclude": "🚫 DẢI CỘT LOẠI TRỪ", "from": "Từ (B)", "to": "Đến (C)",
"compare": "⚖️ CỘT SO SÁNH", "osd_lim": "Cột hạn mức OSD", "help_lim": "Cột hạn mức HELP",
"btn_process": "🚀 XỬ LÝ DỮ LIỆU", "btn_import": "📁 Nạp File", "btn_restore": "↩️ Khôi phục gốc",
"btn_clear": "🗑️ Xóa", "btn_export": "📤 Xuất Excel", "btn_readme": "📖 Hướng dẫn (PDF)",
"msg_success": "Thành công: Đã xử lý logic Max và hiển thị Debug.", "msg_err_input": "Lỗi: Thông số nhập vào không đúng!",
"status_ready": "Sẵn sàng. Vui lòng nạp file...", "status_done": "Đã xử lý xong dữ liệu.",
"select_sheet": "Chọn Sheet"
},
"English": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 DATA COORDINATES", "row_p": "Row", "col_p": "Col",
"exclude": "🚫 EXCLUDE COLUMNS", "from": "From", "to": "To",
"compare": "⚖️ COMPARISON COLS", "osd_lim": "OSD Limit Col", "help_lim": "HELP Limit Col",
"btn_process": "🚀 PROCESS DATA", "btn_import": "📁 Import File", "btn_restore": "↩️ Restore",
"btn_clear": "🗑️ Clear", "btn_export": "📤 Export Excel", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Success: Max logic processed.", "msg_err_input": "Error: Invalid parameters!",
"status_ready": "Ready. Please import file...", "status_done": "Processing completed.",
"select_sheet": "Select Sheet"
},
"日本語 (Nhật)": {
"title": "📊 Excel プロ管理", "coord": "📍 データ座標設定", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 除外列範囲", "from": "開始", "to": "終了",
"compare": "⚖️ 比較列設定", "osd_lim": "OSD 制限列", "help_lim": "HELP 制限列",
"btn_process": "🚀 データ実行", "btn_import": "📁 ファイル読込", "btn_restore": "↩️ 元に戻す",
"btn_clear": "🗑️ クリア", "btn_export": "📤 エクセル出力", "btn_readme": "📖 説明書 (PDF)",
"msg_success": "成功：ロジックが処理されました。", "msg_err_input": "エラー：入力が無効です。",
"status_ready": "準備完了。ファイルを読み込んでください。", "status_done": "処理が完了しました。",
"select_sheet": "シートを選択"
},
"韓国어 (Hàn)": {
"title": "📊 엑셀 마스터 프로", "coord": "📍 데이터 좌표 설정", "row_p": "행 (2)", "col_p": "열 (A)",
"exclude": "🚫 제외 열 범위", "from": "시작", "to": "끝",
"compare": "⚖️ 비교 열 설정", "osd_lim": "OSD 제한 열", "help_lim": "HELP 제한 열",
"btn_process": "🚀 데이터 처리", "btn_import": "📁 파일 불러오기", "btn_restore": "↩️ 원본 복구",
"btn_clear": "🗑️ 삭제", "btn_export": "📤 엑셀 내보내기", "btn_readme": "📖 설명서 (PDF)",
"msg_success": "성공: 로직 처리가 완료되었습니다.", "msg_err_input": "오류: 입력값이 잘못되었습니다.",
"status_ready": "준비됨. 파일을 불러오세요...", "status_done": "처리가 완료되었습니다.",
"select_sheet": "시트 선택"
},
"中文 (Trung)": {
"title": "📊 Excel 专业管理", "coord": "📍 数据坐标设置", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 排除列范围", "from": "从", "to": "到",
"compare": "⚖️ 比较列设置", "osd_lim": "OSD 限制列", "help_lim": "HELP 限制列",
"btn_process": "🚀 处理数据", "btn_import": "📁 导入文件", "btn_restore": "↩️ 恢复原始",
"btn_clear": "🗑️ 清除", "btn_export": "📤 导出 Excel", "btn_readme": "📖 使用说明 (PDF)",
"msg_success": "成功：逻辑已处理并显示调试。", "msg_err_input": "错误：参数输入无效！",
"status_ready": "就绪。请导入文件...", "status_done": "数据处理完成。",
"select_sheet": "选择工作表"
},
"Deutsch (Đức)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 KOORDINATEN", "row_p": "Zeile", "col_p": "Spalte",
"exclude": "🚫 SPALTEN AUSSCHLIESSEN", "from": "Von", "to": "Bis",
"compare": "⚖️ VERGLEICHSSPALTEN", "osd_lim": "OSD-Limit Spalte", "help_lim": "HELP-Limit Spalte",
"btn_process": "🚀 DATEN VERARBEITEN", "btn_import": "📁 Importieren", "btn_restore": "↩️ Wiederherstellen",
"btn_clear": "🗑️ Löschen", "btn_export": "📤 Exportieren", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Erfolg: Max-Logik verarbeitet.", "msg_err_input": "Fehler: Ungültige Parameter!",
"status_ready": "Bereit. Datei importieren...", "status_done": "Verarbeitung abgeschlossen.",
"select_sheet": "Blatt auswählen"
},
"Français (Pháp)": {
"title": "📊 GESTION EXCEL PRO", "coord": "📍 COORDONNÉES", "row_p": "Ligne", "col_p": "Col",
"exclude": "🚫 EXCLURE COLONNES", "from": "De", "to": "À",
"compare": "⚖️ COLONNES DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 TRAITER LES DONNÉES", "btn_import": "📁 Importer", "btn_restore": "↩️ Restaurer",
"btn_clear": "🗑️ Effacer", "btn_export": "📤 Exporter", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Succès : Logique Max traitée.", "msg_err_input": "Erreur : Paramètres invalides !",
"status_ready": "Prêt. Importer un fichier...", "status_done": "Traitement terminé.",
"select_sheet": "Choisir Feuille"
},
"Italiano (Ý)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 COORDINATE DATI", "row_p": "Riga", "col_p": "Col",
"exclude": "🚫 ESCLUDI COLONNE", "from": "Da", "to": "A",
"compare": "⚖️ COLONNE CONFRONTO", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 ELABORA DATI", "btn_import": "📁 Importa", "btn_restore": "Ripristina",
"btn_clear": "🗑️ Cancella", "btn_export": "📤 Esporta", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Successo: Logica Max elaborata.", "msg_err_input": "Errore: Parametri non validi!",
"status_ready": "Pronto. Importa file...", "status_done": "Elaborazione completata.",
"select_sheet": "Seleziona Foglio"
},
"Español (Tây Ban Nha)": {
"title": "📊 GESTIÓN EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Fila", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUMNAS", "from": "Desde", "to": "Hasta",
"compare": "⚖️ COLUMNAS DE COMP.", "osd_lim": "Límite OSD", "help_lim": "Límite HELP",
"btn_process": "🚀 PROCESAR DATOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Borrar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Éxito: Lógica Max procesada.", "msg_err_input": "Error: ¡Parámetros inválidos!",
"status_ready": "Listo. Importar archivo...", "status_done": "Procesamiento finalizado.",
"select_sheet": "Seleccionar Hoja"
},
"Português (Bồ Đào Nha)": {
"title": "📊 GESTÃO EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Linha", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUNUNAS", "from": "De", "to": "Para",
"compare": "⚖️ COLUNAS DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 PROCESSAR DADOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Limpar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Sucesso: Lógica Max processada.", "msg_err_input": "Erro: Parâmetros inválidos!",
"status_ready": "Pronto. Importar arquivo...", "status_done": "Processamento concluído.",
"select_sheet": "Selecionar Folha"
},
"Türkçe (Thổ Nhĩ Kỳ)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 VERİ KOORDİNATLARI", "row_p": "Satır", "col_p": "Sütun",
"exclude": "🚫 SÜTUNLARI HARİÇ TUT", "from": "Başlangıç", "to": "Bitiş",
"compare": "⚖️ KARŞILAŞTIRMA", "osd_lim": "OSD Sınır Sütunu", "help_lim": "HELP Sınır Sütunu",
"btn_process": "🚀 VERİYİ İŞLE", "btn_import": "📁 İçe Aktar", "btn_restore": "Geri Yükle",
"btn_clear": "🗑️ Temizle", "btn_export": "📤 Dışa Aktar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Başarılı: Max mantığı işlendi.", "msg_err_input": "Hata: Geçersiz parametreler!",
"status_ready": "Hazır. Dosya yükleyin...", "status_done": "İşlem tamamlandı.",
"select_sheet": "Sayfa Seç"
},
"Русский (Nga)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТЫ ДАННЫХ", "row_p": "Строка", "col_p": "Столбец",
"exclude": "🚫 ИСКЛЮЧИТЬ СТОЛБЦЫ", "from": "От", "to": "До",
"compare": "⚖️ СТОЛБЦЫ СРАВНЕНИЯ", "osd_lim": "Лимит OSD", "help_lim": "Лимит HELP",
"btn_process": "🚀 ОБРАБОТАТЬ", "btn_import": "📁 Импорт", "btn_restore": "Восстановить",
"btn_clear": "🗑️ Удалить", "btn_export": "📤 Экспорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успех: Логика Max обработана.", "msg_err_input": "Ошибка: Неверные параметры!",
"status_ready": "Готово. Импортируйте файл...", "status_done": "Обработка завершена.",
"select_sheet": "Выбрать лист"
},
"Українська (Ukraine)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТИ ДАНИХ", "row_p": "Рядок", "col_p": "Стовпець",
"exclude": "🚫 ВИКЛЮЧИТИ СТОВПЦІ", "from": "Від", "to": "До",
"compare": "⚖️ СТОВПЦІ ПОРІВНЯННЯ", "osd_lim": "Межа OSD", "help_lim": "Межа HELP",
"btn_process": "🚀 ОБРОБИТІ ДАНІ", "btn_import": "📁 Імпорт", "btn_restore": "Відновити",
"btn_clear": "🗑️ Видалити", "btn_export": "📤 Експорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успіх: Логіку Max обролено.", "msg_err_input": "Помилка: Неправильні параметри!",
"status_ready": "Готово. Імпортуйте файл...", "status_done": "Обробка завершена.",
"select_sheet": "Вибрати аркуш"
},
"Magyar (Hungary)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 ADATKOORDINÁTÁK", "row_p": "Sor", "col_p": "Oszlop",
"exclude": "🚫 OSZLOPOK KIHAGYÁSA", "from": "Ettől", "to": "Eddig",
"compare": "⚖️ ÖSSZEHASONLÍTÁS", "osd_lim": "OSD limit oszlop", "help_lim": "HELP limit oszlop",
"btn_process": "🚀 FELDOLGOZÁS", "btn_import": "📁 Betöltés", "btn_restore": "Visszaállítás",
"btn_clear": "🗑️ Törlés", "btn_export": "📤 Mentés", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Siker: Max logika feldolgozva.", "msg_err_input": "Hiba: Érvénytelen paraméterek!",
"status_ready": "Kész. Töltse be a fájlt...", "status_done": "Feldolgozás befejezve.",
"select_sheet": "Lap kiválasztása"
},
"Oʻzbekcha (Uzbek)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 MA'LUMOT KOORDINATASI", "row_p": "Qator", "col_p": "Ustun",
"exclude": "🚫 USTUNLARNI CHIQARISH", "from": "Dan", "to": "Gacha",
"compare": "⚖️ TAQQOSLASH USTUNLARI", "osd_lim": "OSD limiti ustuni", "help_lim": "HELP limiti ustuni",
"btn_process": "🚀 ISHLOV BERISH", "btn_import": "📁 Yuklash", "btn_restore": "Tiklash",
"btn_clear": "🗑️ O'chirish", "btn_export": "📤 Eksport", "btn_readme": "📖 Qo'llanma (PDF)",
"msg_success": "Muvaffaqiyat: Max mantiq ishlov berildi.", "msg_err_input": "Xato: Noto'g'ri parametrlar!",
"status_ready": "Tayyor. Faylni yuklang...", "status_done": "Ishlov berish yakunlandi.",
"select_sheet": "Varaqni tanlash"
},
"Mongolian(Mông cổ)": {
"title": "📊 EXCEL МАСТЕР ПРО", "coord": "📍 ӨГӨГДЛИЙН КООРДИНАТ", "row_p": "Мөр", "col_p": "Багана",
"exclude": "🚫 БАГАНУУДЫГ ХАСАХ", "from": "Эхлэх", "to": "Дуусах",
"compare": "⚖️ ХАРЬЦУУЛАХ БАГАНУУД", "osd_lim": "OSD Хязгаар багана", "help_lim": "Туслах хязгаар багана",
"btn_process": "🚀 ӨГӨГДӨЛ БОЛОВСРУУЛАХ", "btn_import": "📁 Файл оруулах", "btn_restore": "↩️ Сэргээх",
"btn_clear": "🗑️ Цэвэрлэх", "btn_export": "📤 Excel экспортлох", "btn_readme": "📖 Танилцуулга (PDF)",
"msg_success": "Амжилттай: Дээд логик боловсруулагдлаа.", "msg_err_input": "Алдаа: Буруу параметр!",
"status_ready": "Бэлэн. Файл оруулна уу...", "status_done": "Боловсруулалт дууслаа.",
"select_sheet": "Хуудас сонгох"
}
}
# =================================================================
# --- CỬA SỔ XEM PDF ---
# =================================================================
class PDFViewerWindow(ctk.CTkToplevel):
def __init__(self, master, file_path):
super().__init__(master)
self.title("Hướng dẫn sử dụng - PDF Viewer")
self.geometry("1000x850")
try:
self.pdf_doc = fitz.open(file_path)
self.current_page = 0
self.setup_ui()
self.show_p(0)
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể đọc file PDF: {e}")
self.destroy()
self.icon_path = r"D:\Python\T1\app_icon.ico"
if os.path.exists(self.icon_path):
self.iconbitmap(self.icon_path)
def setup_ui(self):
self.ctrl_frame = ctk.CTkFrame(self)
self.ctrl_frame.pack(side="top", fill="x", padx=10, pady=5)
ctk.CTkButton(self.ctrl_frame, text="<<", width=50, command=self.prev_p).pack(side="left", padx=10)
self.page_lab = ctk.CTkLabel(self.ctrl_frame, text="", font=("Arial", 12, "bold"))
self.page_lab.pack(side="left", expand=True)
ctk.CTkButton(self.ctrl_frame, text=">>", width=50, command=self.next_p).pack(side="right", padx=10)
self.scroll_frame = ctk.CTkScrollableFrame(self)
self.scroll_frame.pack(fill="both", expand=True, padx=10, pady=10)
self.img_label = ctk.CTkLabel(self.scroll_frame, text="")
self.img_label.pack()
def show_p(self, n):
if 0 <= n < len(self.pdf_doc):
page = self.pdf_doc.load_page(n)
pix = page.get_pixmap(matrix=fitz.Matrix(1.5, 1.5))
img = Image.frombytes("RGB", [pix.width, pix.height], pix.samples)
ctk_img = ctk.CTkImage(light_image=img, dark_image=img, size=(pix.width, pix.height))
self.img_label.configure(image=ctk_img)
self.current_page = n
self.page_lab.configure(text=f"Trang {n + 1} / {len(self.pdf_doc)}")
def prev_p(self): self.show_p(self.current_page - 1)
def next_p(self): self.show_p(self.current_page + 1)
# =================================================================
# --- CHƯƠNG TRÌNH CHÍNH (PREMIUM EDITION) ---
# =================================================================
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - OSD Ultimate (Premium Edition)")
self.geometry("1550x900")
self.icon_path = r"D:\Python\T1\app_icon.ico"
if os.path.exists(self.icon_path):
self.iconbitmap(self.icon_path)
self.current_file_path = None
self.wb = None
self.backup_data = None
self.backup_highlights = None
self.backup_headers = None
self.grid_columnconfigure(1, weight=1)
self.grid_rowconfigure(1, weight=1)
self.setup_top_bar()
self.setup_sidebar()
self.setup_work_area()
self.setup_status_bar()
# Tải cấu hình đã lưu (Theme & Ngôn ngữ)
self.restore_settings()
def save_settings(self):
"""Lưu lựa chọn theme và ngôn ngữ vào file json"""
settings = {
"theme": self.theme_menu.get(),
"language": self.lang_menu.get()
}
try:
with open(CONFIG_FILE, "w", encoding="utf-8") as f:
json.dump(settings, f, ensure_ascii=False, indent=4)
except:
pass
def restore_settings(self):
"""Khôi phục theme và ngôn ngữ khi khởi động"""
if os.path.exists(CONFIG_FILE):
try:
with open(CONFIG_FILE, "r", encoding="utf-8") as f:
settings = json.load(f)
lang = settings.get("language", "Tiếng Việt")
theme = settings.get("theme", "Light")
self.lang_menu.set(lang)
self.update_ui_text(lang)
self.theme_menu.set(theme)
self.change_theme(theme)
return
except:
pass
# Mặc định nếu không có cấu hình
self.update_ui_text("Tiếng Việt")
self.change_theme("Light")
def setup_top_bar(self):
self.top_bar = ctk.CTkFrame(self, height=75, corner_radius=0, fg_color="#FFFFFF", border_width=1, border_color="#E0E0E0")
self.top_bar.grid(row=0, column=0, columnspan=2, sticky="ew")
self.logo_label = ctk.CTkLabel(self.top_bar, text="", font=("Segoe UI", 24, "bold"), text_color="#1A5276")
self.logo_label.pack(side="left", padx=35)
self.btn_export = ctk.CTkButton(self.top_bar, text="", width=120, height=40, fg_color="#27AE60", hover_color="#1E8449", font=("Arial", 12, "bold"), command=self.export_excel)
self.btn_export.pack(side="right", padx=(5, 35))
self.btn_import = ctk.CTkButton(self.top_bar, text="", width=120, height=40, fg_color="#2980B9", hover_color="#21618C", font=("Arial", 12, "bold"), command=self.import_excel)
self.btn_import.pack(side="right", padx=5)
self.sheet_menu = ctk.CTkOptionMenu(self.top_bar, values=["Chọn Sheet"], command=self.load_specific_sheet, width=160, height=40, fg_color="#5D6D7E")
self.sheet_menu.pack(side="right", padx=10)
self.lang_menu = ctk.CTkOptionMenu(self.top_bar, values=list(LANG_DICT.keys()), command=self.update_ui_text, width=140, height=40)
self.lang_menu.pack(side="right", padx=10)
themes = ["Light", "Dark", "System", "Azure", "Emerald", "Sand", "Amethyst", "Midnight", "Forest", "Rose"]
self.theme_menu = ctk.CTkOptionMenu(
self.top_bar,
values=themes,
command=self.change_theme,
width=120,
height=40,
fg_color="#34495E"
)
self.theme_menu.pack(side="right", padx=10)
def change_theme(self, name):
dark_modes = ["Dark", "Midnight", "Forest", "Amethyst"]
mode = "dark" if name in dark_modes else "light"
ctk.set_appearance_mode(mode)
theme_config = {
"Light": {"bg": "#FFFFFF", "side": "#7DD8D8", "sheet": "light blue"},
"Dark": {"bg": "#1C1C1C", "side": "#262626", "sheet": "dark blue"},
"System": {"bg": "#FFFFFF", "side": "#F8F9F9", "sheet": "light blue"},
"Azure": {"bg": "#F0F7FF", "side": "#E1EFFF", "sheet": "light blue"},
"Emerald": {"bg": "#F1FAF5", "side": "#E3F2E9", "sheet": "light green"},
"Sand": {"bg": "#FDFCF0", "side": "#F7F3D7", "sheet": "light yellow"},
"Amethyst": {"bg": "#1A1625", "side": "#2D263D", "sheet": "dark purple"},
"Midnight": {"bg": "#0B0E14", "side": "#151921", "sheet": "dark black"},
"Forest": {"bg": "#111A11", "side": "#1B291B", "sheet": "dark green"},
"Rose": {"bg": "#FFF5F7", "side": "#FFE4E9", "sheet": "light red"}
}
cfg = theme_config.get(name, theme_config["Light"])
self.top_bar.configure(fg_color=cfg["bg"], border_color="#D0D0D0" if mode=="light" else "#404040")
self.work_area.configure(fg_color=cfg["bg"])
self.sidebar.configure(fg_color=cfg["side"])
self.status_bar.configure(fg_color=cfg["side"])
self.sheet.change_theme(cfg["sheet"])
self.sheet.redraw()
# Lưu cài đặt theme
self.save_settings()
def setup_sidebar(self):
self.sidebar = ctk.CTkFrame(self, width=310, corner_radius=0, fg_color="#F8F9F9")
self.sidebar.grid(row=1, column=0, sticky="nsew")
self.group_coord = ctk.CTkFrame(self.sidebar, fg_color="transparent")
self.group_coord.pack(fill="x", padx=25, pady=(35, 15))
self.coord_lab = ctk.CTkLabel(self.group_coord, text="", font=("Arial", 13, "bold"), text_color="#34495E")
self.coord_lab.pack(anchor="w", pady=(0, 5))
f_rc = ctk.CTkFrame(self.group_coord, fg_color="transparent")
f_rc.pack(fill="x")
self.row_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Dòng")
self.row_start_entry.insert(0, "2")
self.row_start_entry.pack(side="left", padx=(0, 5))
self.col_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Cột")
self.col_start_entry.insert(0, "K")
self.col_start_entry.pack(side="left")
self.ex_card = ctk.CTkFrame(self.sidebar, fg_color="#FDEDEC", corner_radius=15, border_width=1, border_color="#FADBD8")
self.ex_card.pack(fill="x", padx=20, pady=15)
self.exclude_lab = ctk.CTkLabel(self.ex_card, text="", text_color="#C0392B", font=("Arial", 12, "bold"))
self.exclude_lab.pack(pady=(12, 0))
f_ex = ctk.CTkFrame(self.ex_card, fg_color="transparent")
f_ex.pack(pady=15)
self.exclude_start_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Từ")
self.exclude_start_entry.pack(side="left", padx=5)
self.exclude_end_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Đến")
self.exclude_end_entry.pack(side="left", padx=5)
self.lim_card = ctk.CTkFrame(self.sidebar, fg_color="#EBF5FB", corner_radius=15, border_width=1, border_color="#D6EAF8")
self.lim_card.pack(fill="x", padx=20, pady=15)
self.compare_lab = ctk.CTkLabel(self.lim_card, text="", text_color="#2E86C1", font=("Arial", 12, "bold"))
self.compare_lab.pack(pady=8)
self.osd_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.osd_limit_col_entry.pack(pady=5, padx=15)
self.help_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.help_limit_col_entry.pack(pady=(5, 15), padx=15)
self.btn_process = ctk.CTkButton(self.sidebar, text="", height=60, font=("Arial", 16, "bold"), fg_color="#E67E22", hover_color="#D35400", command=self.process_advanced_logic)
self.btn_process.pack(fill="x", padx=25, pady=(25, 15))
self.btn_restore = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#7F8C8D", hover_color="#707B7C", command=self.restore_backup)
self.btn_restore.pack(fill="x", padx=25, pady=5)
self.btn_readme = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#515A5A", hover_color="#424949", command=self.open_readme_smart)
self.btn_readme.pack(fill="x", padx=25, pady=5)
self.btn_clear = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#C0392B", hover_color="#922B21", command=self.clear_file)
self.btn_clear.pack(fill="x", padx=25, pady=5)
def setup_work_area(self):
self.work_area = ctk.CTkFrame(self, fg_color="#FFFFFF", corner_radius=0)
self.work_area.grid(row=1, column=1, sticky="nsew", padx=2, pady=2)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
def setup_status_bar(self):
self.status_bar = ctk.CTkFrame(self, height=35, corner_radius=0, fg_color="#F2F4F4", border_width=1, border_color="#E0E0E0")
self.status_bar.grid(row=2, column=0, columnspan=2, sticky="ew")
self.status_label = ctk.CTkLabel(self.status_bar, text="", font=("Arial", 11), text_color="#566573")
self.status_label.pack(side="left", padx=30)
ctk.CTkLabel(self.status_bar, text="Premium Version 2.0 | © 2026", font=("Arial", 10, "italic"), text_color="#ABB2B9").pack(side="right", padx=25)
def update_ui_text(self, choice):
d = LANG_DICT.get(choice, LANG_DICT["Tiếng Việt"])
self.logo_label.configure(text=d["title"])
self.coord_lab.configure(text=d["coord"])
self.exclude_lab.configure(text=d["exclude"])
self.compare_lab.configure(text=d["compare"])
self.btn_process.configure(text=d["btn_process"])
self.btn_import.configure(text=d["btn_import"])
self.btn_export.configure(text=d["btn_export"])
self.btn_restore.configure(text=d["btn_restore"])
self.btn_readme.configure(text=d["btn_readme"])
self.btn_clear.configure(text=d["btn_clear"])
self.status_label.configure(text=d["status_ready"])
self.osd_limit_col_entry.configure(placeholder_text=d["osd_lim"])
self.help_limit_col_entry.configure(placeholder_text=d["help_lim"])
self.exclude_start_entry.configure(placeholder_text=d["from"])
self.exclude_end_entry.configure(placeholder_text=d["to"])
current_val = self.sheet_menu.get()
sheet_placeholders = ["Chọn Sheet", "Select Sheet", "シートを選択", "시트 선택", "选择工作表", "Blatt auswählen", "Choisir Feuille", "Seleziona Foglio", "Seleccionar Hoja", "Selecionar Folha", "Sayfa Seç", "Выбрать лист", "Вибрати аркуш", "Lap kiválasztása", "Varaqni tanlash","Хуудас сонгох"]
if current_val in sheet_placeholders:
self.sheet_menu.set(d["select_sheet"])
# Lưu cài đặt ngôn ngữ
self.save_settings()
def col_to_idx(self, letter):
if not letter: return -1
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def get_actual_hex(self, fill_obj):
if not fill_obj or not fill_obj.start_color: return None
clr = fill_obj.start_color
rgb = None
if clr.type == 'rgb' and clr.rgb: rgb = str(clr.rgb)
elif clr.type == 'theme' and clr.theme is not None:
themes = ['ffffff', '000000', 'eeece1', '1f497d', '4f81bd', 'c0504d', '9bbb59', '8064a2', '4bacc6', 'f79646']
try: rgb = themes[clr.theme]
except: return None
elif clr.type == 'indexed' and clr.indexed is not None:
try: rgb = COLOR_INDEX[clr.indexed]
except: return None
if not rgb or rgb == '00000000': return None
if len(rgb) == 8: rgb = rgb[2:]
try:
r, g, b = int(rgb[0:2], 16), int(rgb[2:4], 16), int(rgb[4:6], 16)
if clr.tint:
t = clr.tint
if t < 0: r, g, b = int(r*(1+t)), int(g*(1+t)), int(b*(1+t))
else: r, g, b = int(r+(255-r)*t), int(g+(255-g)*t), int(b+(255-b)*t)
return f'#{max(0, min(255, r)):02x}{max(0, min(255, g)):02x}{max(0, min(255, b)):02x}'
except: return None
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if path:
self.current_file_path = path
try:
self.wb = openpyxl.load_workbook(path, data_only=True)
sheets = self.wb.sheetnames
self.sheet_menu.configure(values=sheets)
self.sheet_menu.set(sheets[0])
self.load_specific_sheet(sheets[0])
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể mở file: {e}")
def load_specific_sheet(self, sheet_name):
if not self.wb: return
self.status_label.configure(text=f"📂 Loading Sheet: {sheet_name}...")
self.update()
try:
ws = self.wb[sheet_name]
max_r, max_c = ws.max_row, ws.max_column
data = [[(ws.cell(row=r, column=c).value if ws.cell(row=r, column=c).value is not None else "") for c in range(1, max_c + 1)] for r in range(1, max_r + 1)]
self.sheet.set_sheet_data(data, redraw=False)
self.sheet.headers([self.idx_to_col(i) for i in range(max_c)])
self.sheet.dehighlight_cells()
for r_idx in range(1, max_r + 1):
for c_idx in range(1, max_c + 1):
bg = self.get_actual_hex(ws.cell(row=r_idx, column=c_idx).fill)
if bg and bg.lower() != "#ffffff":
self.sheet.highlight_cells(row=r_idx-1, column=c_idx-1, bg=bg, redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✅ {sheet_name} | {max_r} Rows.")
except Exception as e:
messagebox.showerror("Sheet Error", f"Failed to load data: {e}")
def process_advanced_logic(self):
d = LANG_DICT.get(self.lang_menu.get(), LANG_DICT["Tiếng Việt"])
try:
start_row = int(self.row_start_entry.get() or 2) - 1
start_col_idx = self.col_to_idx(self.col_start_entry.get())
s_ex, e_ex = self.exclude_start_entry.get().strip().upper(), self.exclude_end_entry.get().strip().upper()
exclude_indices = []
if s_ex and e_ex:
si, ei = self.col_to_idx(s_ex), self.col_to_idx(e_ex)
exclude_indices = list(range(min(si, ei), max(si, ei) + 1))
orig_osd_idx = self.col_to_idx(self.osd_limit_col_entry.get())
orig_help_idx = self.col_to_idx(self.help_limit_col_entry.get())
if orig_osd_idx < 0 or orig_help_idx < 0: raise ValueError()
except:
messagebox.showerror("Error", d["msg_err_input"])
return
old_data = self.sheet.get_sheet_data()
if not old_data: return
self.backup_data = [row[:] for row in old_data]
self.backup_headers = list(self.sheet.headers())
self.backup_highlights = self.sheet.get_highlighted_cells().copy()
highlighted = self.sheet.get_highlighted_cells()
new_data, final_headers, new_highlights = [], [], {}
original_header_row = old_data[0]
for r_idx, row in enumerate(old_data):
processed_row = []
t_osd_w, t_osd_l = 0, 0
t_help_w, t_help_l = 0, 0
m_w_osd, m_l_osd = 0, 0
m_w_help, m_l_help = 0, 0
curr_c = 0
for c_idx, val in enumerate(row):
processed_row.append(val)
if r_idx == 0: final_headers.append(val if val else self.idx_to_col(c_idx))
if (r_idx, c_idx) in highlighted: new_highlights[(r_idx, curr_c)] = highlighted[(r_idx, c_idx)]
curr_c += 1
if c_idx >= start_col_idx:
lw, wc = 0, 0
h_name = str(original_header_row[c_idx]).upper() if original_header_row[c_idx] else ""
if r_idx == 0:
lw, wc = f"LW_{final_headers[curr_c-1]}", f"WC_{final_headers[curr_c-1]}"
elif r_idx >= start_row:
txt = str(val).strip()
if txt and txt.lower() not in ["none", "nan"]:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
lw = len(max(words, key=len)) if words else 0
wc = len(txt.split())
if c_idx == orig_osd_idx: t_osd_w, t_osd_l = lw, wc
if c_idx == orig_help_idx: t_help_w, t_help_l = lw, wc
if c_idx not in exclude_indices:
if "HELP" in h_name: m_w_help, m_l_help = max(m_w_help, lw), max(m_l_help, wc)
else: m_w_osd, m_l_osd = max(m_w_osd, lw), max(m_l_osd, wc)
new_highlights[(r_idx, curr_c)] = "#08DBDB"
new_highlights[(r_idx, curr_c + 1)] = "#08DBDB"
processed_row.extend([lw, wc])
if r_idx == 0: final_headers.extend([lw, wc])
curr_c += 2
if r_idx == 0:
res_h = ["MAX Word OSD", "RES Word OSD", "MAX Word HELP", "RES Word HELP", "MAX Len OSD", "RES Len OSD", "MAX Len HELP", "RES Len HELP"]
processed_row.extend(res_h); final_headers.extend(res_h)
else:
processed_row.extend([m_w_osd, "TRUE" if m_w_osd >= t_osd_w else "FALSE", m_w_help, "TRUE" if m_w_help >= t_help_w else "FALSE",
m_l_osd, "TRUE" if m_l_osd >= t_osd_l else "FALSE", m_l_help, "TRUE" if m_l_help >= t_help_l else "FALSE"])
new_data.append(processed_row)
self.sheet.set_sheet_data(new_data, redraw=False)
self.sheet.headers(final_headers)
self.sheet.dehighlight_cells()
for pos, color in new_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
for c in range(len(final_headers) - 8, len(final_headers)): self.sheet.highlight_cells(row=0, column=c, bg="#FFD700", redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✨ {d['status_done']}")
messagebox.showinfo("Success", d["msg_success"])
def restore_backup(self):
if self.backup_data:
self.sheet.dehighlight_cells()
self.sheet.headers(self.backup_headers)
self.sheet.set_sheet_data(self.backup_data, redraw=True)
for pos, color in self.backup_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
self.sheet.redraw()
def clear_file(self):
self.sheet.set_sheet_data([], redraw=True)
self.sheet.headers([])
self.sheet.dehighlight_cells()
def export_excel(self):
data = self.sheet.get_sheet_data()
if not data: return
path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel Files", "*.xlsx")])
if path:
wb = openpyxl.Workbook()
ws = wb.active
h_cells = self.sheet.get_highlighted_cells()
for r_idx, row in enumerate(data, 1):
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=val)
if (r_idx-1, c_idx-1) in h_cells:
color_val = h_cells[(r_idx-1, c_idx-1)]
hex_c = (color_val[0] if isinstance(color_val, (tuple, list)) else color_val).replace("#", "")
cell.fill = PatternFill(start_color=hex_c, end_color=hex_c, fill_type="solid")
wb.save(path)
def open_readme_smart(self):
base_dir = r"D:\Python\T1"
target = None
for f in ["readme.pdf", "readme.docx"]:
p = os.path.join(base_dir, f)
if os.path.exists(p): target = p; break
if not target: return
if target.endswith(".pdf"): PDFViewerWindow(self, target)
else:
if sys.platform == "win32": os.startfile(target)
if __name__ == "__main__":
app = ExcelMasterTool()
app.mainloop()
v7
import customtkinter as ctk
import pandas as pd
import string
import openpyxl
import os
import sys
import fitz # Thư viện PyMuPDF
from PIL import Image
from tkinter import filedialog, messagebox
from tksheet import Sheet
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import COLOR_INDEX
import ctypes
# --- THIẾT LẬP APP ID CHO TASKBAR ---
try:
myappid = 'nsoft.excelpro.osd.2.0'
ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID(myappid)
except:
pass
# =================================================================
# --- HỆ THỐNG TỪ ĐIỂN 15 NGÔN NGỮ ĐẦY ĐỦ ---
# =================================================================
LANG_DICT = {
"Tiếng Việt": {
"title": "📊 QUẢN LÝ EXCEL PRO", "coord": "📍 CẤU HÌNH VỊ TRÍ", "row_p": "Dòng (2)", "col_p": "Cột (A)",
"exclude": "🚫 DẢI CỘT LOẠI TRỪ", "from": "Từ (B)", "to": "Đến (C)",
"compare": "⚖️ CỘT SO SÁNH", "osd_lim": "Cột hạn mức OSD", "help_lim": "Cột hạn mức HELP",
"btn_process": "🚀 XỬ LÝ DỮ LIỆU", "btn_import": "📁 Nạp File", "btn_restore": "↩️ Khôi phục gốc",
"btn_clear": "🗑️ Xóa", "btn_export": "📤 Xuất Excel", "btn_readme": "📖 Hướng dẫn (PDF)",
"msg_success": "Thành công: Đã xử lý logic Max và hiển thị Debug.", "msg_err_input": "Lỗi: Thông số nhập vào không đúng!",
"status_ready": "Sẵn sàng. Vui lòng nạp file...", "status_done": "Đã xử lý xong dữ liệu.",
"select_sheet": "Chọn Sheet"
},
"English": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 DATA COORDINATES", "row_p": "Row", "col_p": "Col",
"exclude": "🚫 EXCLUDE COLUMNS", "from": "From", "to": "To",
"compare": "⚖️ COMPARISON COLS", "osd_lim": "OSD Limit Col", "help_lim": "HELP Limit Col",
"btn_process": "🚀 PROCESS DATA", "btn_import": "📁 Import File", "btn_restore": "↩️ Restore",
"btn_clear": "🗑️ Clear", "btn_export": "📤 Export Excel", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Success: Max logic processed.", "msg_err_input": "Error: Invalid parameters!",
"status_ready": "Ready. Please import file...", "status_done": "Processing completed.",
"select_sheet": "Select Sheet"
},
"日本語 (Nhật)": {
"title": "📊 Excel プロ管理", "coord": "📍 データ座標設定", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 除外列範囲", "from": "開始", "to": "終了",
"compare": "⚖️ 比較列設定", "osd_lim": "OSD 制限列", "help_lim": "HELP 制限列",
"btn_process": "🚀 データ実行", "btn_import": "📁 ファイル読込", "btn_restore": "↩️ 元に戻す",
"btn_clear": "🗑️ クリア", "btn_export": "📤 エクセル出力", "btn_readme": "📖 説明書 (PDF)",
"msg_success": "成功：ロジックが処理されました。", "msg_err_input": "エラー：入力が無効です。",
"status_ready": "準備完了。ファイルを読み込んでください。", "status_done": "処理が完了しました。",
"select_sheet": "シートを選択"
},
"韓国어 (Hàn)": {
"title": "📊 엑셀 마스터 프로", "coord": "📍 데이터 좌표 설정", "row_p": "행 (2)", "col_p": "열 (A)",
"exclude": "🚫 제외 열 범위", "from": "시작", "to": "끝",
"compare": "⚖️ 비교 열 설정", "osd_lim": "OSD 제한 열", "help_lim": "HELP 제한 열",
"btn_process": "🚀 데이터 처리", "btn_import": "📁 파일 불러오기", "btn_restore": "↩️ 원본 복구",
"btn_clear": "🗑️ 삭제", "btn_export": "📤 엑셀 내보내기", "btn_readme": "📖 설명서 (PDF)",
"msg_success": "성공: 로직 처리가 완료되었습니다.", "msg_err_input": "오류: 입력값이 잘못되었습니다.",
"status_ready": "준비됨. 파일을 불러오세요...", "status_done": "처리가 완료되었습니다.",
"select_sheet": "시트 선택"
},
"中文 (Trung)": {
"title": "📊 Excel 专业管理", "coord": "📍 数据坐标设置", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 排除列范围", "from": "从", "to": "到",
"compare": "⚖️ 比较列设置", "osd_lim": "OSD 限制列", "help_lim": "HELP 限制列",
"btn_process": "🚀 处理数据", "btn_import": "📁 导入文件", "btn_restore": "↩️ 恢复原始",
"btn_clear": "🗑️ 清除", "btn_export": "📤 导出 Excel", "btn_readme": "📖 使用说明 (PDF)",
"msg_success": "成功：逻辑已处理并显示调试。", "msg_err_input": "错误：参数输入无效！",
"status_ready": "就绪。请导入文件...", "status_done": "数据处理完成。",
"select_sheet": "选择工作表"
},
"Deutsch (Đức)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 KOORDINATEN", "row_p": "Zeile", "col_p": "Spalte",
"exclude": "🚫 SPALTEN AUSSCHLIESSEN", "from": "Von", "to": "Bis",
"compare": "⚖️ VERGLEICHSSPALTEN", "osd_lim": "OSD-Limit Spalte", "help_lim": "HELP-Limit Spalte",
"btn_process": "🚀 DATEN VERARBEITEN", "btn_import": "📁 Importieren", "btn_restore": "↩️ Wiederherstellen",
"btn_clear": "🗑️ Löschen", "btn_export": "📤 Exportieren", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Erfolg: Max-Logik verarbeitet.", "msg_err_input": "Fehler: Ungültige Parameter!",
"status_ready": "Bereit. Datei importieren...", "status_done": "Verarbeitung abgeschlossen.",
"select_sheet": "Blatt auswählen"
},
"Français (Pháp)": {
"title": "📊 GESTION EXCEL PRO", "coord": "📍 COORDONNÉES", "row_p": "Ligne", "col_p": "Col",
"exclude": "🚫 EXCLURE COLONNES", "from": "De", "to": "À",
"compare": "⚖️ COLONNES DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 TRAITER LES DONNÉES", "btn_import": "📁 Importer", "btn_restore": "↩️ Restaurer",
"btn_clear": "🗑️ Effacer", "btn_export": "📤 Exporter", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Succès : Logique Max traitée.", "msg_err_input": "Erreur : Paramètres invalides !",
"status_ready": "Prêt. Importer un fichier...", "status_done": "Traitement terminé.",
"select_sheet": "Choisir Feuille"
},
"Italiano (Ý)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 COORDINATE DATI", "row_p": "Riga", "col_p": "Col",
"exclude": "🚫 ESCLUDI COLONNE", "from": "Da", "to": "A",
"compare": "⚖️ COLONNE CONFRONTO", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 ELABORA DATI", "btn_import": "📁 Importa", "btn_restore": "Ripristina",
"btn_clear": "🗑️ Cancella", "btn_export": "📤 Esporta", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Successo: Logica Max elaborata.", "msg_err_input": "Errore: Parametri non validi!",
"status_ready": "Pronto. Importa file...", "status_done": "Elaborazione completata.",
"select_sheet": "Seleziona Foglio"
},
"Español (Tây Ban Nha)": {
"title": "📊 GESTIÓN EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Fila", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUMNAS", "from": "Desde", "to": "Hasta",
"compare": "⚖️ COLUMNAS DE COMP.", "osd_lim": "Límite OSD", "help_lim": "Límite HELP",
"btn_process": "🚀 PROCESAR DATOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Borrar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Éxito: Lógica Max procesada.", "msg_err_input": "Error: ¡Parámetros inválidos!",
"status_ready": "Listo. Importar archivo...", "status_done": "Procesamiento finalizado.",
"select_sheet": "Seleccionar Hoja"
},
"Português (Bồ Đào Nha)": {
"title": "📊 GESTÃO EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Linha", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUNUNAS", "from": "De", "to": "Para",
"compare": "⚖️ COLUNAS DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 PROCESSAR DADOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Limpar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Sucesso: Lógica Max processada.", "msg_err_input": "Erro: Parâmetros inválidos!",
"status_ready": "Pronto. Importar arquivo...", "status_done": "Processamento concluído.",
"select_sheet": "Selecionar Folha"
},
"Türkçe (Thổ Nhĩ Kỳ)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 VERİ KOORDİNATLARI", "row_p": "Satır", "col_p": "Sütun",
"exclude": "🚫 SÜTUNLARI HARİÇ TUT", "from": "Başlangıç", "to": "Bitiş",
"compare": "⚖️ KARŞILAŞTIRMA", "osd_lim": "OSD Sınır Sütunu", "help_lim": "HELP Sınır Sütunu",
"btn_process": "🚀 VERİYİ İŞLE", "btn_import": "📁 İçe Aktar", "btn_restore": "Geri Yükle",
"btn_clear": "🗑️ Temizle", "btn_export": "📤 Dışa Aktar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Başarılı: Max mantığı işlendi.", "msg_err_input": "Hata: Geçersiz parametreler!",
"status_ready": "Hazır. Dosya yükleyin...", "status_done": "İşlem tamamlandı.",
"select_sheet": "Sayfa Seç"
},
"Русский (Nga)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТЫ ДАННЫХ", "row_p": "Строка", "col_p": "Столбец",
"exclude": "🚫 ИСКЛЮЧИТЬ СТОЛБЦЫ", "from": "От", "to": "До",
"compare": "⚖️ СТОЛБЦЫ СРАВНЕНИЯ", "osd_lim": "Лимит OSD", "help_lim": "Лимит HELP",
"btn_process": "🚀 ОБРАБОТАТЬ", "btn_import": "📁 Импорт", "btn_restore": "Восстановить",
"btn_clear": "🗑️ Удалить", "btn_export": "📤 Экспорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успех: Логика Max обработана.", "msg_err_input": "Ошибка: Неверные параметры!",
"status_ready": "Готово. Импортируйте файл...", "status_done": "Обработка завершена.",
"select_sheet": "Выбрать лист"
},
"Українська (Ukraine)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТИ ДАНИХ", "row_p": "Рядок", "col_p": "Стовпець",
"exclude": "🚫 ВИКЛЮЧИТИ СТОВПЦІ", "from": "Від", "to": "До",
"compare": "⚖️ СТОВПЦІ ПОРІВНЯННЯ", "osd_lim": "Межа OSD", "help_lim": "Межа HELP",
"btn_process": "🚀 ОБРОБИТІ ДАНІ", "btn_import": "📁 Імпорт", "btn_restore": "Відновити",
"btn_clear": "🗑️ Видалити", "btn_export": "📤 Експорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успіх: Логіку Max обролено.", "msg_err_input": "Помилка: Неправильні параметри!",
"status_ready": "Готово. Імпортуйте файл...", "status_done": "Обробка завершена.",
"select_sheet": "Вибрати аркуш"
},
"Magyar (Hungary)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 ADATKOORDINÁTÁK", "row_p": "Sor", "col_p": "Oszlop",
"exclude": "🚫 OSZLOPOK KIHAGYÁSA", "from": "Ettől", "to": "Eddig",
"compare": "⚖️ ÖSSZEHASONLÍTÁS", "osd_lim": "OSD limit oszlop", "help_lim": "HELP limit oszlop",
"btn_process": "🚀 FELDOLGOZÁS", "btn_import": "📁 Betöltés", "btn_restore": "Visszaállítás",
"btn_clear": "🗑️ Törlés", "btn_export": "📤 Mentés", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Siker: Max logika feldolgozva.", "msg_err_input": "Hiba: Érvénytelen paraméterek!",
"status_ready": "Kész. Töltse be a fájlt...", "status_done": "Feldolgozás befejezve.",
"select_sheet": "Lap kiválasztása"
},
"Oʻzbekcha (Uzbek)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 MA'LUMOT KOORDINATASI", "row_p": "Qator", "col_p": "Ustun",
"exclude": "🚫 USTUNLARNI CHIQARISH", "from": "Dan", "to": "Gacha",
"compare": "⚖️ TAQQOSLASH USTUNLARI", "osd_lim": "OSD limiti ustuni", "help_lim": "HELP limiti ustuni",
"btn_process": "🚀 ISHLOV BERISH", "btn_import": "📁 Yuklash", "btn_restore": "Tiklash",
"btn_clear": "🗑️ O'chirish", "btn_export": "📤 Eksport", "btn_readme": "📖 Qo'llanma (PDF)",
"msg_success": "Muvaffaqiyat: Max mantiq ishlov berildi.", "msg_err_input": "Xato: Noto'g'ri parametrlar!",
"status_ready": "Tayyor. Faylni yuklang...", "status_done": "Ishlov berish yakunlandi.",
"select_sheet": "Varaqni tanlash"
}
}
# =================================================================
# --- CỬA SỔ XEM PDF ---
# =================================================================
class PDFViewerWindow(ctk.CTkToplevel):
def __init__(self, master, file_path):
super().__init__(master)
self.title("Hướng dẫn sử dụng - PDF Viewer")
self.geometry("1000x850")
try:
self.pdf_doc = fitz.open(file_path)
self.current_page = 0
self.setup_ui()
self.show_p(0)
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể đọc file PDF: {e}")
self.destroy()
self.icon_path = r"D:\Python\T1\app_icon.ico"
if os.path.exists(self.icon_path):
self.iconbitmap(self.icon_path)
def setup_ui(self):
self.ctrl_frame = ctk.CTkFrame(self)
self.ctrl_frame.pack(side="top", fill="x", padx=10, pady=5)
ctk.CTkButton(self.ctrl_frame, text="<<", width=50, command=self.prev_p).pack(side="left", padx=10)
self.page_lab = ctk.CTkLabel(self.ctrl_frame, text="", font=("Arial", 12, "bold"))
self.page_lab.pack(side="left", expand=True)
ctk.CTkButton(self.ctrl_frame, text=">>", width=50, command=self.next_p).pack(side="right", padx=10)
self.scroll_frame = ctk.CTkScrollableFrame(self)
self.scroll_frame.pack(fill="both", expand=True, padx=10, pady=10)
self.img_label = ctk.CTkLabel(self.scroll_frame, text="")
self.img_label.pack()
def show_p(self, n):
if 0 <= n < len(self.pdf_doc):
page = self.pdf_doc.load_page(n)
pix = page.get_pixmap(matrix=fitz.Matrix(1.5, 1.5))
img = Image.frombytes("RGB", [pix.width, pix.height], pix.samples)
ctk_img = ctk.CTkImage(light_image=img, dark_image=img, size=(pix.width, pix.height))
self.img_label.configure(image=ctk_img)
self.current_page = n
self.page_lab.configure(text=f"Trang {n + 1} / {len(self.pdf_doc)}")
def prev_p(self): self.show_p(self.current_page - 1)
def next_p(self): self.show_p(self.current_page + 1)
# =================================================================
# --- CHƯƠNG TRÌNH CHÍNH (PREMIUM EDITION) ---
# =================================================================
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - OSD Ultimate (Premium Edition)")
self.geometry("1550x900")
ctk.set_appearance_mode("light")
self.icon_path = r"D:\Python\T1\app_icon.ico"
if os.path.exists(self.icon_path):
self.iconbitmap(self.icon_path)
self.current_file_path = None
self.wb = None
self.backup_data = None
self.backup_highlights = None
self.backup_headers = None
self.grid_columnconfigure(1, weight=1)
self.grid_rowconfigure(1, weight=1)
self.setup_top_bar()
self.setup_sidebar()
self.setup_work_area()
self.setup_status_bar()
self.update_ui_text("Tiếng Việt")
def setup_top_bar(self):
self.top_bar = ctk.CTkFrame(self, height=75, corner_radius=0, fg_color="#FFFFFF", border_width=1, border_color="#E0E0E0")
self.top_bar.grid(row=0, column=0, columnspan=2, sticky="ew")
self.logo_label = ctk.CTkLabel(self.top_bar, text="", font=("Segoe UI", 24, "bold"), text_color="#1A5276")
self.logo_label.pack(side="left", padx=35)
self.btn_export = ctk.CTkButton(self.top_bar, text="", width=120, height=40, fg_color="#27AE60", hover_color="#1E8449", font=("Arial", 12, "bold"), command=self.export_excel)
self.btn_export.pack(side="right", padx=(5, 35))
self.btn_import = ctk.CTkButton(self.top_bar, text="", width=120, height=40, fg_color="#2980B9", hover_color="#21618C", font=("Arial", 12, "bold"), command=self.import_excel)
self.btn_import.pack(side="right", padx=5)
# Thanh chọn Sheet
self.sheet_menu = ctk.CTkOptionMenu(self.top_bar, values=["Chọn Sheet"], command=self.load_specific_sheet, width=160, height=40, fg_color="#5D6D7E")
self.sheet_menu.pack(side="right", padx=10)
# Menu chọn Ngôn ngữ
self.lang_menu = ctk.CTkOptionMenu(self.top_bar, values=list(LANG_DICT.keys()), command=self.update_ui_text, width=140, height=40)
self.lang_menu.pack(side="right", padx=10)
# --- MENU CHỌN 10 THEME ---
themes = ["Light", "Dark", "System", "Azure", "Emerald", "Sand", "Amethyst", "Midnight", "Forest", "Rose"]
self.theme_menu = ctk.CTkOptionMenu(
self.top_bar,
values=themes,
command=self.change_theme,
width=120,
height=40,
fg_color="#34495E"
)
self.theme_menu.pack(side="right", padx=10)
self.theme_menu.set("Light")
def change_theme(self, name):
"""Hàm thay đổi theme đồng bộ cho ứng dụng và tksheet"""
dark_modes = ["Dark", "Midnight", "Forest", "Amethyst"]
mode = "dark" if name in dark_modes else "light"
ctk.set_appearance_mode(mode)
theme_config = {
"Light": {"bg": "#FFFFFF", "side": "#F8F9F9", "sheet": "light blue"},
"Dark": {"bg": "#1C1C1C", "side": "#262626", "sheet": "dark blue"},
"System": {"bg": "#FFFFFF", "side": "#F8F9F9", "sheet": "light blue"},
"Azure": {"bg": "#F0F7FF", "side": "#E1EFFF", "sheet": "light blue"},
"Emerald": {"bg": "#F1FAF5", "side": "#E3F2E9", "sheet": "light green"},
"Sand": {"bg": "#FDFCF0", "side": "#F7F3D7", "sheet": "light yellow"},
"Amethyst": {"bg": "#1A1625", "side": "#2D263D", "sheet": "dark purple"},
"Midnight": {"bg": "#0B0E14", "side": "#151921", "sheet": "dark black"},
"Forest": {"bg": "#111A11", "side": "#1B291B", "sheet": "dark green"},
"Rose": {"bg": "#FFF5F7", "side": "#FFE4E9", "sheet": "light red"}
}
cfg = theme_config.get(name, theme_config["Light"])
# Cập nhật màu giao diện
self.top_bar.configure(fg_color=cfg["bg"], border_color="#D0D0D0" if mode=="light" else "#404040")
self.work_area.configure(fg_color=cfg["bg"])
self.sidebar.configure(fg_color=cfg["side"])
self.status_bar.configure(fg_color=cfg["side"])
# Cập nhật tksheet theme
self.sheet.change_theme(cfg["sheet"])
self.sheet.redraw()
def setup_sidebar(self):
self.sidebar = ctk.CTkFrame(self, width=310, corner_radius=0, fg_color="#F8F9F9")
self.sidebar.grid(row=1, column=0, sticky="nsew")
self.group_coord = ctk.CTkFrame(self.sidebar, fg_color="transparent")
self.group_coord.pack(fill="x", padx=25, pady=(35, 15))
self.coord_lab = ctk.CTkLabel(self.group_coord, text="", font=("Arial", 13, "bold"), text_color="#34495E")
self.coord_lab.pack(anchor="w", pady=(0, 5))
f_rc = ctk.CTkFrame(self.group_coord, fg_color="transparent")
f_rc.pack(fill="x")
self.row_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Dòng")
self.row_start_entry.insert(0, "2")
self.row_start_entry.pack(side="left", padx=(0, 5))
self.col_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Cột")
self.col_start_entry.insert(0, "A")
self.col_start_entry.pack(side="left")
self.ex_card = ctk.CTkFrame(self.sidebar, fg_color="#FDEDEC", corner_radius=15, border_width=1, border_color="#FADBD8")
self.ex_card.pack(fill="x", padx=20, pady=15)
self.exclude_lab = ctk.CTkLabel(self.ex_card, text="", text_color="#C0392B", font=("Arial", 12, "bold"))
self.exclude_lab.pack(pady=(12, 0))
f_ex = ctk.CTkFrame(self.ex_card, fg_color="transparent")
f_ex.pack(pady=15)
self.exclude_start_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Từ")
self.exclude_start_entry.pack(side="left", padx=5)
self.exclude_end_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Đến")
self.exclude_end_entry.pack(side="left", padx=5)
self.lim_card = ctk.CTkFrame(self.sidebar, fg_color="#EBF5FB", corner_radius=15, border_width=1, border_color="#D6EAF8")
self.lim_card.pack(fill="x", padx=20, pady=15)
self.compare_lab = ctk.CTkLabel(self.lim_card, text="", text_color="#2E86C1", font=("Arial", 12, "bold"))
self.compare_lab.pack(pady=8)
self.osd_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.osd_limit_col_entry.pack(pady=5, padx=15)
self.help_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.help_limit_col_entry.pack(pady=(5, 15), padx=15)
self.btn_process = ctk.CTkButton(self.sidebar, text="", height=60, font=("Arial", 16, "bold"), fg_color="#E67E22", hover_color="#D35400", command=self.process_advanced_logic)
self.btn_process.pack(fill="x", padx=25, pady=(25, 15))
self.btn_restore = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#7F8C8D", hover_color="#707B7C", command=self.restore_backup)
self.btn_restore.pack(fill="x", padx=25, pady=5)
self.btn_readme = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#515A5A", hover_color="#424949", command=self.open_readme_smart)
self.btn_readme.pack(fill="x", padx=25, pady=5)
self.btn_clear = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#C0392B", hover_color="#922B21", command=self.clear_file)
self.btn_clear.pack(fill="x", padx=25, pady=5)
def setup_work_area(self):
self.work_area = ctk.CTkFrame(self, fg_color="#FFFFFF", corner_radius=0)
self.work_area.grid(row=1, column=1, sticky="nsew", padx=2, pady=2)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
def setup_status_bar(self):
self.status_bar = ctk.CTkFrame(self, height=35, corner_radius=0, fg_color="#F2F4F4", border_width=1, border_color="#E0E0E0")
self.status_bar.grid(row=2, column=0, columnspan=2, sticky="ew")
self.status_label = ctk.CTkLabel(self.status_bar, text="", font=("Arial", 11), text_color="#566573")
self.status_label.pack(side="left", padx=30)
ctk.CTkLabel(self.status_bar, text="Premium Version 2.0 | © 2026", font=("Arial", 10, "italic"), text_color="#ABB2B9").pack(side="right", padx=25)
def update_ui_text(self, choice):
d = LANG_DICT.get(choice, LANG_DICT["Tiếng Việt"])
self.logo_label.configure(text=d["title"])
self.coord_lab.configure(text=d["coord"])
self.exclude_lab.configure(text=d["exclude"])
self.compare_lab.configure(text=d["compare"])
self.btn_process.configure(text=d["btn_process"])
self.btn_import.configure(text=d["btn_import"])
self.btn_export.configure(text=d["btn_export"])
self.btn_restore.configure(text=d["btn_restore"])
self.btn_readme.configure(text=d["btn_readme"])
self.btn_clear.configure(text=d["btn_clear"])
self.status_label.configure(text=d["status_ready"])
self.osd_limit_col_entry.configure(placeholder_text=d["osd_lim"])
self.help_limit_col_entry.configure(placeholder_text=d["help_lim"])
self.exclude_start_entry.configure(placeholder_text=d["from"])
self.exclude_end_entry.configure(placeholder_text=d["to"])
# Cập nhật từ dịch cho Menu chọn Sheet
current_val = self.sheet_menu.get()
sheet_placeholders = ["Chọn Sheet", "Select Sheet", "シートを選択", "시트 선택", "选择工作表", "Blatt auswählen", "Choisir Feuille", "Seleziona Foglio", "Seleccionar Hoja", "Selecionar Folha", "Sayfa Seç", "Выбрать лист", "Вибрати аркуш", "Lap kiválasztása", "Varaqni tanlash"]
if current_val in sheet_placeholders:
self.sheet_menu.set(d["select_sheet"])
def col_to_idx(self, letter):
if not letter: return -1
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def get_actual_hex(self, fill_obj):
if not fill_obj or not fill_obj.start_color: return None
clr = fill_obj.start_color
rgb = None
if clr.type == 'rgb' and clr.rgb: rgb = str(clr.rgb)
elif clr.type == 'theme' and clr.theme is not None:
themes = ['ffffff', '000000', 'eeece1', '1f497d', '4f81bd', 'c0504d', '9bbb59', '8064a2', '4bacc6', 'f79646']
try: rgb = themes[clr.theme]
except: return None
elif clr.type == 'indexed' and clr.indexed is not None:
try: rgb = COLOR_INDEX[clr.indexed]
except: return None
if not rgb or rgb == '00000000': return None
if len(rgb) == 8: rgb = rgb[2:]
try:
r, g, b = int(rgb[0:2], 16), int(rgb[2:4], 16), int(rgb[4:6], 16)
if clr.tint:
t = clr.tint
if t < 0: r, g, b = int(r*(1+t)), int(g*(1+t)), int(b*(1+t))
else: r, g, b = int(r+(255-r)*t), int(g+(255-g)*t), int(b+(255-b)*t)
return f'#{max(0, min(255, r)):02x}{max(0, min(255, g)):02x}{max(0, min(255, b)):02x}'
except: return None
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if path:
self.current_file_path = path
try:
self.wb = openpyxl.load_workbook(path, data_only=True)
sheets = self.wb.sheetnames
self.sheet_menu.configure(values=sheets)
self.sheet_menu.set(sheets[0])
self.load_specific_sheet(sheets[0])
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể mở file: {e}")
def load_specific_sheet(self, sheet_name):
if not self.wb: return
self.status_label.configure(text=f"📂 Loading Sheet: {sheet_name}...")
self.update()
try:
ws = self.wb[sheet_name]
max_r, max_c = ws.max_row, ws.max_column
data = [[(ws.cell(row=r, column=c).value if ws.cell(row=r, column=c).value is not None else "") for c in range(1, max_c + 1)] for r in range(1, max_r + 1)]
self.sheet.set_sheet_data(data, redraw=False)
self.sheet.headers([self.idx_to_col(i) for i in range(max_c)])
self.sheet.dehighlight_cells()
for r_idx in range(1, max_r + 1):
for c_idx in range(1, max_c + 1):
bg = self.get_actual_hex(ws.cell(row=r_idx, column=c_idx).fill)
if bg and bg.lower() != "#ffffff":
self.sheet.highlight_cells(row=r_idx-1, column=c_idx-1, bg=bg, redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✅ {sheet_name} | {max_r} Rows.")
except Exception as e:
messagebox.showerror("Sheet Error", f"Failed to load data: {e}")
def process_advanced_logic(self):
d = LANG_DICT.get(self.lang_menu.get(), LANG_DICT["Tiếng Việt"])
try:
start_row = int(self.row_start_entry.get() or 2) - 1
start_col_idx = self.col_to_idx(self.col_start_entry.get())
s_ex, e_ex = self.exclude_start_entry.get().strip().upper(), self.exclude_end_entry.get().strip().upper()
exclude_indices = []
if s_ex and e_ex:
si, ei = self.col_to_idx(s_ex), self.col_to_idx(e_ex)
exclude_indices = list(range(min(si, ei), max(si, ei) + 1))
orig_osd_idx = self.col_to_idx(self.osd_limit_col_entry.get())
orig_help_idx = self.col_to_idx(self.help_limit_col_entry.get())
if orig_osd_idx < 0 or orig_help_idx < 0: raise ValueError()
except:
messagebox.showerror("Error", d["msg_err_input"])
return
old_data = self.sheet.get_sheet_data()
if not old_data: return
self.backup_data = [row[:] for row in old_data]
self.backup_headers = list(self.sheet.headers())
self.backup_highlights = self.sheet.get_highlighted_cells().copy()
highlighted = self.sheet.get_highlighted_cells()
new_data, final_headers, new_highlights = [], [], {}
original_header_row = old_data[0]
for r_idx, row in enumerate(old_data):
processed_row = []
t_osd_w, t_osd_l = 0, 0
t_help_w, t_help_l = 0, 0
m_w_osd, m_l_osd = 0, 0
m_w_help, m_l_help = 0, 0
curr_c = 0
for c_idx, val in enumerate(row):
processed_row.append(val)
if r_idx == 0: final_headers.append(val if val else self.idx_to_col(c_idx))
if (r_idx, c_idx) in highlighted: new_highlights[(r_idx, curr_c)] = highlighted[(r_idx, c_idx)]
curr_c += 1
if c_idx >= start_col_idx:
lw, wc = 0, 0
h_name = str(original_header_row[c_idx]).upper() if original_header_row[c_idx] else ""
if r_idx == 0:
lw, wc = f"LW_{final_headers[curr_c-1]}", f"WC_{final_headers[curr_c-1]}"
elif r_idx >= start_row:
txt = str(val).strip()
if txt and txt.lower() not in ["none", "nan"]:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
lw = len(max(words, key=len)) if words else 0
wc = len(txt.split())
if c_idx == orig_osd_idx: t_osd_w, t_osd_l = lw, wc
if c_idx == orig_help_idx: t_help_w, t_help_l = lw, wc
if c_idx not in exclude_indices:
if "HELP" in h_name: m_w_help, m_l_help = max(m_w_help, lw), max(m_l_help, wc)
else: m_w_osd, m_l_osd = max(m_w_osd, lw), max(m_l_osd, wc)
processed_row.extend([lw, wc])
if r_idx == 0: final_headers.extend([lw, wc])
curr_c += 2
if r_idx == 0:
res_h = ["MAX Word OSD", "RES Word OSD", "MAX Word HELP", "RES Word HELP", "MAX Len OSD", "RES Len OSD", "MAX Len HELP", "RES Len HELP"]
processed_row.extend(res_h); final_headers.extend(res_h)
else:
processed_row.extend([m_w_osd, "TRUE" if m_w_osd >= t_osd_w else "FALSE", m_w_help, "TRUE" if m_w_help >= t_help_w else "FALSE",
m_l_osd, "TRUE" if m_l_osd >= t_osd_l else "FALSE", m_l_help, "TRUE" if m_l_help >= t_help_l else "FALSE"])
new_data.append(processed_row)
self.sheet.set_sheet_data(new_data, redraw=False)
self.sheet.headers(final_headers)
self.sheet.dehighlight_cells()
for pos, color in new_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
for c in range(len(final_headers) - 8, len(final_headers)): self.sheet.highlight_cells(row=0, column=c, bg="#FFD700", redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✨ {d['status_done']}")
messagebox.showinfo("Success", d["msg_success"])
def restore_backup(self):
if self.backup_data:
self.sheet.dehighlight_cells()
self.sheet.headers(self.backup_headers)
self.sheet.set_sheet_data(self.backup_data, redraw=True)
for pos, color in self.backup_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
self.sheet.redraw()
def clear_file(self):
self.sheet.set_sheet_data([], redraw=True)
self.sheet.headers([])
self.sheet.dehighlight_cells()
def export_excel(self):
data = self.sheet.get_sheet_data()
if not data: return
path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel Files", "*.xlsx")])
if path:
wb = openpyxl.Workbook()
ws = wb.active
h_cells = self.sheet.get_highlighted_cells()
for r_idx, row in enumerate(data, 1):
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=val)
if (r_idx-1, c_idx-1) in h_cells:
color_val = h_cells[(r_idx-1, c_idx-1)]
hex_c = (color_val[0] if isinstance(color_val, (tuple, list)) else color_val).replace("#", "")
cell.fill = PatternFill(start_color=hex_c, end_color=hex_c, fill_type="solid")
wb.save(path)
def open_readme_smart(self):
base_dir = r"D:\Python\T1"
target = None
for f in ["readme.pdf", "readme.docx"]:
p = os.path.join(base_dir, f)
if os.path.exists(p): target = p; break
if not target: return
if target.endswith(".pdf"): PDFViewerWindow(self, target)
else:
if sys.platform == "win32": os.startfile(target)
if __name__ == "__main__":
app = ExcelMasterTool()
app.mainloop()
v6
import customtkinter as ctk
import pandas as pd
import string
import openpyxl
import os
import sys
import fitz # Thư viện PyMuPDF
from PIL import Image
from tkinter import filedialog, messagebox
from tksheet import Sheet
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import COLOR_INDEX
import ctypes
# --- THIẾT LẬP APP ID CHO TASKBAR ---
try:
myappid = 'nsoft.excelpro.osd.2.0'
ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID(myappid)
except:
pass
# =================================================================
# --- HỆ THỐNG TỪ ĐIỂN 15 NGÔN NGỮ ĐẦY ĐỦ ---
# =================================================================
LANG_DICT = {
"Tiếng Việt": {
"title": "📊 QUẢN LÝ EXCEL PRO", "coord": "📍 CẤU HÌNH VỊ TRÍ", "row_p": "Dòng (2)", "col_p": "Cột (A)",
"exclude": "🚫 DẢI CỘT LOẠI TRỪ", "from": "Từ (B)", "to": "Đến (C)",
"compare": "⚖️ CỘT SO SÁNH", "osd_lim": "Cột hạn mức OSD", "help_lim": "Cột hạn mức HELP",
"btn_process": "🚀 XỬ LÝ DỮ LIỆU", "btn_import": "📁 Nạp File", "btn_restore": "↩️ Khôi phục gốc",
"btn_clear": "🗑️ Xóa", "btn_export": "📤 Xuất Excel", "btn_readme": "📖 Hướng dẫn (PDF)",
"msg_success": "Thành công: Đã xử lý logic Max và hiển thị Debug.", "msg_err_input": "Lỗi: Thông số nhập vào không đúng!",
"status_ready": "Sẵn sàng. Vui lòng nạp file...", "status_done": "Đã xử lý xong dữ liệu.",
"select_sheet": "Chọn Sheet"
},
"English": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 DATA COORDINATES", "row_p": "Row", "col_p": "Col",
"exclude": "🚫 EXCLUDE COLUMNS", "from": "From", "to": "To",
"compare": "⚖️ COMPARISON COLS", "osd_lim": "OSD Limit Col", "help_lim": "HELP Limit Col",
"btn_process": "🚀 PROCESS DATA", "btn_import": "📁 Import File", "btn_restore": "↩️ Restore",
"btn_clear": "🗑️ Clear", "btn_export": "📤 Export Excel", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Success: Max logic processed.", "msg_err_input": "Error: Invalid parameters!",
"status_ready": "Ready. Please import file...", "status_done": "Processing completed.",
"select_sheet": "Select Sheet"
},
"日本語 (Nhật)": {
"title": "📊 Excel プロ管理", "coord": "📍 データ座標設定", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 除外列範囲", "from": "開始", "to": "終了",
"compare": "⚖️ 比較列設定", "osd_lim": "OSD 制限列", "help_lim": "HELP 制限列",
"btn_process": "🚀 データ実行", "btn_import": "📁 ファイル読込", "btn_restore": "↩️ 元に戻す",
"btn_clear": "🗑️ クリア", "btn_export": "📤 エクセル出力", "btn_readme": "📖 説明書 (PDF)",
"msg_success": "成功：ロジックが処理されました。", "msg_err_input": "エラー：入力が無効です。",
"status_ready": "準備完了。ファイルを読み込んでください。", "status_done": "処理が完了しました。",
"select_sheet": "シートを選択"
},
"韓国어 (Hàn)": {
"title": "📊 엑셀 마스터 프로", "coord": "📍 데이터 좌표 설정", "row_p": "행 (2)", "col_p": "열 (A)",
"exclude": "🚫 제외 열 범위", "from": "시작", "to": "끝",
"compare": "⚖️ 비교 열 설정", "osd_lim": "OSD 제한 열", "help_lim": "HELP 제한 열",
"btn_process": "🚀 데이터 처리", "btn_import": "📁 파일 불러오기", "btn_restore": "↩️ 원본 복구",
"btn_clear": "🗑️ 삭제", "btn_export": "📤 엑셀 내보내기", "btn_readme": "📖 설명서 (PDF)",
"msg_success": "성공: 로직 처리가 완료되었습니다.", "msg_err_input": "오류: 입력값이 잘못되었습니다.",
"status_ready": "준비됨. 파일을 불러오세요...", "status_done": "처리가 완료되었습니다.",
"select_sheet": "시트 선택"
},
"中文 (Trung)": {
"title": "📊 Excel 专业管理", "coord": "📍 数据坐标设置", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 排除列范围", "from": "从", "to": "到",
"compare": "⚖️ 比较列设置", "osd_lim": "OSD 限制列", "help_lim": "HELP 限制列",
"btn_process": "🚀 处理数据", "btn_import": "📁 导入文件", "btn_restore": "↩️ 恢复原始",
"btn_clear": "🗑️ 清除", "btn_export": "📤 导出 Excel", "btn_readme": "📖 使用说明 (PDF)",
"msg_success": "成功：逻辑已处理并显示调试。", "msg_err_input": "错误：参数输入无效！",
"status_ready": "就绪。请导入文件...", "status_done": "数据处理完成。",
"select_sheet": "选择工作表"
},
"Deutsch (Đức)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 KOORDINATEN", "row_p": "Zeile", "col_p": "Spalte",
"exclude": "🚫 SPALTEN AUSSCHLIESSEN", "from": "Von", "to": "Bis",
"compare": "⚖️ VERGLEICHSSPALTEN", "osd_lim": "OSD-Limit Spalte", "help_lim": "HELP-Limit Spalte",
"btn_process": "🚀 DATEN VERARBEITEN", "btn_import": "📁 Importieren", "btn_restore": "↩️ Wiederherstellen",
"btn_clear": "🗑️ Löschen", "btn_export": "📤 Exportieren", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Erfolg: Max-Logik verarbeitet.", "msg_err_input": "Fehler: Ungültige Parameter!",
"status_ready": "Bereit. Datei importieren...", "status_done": "Verarbeitung abgeschlossen.",
"select_sheet": "Blatt auswählen"
},
"Français (Pháp)": {
"title": "📊 GESTION EXCEL PRO", "coord": "📍 COORDONNÉES", "row_p": "Ligne", "col_p": "Col",
"exclude": "🚫 EXCLURE COLONNES", "from": "De", "to": "À",
"compare": "⚖️ COLONNES DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 TRAITER LES DONNÉES", "btn_import": "📁 Importer", "btn_restore": "↩️ Restaurer",
"btn_clear": "🗑️ Effacer", "btn_export": "📤 Exporter", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Succès : Logique Max traitée.", "msg_err_input": "Erreur : Paramètres invalides !",
"status_ready": "Prêt. Importer un fichier...", "status_done": "Traitement terminé.",
"select_sheet": "Choisir Feuille"
},
"Italiano (Ý)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 COORDINATE DATI", "row_p": "Riga", "col_p": "Col",
"exclude": "🚫 ESCLUDI COLONNE", "from": "Da", "to": "A",
"compare": "⚖️ COLONNE CONFRONTO", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 ELABORA DATI", "btn_import": "📁 Importa", "btn_restore": "Ripristina",
"btn_clear": "🗑️ Cancella", "btn_export": "📤 Esporta", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Successo: Logica Max elaborata.", "msg_err_input": "Errore: Parametri non validi!",
"status_ready": "Pronto. Importa file...", "status_done": "Elaborazione completata.",
"select_sheet": "Seleziona Foglio"
},
"Español (Tây Ban Nha)": {
"title": "📊 GESTIÓN EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Fila", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUMNAS", "from": "Desde", "to": "Hasta",
"compare": "⚖️ COLUMNAS DE COMP.", "osd_lim": "Límite OSD", "help_lim": "Límite HELP",
"btn_process": "🚀 PROCESAR DATOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Borrar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Éxito: Lógica Max procesada.", "msg_err_input": "Error: ¡Parámetros inválidos!",
"status_ready": "Listo. Importar archivo...", "status_done": "Procesamiento finalizado.",
"select_sheet": "Seleccionar Hoja"
},
"Português (Bồ Đào Nha)": {
"title": "📊 GESTÃO EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Linha", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUNAS", "from": "De", "to": "Para",
"compare": "⚖️ COLUNAS DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 PROCESSAR DADOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Limpar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Sucesso: Lógica Max processada.", "msg_err_input": "Erro: Parâmetros inválidos!",
"status_ready": "Pronto. Importar arquivo...", "status_done": "Processamento concluído.",
"select_sheet": "Selecionar Folha"
},
"Türkçe (Thổ Nhĩ Kỳ)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 VERİ KOORDİNATLARI", "row_p": "Satır", "col_p": "Sütun",
"exclude": "🚫 SÜTUNLARI HARİÇ TUT", "from": "Başlangıç", "to": "Bitiş",
"compare": "⚖️ KARŞILAŞTIRMA", "osd_lim": "OSD Sınır Sütunu", "help_lim": "HELP Sınır Sütunu",
"btn_process": "🚀 VERİYİ İŞLE", "btn_import": "📁 İçe Aktar", "btn_restore": "Geri Yükle",
"btn_clear": "🗑️ Temizle", "btn_export": "📤 Dışa Aktar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Başarılı: Max mantığı işlendi.", "msg_err_input": "Hata: Geçersiz parametreler!",
"status_ready": "Hazır. Dosya yükleyin...", "status_done": "İşlem tamamlandı.",
"select_sheet": "Sayfa Seç"
},
"Русский (Nga)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТЫ ДАННЫХ", "row_p": "Строка", "col_p": "Столбец",
"exclude": "🚫 ИСКЛЮЧИТЬ СТОЛБЦЫ", "from": "От", "to": "До",
"compare": "⚖️ СТОЛБЦЫ СРАВНЕНИЯ", "osd_lim": "Лимит OSD", "help_lim": "Лимит HELP",
"btn_process": "🚀 ОБРАБОТАТЬ", "btn_import": "📁 Импорт", "btn_restore": "Восстановить",
"btn_clear": "🗑️ Удалить", "btn_export": "📤 Экспорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успех: Логика Max обработана.", "msg_err_input": "Ошибка: Неверные параметры!",
"status_ready": "Готово. Импортируйте файл...", "status_done": "Обработка завершена.",
"select_sheet": "Выбрать лист"
},
"Українська (Ukraine)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТИ ДАНИХ", "row_p": "Рядок", "col_p": "Стовпець",
"exclude": "🚫 ВИКЛЮЧИТИ СТОВПЦІ", "from": "Від", "to": "До",
"compare": "⚖️ СТОВПЦІ ПОРІВНЯННЯ", "osd_lim": "Межа OSD", "help_lim": "Межа HELP",
"btn_process": "🚀 ОБРОБИТІ ДАНІ", "btn_import": "📁 Імпорт", "btn_restore": "Відновити",
"btn_clear": "🗑️ Видалити", "btn_export": "📤 Експорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успіх: Логіку Max обролено.", "msg_err_input": "Помилка: Неправильні параметри!",
"status_ready": "Готово. Імпортуйте файл...", "status_done": "Обробка завершена.",
"select_sheet": "Вибрати аркуш"
},
"Magyar (Hungary)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 ADATKOORDINÁTÁK", "row_p": "Sor", "col_p": "Oszlop",
"exclude": "🚫 OSZLOPOK KIHAGYÁSA", "from": "Ettől", "to": "Eddig",
"compare": "⚖️ ÖSSZEHASONLÍTÁS", "osd_lim": "OSD limit oszlop", "help_lim": "HELP limit oszlop",
"btn_process": "🚀 FELDOLGOZÁS", "btn_import": "📁 Betöltés", "btn_restore": "Visszaállítás",
"btn_clear": "🗑️ Törlés", "btn_export": "📤 Mentés", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Siker: Max logika feldolgozva.", "msg_err_input": "Hiba: Érvénytelen paraméterek!",
"status_ready": "Kész. Töltse be a fájlt...", "status_done": "Feldolgozás befejezve.",
"select_sheet": "Lap kiválasztása"
},
"Oʻzbekcha (Uzbek)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 MA'LUMOT KOORDINATASI", "row_p": "Qator", "col_p": "Ustun",
"exclude": "🚫 USTUNLARNI CHIQARISH", "from": "Dan", "to": "Gacha",
"compare": "⚖️ TAQQOSLASH USTUNLARI", "osd_lim": "OSD limiti ustuni", "help_lim": "HELP limiti ustuni",
"btn_process": "🚀 ISHLOV BERISH", "btn_import": "📁 Yuklash", "btn_restore": "Tiklash",
"btn_clear": "🗑️ O'chirish", "btn_export": "📤 Eksport", "btn_readme": "📖 Qo'llanma (PDF)",
"msg_success": "Muvaffaqiyat: Max mantiq ishlov berildi.", "msg_err_input": "Xato: Noto'g'ri parametrlar!",
"status_ready": "Tayyor. Faylni yuklang...", "status_done": "Ishlov berish yakunlandi.",
"select_sheet": "Varaqni tanlash"
}
}
# =================================================================
# --- CỬA SỔ XEM PDF (DÀNH CHO HƯỚNG DẪN) ---
# =================================================================
class PDFViewerWindow(ctk.CTkToplevel):
def __init__(self, master, file_path):
super().__init__(master)
self.title("Hướng dẫn sử dụng - PDF Viewer")
self.geometry("1000x850")
try:
self.pdf_doc = fitz.open(file_path)
self.current_page = 0
self.setup_ui()
self.show_p(0)
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể đọc file PDF: {e}")
self.destroy()
self.icon_path = r"D:\Python\T1\app_icon.ico"
if os.path.exists(self.icon_path):
self.iconbitmap(self.icon_path)
def setup_ui(self):
self.ctrl_frame = ctk.CTkFrame(self)
self.ctrl_frame.pack(side="top", fill="x", padx=10, pady=5)
ctk.CTkButton(self.ctrl_frame, text="<<", width=50, command=self.prev_p).pack(side="left", padx=10)
self.page_lab = ctk.CTkLabel(self.ctrl_frame, text="", font=("Arial", 12, "bold"))
self.page_lab.pack(side="left", expand=True)
ctk.CTkButton(self.ctrl_frame, text=">>", width=50, command=self.next_p).pack(side="right", padx=10)
self.scroll_frame = ctk.CTkScrollableFrame(self)
self.scroll_frame.pack(fill="both", expand=True, padx=10, pady=10)
self.img_label = ctk.CTkLabel(self.scroll_frame, text="")
self.img_label.pack()
def show_p(self, n):
if 0 <= n < len(self.pdf_doc):
page = self.pdf_doc.load_page(n)
pix = page.get_pixmap(matrix=fitz.Matrix(1.5, 1.5))
img = Image.frombytes("RGB", [pix.width, pix.height], pix.samples)
ctk_img = ctk.CTkImage(light_image=img, dark_image=img, size=(pix.width, pix.height))
self.img_label.configure(image=ctk_img)
self.current_page = n
self.page_lab.configure(text=f"Trang {n + 1} / {len(self.pdf_doc)}")
def prev_p(self): self.show_p(self.current_page - 1)
def next_p(self): self.show_p(self.current_page + 1)
# =================================================================
# --- CHƯƠNG TRÌNH CHÍNH (PREMIUM EDITION) ---
# =================================================================
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - OSD Ultimate (Premium Edition)")
self.geometry("1550x900")
ctk.set_appearance_mode("light")
self.icon_path = r"D:\Python\T1\app_icon.ico"
if os.path.exists(self.icon_path):
self.iconbitmap(self.icon_path)
self.current_file_path = None
self.wb = None
self.backup_data = None
self.backup_highlights = None
self.backup_headers = None
self.grid_columnconfigure(1, weight=1)
self.grid_rowconfigure(1, weight=1)
self.setup_top_bar()
self.setup_sidebar()
self.setup_work_area()
self.setup_status_bar()
self.update_ui_text("Tiếng Việt")
def setup_top_bar(self):
self.top_bar = ctk.CTkFrame(self, height=75, corner_radius=0, fg_color="#FFFFFF", border_width=1, border_color="#E0E0E0")
self.top_bar.grid(row=0, column=0, columnspan=2, sticky="ew")
self.logo_label = ctk.CTkLabel(self.top_bar, text="", font=("Segoe UI", 24, "bold"), text_color="#1A5276")
self.logo_label.pack(side="left", padx=35)
self.btn_export = ctk.CTkButton(self.top_bar, text="", width=120, height=40, fg_color="#27AE60", hover_color="#1E8449", font=("Arial", 12, "bold"), command=self.export_excel)
self.btn_export.pack(side="right", padx=(5, 35))
self.btn_import = ctk.CTkButton(self.top_bar, text="", width=120, height=40, fg_color="#2980B9", hover_color="#21618C", font=("Arial", 12, "bold"), command=self.import_excel)
self.btn_import.pack(side="right", padx=5)
# Thanh chọn Sheet
self.sheet_menu = ctk.CTkOptionMenu(self.top_bar, values=["Chọn Sheet"], command=self.load_specific_sheet, width=160, height=40, fg_color="#5D6D7E")
self.sheet_menu.pack(side="right", padx=10)
self.lang_menu = ctk.CTkOptionMenu(self.top_bar, values=list(LANG_DICT.keys()), command=self.update_ui_text, width=140, height=40)
self.lang_menu.pack(side="right", padx=10)
def setup_sidebar(self):
self.sidebar = ctk.CTkFrame(self, width=310, corner_radius=0, fg_color="#F8F9F9")
self.sidebar.grid(row=1, column=0, sticky="nsew")
self.group_coord = ctk.CTkFrame(self.sidebar, fg_color="transparent")
self.group_coord.pack(fill="x", padx=25, pady=(35, 15))
self.coord_lab = ctk.CTkLabel(self.group_coord, text="", font=("Arial", 13, "bold"), text_color="#34495E")
self.coord_lab.pack(anchor="w", pady=(0, 5))
f_rc = ctk.CTkFrame(self.group_coord, fg_color="transparent")
f_rc.pack(fill="x")
self.row_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Dòng")
self.row_start_entry.insert(0, "2")
self.row_start_entry.pack(side="left", padx=(0, 5))
self.col_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Cột")
self.col_start_entry.insert(0, "A")
self.col_start_entry.pack(side="left")
self.ex_card = ctk.CTkFrame(self.sidebar, fg_color="#FDEDEC", corner_radius=15, border_width=1, border_color="#FADBD8")
self.ex_card.pack(fill="x", padx=20, pady=15)
self.exclude_lab = ctk.CTkLabel(self.ex_card, text="", text_color="#C0392B", font=("Arial", 12, "bold"))
self.exclude_lab.pack(pady=(12, 0))
f_ex = ctk.CTkFrame(self.ex_card, fg_color="transparent")
f_ex.pack(pady=15)
self.exclude_start_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Từ")
self.exclude_start_entry.pack(side="left", padx=5)
self.exclude_end_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Đến")
self.exclude_end_entry.pack(side="left", padx=5)
self.lim_card = ctk.CTkFrame(self.sidebar, fg_color="#EBF5FB", corner_radius=15, border_width=1, border_color="#D6EAF8")
self.lim_card.pack(fill="x", padx=20, pady=15)
self.compare_lab = ctk.CTkLabel(self.lim_card, text="", text_color="#2E86C1", font=("Arial", 12, "bold"))
self.compare_lab.pack(pady=8)
self.osd_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.osd_limit_col_entry.pack(pady=5, padx=15)
self.help_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.help_limit_col_entry.pack(pady=(5, 15), padx=15)
self.btn_process = ctk.CTkButton(self.sidebar, text="", height=60, font=("Arial", 16, "bold"), fg_color="#E67E22", hover_color="#D35400", command=self.process_advanced_logic)
self.btn_process.pack(fill="x", padx=25, pady=(25, 15))
self.btn_restore = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#7F8C8D", hover_color="#707B7C", command=self.restore_backup)
self.btn_restore.pack(fill="x", padx=25, pady=5)
self.btn_readme = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#515A5A", hover_color="#424949", command=self.open_readme_smart)
self.btn_readme.pack(fill="x", padx=25, pady=5)
self.btn_clear = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#C0392B", hover_color="#922B21", command=self.clear_file)
self.btn_clear.pack(fill="x", padx=25, pady=5)
def setup_work_area(self):
self.work_area = ctk.CTkFrame(self, fg_color="#FFFFFF", corner_radius=0)
self.work_area.grid(row=1, column=1, sticky="nsew", padx=2, pady=2)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
def setup_status_bar(self):
self.status_bar = ctk.CTkFrame(self, height=35, corner_radius=0, fg_color="#F2F4F4", border_width=1, border_color="#E0E0E0")
self.status_bar.grid(row=2, column=0, columnspan=2, sticky="ew")
self.status_label = ctk.CTkLabel(self.status_bar, text="", font=("Arial", 11), text_color="#566573")
self.status_label.pack(side="left", padx=30)
ctk.CTkLabel(self.status_bar, text="Premium Version 2.0 | © 2026", font=("Arial", 10, "italic"), text_color="#ABB2B9").pack(side="right", padx=25)
def update_ui_text(self, choice):
d = LANG_DICT.get(choice, LANG_DICT["Tiếng Việt"])
self.logo_label.configure(text=d["title"])
self.coord_lab.configure(text=d["coord"])
self.exclude_lab.configure(text=d["exclude"])
self.compare_lab.configure(text=d["compare"])
self.btn_process.configure(text=d["btn_process"])
self.btn_import.configure(text=d["btn_import"])
self.btn_export.configure(text=d["btn_export"])
self.btn_restore.configure(text=d["btn_restore"])
self.btn_readme.configure(text=d["btn_readme"])
self.btn_clear.configure(text=d["btn_clear"])
self.status_label.configure(text=d["status_ready"])
self.osd_limit_col_entry.configure(placeholder_text=d["osd_lim"])
self.help_limit_col_entry.configure(placeholder_text=d["help_lim"])
self.exclude_start_entry.configure(placeholder_text=d["from"])
self.exclude_end_entry.configure(placeholder_text=d["to"])
# Cập nhật từ dịch cho Menu chọn Sheet
if self.sheet_menu.get() in ["Chọn Sheet", "Select Sheet", "シートを選択", "시트 선택", "选择工作表", "Blatt auswählen", "Choisir Feuille", "Seleziona Foglio", "Seleccionar Hoja", "Selecionar Folha", "Sayfa Seç", "Выбрать лист", "Вибрати аркуш", "Lap kiválasztása", "Varaqni tanlash"]:
self.sheet_menu.set(d["select_sheet"])
def col_to_idx(self, letter):
if not letter: return -1
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def get_actual_hex(self, fill_obj):
if not fill_obj or not fill_obj.start_color: return None
clr = fill_obj.start_color
rgb = None
if clr.type == 'rgb' and clr.rgb: rgb = str(clr.rgb)
elif clr.type == 'theme' and clr.theme is not None:
themes = ['ffffff', '000000', 'eeece1', '1f497d', '4f81bd', 'c0504d', '9bbb59', '8064a2', '4bacc6', 'f79646']
try: rgb = themes[clr.theme]
except: return None
elif clr.type == 'indexed' and clr.indexed is not None:
try: rgb = COLOR_INDEX[clr.indexed]
except: return None
if not rgb or rgb == '00000000': return None
if len(rgb) == 8: rgb = rgb[2:]
try:
r, g, b = int(rgb[0:2], 16), int(rgb[2:4], 16), int(rgb[4:6], 16)
if clr.tint:
t = clr.tint
if t < 0: r, g, b = int(r*(1+t)), int(g*(1+t)), int(b*(1+t))
else: r, g, b = int(r+(255-r)*t), int(g+(255-g)*t), int(b+(255-b)*t)
return f'#{max(0, min(255, r)):02x}{max(0, min(255, g)):02x}{max(0, min(255, b)):02x}'
except: return None
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if path:
self.current_file_path = path
try:
self.wb = openpyxl.load_workbook(path, data_only=True)
sheets = self.wb.sheetnames
self.sheet_menu.configure(values=sheets)
self.sheet_menu.set(sheets[0])
self.load_specific_sheet(sheets[0])
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể mở file: {e}")
def load_specific_sheet(self, sheet_name):
if not self.wb: return
self.status_label.configure(text=f"📂 Loading Sheet: {sheet_name}...")
self.update()
try:
ws = self.wb[sheet_name]
max_r, max_c = ws.max_row, ws.max_column
data = [[(ws.cell(row=r, column=c).value if ws.cell(row=r, column=c).value is not None else "") for c in range(1, max_c + 1)] for r in range(1, max_r + 1)]
self.sheet.set_sheet_data(data, redraw=False)
self.sheet.headers([self.idx_to_col(i) for i in range(max_c)])
self.sheet.dehighlight_cells()
for r_idx in range(1, max_r + 1):
for c_idx in range(1, max_c + 1):
bg = self.get_actual_hex(ws.cell(row=r_idx, column=c_idx).fill)
if bg and bg.lower() != "#ffffff":
self.sheet.highlight_cells(row=r_idx-1, column=c_idx-1, bg=bg, redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✅ {sheet_name} | {max_r} Rows.")
except Exception as e:
messagebox.showerror("Sheet Error", f"Failed to load data: {e}")
def process_advanced_logic(self):
d = LANG_DICT.get(self.lang_menu.get(), LANG_DICT["Tiếng Việt"])
try:
start_row = int(self.row_start_entry.get() or 2) - 1
start_col_idx = self.col_to_idx(self.col_start_entry.get())
s_ex, e_ex = self.exclude_start_entry.get().strip().upper(), self.exclude_end_entry.get().strip().upper()
exclude_indices = []
if s_ex and e_ex:
si, ei = self.col_to_idx(s_ex), self.col_to_idx(e_ex)
exclude_indices = list(range(min(si, ei), max(si, ei) + 1))
orig_osd_idx = self.col_to_idx(self.osd_limit_col_entry.get())
orig_help_idx = self.col_to_idx(self.help_limit_col_entry.get())
if orig_osd_idx < 0 or orig_help_idx < 0: raise ValueError()
except:
messagebox.showerror("Error", d["msg_err_input"])
return
old_data = self.sheet.get_sheet_data()
if not old_data: return
self.backup_data = [row[:] for row in old_data]
self.backup_headers = list(self.sheet.headers())
self.backup_highlights = self.sheet.get_highlighted_cells().copy()
highlighted = self.sheet.get_highlighted_cells()
new_data, final_headers, new_highlights = [], [], {}
original_header_row = old_data[0]
for r_idx, row in enumerate(old_data):
processed_row = []
t_osd_w, t_osd_l = 0, 0
t_help_w, t_help_l = 0, 0
m_w_osd, m_l_osd = 0, 0
m_w_help, m_l_help = 0, 0
curr_c = 0
for c_idx, val in enumerate(row):
processed_row.append(val)
if r_idx == 0: final_headers.append(val if val else self.idx_to_col(c_idx))
if (r_idx, c_idx) in highlighted: new_highlights[(r_idx, curr_c)] = highlighted[(r_idx, c_idx)]
curr_c += 1
if c_idx >= start_col_idx:
lw, wc = 0, 0
h_name = str(original_header_row[c_idx]).upper() if original_header_row[c_idx] else ""
if r_idx == 0:
lw, wc = f"LW_{final_headers[curr_c-1]}", f"WC_{final_headers[curr_c-1]}"
elif r_idx >= start_row:
txt = str(val).strip()
if txt and txt.lower() not in ["none", "nan"]:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
lw = len(max(words, key=len)) if words else 0
wc = len(txt.split())
if c_idx == orig_osd_idx: t_osd_w, t_osd_l = lw, wc
if c_idx == orig_help_idx: t_help_w, t_help_l = lw, wc
if c_idx not in exclude_indices:
if "HELP" in h_name: m_w_help, m_l_help = max(m_w_help, lw), max(m_l_help, wc)
else: m_w_osd, m_l_osd = max(m_w_osd, lw), max(m_l_osd, wc)
processed_row.extend([lw, wc])
if r_idx == 0: final_headers.extend([lw, wc])
curr_c += 2
if r_idx == 0:
res_h = ["MAX Word OSD", "RES Word OSD", "MAX Word HELP", "RES Word HELP", "MAX Len OSD", "RES Len OSD", "MAX Len HELP", "RES Len HELP"]
processed_row.extend(res_h); final_headers.extend(res_h)
else:
processed_row.extend([m_w_osd, "TRUE" if m_w_osd >= t_osd_w else "FALSE", m_w_help, "TRUE" if m_w_help >= t_help_w else "FALSE",
m_l_osd, "TRUE" if m_l_osd >= t_osd_l else "FALSE", m_l_help, "TRUE" if m_l_help >= t_help_l else "FALSE"])
new_data.append(processed_row)
self.sheet.set_sheet_data(new_data, redraw=False)
self.sheet.headers(final_headers)
self.sheet.dehighlight_cells()
for pos, color in new_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
for c in range(len(final_headers) - 8, len(final_headers)): self.sheet.highlight_cells(row=0, column=c, bg="#FFD700", redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✨ {d['status_done']}")
messagebox.showinfo("Success", d["msg_success"])
def restore_backup(self):
if self.backup_data:
self.sheet.dehighlight_cells()
self.sheet.headers(self.backup_headers)
self.sheet.set_sheet_data(self.backup_data, redraw=True)
for pos, color in self.backup_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
self.sheet.redraw()
def clear_file(self):
self.sheet.set_sheet_data([], redraw=True)
self.sheet.headers([])
self.sheet.dehighlight_cells()
def export_excel(self):
data = self.sheet.get_sheet_data()
if not data: return
path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel Files", "*.xlsx")])
if path:
wb = openpyxl.Workbook()
ws = wb.active
h_cells = self.sheet.get_highlighted_cells()
for r_idx, row in enumerate(data, 1):
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=val)
if (r_idx-1, c_idx-1) in h_cells:
color_val = h_cells[(r_idx-1, c_idx-1)]
hex_c = (color_val[0] if isinstance(color_val, (tuple, list)) else color_val).replace("#", "")
cell.fill = PatternFill(start_color=hex_c, end_color=hex_c, fill_type="solid")
wb.save(path)
def open_readme_smart(self):
base_dir = r"D:\Python\T1"
target = None
for f in ["readme.pdf", "readme.docx"]:
p = os.path.join(base_dir, f)
if os.path.exists(p): target = p; break
if not target: return
if target.endswith(".pdf"): PDFViewerWindow(self, target)
else:
if sys.platform == "win32": os.startfile(target)
if __name__ == "__main__":
app = ExcelMasterTool()
app.mainloop()
vV5
import customtkinter as ctk
import pandas as pd
import string
import openpyxl
import os
import sys
import fitz # Thư viện PyMuPDF
from PIL import Image
from tkinter import filedialog, messagebox
from tksheet import Sheet
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import COLOR_INDEX
import ctypes
# Thêm đoạn này để ép Windows hiển thị icon riêng dưới thanh Taskbar
try:
myappid = 'nsoft.excelpro.osd.2.0' # Chuỗi định danh bất kỳ
ctypes.windll.shell32.SetCurrentProcessExplicitAppUserModelID(myappid)
except:
pass
# =================================================================
# --- HỆ THỐNG TỪ ĐIỂN 15 NGÔN NGỮ ĐẦY ĐỦ ---
# =================================================================
LANG_DICT = {
"Tiếng Việt": {
"title": "📊 QUẢN LÝ EXCEL PRO", "coord": "📍 CẤU HÌNH VỊ TRÍ", "row_p": "Dòng (2)", "col_p": "Cột (A)",
"exclude": "🚫 DẢI CỘT LOẠI TRỪ", "from": "Từ (B)", "to": "Đến (C)",
"compare": "⚖️ CỘT SO SÁNH", "osd_lim": "Cột hạn mức OSD", "help_lim": "Cột hạn mức HELP",
"btn_process": "🚀 XỬ LÝ DỮ LIỆU", "btn_import": "📁 Nạp File", "btn_restore": "↩️ Khôi phục gốc",
"btn_clear": "🗑️ Xóa", "btn_export": "📤 Xuất Excel", "btn_readme": "📖 Hướng dẫn (PDF)",
"msg_success": "Thành công: Đã xử lý logic Max và hiển thị Debug.", "msg_err_input": "Lỗi: Thông số nhập vào không đúng!",
"status_ready": "Sẵn sàng. Vui lòng nạp file...", "status_done": "Đã xử lý xong dữ liệu."
},
"English": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 DATA COORDINATES", "row_p": "Row", "col_p": "Col",
"exclude": "🚫 EXCLUDE COLUMNS", "from": "From", "to": "To",
"compare": "⚖️ COMPARISON COLS", "osd_lim": "OSD Limit Col", "help_lim": "HELP Limit Col",
"btn_process": "🚀 PROCESS DATA", "btn_import": "📁 Import File", "btn_restore": "↩️ Restore",
"btn_clear": "🗑️ Clear", "btn_export": "📤 Export Excel", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Success: Max logic processed.", "msg_err_input": "Error: Invalid parameters!",
"status_ready": "Ready. Please import file...", "status_done": "Processing completed."
},
"日本語 (Nhật)": {
"title": "📊 Excel プロ管理", "coord": "📍 データ座標設定", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 除外列範囲", "from": "開始", "to": "終了",
"compare": "⚖️ 比較列設定", "osd_lim": "OSD 制限列", "help_lim": "HELP 制限列",
"btn_process": "🚀 データ実行", "btn_import": "📁 ファイル読込", "btn_restore": "↩️ 元に戻す",
"btn_clear": "🗑️ クリア", "btn_export": "📤 エクセル出力", "btn_readme": "📖 説明書 (PDF)",
"msg_success": "成功：ロジックが処理されました。", "msg_err_input": "エラー：入力が無効です。",
"status_ready": "準備完了。ファイルを読み込んでください。", "status_done": "処理が完了しました。"
},
"한국어 (Hàn)": {
"title": "📊 엑셀 마스터 프로", "coord": "📍 데이터 좌표 설정", "row_p": "행 (2)", "col_p": "열 (A)",
"exclude": "🚫 제외 열 범위", "from": "시작", "to": "끝",
"compare": "⚖️ 비교 열 설정", "osd_lim": "OSD 제한 열", "help_lim": "HELP 제한 열",
"btn_process": "🚀 데이터 처리", "btn_import": "📁 파일 불러오기", "btn_restore": "↩️ 원본 복구",
"btn_clear": "🗑️ 삭제", "btn_export": "📤 엑셀 내보내기", "btn_readme": "📖 설명서 (PDF)",
"msg_success": "성공: 로직 처리가 완료되었습니다.", "msg_err_input": "오류: 입력값이 잘못되었습니다.",
"status_ready": "준비됨. 파일을 불러오세요...", "status_done": "처리가 완료되었습니다."
},
"中文 (Trung)": {
"title": "📊 Excel 专业管理", "coord": "📍 数据坐标设置", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 排除列范围", "from": "从", "to": "到",
"compare": "⚖️ 比较列设置", "osd_lim": "OSD 限制列", "help_lim": "HELP 限制列",
"btn_process": "🚀 处理数据", "btn_import": "📁 导入文件", "btn_restore": "↩️ 恢复原始",
"btn_clear": "🗑️ 清除", "btn_export": "📤 导出 Excel", "btn_readme": "📖 使用说明 (PDF)",
"msg_success": "成功：逻辑已处理并显示调试。", "msg_err_input": "错误：参数输入无效！",
"status_ready": "就绪。请导入文件...", "status_done": "数据处理完成。"
},
"Deutsch (Đức)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 KOORDINATEN", "row_p": "Zeile", "col_p": "Spalte",
"exclude": "🚫 SPALTEN AUSSCHLIESSEN", "from": "Von", "to": "Bis",
"compare": "⚖️ VERGLEICHSSPALTEN", "osd_lim": "OSD-Limit Spalte", "help_lim": "HELP-Limit Spalte",
"btn_process": "🚀 DATEN VERARBEITEN", "btn_import": "📁 Importieren", "btn_restore": "↩️ Wiederherstellen",
"btn_clear": "🗑️ Löschen", "btn_export": "📤 Exportieren", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Erfolg: Max-Logik verarbeitet.", "msg_err_input": "Fehler: Ungültige Parameter!",
"status_ready": "Bereit. Datei importieren...", "status_done": "Verarbeitung abgeschlossen."
},
"Français (Pháp)": {
"title": "📊 GESTION EXCEL PRO", "coord": "📍 COORDONNÉES", "row_p": "Ligne", "col_p": "Col",
"exclude": "🚫 EXCLURE COLONNES", "from": "De", "to": "À",
"compare": "⚖️ COLONNES DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 TRAITER LES DONNÉES", "btn_import": "📁 Importer", "btn_restore": "↩️ Restaurer",
"btn_clear": "🗑️ Effacer", "btn_export": "📤 Exporter", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Succès : Logique Max traitée.", "msg_err_input": "Erreur : Paramètres invalides !",
"status_ready": "Prêt. Importer un fichier...", "status_done": "Traitement terminé."
},
"Italiano (Ý)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 COORDINATE DATI", "row_p": "Riga", "col_p": "Col",
"exclude": "🚫 ESCLUDI COLONNE", "from": "Da", "to": "A",
"compare": "⚖️ COLONNE CONFRONTO", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 ELABORA DATI", "btn_import": "📁 Importa", "btn_restore": "↩️ Ripristina",
"btn_clear": "🗑️ Cancella", "btn_export": "📤 Esporta", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Successo: Logica Max elaborata.", "msg_err_input": "Errore: Parametri non validi!",
"status_ready": "Pronto. Importa file...", "status_done": "Elaborazione completata."
},
"Español (Tây Ban Nha)": {
"title": "📊 GESTIÓN EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Fila", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUMNAS", "from": "Desde", "to": "Hasta",
"compare": "⚖️ COLUMNAS DE COMP.", "osd_lim": "Límite OSD", "help_lim": "Límite HELP",
"btn_process": "🚀 PROCESAR DATOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Borrar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Éxito: Lógica Max procesada.", "msg_err_input": "Error: ¡Parámetros inválidos!",
"status_ready": "Listo. Importar archivo...", "status_done": "Procesamiento finalizado."
},
"Português (Bồ Đào Nha)": {
"title": "📊 GESTÃO EXCEL PRO", "coord": "📍 COORDENADAS", "row_p": "Linha", "col_p": "Col",
"exclude": "🚫 EXCLUIR COLUNAS", "from": "De", "to": "Para",
"compare": "⚖️ COLUNAS DE COMP.", "osd_lim": "Limite OSD", "help_lim": "Limite HELP",
"btn_process": "🚀 PROCESSAR DADOS", "btn_import": "📁 Importar", "btn_restore": "Restaurar",
"btn_clear": "🗑️ Limpar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Sucesso: Lógica Max processada.", "msg_err_input": "Erro: Parâmetros inválidos!",
"status_ready": "Pronto. Importar arquivo...", "status_done": "Processamento concluído."
},
"Türkçe (Thổ Nhĩ Kỳ)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 VERİ KOORDİNATLARI", "row_p": "Satır", "col_p": "Sütun",
"exclude": "🚫 SÜTUNLARI HARİÇ TUT", "from": "Başlangıç", "to": "Bitiş",
"compare": "⚖️ KARŞILAŞTIRMA", "osd_lim": "OSD Sınır Sütunu", "help_lim": "HELP Sınır Sütunu",
"btn_process": "🚀 VERİYİ İŞLE", "btn_import": "📁 İçe Aktar", "btn_restore": "Geri Yükle",
"btn_clear": "🗑️ Temizle", "btn_export": "📤 Dışa Aktar", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Başarılı: Max mantığı işlendi.", "msg_err_input": "Hata: Geçersiz parametreler!",
"status_ready": "Hazır. Dosya yükleyin...", "status_done": "İşlem tamamlandı."
},
"Русский (Nga)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТЫ ДАННЫХ", "row_p": "Строка", "col_p": "Столбец",
"exclude": "🚫 ИСКЛЮЧИТЬ СТОЛБЦЫ", "from": "От", "to": "До",
"compare": "⚖️ СТОЛБЦЫ СРАВНЕНИЯ", "osd_lim": "Лимит OSD", "help_lim": "Лимит HELP",
"btn_process": "🚀 ОБРАБОТАТЬ", "btn_import": "📁 Импорт", "btn_restore": "Восстановить",
"btn_clear": "🗑️ Удалить", "btn_export": "📤 Экспорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успех: Логика Max обработана.", "msg_err_input": "Ошибка: Неверные параметры!",
"status_ready": "Готово. Импортируйте файл...", "status_done": "Обработка завершена."
},
"Українська (Ukraine)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 КООРДИНАТИ ДАНИХ", "row_p": "Рядок", "col_p": "Стовпець",
"exclude": "🚫 ВИКЛЮЧИТИ СТОВПЦІ", "from": "Від", "to": "До",
"compare": "⚖️ СТОВПЦІ ПОРІВНЯННЯ", "osd_lim": "Межа OSD", "help_lim": "Межа HELP",
"btn_process": "🚀 ОБРОБИТІ ДАНІ", "btn_import": "📁 Імпорт", "btn_restore": "Відновити",
"btn_clear": "🗑️ Видалити", "btn_export": "📤 Експорт", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Успіх: Логіку Max оброблено.", "msg_err_input": "Помилка: Неправильні параметри!",
"status_ready": "Готово. Імпортуйте файл...", "status_done": "Обробка завершена."
},
"Magyar (Hungary)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 ADATKOORDINÁTÁK", "row_p": "Sor", "col_p": "Oszlop",
"exclude": "🚫 OSZLOPOK KIHAGYÁSA", "from": "Ettől", "to": "Eddig",
"compare": "⚖️ ÖSSZEHASONLÍTÁS", "osd_lim": "OSD limit oszlop", "help_lim": "HELP limit oszlop",
"btn_process": "🚀 FELDOLGOZÁS", "btn_import": "📁 Betöltés", "btn_restore": "Visszaállítás",
"btn_clear": "🗑️ Törlés", "btn_export": "📤 Mentés", "btn_readme": "📖 Read Me (PDF)",
"msg_success": "Siker: Max logika feldolgozva.", "msg_err_input": "Hiba: Érvénytelen paraméterek!",
"status_ready": "Kész. Töltse be a fájlt...", "status_done": "Feldolgozás befejezve."
},
"Oʻzbekcha (Uzbek)": {
"title": "📊 EXCEL MASTER PRO", "coord": "📍 MA'LUMOT KOORDINATASI", "row_p": "Qator", "col_p": "Ustun",
"exclude": "🚫 USTUNLARNI CHIQARISH", "from": "Dan", "to": "Gacha",
"compare": "⚖️ TAQQOSLASH USTUNLARI", "osd_lim": "OSD limiti ustuni", "help_lim": "HELP limiti ustuni",
"btn_process": "🚀 ISHLOV BERISH", "btn_import": "📁 Yuklash", "btn_restore": "Tiklash",
"btn_clear": "🗑️ O'chirish", "btn_export": "📤 Eksport", "btn_readme": "📖 Qo'llanma (PDF)",
"msg_success": "Muvaffaqiyat: Max mantiq ishlov berildi.", "msg_err_input": "Xato: Noto'g'ri parametrlar!",
"status_ready": "Tayyor. Faylni yuklang...", "status_done": "Ishlov berish yakunlandi."
}
}
# =================================================================
# --- CỬA SỔ XEM PDF (DÀNH CHO HƯỚNG DẪN) ---
# =================================================================
class PDFViewerWindow(ctk.CTkToplevel):
def __init__(self, master, file_path):
super().__init__(master)
self.title("Hướng dẫn sử dụng - PDF Viewer")
self.geometry("1000x850")
try:
self.pdf_doc = fitz.open(file_path)
self.current_page = 0
self.setup_ui()
self.show_p(0)
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể đọc file PDF: {e}")
self.destroy()
# --- ĐOẠN CODE THIẾT LẬP ICON ---
self.icon_path = r"D:\Python\T1\app_icon.ico"
if os.path.exists(self.icon_path):
self.iconbitmap(self.icon_path)
# -------------------------------
def setup_ui(self):
self.ctrl_frame = ctk.CTkFrame(self)
self.ctrl_frame.pack(side="top", fill="x", padx=10, pady=5)
ctk.CTkButton(self.ctrl_frame, text="<<", width=50, command=self.prev_p).pack(side="left", padx=10)
self.page_lab = ctk.CTkLabel(self.ctrl_frame, text="", font=("Arial", 12, "bold"))
self.page_lab.pack(side="left", expand=True)
ctk.CTkButton(self.ctrl_frame, text=">>", width=50, command=self.next_p).pack(side="right", padx=10)
self.scroll_frame = ctk.CTkScrollableFrame(self)
self.scroll_frame.pack(fill="both", expand=True, padx=10, pady=10)
self.img_label = ctk.CTkLabel(self.scroll_frame, text="")
self.img_label.pack()
def show_p(self, n):
if 0 <= n < len(self.pdf_doc):
page = self.pdf_doc.load_page(n)
pix = page.get_pixmap(matrix=fitz.Matrix(1.5, 1.5))
img = Image.frombytes("RGB", [pix.width, pix.height], pix.samples)
ctk_img = ctk.CTkImage(light_image=img, dark_image=img, size=(pix.width, pix.height))
self.img_label.configure(image=ctk_img)
self.current_page = n
self.page_lab.configure(text=f"Trang {n + 1} / {len(self.pdf_doc)}")
def prev_p(self): self.show_p(self.current_page - 1)
def next_p(self): self.show_p(self.current_page + 1)
# =================================================================
# --- CHƯƠNG TRÌNH CHÍNH (PREMIUM EDITION) ---
# =================================================================
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - OSD Ultimate (Premium Edition)")
self.geometry("1500x900")
ctk.set_appearance_mode("light")
self.current_file_path = None
self.backup_data = None
self.backup_highlights = None
self.backup_headers = None
# Cấu hình Layout chính (Grid 3 tầng)
self.grid_columnconfigure(1, weight=1)
self.grid_rowconfigure(1, weight=1)
self.setup_top_bar()
self.setup_sidebar()
self.setup_work_area()
self.setup_status_bar()
self.update_ui_text("Tiếng Việt")
# --- UI: THANH CÔNG CỤ TRÊN CÙNG ---
def setup_top_bar(self):
self.top_bar = ctk.CTkFrame(self, height=75, corner_radius=0, fg_color="#FFFFFF", border_width=1, border_color="#E0E0E0")
self.top_bar.grid(row=0, column=0, columnspan=2, sticky="ew")
self.logo_label = ctk.CTkLabel(self.top_bar, text="", font=("Segoe UI", 24, "bold"), text_color="#1A5276")
self.logo_label.pack(side="left", padx=35)
self.btn_export = ctk.CTkButton(self.top_bar, text="", width=130, height=40, fg_color="#27AE60", hover_color="#1E8449", font=("Arial", 12, "bold"), command=self.export_excel)
self.btn_export.pack(side="right", padx=(10, 35))
self.btn_import = ctk.CTkButton(self.top_bar, text="", width=130, height=40, fg_color="#2980B9", hover_color="#21618C", font=("Arial", 12, "bold"), command=self.import_excel)
self.btn_import.pack(side="right", padx=10)
self.lang_menu = ctk.CTkOptionMenu(self.top_bar, values=list(LANG_DICT.keys()), command=self.update_ui_text, width=150, height=40)
self.lang_menu.pack(side="right", padx=20)
# --- UI: THANH CÀI ĐẶT BÊN TRÁI ---
def setup_sidebar(self):
self.sidebar = ctk.CTkFrame(self, width=310, corner_radius=0, fg_color="#F8F9F9")
self.sidebar.grid(row=1, column=0, sticky="nsew")
# 1. Tọa độ dữ liệu
self.group_coord = ctk.CTkFrame(self.sidebar, fg_color="transparent")
self.group_coord.pack(fill="x", padx=25, pady=(35, 15))
self.coord_lab = ctk.CTkLabel(self.group_coord, text="", font=("Arial", 13, "bold"), text_color="#34495E")
self.coord_lab.pack(anchor="w", pady=(0, 5))
f_rc = ctk.CTkFrame(self.group_coord, fg_color="transparent")
f_rc.pack(fill="x")
self.row_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Dòng")
self.row_start_entry.insert(0, "2")
self.row_start_entry.pack(side="left", padx=(0, 5))
self.col_start_entry = ctk.CTkEntry(f_rc, width=120, height=38, placeholder_text="Cột")
self.col_start_entry.insert(0, "A")
self.col_start_entry.pack(side="left")
# 2. Loại trừ (Card UI)
self.ex_card = ctk.CTkFrame(self.sidebar, fg_color="#FDEDEC", corner_radius=15, border_width=1, border_color="#FADBD8")
self.ex_card.pack(fill="x", padx=20, pady=15)
self.exclude_lab = ctk.CTkLabel(self.ex_card, text="", text_color="#C0392B", font=("Arial", 12, "bold"))
self.exclude_lab.pack(pady=(12, 0))
f_ex = ctk.CTkFrame(self.ex_card, fg_color="transparent")
f_ex.pack(pady=15)
self.exclude_start_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Từ")
self.exclude_start_entry.pack(side="left", padx=5)
self.exclude_end_entry = ctk.CTkEntry(f_ex, width=110, height=38, placeholder_text="Đến")
self.exclude_end_entry.pack(side="left", padx=5)
# 3. Hạn mức so sánh (Card UI)
self.lim_card = ctk.CTkFrame(self.sidebar, fg_color="#EBF5FB", corner_radius=15, border_width=1, border_color="#D6EAF8")
self.lim_card.pack(fill="x", padx=20, pady=15)
self.compare_lab = ctk.CTkLabel(self.lim_card, text="", text_color="#2E86C1", font=("Arial", 12, "bold"))
self.compare_lab.pack(pady=8)
self.osd_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.osd_limit_col_entry.pack(pady=5, padx=15)
self.help_limit_col_entry = ctk.CTkEntry(self.lim_card, width=230, height=38)
self.help_limit_col_entry.pack(pady=(5, 15), padx=15)
# 4. Nút hành động
self.btn_process = ctk.CTkButton(self.sidebar, text="", height=60, font=("Arial", 16, "bold"), fg_color="#E67E22", hover_color="#D35400", command=self.process_advanced_logic)
self.btn_process.pack(fill="x", padx=25, pady=(25, 15))
self.btn_restore = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#7F8C8D", hover_color="#707B7C", command=self.restore_backup)
self.btn_restore.pack(fill="x", padx=25, pady=5)
self.btn_readme = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#515A5A", hover_color="#424949", command=self.open_readme_smart)
self.btn_readme.pack(fill="x", padx=25, pady=5)
self.btn_clear = ctk.CTkButton(self.sidebar, text="", height=40, fg_color="#C0392B", hover_color="#922B21", command=self.clear_file)
self.btn_clear.pack(fill="x", padx=25, pady=5)
# --- UI: KHU VỰC LÀM VIỆC ---
def setup_work_area(self):
self.work_area = ctk.CTkFrame(self, fg_color="#FFFFFF", corner_radius=0)
self.work_area.grid(row=1, column=1, sticky="nsew", padx=2, pady=2)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
# --- UI: THANH TRẠNG THÁI ---
def setup_status_bar(self):
self.status_bar = ctk.CTkFrame(self, height=35, corner_radius=0, fg_color="#F2F4F4", border_width=1, border_color="#E0E0E0")
self.status_bar.grid(row=2, column=0, columnspan=2, sticky="ew")
self.status_label = ctk.CTkLabel(self.status_bar, text="", font=("Arial", 11), text_color="#566573")
self.status_label.pack(side="left", padx=30)
ctk.CTkLabel(self.status_bar, text="Premium Version 2.0 | © 2026", font=("Arial", 10, "italic"), text_color="#ABB2B9").pack(side="right", padx=25)
# --- HÀM CẬP NHẬT NGÔN NGỮ ---
def update_ui_text(self, choice):
d = LANG_DICT.get(choice, LANG_DICT["Tiếng Việt"])
self.logo_label.configure(text=d["title"])
self.coord_lab.configure(text=d["coord"])
self.exclude_lab.configure(text=d["exclude"])
self.compare_lab.configure(text=d["compare"])
self.btn_process.configure(text=d["btn_process"])
self.btn_import.configure(text=d["btn_import"])
self.btn_export.configure(text=d["btn_export"])
self.btn_restore.configure(text=d["btn_restore"])
self.btn_readme.configure(text=d["btn_readme"])
self.btn_clear.configure(text=d["btn_clear"])
self.status_label.configure(text=d["status_ready"])
self.osd_limit_col_entry.configure(placeholder_text=d["osd_lim"])
self.help_limit_col_entry.configure(placeholder_text=d["help_lim"])
self.exclude_start_entry.configure(placeholder_text=d["from"])
self.exclude_end_entry.configure(placeholder_text=d["to"])
# --- LOGIC TIỆN ÍCH ---
def col_to_idx(self, letter):
if not letter: return -1
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def get_actual_hex(self, fill_obj):
if not fill_obj or not fill_obj.start_color: return None
clr = fill_obj.start_color
rgb = None
if clr.type == 'rgb' and clr.rgb: rgb = str(clr.rgb)
elif clr.type == 'theme' and clr.theme is not None:
themes = ['ffffff', '000000', 'eeece1', '1f497d', '4f81bd', 'c0504d', '9bbb59', '8064a2', '4bacc6', 'f79646']
try: rgb = themes[clr.theme]
except: return None
elif clr.type == 'indexed' and clr.indexed is not None:
try: rgb = COLOR_INDEX[clr.indexed]
except: return None
if not rgb or rgb == '00000000': return None
if len(rgb) == 8: rgb = rgb[2:]
try:
r, g, b = int(rgb[0:2], 16), int(rgb[2:4], 16), int(rgb[4:6], 16)
if clr.tint:
t = clr.tint
if t < 0: r, g, b = int(r*(1+t)), int(g*(1+t)), int(b*(1+t))
else: r, g, b = int(r+(255-r)*t), int(g+(255-g)*t), int(b+(255-b)*t)
return f'#{max(0, min(255, r)):02x}{max(0, min(255, g)):02x}{max(0, min(255, b)):02x}'
except: return None
# --- LOGIC CHÍNH: NẠP EXCEL ---
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if path:
self.current_file_path = path
self.status_label.configure(text=f"📂 Đang nạp: {os.path.basename(path)}...")
self.update()
try:
wb = openpyxl.load_workbook(path, data_only=True)
ws = wb.active
max_r, max_c = ws.max_row, ws.max_column
data = [[(ws.cell(row=r, column=c).value if ws.cell(row=r, column=c).value is not None else "") for c in range(1, max_c + 1)] for r in range(1, max_r + 1)]
self.sheet.set_sheet_data(data, redraw=False)
self.sheet.headers([self.idx_to_col(i) for i in range(max_c)])
for r_idx in range(1, max_r + 1):
for c_idx in range(1, max_c + 1):
bg = self.get_actual_hex(ws.cell(row=r_idx, column=c_idx).fill)
if bg and bg.lower() != "#ffffff":
self.sheet.highlight_cells(row=r_idx-1, column=c_idx-1, bg=bg, redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✅ Đã nạp: {os.path.basename(path)} | {max_r} dòng.")
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể mở file: {e}")
# --- LOGIC CHÍNH: XỬ LÝ MAX ---
def process_advanced_logic(self):
d = LANG_DICT.get(self.lang_menu.get(), LANG_DICT["Tiếng Việt"])
try:
start_row = int(self.row_start_entry.get() or 2) - 1
start_col_idx = self.col_to_idx(self.col_start_entry.get())
s_ex, e_ex = self.exclude_start_entry.get().strip().upper(), self.exclude_end_entry.get().strip().upper()
exclude_indices = []
if s_ex and e_ex:
si, ei = self.col_to_idx(s_ex), self.col_to_idx(e_ex)
exclude_indices = list(range(min(si, ei), max(si, ei) + 1))
orig_osd_idx = self.col_to_idx(self.osd_limit_col_entry.get())
orig_help_idx = self.col_to_idx(self.help_limit_col_entry.get())
if orig_osd_idx < 0 or orig_help_idx < 0:
raise ValueError("Cột hạn mức không hợp lệ")
except:
messagebox.showerror("Error", d["msg_err_input"])
return
old_data = self.sheet.get_sheet_data()
if not old_data: return
# Sao lưu
self.backup_data = [row[:] for row in old_data]
self.backup_headers = list(self.sheet.headers())
self.backup_highlights = self.sheet.get_highlighted_cells().copy()
highlighted = self.sheet.get_highlighted_cells()
new_data, final_headers, new_highlights = [], [], {}
original_header_row = old_data[0]
for r_idx, row in enumerate(old_data):
processed_row = []
t_osd_w, t_osd_l = 0, 0
t_help_w, t_help_l = 0, 0
m_w_osd, m_l_osd = 0, 0
m_w_help, m_l_help = 0, 0
curr_c = 0
for c_idx, val in enumerate(row):
processed_row.append(val)
if r_idx == 0: final_headers.append(val if val else self.idx_to_col(c_idx))
if (r_idx, c_idx) in highlighted: new_highlights[(r_idx, curr_c)] = highlighted[(r_idx, c_idx)]
curr_c += 1
if c_idx >= start_col_idx:
lw, wc = 0, 0
h_name = str(original_header_row[c_idx]).upper() if original_header_row[c_idx] else ""
if r_idx == 0:
lw, wc = f"LW_{final_headers[curr_c-1]}", f"WC_{final_headers[curr_c-1]}"
elif r_idx >= start_row:
txt = str(val).strip()
if txt and txt.lower() not in ["none", "nan"]:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
lw = len(max(words, key=len)) if words else 0
wc = len(txt.split())
if c_idx == orig_osd_idx: t_osd_w, t_osd_l = lw, wc
if c_idx == orig_help_idx: t_help_w, t_help_l = lw, wc
if c_idx not in exclude_indices:
if "HELP" in h_name: m_w_help, m_l_help = max(m_w_help, lw), max(m_l_help, wc)
else: m_w_osd, m_l_osd = max(m_w_osd, lw), max(m_l_osd, wc)
processed_row.extend([lw, wc])
if r_idx == 0: final_headers.extend([lw, wc])
curr_c += 2
if r_idx == 0:
res_h = ["MAX Word OSD", "RES Word OSD", "MAX Word HELP", "RES Word HELP", "MAX Len OSD", "RES Len OSD", "MAX Len HELP", "RES Len HELP"]
processed_row.extend(res_h); final_headers.extend(res_h)
else:
processed_row.extend([m_w_osd, "TRUE" if m_w_osd >= t_osd_w else "FALSE", m_w_help, "TRUE" if m_w_help >= t_help_w else "FALSE",
m_l_osd, "TRUE" if m_l_osd >= t_osd_l else "FALSE", m_l_help, "TRUE" if m_l_help >= t_help_l else "FALSE"])
new_data.append(processed_row)
self.sheet.set_sheet_data(new_data, redraw=False)
self.sheet.headers(final_headers)
self.sheet.dehighlight_cells()
for pos, color in new_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
# Highlight tiêu đề cột kết quả
for c in range(len(final_headers) - 8, len(final_headers)):
self.sheet.highlight_cells(row=0, column=c, bg="#FFD700", redraw=False)
self.sheet.redraw()
self.status_label.configure(text=f"✨ {d['status_done']}")
messagebox.showinfo("Success", d["msg_success"])
def restore_backup(self):
if self.backup_data:
self.sheet.dehighlight_cells()
self.sheet.headers(self.backup_headers)
self.sheet.set_sheet_data(self.backup_data, redraw=True)
for pos, color in self.backup_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
self.sheet.redraw()
self.status_label.configure(text="↩️ Đã khôi phục dữ liệu gốc.")
def clear_file(self):
self.sheet.set_sheet_data([], redraw=True)
self.sheet.headers([])
self.sheet.dehighlight_cells()
self.status_label.configure(text="🗑️ Đã xóa dữ liệu bảng.")
def export_excel(self):
data = self.sheet.get_sheet_data()
if not data: return
path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel Files", "*.xlsx")])
if path:
self.status_label.configure(text="📤 Đang xuất file...")
self.update()
wb = openpyxl.Workbook()
ws = wb.active
h_cells = self.sheet.get_highlighted_cells()
for r_idx, row in enumerate(data, 1):
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=val)
if (r_idx-1, c_idx-1) in h_cells:
color_val = h_cells[(r_idx-1, c_idx-1)]
hex_c = (color_val[0] if isinstance(color_val, (tuple, list)) else color_val).replace("#", "")
cell.fill = PatternFill(start_color=hex_c, end_color=hex_c, fill_type="solid")
wb.save(path)
self.status_label.configure(text=f"💾 Đã lưu file: {os.path.basename(path)}")
def open_readme_smart(self):
base_dir = r"D:\Python\T1"
target = None
for f in ["readme.pdf", "readme.docx"]:
p = os.path.join(base_dir, f)
if os.path.exists(p): target = p; break
if not target:
messagebox.showerror("Lỗi", f"Không tìm thấy file Readme trong {base_dir}")
return
if target.endswith(".pdf"):
PDFViewerWindow(self, target)
else:
if sys.platform == "win32": os.startfile(target)
if __name__ == "__main__":
app = ExcelMasterTool()
app.mainloop()
V4
import customtkinter as ctk
import pandas as pd
import string
import openpyxl
import os
import sys
import fitz # Cần cài đặt: pip install PyMuPDF Pillow
from PIL import Image
from tkinter import filedialog, messagebox
from tksheet import Sheet
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import COLOR_INDEX
# --- HỆ THỐNG TỪ ĐIỂN 15 NGÔN NGỮ (BỔ SUNG) ---
LANG_DICT = {
"Tiếng Việt": {
"title": "📊 QUẢN LÝ EXCEL", "coord": "📍 Tọa độ dữ liệu:", "row_p": "Dòng (2)", "col_p": "Cột (A)",
"exclude": "🚫 DẢI CỘT LOẠI TRỪ", "from": "Từ (vd: B)", "to": "Đến (vd: C)",
"compare": "⚖️ SO SÁNH TƯƠNG ỨNG", "osd_lim": "Cột hạn mức OSD:", "help_lim": "Cột hạn mức HELP:",
"btn_process": "🚀 XỬ LÝ & SO SÁNH", "btn_import": "📁 Nạp Excel", "btn_restore": "↩️ Khôi phục gốc",
"btn_clear": "🗑️ Xóa File", "btn_export": "📤 Xuất Excel", "btn_readme": "📖 Read Me"
},
"English": {
"title": "📊 EXCEL MANAGEMENT", "coord": "📍 Data Coordinates:", "row_p": "Row (2)", "col_p": "Col (A)",
"exclude": "🚫 EXCLUDE COLUMNS", "from": "From (ex: B)", "to": "To (ex: C)",
"compare": "⚖️ CORRESPONDING COMP.", "osd_lim": "OSD Limit Col:", "help_lim": "HELP Limit Col:",
"btn_process": "🚀 PROCESS & COMPARE", "btn_import": "📁 Import Excel", "btn_restore": "↩️ Restore Original",
"btn_clear": "🗑️ Clear File", "btn_export": "📤 Export Excel", "btn_readme": "📖 Read Me"
},
"日本語 (Nhật)": {
"title": "📊 Excel 管理", "coord": "📍 データ座標:", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 除外列範囲", "from": "から", "to": "まで",
"compare": "⚖️ 比較設定", "osd_lim": "OSD 制限列:", "help_lim": "HELP 制限列:",
"btn_process": "🚀 実行と比較", "btn_import": "📁 Excel 読み込み", "btn_restore": "↩️ 元に戻す",
"btn_clear": "🗑️ クリア", "btn_export": "📤 Excel 出力", "btn_readme": "📖 リードミー"
},
"한국어 (Hàn)": {
"title": "📊 엑셀 관리", "coord": "📍 데이터 좌표:", "row_p": "행 (2)", "col_p": "열 (A)",
"exclude": "🚫 제외 열 범위", "from": "시작", "to": "끝",
"compare": "⚖️ 상응 비교", "osd_lim": "OSD 제한 열:", "help_lim": "HELP 제한 열:",
"btn_process": "🚀 처리 및 비교", "btn_import": "📁 엑셀 불러오기", "btn_restore": "↩️ 원본 복구",
"btn_clear": "🗑️ 파일 삭제", "btn_export": "📤 엑셀 내보내기", "btn_readme": "📖 설명서"
},
"中文 (Trung)": {
"title": "📊 Excel 管理", "coord": "📍 数据坐标:", "row_p": "行 (2)", "col_p": "列 (A)",
"exclude": "🚫 排除列范围", "from": "从", "to": "到",
"compare": "⚖️ 相应比较", "osd_lim": "OSD 限制列:", "help_lim": "HELP 限制列:",
"btn_process": "🚀 处理与比较", "btn_import": "📁 导入 Excel", "btn_restore": "↩️ 恢复原始",
"btn_clear": "🗑️ 删除文件", "btn_export": "📤 导出 Excel", "btn_readme": "📖 阅读说明"
},
"Deutsch (Đức)": { "title": "📊 EXCEL-VERWALTUNG", "coord": "📍 Datenkoordinaten:", "row_p": "Zeile", "col_p": "Spalte", "exclude": "🚫 SPALTEN AUSSCHLIESSEN", "from": "Von", "to": "Bis", "compare": "⚖️ VERGLEICH", "osd_lim": "OSD-Limit:", "help_lim": "HELP-Limit:", "btn_process": "🚀 VERARBEITEN", "btn_import": "📁 Importieren", "btn_restore": "↩️ Wiederherstellen", "btn_clear": "🗑️ Löschen", "btn_export": "📤 Exportieren", "btn_readme": "📖 Read Me" },
"Français (Pháp)": { "title": "📊 GESTION EXCEL", "coord": "📍 Coordonnées:", "row_p": "Ligne", "col_p": "Col", "exclude": "🚫 EXCLURE COLONNES", "from": "De", "to": "À", "compare": "⚖️ COMPARAISON", "osd_lim": "Limite OSD:", "help_lim": "Limite HELP:", "btn_process": "🚀 TRAITER", "btn_import": "📁 Importer", "btn_restore": "↩️ Restaurer", "btn_clear": "🗑️ Effacer", "btn_export": "📤 Exporter", "btn_readme": "📖 Read Me" },
"Italiano (Ý)": { "title": "📊 GESTIONE EXCEL", "coord": "📍 Coordinate dati:", "row_p": "Riga", "col_p": "Col", "exclude": "🚫 ESCLUDI COLONNE", "from": "Da", "to": "A", "compare": "⚖️ CONFRONTO", "osd_lim": "Limite OSD:", "help_lim": "Limite HELP:", "btn_process": "🚀 ELABORA", "btn_import": "📁 Importa", "btn_restore": "↩️ Ripristina", "btn_clear": "🗑️ Cancella", "btn_export": "📤 Esporta", "btn_readme": "📖 Read Me" },
"Español (Tây Ban Nha)": { "title": "📊 GESTIÓN DE EXCEL", "coord": "📍 Coordenadas:", "row_p": "Fila", "col_p": "Col", "exclude": "🚫 EXCLUIR COLUMNAS", "from": "Desde", "to": "Hasta", "compare": "⚖️ COMPARACIÓN", "osd_lim": "Límite OSD:", "help_lim": "Límite HELP:", "btn_process": "🚀 PROCESAR", "btn_import": "📁 Importar", "btn_restore": "↩️ Restaurar", "btn_clear": "🗑️ Borrar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me" },
"Português (Bồ Đào Nha)": { "title": "📊 GESTÃO DE EXCEL", "coord": "📍 Coordenadas:", "row_p": "Linha", "col_p": "Col", "exclude": "🚫 EXCLUIR COLUNAS", "from": "De", "to": "Para", "compare": "⚖️ COMPARAÇÃO", "osd_lim": "Limite OSD:", "help_lim": "Limite HELP:", "btn_process": "🚀 PROCESSAR", "btn_import": "📁 Importar", "btn_restore": "↩️ Restaurar", "btn_clear": "🗑️ Limpar", "btn_export": "📤 Exportar", "btn_readme": "📖 Read Me" },
"Türkçe (Thổ Nhĩ Kỳ)": { "title": "📊 EXCEL YÖNETİMİ", "coord": "📍 Veri Koordinatları:", "row_p": "Satır", "col_p": "Sütun", "exclude": "🚫 SÜTUNLARI HARİÇ TUT", "from": "Başlangıç", "to": "Bitiş", "compare": "⚖️ KARŞILAŞTIRMA", "osd_lim": "OSD Sınır:", "help_lim": "HELP Sınır:", "btn_process": "🚀 İŞLE", "btn_import": "📁 Yükle", "btn_restore": "↩️ Geri Yükle", "btn_clear": "🗑️ Sil", "btn_export": "📤 Aktar", "btn_readme": "📖 Read Me" },
"Русский (Nga)": { "title": "📊 УПРАВЛЕНИЕ EXCEL", "coord": "📍 Координаты данных:", "row_p": "Строка", "col_p": "Столбец", "exclude": "🚫 ИСКЛЮЧИТЬ", "from": "От", "to": "До", "compare": "⚖️ СРАВНЕНИЕ", "osd_lim": "Лимит OSD:", "help_lim": "Лимит HELP:", "btn_process": "🚀 ОБРАБОТАТЬ", "btn_import": "📁 Импорт", "btn_restore": "↩️ Восстановить", "btn_clear": "🗑️ Удалить", "btn_export": "📤 Экспорт", "btn_readme": "📖 Read Me" },
"Українська (Ukraine)": { "title": "📊 УПРАВЛІННЯ EXCEL", "coord": "📍 Координати даних:", "row_p": "Рядок", "col_p": "Стовпець", "exclude": "🚫 ВИКЛЮЧИТИ", "from": "Від", "to": "До", "compare": "⚖️ ПОРІВНЯННЯ", "osd_lim": "Межа OSD:", "help_lim": "Межа HELP:", "btn_process": "🚀 ОБРОБИТИ", "btn_import": "📁 Імпорт", "btn_restore": "↩️ Відновити", "btn_clear": "🗑️ Видалити", "btn_export": "📤 Експорт", "btn_readme": "📖 Read Me" },
"Magyar (Hungary)": { "title": "📊 EXCEL KEZELÉS", "coord": "📍 Koordináták:", "row_p": "Sor", "col_p": "Oszlop", "exclude": "🚫 KIHAGYÁS", "from": "Ettől", "to": "Eddig", "compare": "⚖️ ÖSSZEHAS.", "osd_lim": "OSD limit:", "help_lim": "HELP limit:", "btn_process": "🚀 FELDOLGOZÁS", "btn_import": "📁 Betöltés", "btn_restore": "↩️ Visszaállítás", "btn_clear": "🗑️ Törlés", "btn_export": "📤 Mentés", "btn_readme": "📖 Read Me" },
"Oʻzbekcha (Uzbek)": { "title": "📊 EXCEL BOSHQARUVI", "coord": "📍 Koordinata:", "row_p": "Qator", "col_p": "Ustun", "exclude": "🚫 CHIQARISH", "from": "Dan", "to": "Gacha", "compare": "⚖️ TAQQOSLASH", "osd_lim": "OSD limiti:", "help_lim": "HELP limiti:", "btn_process": "🚀 ISHLOV BERISH", "btn_import": "📁 Yuklash", "btn_restore": "↩️ Tiklash", "btn_clear": "🗑️ O'chirish", "btn_export": "📤 Eksport", "btn_readme": "📖 Read Me" }
}
# --- CỬA SỔ XEM PDF NỘI BỘ (GIỮ NGUYÊN) ---
class PDFViewerWindow(ctk.CTkToplevel):
def __init__(self, master, file_path):
super().__init__(master)
self.title("Hướng dẫn sử dụng - PDF Viewer")
self.geometry("1000x850")
try:
self.pdf_doc = fitz.open(file_path)
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể đọc file PDF: {e}")
self.destroy()
return
self.current_page = 0
self.ctrl_frame = ctk.CTkFrame(self)
self.ctrl_frame.pack(side="top", fill="x", padx=10, pady=5)
ctk.CTkButton(self.ctrl_frame, text="<< Trang trước", command=self.prev_p).pack(side="left", padx=10)
self.page_lab = ctk.CTkLabel(self.ctrl_frame, text="")
self.page_lab.pack(side="left", expand=True)
ctk.CTkButton(self.ctrl_frame, text="Trang sau >>", command=self.next_p).pack(side="right", padx=10)
self.scroll_frame = ctk.CTkScrollableFrame(self)
self.scroll_frame.pack(fill="both", expand=True, padx=10, pady=10)
self.img_label = ctk.CTkLabel(self.scroll_frame, text="")
self.img_label.pack()
self.show_p(0)
def show_p(self, n):
if 0 <= n < len(self.pdf_doc):
page = self.pdf_doc.load_page(n)
pix = page.get_pixmap(matrix=fitz.Matrix(1.5, 1.5))
img = Image.frombytes("RGB", [pix.width, pix.height], pix.samples)
ctk_img = ctk.CTkImage(light_image=img, dark_image=img, size=(pix.width, pix.height))
self.img_label.configure(image=ctk_img)
self.img_label.image = ctk_img
self.current_page = n
self.page_lab.configure(text=f"Trang {n + 1} / {len(self.pdf_doc)}")
def prev_p(self): self.show_p(self.current_page - 1)
def next_p(self): self.show_p(self.current_page + 1)
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - OSD Ultimate (Standard Version)")
self.geometry("1450x900")
ctk.set_appearance_mode("light")
self.current_file_path = None
self.backup_data = None
self.backup_highlights = None
self.backup_headers = None
self.readme_win = None
# --- SIDEBAR GUI ---
self.sidebar = ctk.CTkFrame(self, width=300, corner_radius=0)
self.sidebar.pack(side="left", fill="y")
self.main_title_lab = ctk.CTkLabel(self.sidebar, text="", font=("Arial", 22, "bold"), text_color="#2C3E50")
self.main_title_lab.pack(pady=20)
# Bộ chọn ngôn ngữ (Mới thêm vào giao diện)
self.lang_var = ctk.StringVar(value="Tiếng Việt")
self.lang_menu = ctk.CTkOptionMenu(self.sidebar, values=list(LANG_DICT.keys()), variable=self.lang_var, command=self.update_ui_text)
self.lang_menu.pack(pady=(0, 10), padx=20, fill="x")
# Tọa độ dữ liệu
self.setup_group = ctk.CTkFrame(self.sidebar, fg_color="transparent")
self.setup_group.pack(pady=5, padx=20, fill="x")
self.coord_lab = ctk.CTkLabel(self.setup_group, text="", font=("Arial", 12, "bold"))
self.coord_lab.pack(anchor="w")
self.row_start_entry = ctk.CTkEntry(self.setup_group, width=120)
self.row_start_entry.insert(0, "2")
self.row_start_entry.pack(side="left", pady=5, padx=(0, 5))
self.col_start_entry = ctk.CTkEntry(self.setup_group, width=120)
self.col_start_entry.insert(0, "A")
self.col_start_entry.pack(side="left", pady=5)
# Loại trừ
self.exclude_group = ctk.CTkFrame(self.sidebar, fg_color="#FADBD8", corner_radius=10)
self.exclude_group.pack(pady=10, padx=20, fill="x")
self.exclude_lab = ctk.CTkLabel(self.exclude_group, text="", font=("Arial", 12, "bold"), text_color="#C0392B")
self.exclude_lab.pack(pady=5)
self.exclude_start_entry = ctk.CTkEntry(self.exclude_group, width=115)
self.exclude_start_entry.pack(side="left", padx=10, pady=10)
self.exclude_end_entry = ctk.CTkEntry(self.exclude_group, width=115)
self.exclude_end_entry.pack(side="left", padx=5, pady=10)
# Hạn mức
self.compare_group = ctk.CTkFrame(self.sidebar, fg_color="#F2F4F4", corner_radius=10)
self.compare_group.pack(pady=10, padx=20, fill="x")
self.compare_lab = ctk.CTkLabel(self.compare_group, text="", font=("Arial", 12, "bold"))
self.compare_lab.pack(pady=5)
self.osd_limit_lab = ctk.CTkLabel(self.compare_group, text="", font=("Arial", 11))
self.osd_limit_lab.pack(anchor="w", padx=15)
self.osd_limit_col_entry = ctk.CTkEntry(self.compare_group, width=220)
self.osd_limit_col_entry.pack(pady=2, padx=15)
self.help_limit_lab = ctk.CTkLabel(self.compare_group, text="", font=("Arial", 11))
self.help_limit_lab.pack(anchor="w", padx=15)
self.help_limit_col_entry = ctk.CTkEntry(self.compare_group, width=220)
self.help_limit_col_entry.pack(pady=(2, 10), padx=15)
# Nút bấm chính
self.btn_process = ctk.CTkButton(self.sidebar, text="", fg_color="#E67E22", hover_color="#D35400", height=50, font=("Arial", 14, "bold"), command=self.process_advanced_logic)
self.btn_process.pack(pady=20, padx=20, fill="x")
self.btn_import = ctk.CTkButton(self.sidebar, text="", fg_color="#34495E", command=self.import_excel)
self.btn_import.pack(pady=5, padx=20, fill="x")
self.btn_restore = ctk.CTkButton(self.sidebar, text="", fg_color="#5D6D7E", command=self.restore_backup)
self.btn_restore.pack(pady=5, padx=20, fill="x")
self.btn_clear = ctk.CTkButton(self.sidebar, text="", fg_color="#C0392B", command=self.clear_file)
self.btn_clear.pack(pady=5, padx=20, fill="x")
self.btn_export = ctk.CTkButton(self.sidebar, text="", fg_color="#27AE60", command=self.export_excel)
self.btn_export.pack(pady=5, padx=20, fill="x")
# NÚT READ ME
self.btn_readme = ctk.CTkButton(self.sidebar, text="", fg_color="#7F8C8D", hover_color="#95A5A6", command=self.open_readme_smart)
self.btn_readme.pack(pady=(20, 5), padx=20, fill="x")
# --- BẢNG DỮ LIỆU ---
self.work_area = ctk.CTkFrame(self)
self.work_area.pack(side="right", fill="both", expand=True, padx=10, pady=10)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
# Khởi tạo text ban đầu
self.update_ui_text("Tiếng Việt")
# --- HÀM CẬP NHẬT NGÔN NGỮ (THÊM MỚI) ---
def update_ui_text(self, choice):
d = LANG_DICT.get(choice, LANG_DICT["Tiếng Việt"])
self.main_title_lab.configure(text=d["title"])
self.coord_lab.configure(text=d["coord"])
self.row_start_entry.configure(placeholder_text=d["row_p"])
self.col_start_entry.configure(placeholder_text=d["col_p"])
self.exclude_lab.configure(text=d["exclude"])
self.exclude_start_entry.configure(placeholder_text=d["from"])
self.exclude_end_entry.configure(placeholder_text=d["to"])
self.compare_lab.configure(text=d["compare"])
self.osd_limit_lab.configure(text=d["osd_lim"])
self.help_limit_lab.configure(text=d["help_lim"])
self.btn_process.configure(text=d["btn_process"])
self.btn_import.configure(text=d["btn_import"])
self.btn_restore.configure(text=d["btn_restore"])
self.btn_clear.configure(text=d["btn_clear"])
self.btn_export.configure(text=d["btn_export"])
self.btn_readme.configure(text=d["btn_readme"])
# --- HÀM MỞ README THÔNG MINH (GIỮ NGUYÊN) ---
def open_readme_smart(self):
base_dir = r"D:\Python\T1"
files = ["readme.pdf", "readme.docx", "readme.doc"]
target = None
for f in files:
p = os.path.join(base_dir, f)
if os.path.exists(p):
target = p
break
if not target:
messagebox.showerror("Lỗi", f"Không tìm thấy file Readme trong {base_dir}")
return
ext = os.path.splitext(target)[1].lower()
if ext == ".pdf":
if self.readme_win is None or not self.readme_win.winfo_exists():
self.readme_win = PDFViewerWindow(self, target)
self.readme_win.focus()
else:
try:
if sys.platform == "win32":
os.startfile(target)
else:
opener = "open" if sys.platform == "darwin" else "xdg-open"
import subprocess
subprocess.call([opener, target])
except Exception as e:
messagebox.showerror("Lỗi", f"Không thể mở file Word: {e}")
# --- TẤT CẢ CÁC HÀM LOGIC DƯỚI ĐÂY GIỮ NGUYÊN 100% ---
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def col_to_idx(self, letter):
if not letter: return -1
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
def get_exclude_range(self):
s, e = self.exclude_start_entry.get().strip().upper(), self.exclude_end_entry.get().strip().upper()
if not s or not e: return []
try:
si, ei = self.col_to_idx(s), self.col_to_idx(e)
return list(range(min(si, ei), max(si, ei) + 1))
except: return []
def get_actual_hex(self, fill_obj):
if not fill_obj or not fill_obj.start_color: return None
clr = fill_obj.start_color
rgb = None
if clr.type == 'rgb' and clr.rgb: rgb = str(clr.rgb)
elif clr.type == 'theme' and clr.theme is not None:
themes = ['ffffff', '000000', 'eeece1', '1f497d', '4f81bd', 'c0504d', '9bbb59', '8064a2', '4bacc6', 'f79646']
try: rgb = themes[clr.theme]
except: return None
elif clr.type == 'indexed' and clr.indexed is not None:
try: rgb = COLOR_INDEX[clr.indexed]
except: return None
if not rgb or rgb == '00000000': return None
if len(rgb) == 8: rgb = rgb[2:]
try:
r, g, b = int(rgb[0:2], 16), int(rgb[2:4], 16), int(rgb[4:6], 16)
if clr.tint:
t = clr.tint
if t < 0: r, g, b = int(r*(1+t)), int(g*(1+t)), int(b*(1+t))
else: r, g, b = int(r+(255-r)*t), int(g+(255-g)*t), int(b+(255-b)*t)
return f'#{max(0, min(255, r)):02x}{max(0, min(255, g)):02x}{max(0, min(255, b)):02x}'
except: return None
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if path:
self.current_file_path = path
wb = openpyxl.load_workbook(path, data_only=True)
ws = wb.active
max_r, max_c = ws.max_row, ws.max_column
data = [[(ws.cell(row=r, column=c).value if ws.cell(row=r, column=c).value is not None else "") for c in range(1, max_c + 1)] for r in range(1, max_r + 1)]
self.sheet.set_sheet_data(data, redraw=False)
self.sheet.headers([self.idx_to_col(i) for i in range(max_c)])
for r_idx in range(1, max_r + 1):
for c_idx in range(1, max_c + 1):
bg = self.get_actual_hex(ws.cell(row=r_idx, column=c_idx).fill)
if bg and bg.lower() != "#ffffff":
self.sheet.highlight_cells(row=r_idx-1, column=c_idx-1, bg=bg, redraw=False)
self.sheet.redraw()
def process_advanced_logic(self):
try:
start_row = int(self.row_start_entry.get() or 2) - 1
start_col_idx = self.col_to_idx(self.col_start_entry.get())
exclude_indices = self.get_exclude_range()
osd_target_input = self.osd_limit_col_entry.get().upper().strip()
help_target_input = self.help_limit_col_entry.get().upper().strip()
orig_osd_limit_idx = self.col_to_idx(osd_target_input)
orig_help_limit_idx = self.col_to_idx(help_target_input)
except Exception as e:
messagebox.showerror("Lỗi", f"Thông số không đúng: {e}")
return
old_data = self.sheet.get_sheet_data()
if not old_data: return
self.backup_data = [row[:] for row in old_data]
self.backup_headers = list(self.sheet.headers())
self.backup_highlights = self.sheet.get_highlighted_cells().copy()
highlighted = self.sheet.get_highlighted_cells()
new_data, final_headers, new_highlights = [], [], {}
original_header_row = old_data[0]
for r_idx, row in enumerate(old_data):
processed_row = []
target_osd_word, target_osd_len = 0, 0
target_help_word, target_help_len = 0, 0
limit_val_osd, limit_val_help = 0.0, 0.0
max_word_osd, max_len_osd = 0, 0
max_word_help, max_len_help = 0, 0
if r_idx >= start_row:
try:
v_o = str(row[orig_osd_limit_idx]).strip()
limit_val_osd = float(v_o) if v_o and v_o.lower() != "none" else 0.0
v_h = str(row[orig_help_limit_idx]).strip()
limit_val_help = float(v_h) if v_h and v_h.lower() != "none" else 0.0
except: pass
curr_c = 0
for c_idx, val in enumerate(row):
processed_row.append(val)
if r_idx == 0: final_headers.append(val if val else self.idx_to_col(c_idx))
if (r_idx, c_idx) in highlighted:
new_highlights[(r_idx, curr_c)] = highlighted[(r_idx, c_idx)]
curr_c += 1
if c_idx >= start_col_idx:
lw, wc = 0, 0
h_name = str(original_header_row[c_idx]).upper() if original_header_row[c_idx] else ""
if r_idx == 0:
lw, wc = f"LW_{final_headers[curr_c-1]}", f"WC_{final_headers[curr_c-1]}"
elif r_idx >= start_row:
txt = str(val).strip()
if txt and txt.lower() not in ["none", "nan"]:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
lw = len(max(words, key=len)) if words else 0
wc = len(txt.split())
if c_idx == orig_osd_limit_idx:
target_osd_word, target_osd_len = lw, wc
if c_idx == orig_help_limit_idx:
target_help_word, target_help_len = lw, wc
if c_idx not in exclude_indices:
if "HELP" in h_name:
max_word_help, max_len_help = max(max_word_help, lw), max(max_len_help, wc)
else:
max_word_osd, max_len_osd = max(max_word_osd, lw), max(max_len_osd, wc)
processed_row.extend([lw, wc])
if r_idx == 0: final_headers.extend([lw, wc])
curr_c += 2
if r_idx == 0:
res_h = ["MAX Word OSD", "RES Word OSD", "MAX Word HELP", "RES Word HELP",
"MAX Len OSD", "RES Len OSD", "MAX Len HELP", "RES Len HELP"]
processed_row.extend(res_h)
final_headers.extend(res_h)
else:
res_w_o = "TRUE" if max_word_osd >= target_osd_word else "FALSE"
res_w_h = "TRUE" if max_word_help >= target_help_word else "FALSE"
res_l_o = "TRUE" if max_len_osd >= target_osd_len else "FALSE"
res_l_h = "TRUE" if max_len_help >= target_help_len else "FALSE"
processed_row.extend([max_word_osd, res_w_o, max_word_help, res_w_h,
max_len_osd, res_l_o, max_len_help, res_l_h])
new_data.append(processed_row)
self.sheet.set_sheet_data(new_data, redraw=False)
self.sheet.headers(final_headers)
self.sheet.dehighlight_cells()
for pos, color in new_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
for c in range(len(final_headers) - 8, len(final_headers)):
self.sheet.highlight_cells(row=0, column=c, bg="#FFD700", redraw=False)
self.sheet.redraw()
messagebox.showinfo("Thành công", "Đã xử lý logic Max và hiển thị Debug.")
def restore_backup(self):
if self.backup_data is None: return
self.sheet.dehighlight_cells()
self.sheet.headers(self.backup_headers)
self.sheet.set_sheet_data(self.backup_data, redraw=False)
for pos, color in self.backup_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=(color[0] if isinstance(color, (tuple, list)) else color), redraw=False)
self.sheet.redraw()
def clear_file(self):
self.sheet.set_sheet_data([], redraw=True)
self.sheet.headers([])
self.sheet.dehighlight_cells()
def export_excel(self):
data = self.sheet.get_sheet_data()
if not data: return
path = filedialog.asksaveasfilename(defaultextension=".xlsx")
if path:
wb = openpyxl.Workbook()
ws = wb.active
h_cells = self.sheet.get_highlighted_cells()
for r_idx, row in enumerate(data, 1):
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=val)
if (r_idx-1, c_idx-1) in h_cells:
color_val = h_cells[(r_idx-1, c_idx-1)]
hex_c = (color_val[0] if isinstance(color_val, (tuple, list)) else color_val).replace("#", "")
cell.fill = PatternFill(start_color=hex_c, end_color=hex_c, fill_type="solid")
wb.save(path)
if __name__ == "__main__":
app = ExcelMasterTool()
app.mainloop()
V3
import customtkinter as ctk
import pandas as pd
import string
import openpyxl
from tkinter import filedialog, messagebox
from tksheet import Sheet
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import COLOR_INDEX
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - OSD Ultimate (Fixed Color & Final Columns)")
self.geometry("1450x900")
ctk.set_appearance_mode("light")
self.current_file_path = None
self.backup_data = None
self.backup_highlights = None
self.backup_headers = None
# --- SIDEBAR (Giữ nguyên giao diện của bạn) ---
self.sidebar = ctk.CTkFrame(self, width=300, corner_radius=0)
self.sidebar.pack(side="left", fill="y")
ctk.CTkLabel(self.sidebar, text="📊 QUẢN LÝ EXCEL", font=("Arial", 22, "bold"), text_color="#2C3E50").pack(pady=20)
self.setup_group = ctk.CTkFrame(self.sidebar, fg_color="transparent")
self.setup_group.pack(pady=5, padx=20, fill="x")
ctk.CTkLabel(self.setup_group, text="📍 Tọa độ dữ liệu:", font=("Arial", 12, "bold")).pack(anchor="w")
self.row_start_entry = ctk.CTkEntry(self.setup_group, width=120, placeholder_text="Dòng (2)")
self.row_start_entry.insert(0, "2")
self.row_start_entry.pack(side="left", pady=5, padx=(0, 5))
self.col_start_entry = ctk.CTkEntry(self.setup_group, width=120, placeholder_text="Cột (A)")
self.col_start_entry.insert(0, "A")
self.col_start_entry.pack(side="left", pady=5)
self.exclude_group = ctk.CTkFrame(self.sidebar, fg_color="#FADBD8", corner_radius=10)
self.exclude_group.pack(pady=10, padx=20, fill="x")
ctk.CTkLabel(self.exclude_group, text="🚫 DẢI CỘT LOẠI TRỪ", font=("Arial", 12, "bold"), text_color="#C0392B").pack(pady=5)
self.exclude_start_entry = ctk.CTkEntry(self.exclude_group, width=115, placeholder_text="Từ (vd: B)")
self.exclude_start_entry.pack(side="left", padx=10, pady=10)
self.exclude_end_entry = ctk.CTkEntry(self.exclude_group, width=115, placeholder_text="Đến (vd: C)")
self.exclude_end_entry.pack(side="left", padx=5, pady=10)
self.compare_group = ctk.CTkFrame(self.sidebar, fg_color="#D5F5E3", corner_radius=10)
self.compare_group.pack(pady=10, padx=20, fill="x")
ctk.CTkLabel(self.compare_group, text="⚖️ SO SÁNH TƯƠNG ỨNG", font=("Arial", 12, "bold"), text_color="#1E8449").pack(pady=5)
ctk.CTkLabel(self.compare_group, text="Cột hạn mức OSD:", font=("Arial", 11)).pack(anchor="w", padx=15)
self.osd_limit_col_entry = ctk.CTkEntry(self.compare_group, width=220, placeholder_text="Ví dụ: Z")
self.osd_limit_col_entry.pack(pady=2, padx=15)
ctk.CTkLabel(self.compare_group, text="Cột hạn mức HELP:", font=("Arial", 11)).pack(anchor="w", padx=15)
self.help_limit_col_entry = ctk.CTkEntry(self.compare_group, width=220, placeholder_text="Ví dụ: AA")
self.help_limit_col_entry.pack(pady=(2, 10), padx=15)
self.btn_process = ctk.CTkButton(self.sidebar, text="🚀 XỬ LÝ & SO SÁNH", fg_color="#E67E22", hover_color="#D35400", height=50, font=("Arial", 14, "bold"), command=self.process_advanced_logic)
self.btn_process.pack(pady=20, padx=20, fill="x")
ctk.CTkButton(self.sidebar, text="📁 Nạp Excel", fg_color="#34495E", command=self.import_excel).pack(pady=5, padx=20, fill="x")
ctk.CTkButton(self.sidebar, text="↩️ Khôi phục gốc", fg_color="#5D6D7E", command=self.restore_backup).pack(pady=5, padx=20, fill="x")
ctk.CTkButton(self.sidebar, text="🗑️ Xóa File đã nạp", fg_color="#C0392B", hover_color="#922B21", command=self.clear_file).pack(pady=5, padx=20, fill="x")
ctk.CTkButton(self.sidebar, text="📤 Xuất Excel", fg_color="#27AE60", command=self.export_excel).pack(pady=5, padx=20, fill="x")
# --- BẢNG DỮ LIỆU ---
self.work_area = ctk.CTkFrame(self)
self.work_area.pack(side="right", fill="both", expand=True, padx=10, pady=10)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
def restore_backup(self):
if self.backup_data is None:
messagebox.showinfo("Thông báo", "Không có bản lưu tạm nào để khôi phục.")
return
self.sheet.set_sheet_data(self.backup_data, redraw=False)
self.sheet.headers(self.backup_headers)
self.sheet.dehighlight_all_cells()
for pos, color in self.backup_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=color[0], redraw=False)
self.sheet.redraw()
def get_exclude_range(self):
s, e = self.exclude_start_entry.get().strip().upper(), self.exclude_end_entry.get().strip().upper()
if not s or not e: return []
try:
si, ei = self.col_to_idx(s), self.col_to_idx(e)
return list(range(min(si, ei), max(si, ei) + 1))
except: return []
def get_actual_hex(self, fill_obj):
if not fill_obj or not fill_obj.start_color: return None
clr = fill_obj.start_color
rgb = None
if clr.type == 'rgb' and clr.rgb: rgb = str(clr.rgb)
elif clr.type == 'theme' and clr.theme is not None:
themes = ['ffffff', '000000', 'eeece1', '1f497d', '4f81bd', 'c0504d', '9bbb59', '8064a2', '4bacc6', 'f79646']
try: rgb = themes[clr.theme]
except: return None
elif clr.type == 'indexed' and clr.indexed is not None:
try: rgb = COLOR_INDEX[clr.indexed]
except: return None
if not rgb or rgb == '00000000': return None
if len(rgb) == 8: rgb = rgb[2:]
try:
r, g, b = int(rgb[0:2], 16), int(rgb[2:4], 16), int(rgb[4:6], 16)
if clr.tint:
t = clr.tint
if t < 0: r, g, b = int(r*(1+t)), int(g*(1+t)), int(b*(1+t))
else: r, g, b = int(r+(255-r)*t), int(g+(255-g)*t), int(b+(255-b)*t)
return f'#{max(0, min(255, r)):02x}{max(0, min(255, g)):02x}{max(0, min(255, b)):02x}'
except: return None
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if path:
self.current_file_path = path
wb = openpyxl.load_workbook(path, data_only=True)
ws = wb.active
max_r, max_c = ws.max_row, ws.max_column
data = [[(ws.cell(row=r, column=c).value if ws.cell(row=r, column=c).value is not None else "") for c in range(1, max_c + 1)] for r in range(1, max_r + 1)]
self.sheet.set_sheet_data(data, redraw=False)
self.sheet.headers([self.idx_to_col(i) for i in range(max_c)])
for r_idx in range(1, max_r + 1):
for c_idx in range(1, max_c + 1):
bg = self.get_actual_hex(ws.cell(row=r_idx, column=c_idx).fill)
if bg and bg.lower() != "#ffffff":
self.sheet.highlight_cells(row=r_idx-1, column=c_idx-1, bg=bg, redraw=False)
self.sheet.redraw()
def clear_file(self):
if not self.sheet.get_sheet_data(): return
if messagebox.askyesno("Xác nhận", "Xóa toàn bộ dữ liệu?"):
self.current_file_path = None
self.sheet.set_sheet_data([], redraw=True)
self.sheet.headers([])
self.sheet.dehighlight_all_cells()
def process_advanced_logic(self):
try:
start_row = int(self.row_start_entry.get() or 2) - 1
start_col_idx = self.col_to_idx(self.col_start_entry.get())
exclude_indices = self.get_exclude_range()
osd_limit_col = self.col_to_idx(self.osd_limit_col_entry.get())
help_limit_col = self.col_to_idx(self.help_limit_col_entry.get())
except:
messagebox.showerror("Lỗi", "Kiểm tra lại tham số tọa độ!")
return
old_data = self.sheet.get_sheet_data()
if not old_data: return
# Backup trước khi xử lý
self.backup_data = [row[:] for row in old_data]
self.backup_highlights = self.sheet.get_highlighted_cells().copy()
self.backup_headers = self.sheet.headers()
highlighted = self.sheet.get_highlighted_cells()
new_data, headers, new_highlights = [], [], {}
original_header_row = old_data[0]
for r_idx, row in enumerate(old_data):
new_row, osd_vals, help_vals, curr_c = [], [], [], 0
limit_osd_val, limit_help_val = 0.0, 0.0
# Lấy hạn mức
if r_idx >= start_row:
try:
limit_osd_val = float(row[osd_limit_col]) if str(row[osd_limit_col]).strip() else 0.0
limit_help_val = float(row[help_limit_col]) if str(row[help_limit_col]).strip() else 0.0
except: pass
for c_idx, val in enumerate(row):
# Lấy màu gốc của ô hiện tại
original_color = highlighted.get((r_idx, c_idx))
# 1. Giữ cột gốc và áp lại màu
new_row.append(val)
if r_idx == 0: headers.append(val if val else self.idx_to_col(c_idx))
if original_color:
new_highlights[(r_idx, curr_c)] = original_color
curr_c += 1
# 2. Chèn các cột Longest và Length
if c_idx >= start_col_idx:
lw_v, l_v = "", 0
h_name = str(original_header_row[c_idx]) if original_header_row[c_idx] else self.idx_to_col(c_idx)
if r_idx == 0:
lw_v = f"Longest word {h_name}"
l_v = f"Length {h_name}"
elif r_idx >= start_row:
txt = str(val).strip()
if txt and txt not in ["None", "nan", ""]:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
if words:
lw_v = len(max(words, key=len))
l_v = len(words)
else: lw_v, l_v = 0, 0
else: lw_v, l_v = 0, 0
# Thêm Longest word + Giữ màu gốc
new_row.append(lw_v)
if r_idx == 0: headers.append(lw_v)
if original_color:
new_highlights[(r_idx, curr_c)] = original_color
curr_c += 1
# Thêm Length + Giữ màu gốc
new_row.append(l_v)
if r_idx == 0: headers.append(l_v)
if original_color:
new_highlights[(r_idx, curr_c)] = original_color
curr_c += 1
# Gom giá trị để tính MAX
if r_idx >= start_row and c_idx not in exclude_indices:
if isinstance(lw_v, (int, float)):
if "HELP" in h_name.upper(): help_vals.append(float(lw_v))
else: osd_vals.append(float(lw_v))
# 3. THÊM 8 CỘT KẾT QUẢ CUỐI CÙNG NHƯ CSV
if r_idx == 0:
res_headers = [
"Longest word SID OSD", "Compare Longest word RESULT Length SID OSD",
"Longest word OSD HELP", "Compare Longest word RESULT Length OSD HELP",
"Max_LengTh OSD", "Compare Cột Hạn Mức OSD",
"Max_LengTh OSD HELP", "Compare Cột Hạn Mức OSD HELP"
]
new_row.extend(res_headers)
headers.extend(res_headers)
else:
m_osd = max(osd_vals) if osd_vals else 0
m_help = max(help_vals) if help_vals else 0
# Logic: m_osd > limit_osd_val -> FALSE, ngược lại TRUE
c_osd = "FALSE" if m_osd > limit_osd_val else "TRUE"
c_help = "FALSE" if m_help > limit_help_val else "TRUE"
new_row.extend([m_osd, c_osd, m_help, c_help, m_osd, c_osd, m_help, c_help])
new_data.append(new_row)
# Cập nhật bảng
self.sheet.set_sheet_data(new_data, redraw=False)
self.sheet.headers(headers)
self.sheet.dehighlight_all_cells()
# Áp dụng bản đồ màu mới đã tính toán lại index
for pos, color_info in new_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=color_info[0], redraw=False)
# Tô màu vàng cho các cột tiêu đề kết quả cuối (tùy chọn)
total_cols = len(headers)
for i in range(1, 9):
self.sheet.highlight_cells(0, total_cols - i, bg="#ff4800", redraw=False)
self.sheet.redraw()
messagebox.showinfo("Xong", "Đã xử lý thành công dữ liệu và màu sắc.")
def export_excel(self):
data = self.sheet.get_sheet_data()
if not data: return
path = filedialog.asksaveasfilename(defaultextension=".xlsx")
if path:
wb = openpyxl.Workbook()
ws = wb.active
h_cells = self.sheet.get_highlighted_cells()
for r_idx, row in enumerate(data, 1):
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=val)
if (r_idx-1, c_idx-1) in h_cells:
hex_c = h_cells[(r_idx-1, c_idx-1)][0].replace("#", "")
cell.fill = PatternFill(start_color=hex_c, end_color=hex_c, fill_type="solid")
wb.save(path)
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def col_to_idx(self, letter):
if not letter: return -1
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
if __name__ == "__main__":
app = ExcelMasterTool()
app.mainloop()
v2
import customtkinter as ctk
import pandas as pd
import string
import openpyxl
from tkinter import filedialog, messagebox
from tksheet import Sheet
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import COLOR_INDEX
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - OSD Ultimate (Fixed Logic Only)")
self.geometry("1450x900")
ctk.set_appearance_mode("light")
self.current_file_path = None
# --- SIDEBAR ---
self.sidebar = ctk.CTkFrame(self, width=300, corner_radius=0)
self.sidebar.pack(side="left", fill="y")
ctk.CTkLabel(self.sidebar, text="📊 QUẢN LÝ EXCEL", font=("Arial", 22, "bold"), text_color="#2C3E50").pack(pady=20)
# 1. Tọa độ bắt đầu
self.setup_group = ctk.CTkFrame(self.sidebar, fg_color="transparent")
self.setup_group.pack(pady=5, padx=20, fill="x")
ctk.CTkLabel(self.setup_group, text="📍 Tọa độ dữ liệu:", font=("Arial", 12, "bold")).pack(anchor="w")
self.row_start_entry = ctk.CTkEntry(self.setup_group, width=120, placeholder_text="Dòng (2)")
self.row_start_entry.insert(0, "2")
self.row_start_entry.pack(side="left", pady=5, padx=(0, 5))
self.col_start_entry = ctk.CTkEntry(self.setup_group, width=120, placeholder_text="Cột (A)")
self.col_start_entry.insert(0, "A")
self.col_start_entry.pack(side="left", pady=5)
# 2. Dải cột loại trừ
self.exclude_group = ctk.CTkFrame(self.sidebar, fg_color="#FADBD8", corner_radius=10)
self.exclude_group.pack(pady=10, padx=20, fill="x")
ctk.CTkLabel(self.exclude_group, text="🚫 DẢI CỘT LOẠI TRỪ", font=("Arial", 12, "bold"), text_color="#C0392B").pack(pady=5)
self.exclude_start_entry = ctk.CTkEntry(self.exclude_group, width=115, placeholder_text="Từ (vd: B)")
self.exclude_start_entry.pack(side="left", padx=10, pady=10)
self.exclude_end_entry = ctk.CTkEntry(self.exclude_group, width=115, placeholder_text="Đến (vd: C)")
self.exclude_end_entry.pack(side="left", padx=5, pady=10)
# 3. So sánh tương ứng theo cột
self.compare_group = ctk.CTkFrame(self.sidebar, fg_color="#D5F5E3", corner_radius=10)
self.compare_group.pack(pady=10, padx=20, fill="x")
ctk.CTkLabel(self.compare_group, text="⚖️ SO SÁNH TƯƠNG ỨNG", font=("Arial", 12, "bold"), text_color="#1E8449").pack(pady=5)
ctk.CTkLabel(self.compare_group, text="Cột hạn mức OSD:", font=("Arial", 11)).pack(anchor="w", padx=15)
self.osd_limit_col_entry = ctk.CTkEntry(self.compare_group, width=220, placeholder_text="Ví dụ: Z")
self.osd_limit_col_entry.pack(pady=2, padx=15)
ctk.CTkLabel(self.compare_group, text="Cột hạn mức HELP:", font=("Arial", 11)).pack(anchor="w", padx=15)
self.help_limit_col_entry = ctk.CTkEntry(self.compare_group, width=220, placeholder_text="Ví dụ: AA")
self.help_limit_col_entry.pack(pady=(2, 10), padx=15)
# Nút xử lý
self.btn_process = ctk.CTkButton(
self.sidebar, text="🚀 XỬ LÝ & SO SÁNH",
fg_color="#E67E22", hover_color="#D35400",
height=50, font=("Arial", 14, "bold"),
command=self.process_advanced_logic
)
self.btn_process.pack(pady=20, padx=20, fill="x")
# Nút file
ctk.CTkButton(self.sidebar, text="📂 Nạp Excel", fg_color="#34495E", command=self.import_excel).pack(pady=5, padx=20, fill="x")
ctk.CTkButton(self.sidebar, text="📤 Xuất Excel", fg_color="#27AE60", command=self.export_excel).pack(pady=5, padx=20, fill="x")
# --- BẢNG DỮ LIỆU ---
self.work_area = ctk.CTkFrame(self)
self.work_area.pack(side="right", fill="both", expand=True, padx=10, pady=10)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
def get_exclude_range(self):
s, e = self.exclude_start_entry.get().strip().upper(), self.exclude_end_entry.get().strip().upper()
if not s or not e: return []
try:
si, ei = self.col_to_idx(s), self.col_to_idx(e)
return list(range(min(si, ei), max(si, ei) + 1))
except: return []
def get_actual_hex(self, fill_obj):
if not fill_obj or not fill_obj.start_color: return None
clr = fill_obj.start_color
rgb = None
if clr.type == 'rgb' and clr.rgb: rgb = str(clr.rgb)
elif clr.type == 'theme' and clr.theme is not None:
themes = ['ffffff', '000000', 'eeece1', '1f497d', '4f81bd', 'c0504d', '9bbb59', '8064a2', '4bacc6', 'f79646']
try: rgb = themes[clr.theme]
except: return None
elif clr.type == 'indexed' and clr.indexed is not None:
try: rgb = COLOR_INDEX[clr.indexed]
except: return None
if not rgb or rgb == '00000000': return None
if len(rgb) == 8: rgb = rgb[2:]
try:
r, g, b = int(rgb[0:2], 16), int(rgb[2:4], 16), int(rgb[4:6], 16)
if clr.tint:
t = clr.tint
if t < 0: r, g, b = int(r*(1+t)), int(g*(1+t)), int(b*(1+t))
else: r, g, b = int(r+(255-r)*t), int(g+(255-g)*t), int(b+(255-b)*t)
return f'#{max(0, min(255, r)):02x}{max(0, min(255, g)):02x}{max(0, min(255, b)):02x}'
except: return None
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel Files", "*.xlsx")])
if path:
self.current_file_path = path
wb = openpyxl.load_workbook(path, data_only=True)
ws = wb.active
max_r, max_c = ws.max_row, ws.max_column
data = [[(ws.cell(row=r, column=c).value if ws.cell(row=r, column=c).value is not None else "") for c in range(1, max_c + 1)] for r in range(1, max_r + 1)]
self.sheet.set_sheet_data(data, redraw=False)
self.sheet.headers([self.idx_to_col(i) for i in range(max_c)])
for r_idx in range(1, max_r + 1):
for c_idx in range(1, max_c + 1):
bg = self.get_actual_hex(ws.cell(row=r_idx, column=c_idx).fill)
if bg and bg.lower() != "#ffffff":
self.sheet.highlight_cells(row=r_idx-1, column=c_idx-1, bg=bg, redraw=False)
self.sheet.redraw()
def process_advanced_logic(self):
try:
start_row = int(self.row_start_entry.get() or 2) - 1
start_col_idx = self.col_to_idx(self.col_start_entry.get())
exclude_indices = self.get_exclude_range()
osd_limit_col = self.col_to_idx(self.osd_limit_col_entry.get())
help_limit_col = self.col_to_idx(self.help_limit_col_entry.get())
except:
messagebox.showerror("Lỗi", "Vui lòng kiểm tra tọa độ cột hạn mức!")
return
old_data = self.sheet.get_sheet_data()
highlighted = self.sheet.get_highlighted_cells()
new_data, headers, new_highlights = [], [], {}
for r_idx, row in enumerate(old_data):
new_row, osd_vals, help_vals, curr_c = [], [], [], 0
limit_osd_val, limit_help_val = 0.0, 0.0
if r_idx >= start_row:
try:
limit_osd_val = float(row[osd_limit_col]) if str(row[osd_limit_col]).strip() else 0.0
limit_help_val = float(row[help_limit_col]) if str(row[help_limit_col]).strip() else 0.0
except: pass
for c_idx, val in enumerate(row):
new_row.append(val)
if r_idx == 0: headers.append(self.idx_to_col(c_idx))
if (r_idx, c_idx) in highlighted:
new_highlights[(r_idx, curr_c)] = highlighted[(r_idx, c_idx)]
curr_c += 1
if c_idx >= start_col_idx:
l_val = ""
if r_idx == 0:
l_val = f"Length {val}" if val else f"Length_{self.idx_to_col(c_idx)}"
elif r_idx >= start_row:
txt = str(val).strip()
if txt and txt not in ["None", "nan", ""]:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
l_val = len(max(words, key=len)) if words else 0
else: l_val = 0
#anh
if c_idx == osd_limit_col:
limit_osd_val = l_val
if c_idx == help_limit_col:
limit_help_val = l_val
new_row.append(l_val)
if r_idx == 0: headers.append(f"L_{self.idx_to_col(c_idx)}")
if (r_idx, c_idx) in highlighted:
new_highlights[(r_idx, curr_c)] = highlighted[(r_idx, c_idx)]
if isinstance(l_val, (int, float)) and r_idx >= start_row:
if c_idx not in exclude_indices:
if c_idx % 2 == 0: osd_vals.append(float(l_val))
else: help_vals.append(float(l_val))
curr_c += 1
if r_idx == 0:
new_row.extend(["MAX_OSD", "RESULT_OSD", "MAX_HELP", "RESULT_HELP"])
headers.extend(["MAX_OSD", "RESULT_OSD", "MAX_HELP", "RESULT_HELP"])
else:
m_osd = max(osd_vals) if osd_vals else 0.0
m_help = max(help_vals) if help_vals else 0.0
# CHỈ SỬA ĐÚNG DÒNG NÀY: Hạn mức < Thực tế báo TRUE, ngược lại FALSE
print(osd_limit_col)
res_osd = "TRUE" if limit_osd_val < m_osd else "FALSE"
res_help = "TRUE" if limit_help_val < m_help else "FALSE"
new_row.extend([m_osd, res_osd, m_help, res_help])
new_data.append(new_row)
self.sheet.set_sheet_data(new_data)
self.sheet.headers(headers)
for pos, color in new_highlights.items():
self.sheet.highlight_cells(row=pos[0], column=pos[1], bg=color[0], redraw=False)
last_idx = len(headers)
for i in range(1, 5): self.sheet.highlight_cells(0, last_idx - i, bg="#ffff00", redraw=False)
self.sheet.redraw()
messagebox.showinfo("Xong", "Đã sửa logic: 19 >= 10 sẽ ra FALSE.")
def export_excel(self):
path = filedialog.asksaveasfilename(defaultextension=".xlsx")
if not path: return
wb = openpyxl.Workbook()
ws = wb.active
data = self.sheet.get_sheet_data()
highlights = self.sheet.get_highlighted_cells()
for r_idx, row in enumerate(data, 1):
for c_idx, val in enumerate(row, 1):
cell = ws.cell(row=r_idx, column=c_idx, value=val)
if (r_idx-1, c_idx-1) in highlights:
hex_c = highlights[(r_idx-1, c_idx-1)][0].replace("#", "")
cell.fill = PatternFill(start_color=hex_c, end_color=hex_c, fill_type="solid")
wb.save(path)
messagebox.showinfo("Thành công", "Lưu file thành công!")
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def col_to_idx(self, letter):
if not letter: return -1
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
if __name__ == "__main__":
app = ExcelMasterTool(); app.mainloop()
import customtkinter as ctk
import pandas as pd
import string
from tkinter import filedialog, messagebox
from tksheet import Sheet
class ExcelMasterTool(ctk.CTk):
def __init__(self):
super().__init__()
self.title("Excel Pro - Max Even/Odd & Exclusion")
self.geometry("1400x850")
ctk.set_appearance_mode("light")
self.all_sheets = {"Sheet1": pd.DataFrame([["" for _ in range(20)] for _ in range(50)])}
# --- SIDEBAR ---
self.sidebar = ctk.CTkFrame(self, width=280, corner_radius=0)
self.sidebar.pack(side="left", fill="y")
ctk.CTkLabel(self.sidebar, text="BẢNG ĐIỀU KHIỂN", font=("Arial", 18, "bold")).pack(pady=20)
# 1. Cột bắt đầu & Highlight
ctk.CTkLabel(self.sidebar, text="Bắt đầu từ cột:", font=("Arial", 12, "bold")).pack(pady=(5, 0))
self.col_start_entry = ctk.CTkEntry(self.sidebar, width=180)
self.col_start_entry.pack(pady=5)
self.col_start_entry.insert(0, "A")
self.col_start_entry.bind("<KeyRelease>", lambda e: self.update_highlight())
# 2. Ô loại trừ cột
ctk.CTkLabel(self.sidebar, text="Loại trừ cột (vd: AL,AK):", font=("Arial", 12, "bold")).pack(pady=(10, 0))
self.exclude_entry = ctk.CTkEntry(self.sidebar, placeholder_text="Nhập tên cột...", width=180)
self.exclude_entry.pack(pady=5)
# 3. Nút xử lý
self.btn_process = ctk.CTkButton(
self.sidebar, text="🚀 Chèn & Tính Max Chẵn/Lẻ",
fg_color="#E67E22", hover_color="#D35400",
height=45, font=("Arial", 12, "bold"),
command=self.process_advanced_logic
)
self.btn_process.pack(pady=20, padx=20)
ctk.CTkButton(self.sidebar, text="📂 Nạp File Excel", command=self.import_excel).pack(pady=10, padx=20)
ctk.CTkButton(self.sidebar, text="📤 Xuất File Excel", fg_color="#1D6F42", command=self.export_excel).pack(pady=10, padx=20)
# --- BẢNG TÍNH ---
self.work_area = ctk.CTkFrame(self)
self.work_area.pack(side="right", fill="both", expand=True, padx=10, pady=10)
self.sheet = Sheet(self.work_area, show_row_index=True, show_header=True)
self.sheet.enable_bindings("all")
self.sheet.pack(fill="both", expand=True)
self.refresh_display()
self.update_highlight()
# ================= LOGIC HỖ TRỢ =================
def col_to_idx(self, letter):
letter = letter.upper().strip()
idx = 0
for char in letter: idx = idx * 26 + (ord(char) - ord('A') + 1)
return idx - 1
def idx_to_col(self, n):
name = ""
while n >= 0:
name = chr(n % 26 + 65) + name
n = n // 26 - 1
return name
def update_highlight(self):
self.sheet.dehighlight_all()
letter = self.col_start_entry.get().upper().strip()
if letter:
try:
idx = self.col_to_idx(letter)
if idx >= 0: self.sheet.highlight_columns(columns=[idx], background="#C8E6C9", redo=True)
except: pass
# ================= LOGIC XỬ LÝ CHÍNH =================
def process_advanced_logic(self):
start_letter = self.col_start_entry.get().upper().strip()
exclude_str = self.exclude_entry.get().upper().replace(" ", "")
exclude_list = exclude_str.split(",") if exclude_str else []
try:
start_idx = self.col_to_idx(start_letter)
old_data = self.sheet.get_sheet_data()
if not old_data: return
num_cols = len(old_data[0])
new_data = []
# Tính toán danh sách index bị loại trừ
exclude_indices = [self.col_to_idx(c) for c in exclude_list]
for r_idx in range(len(old_data)):
new_row = []
even_lengths = [] # Chứa độ dài các cột chẵn (0, 2, 4...)
odd_lengths = [] # Chứa độ dài các cột lẻ (1, 3, 5...)
# 1. Giữ nguyên các cột trước start_idx
for c_idx in range(start_idx):
new_row.append(old_data[r_idx][c_idx])
# 2. Xử lý xen kẽ từ start_idx
for c_idx in range(start_idx, num_cols):
val = old_data[r_idx][c_idx]
new_row.append(val) # Thêm cột gốc
# Tính độ dài từ dài nhất
txt = str(val).strip() if val is not None and str(val) != "nan" else ""
length = ""
if txt:
words = txt.translate(str.maketrans('', '', string.punctuation)).split()
length = len(max(words, key=len)) if words else ""
new_row.append(length) # Thêm cột phụ (LENG)
# 3. Gom nhóm để tính Max (Kiểm tra loại trừ tại đây)
if length != "" and (c_idx not in exclude_indices):
if c_idx % 2 == 0:
even_lengths.append(length)
else:
odd_lengths.append(length)
# 4. Thêm 2 cột Max cuối cùng
max_even = max(even_lengths) if even_lengths else ""
max_odd = max(odd_lengths) if odd_lengths else ""
new_row.append(max_even)
new_row.append(max_odd)
new_data.append(new_row)
# Cập nhật Header
final_headers = []
for i in range(start_idx): final_headers.append(self.idx_to_col(i))
for i in range(start_idx, num_cols):
label = self.idx_to_col(i)
final_headers.append(label)
final_headers.append(f"LENG {label}")
final_headers.append("MAX CHẴN (0,2..)")
final_headers.append("MAX LẺ (1,3..)")
# Đổ dữ liệu vào bảng
self.sheet.set_sheet_data(new_data)
self.sheet.headers([self.idx_to_col(i) for i in range(len(new_data[0]))]) # Tiêu đề A,B,C
self.sheet.headers(final_headers) # Tiêu đề nội dung
self.sheet.dehighlight_all()
self.sheet.refresh()
messagebox.showinfo("Thành công", "Đã xử lý xen kẽ và tính Max Chẵn/Lẻ!")
except Exception as e:
messagebox.showerror("Lỗi", f"Lỗi hệ thống: {e}")
# ================= HÀM TIỆN ÍCH =================
def refresh_display(self):
df = self.all_sheets["Sheet1"].fillna("")
self.sheet.set_sheet_data(df.values.tolist())
self.sheet.headers([self.idx_to_col(i) for i in range(len(df.columns))])
self.sheet.refresh()
def import_excel(self):
path = filedialog.askopenfilename(filetypes=[("Excel", "*.xlsx *.xls")])
if path:
df = pd.read_excel(path, header=None).fillna("")
self.sheet.set_sheet_data(df.values.tolist())
self.sheet.headers([self.idx_to_col(i) for i in range(df.shape[1])])
self.update_highlight()
self.sheet.refresh()
def export_excel(self):
path = filedialog.asksaveasfilename(defaultextension=".xlsx")
if path:
data = self.sheet.get_sheet_data()
pd.DataFrame(data).to_excel(path, index=False, header=False)
messagebox.showinfo("Xong", "Lưu thành công!")
if __name__ == "__main__":
app = ExcelMasterTool()
app.mainloop()