import sys
import pandas as pd
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QHBoxLayout, QPushButton, QLineEdit, QFileDialog, QTableWidget, QTableWidgetItem, QCheckBox, QLabel, QRadioButton, QProgressBar, QMessageBox
from PyQt5.QtGui import QColor, QFont, QDragEnterEvent, QDropEvent, QIcon
from PyQt5.QtCore import Qt


class ExcelProcessor(QWidget):
    def __init__(self):
        super().__init__()
        self.setWindowIcon(QIcon("icon.ico"))  # 設置窗口的圖標，icon.ico是圖標文件
        self.setWindowTitle("Excel字符截取工具")
        self.setGeometry(100, 100, 800, 500)
        self.setStyleSheet("background-color: #f0f0f0;")  # 設置背景顏色

        self.layout = QVBoxLayout()

        # 設置字體
        font = QFont("Segoe UI", 20)
        self.setFont(font)

        # 文件選擇按鈕
        self.file_button = QPushButton("打開Excel文件")
        self.file_button.setStyleSheet("background-color: #4CAF50; color: white; padding: 5px 10px; border-radius: 5px;")
        self.file_button.clicked.connect(self.open_file)
        self.layout.addWidget(self.file_button)

        # 表格展示區(qū)域
        self.table_widget = QTableWidget()
        self.table_widget.setStyleSheet("background-color: white; border: 1px solid #ccc;")
        self.layout.addWidget(self.table_widget)

        # 選擇列標簽
        self.column_label = QLabel("請選擇要處理的列：")
        self.column_label.setStyleSheet("font-weight: bold; margin-top: 10px;")
        self.layout.addWidget(self.column_label)

        # 復選框水平布局
        self.column_layout = QHBoxLayout()
        self.layout.addLayout(self.column_layout)

        # 截取方式標簽
        self.cut_option_label = QLabel("請選擇截取方式：")
        self.cut_option_label.setStyleSheet("font-weight: bold; margin-top: 10px;")
        self.layout.addWidget(self.cut_option_label)

        # 截取方式單選框
        self.cut_option_layout = QHBoxLayout()
        self.left_cut_radio = QRadioButton("左截取")
        self.right_cut_radio = QRadioButton("右截取")
        self.custom_range_radio = QRadioButton("自定義截取范圍")

        self.cut_option_layout.addWidget(self.left_cut_radio)
        self.cut_option_layout.addWidget(self.right_cut_radio)
        self.cut_option_layout.addWidget(self.custom_range_radio)

        self.layout.addLayout(self.cut_option_layout)

        # 左右截取輸入框的水平布局
        self.h_layout = QHBoxLayout()
        self.left_input = QLineEdit(self)
        self.left_input.setPlaceholderText("請輸入左截取字符數(shù)")
        self.right_input = QLineEdit(self)
        self.right_input.setPlaceholderText("請輸入右截取字符數(shù)")
        self.h_layout.addWidget(self.left_input)
        self.h_layout.addWidget(self.right_input)

        self.layout.addLayout(self.h_layout)

        # 自定義截取范圍輸入框
        self.range_input = QLineEdit(self)
        self.range_input.setPlaceholderText("請輸入自定義截取范圍，例如: 1-5")
        self.layout.addWidget(self.range_input)

        # 進度條
        self.progress_bar = QProgressBar(self)
        self.progress_bar.setStyleSheet("QProgressBar { border: 2px solid grey; border-radius: 5px; text-align: center; }")
        self.layout.addWidget(self.progress_bar)

        # 控件水平布局
        self.top_layout = QHBoxLayout()

        # 立即截取和導出Excel按鈕
        self.apply_button = QPushButton("立即截取")
        self.apply_button.setEnabled(False)
        self.apply_button.setStyleSheet("background-color: #2196F3; color: white; padding: 5px 10px; border-radius: 5px;")
        self.apply_button.clicked.connect(self.apply_cut)

        self.export_button = QPushButton("導出Excel")
        self.export_button.setStyleSheet("background-color: #FF5722; color: white; padding: 5px 10px; border-radius: 5px;")
        self.export_button.clicked.connect(self.export_to_excel)

        self.top_layout.addWidget(self.apply_button)
        self.top_layout.addWidget(self.export_button)

        # 撤銷操作和退出程序按鈕
        self.bottom_layout = QHBoxLayout()

        self.undo_button = QPushButton("撤銷操作")
        self.undo_button.setStyleSheet("background-color: #FFC107; color: white; padding: 5px 10px; border-radius: 5px;")
        self.undo_button.clicked.connect(self.undo_last_change)

        self.exit_button = QPushButton("退出程序")
        self.exit_button.setStyleSheet("background-color: #F44336; color: white; padding: 5px 10px; border-radius: 5px;")
        self.exit_button.clicked.connect(self.close)

        self.bottom_layout.addWidget(self.undo_button)
        self.bottom_layout.addWidget(self.exit_button)

        self.layout.addLayout(self.top_layout)
        self.layout.addLayout(self.bottom_layout)

        self.setLayout(self.layout)

        self.df = None
        self.history = []  # 用于保存歷史記錄，以便撤銷
        self.column_checkboxes = []  # 存儲列的復選框

        # 連接單選框，選擇時啟用/禁用相應的輸入框
        self.left_cut_radio.clicked.connect(self.toggle_input_state)
        self.right_cut_radio.clicked.connect(self.toggle_input_state)
        self.custom_range_radio.clicked.connect(self.toggle_input_state)

        # 啟用拖放功能
        self.setAcceptDrops(True)

    def open_file(self):
        """打開Excel文件并展示數(shù)據(jù)"""
        options = QFileDialog.Options()
        file_path, _ = QFileDialog.getOpenFileName(self, "打開Excel文件", "", "Excel 文件 (*.xlsx)", options=options)

        if file_path:
            self.df = self.read_excel(file_path)
            if self.df is not None:
                self.save_to_history()  # 保存初始狀態(tài)，支持撤銷
                self.update_table()

    def dragEnterEvent(self, event: QDragEnterEvent):
        """處理拖拽事件，支持拖拽文件到窗口"""
        if event.mimeData().hasUrls():
            event.acceptProposedAction()

    def dropEvent(self, event: QDropEvent):
        """處理拖拽放置事件，獲取文件路徑并打開Excel文件"""
        file_urls = event.mimeData().urls()
        if file_urls:
            file_path = file_urls[0].toLocalFile()
            if file_path.endswith(".xlsx"):
                self.df = self.read_excel(file_path)
                if self.df is not None:
                    self.save_to_history()  # 保存初始狀態(tài)，支持撤銷
                    self.update_table()

    def read_excel(self, file_path):
        """讀取Excel文件并返回DataFrame"""
        try:
            return pd.read_excel(file_path, engine='openpyxl', header=None)  # 不使用第一行作為列名
        except Exception as e:
            print(f"讀取文件錯誤: {e}")
            self.show_error_message(f"讀取文件錯誤: {e}")
            return None

    def update_table(self):
        """更新QTableWidget，展示DataFrame中的數(shù)據(jù)"""
        if self.df is not None:
            self.table_widget.setRowCount(self.df.shape[0])
            self.table_widget.setColumnCount(self.df.shape[1])

            # 設置列名
            self.table_widget.setHorizontalHeaderLabels([self.get_column_name(i) for i in range(self.df.shape[1])])

            # 填充表格內(nèi)容
            for row in range(self.df.shape[0]):
                for col in range(self.df.shape[1]):
                    value = self.df.iat[row, col]
                    if pd.isna(value):
                        value = ""
                    self.table_widget.setItem(row, col, QTableWidgetItem(str(value)))

            # 更新列選擇復選框
            self.update_column_checkboxes()

    def update_column_checkboxes(self):
        """更新列選擇復選框"""
        for checkbox in self.column_checkboxes:
            checkbox.setParent(None)

        self.column_checkboxes.clear()

        for i in range(self.df.shape[1]):
            checkbox = QCheckBox(self.get_column_name(i), self)
            self.column_checkboxes.append(checkbox)
            self.column_layout.addWidget(checkbox)

        self.apply_button.setEnabled(False)

        for checkbox in self.column_checkboxes:
            checkbox.stateChanged.connect(self.check_column_selection)

    def get_column_name(self, col_index):
        """生成Excel風格的列名 (A, B, C...)"""
        col_name = ''
        while col_index >= 0:
            col_name = chr(col_index % 26 + 65) + col_name
            col_index = col_index // 26 - 1
        return col_name

    def check_column_selection(self):
        """檢查至少選中了一個列，并啟用應用按鈕，同時高亮顯示選中的列"""
        if any(checkbox.isChecked() for checkbox in self.column_checkboxes):
            self.apply_button.setEnabled(True)
        else:
            self.apply_button.setEnabled(False)

        # 重置進度條
        self.progress_bar.setValue(0)

        # 遍歷所有的復選框，根據(jù)選中狀態(tài)高亮顯示相應列
        for i, checkbox in enumerate(self.column_checkboxes):
            if checkbox.isChecked():
                self.highlight_column(i, QColor(255, 255, 0))  # 高亮顯示，黃色
            else:
                self.reset_column(i)

    def highlight_column(self, column_index, color):
        """高亮顯示指定的列"""
        for row in range(self.df.shape[0]):
            item = self.table_widget.item(row, column_index)
            if item:
                item.setBackground(color)

    def reset_column(self, column_index):
        """恢復列的默認背景色"""
        for row in range(self.df.shape[0]):
            item = self.table_widget.item(row, column_index)
            if item:
                item.setBackground(QColor(255, 255, 255))  # 白色背景

    def toggle_input_state(self):
        """根據(jù)選擇的截取方式，啟用相應的輸入框"""
        self.left_input.setEnabled(False)
        self.right_input.setEnabled(False)
        self.range_input.setEnabled(False)

        if self.left_cut_radio.isChecked():
            self.left_input.setEnabled(True)
        elif self.right_cut_radio.isChecked():
            self.right_input.setEnabled(True)
        elif self.custom_range_radio.isChecked():
            self.range_input.setEnabled(True)

    def apply_cut(self):
        """根據(jù)輸入值立即應用截取操作"""
        if self.df is not None:
            try:
                self.save_to_history()

                left_chars = int(self.left_input.text()) if self.left_input.text().isdigit() else 0
                right_chars = int(self.right_input.text()) if self.right_input.text().isdigit() else 0

                range_input = self.range_input.text()
                if range_input:
                    start, end = self.parse_range_input(range_input)
                    if start is not None and end is not None:
                        self.apply_custom_range_cut(start, end)

                selected_columns = [i for i, checkbox in enumerate(self.column_checkboxes) if checkbox.isChecked()]
                total_steps = len(selected_columns)  # 每個列一個步驟
                self.progress_bar.setMaximum(total_steps)  # 設置進度條最大值為選中的列數(shù)

                for step, selected_column in enumerate(selected_columns):
                    self.df[selected_column] = self.df[selected_column].astype(str)
                    if self.left_cut_radio.isChecked():
                        self.df[selected_column] = self.df[selected_column].str[:left_chars]
                    elif self.right_cut_radio.isChecked():
                        self.df[selected_column] = self.df[selected_column].str[-right_chars:]

                    # 更新進度條
                    self.progress_bar.setValue(step + 1)

                self.update_table()
                self.show_success_message("字符截取成功！")
            except Exception as e:
                self.show_error_message(f"應用截取時發(fā)生錯誤: {e}")

    def parse_range_input(self, input_text):
        """解析自定義范圍輸入 (如 1-5)"""
        try:
            start, end = map(int, input_text.split('-'))
            return start - 1, end  # 轉換為從零開始的索引
        except ValueError:
            return None, None

    def apply_custom_range_cut(self, start, end):
        """應用自定義截取范圍"""
        for i in range(self.df.shape[1]):
            self.df.iloc[:, i] = self.df.iloc[:, i].astype(str).apply(lambda x: x[start:end])

    def show_error_message(self, message):
        """彈出錯誤提示框"""
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Critical)
        msg.setWindowIcon(self.windowIcon())  # 設置與主窗口一致的圖標
        msg.setText(message)
        msg.setWindowTitle("錯誤")
        msg.exec_()

    def show_success_message(self, message):
        """彈出成功提示框"""
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Information)
        msg.setWindowIcon(self.windowIcon())  # 設置與主窗口一致的圖標
        msg.setText(message)
        msg.setWindowTitle("成功")
        msg.exec_()

    def save_to_history(self):
        """保存歷史數(shù)據(jù)，以便撤銷"""
        self.history.append(self.df.copy())

    def undo_last_change(self):
        """撤銷上一步操作"""
        if self.history:
            self.df = self.history.pop()
            self.update_table()
            self.show_success_message("撤銷操作成功！")

    def export_to_excel(self):
        """導出當前數(shù)據(jù)為Excel"""
        options = QFileDialog.Options()
        file_path, _ = QFileDialog.getSaveFileName(self, "保存Excel文件", "", "Excel 文件 (*.xlsx)", options=options)

        if file_path:
            self.df.to_excel(file_path, index=False, header=False, engine='openpyxl')
            self.show_success_message("表格數(shù)據(jù)導出成功！")


if __name__ == "__main__":
    app = QApplication(sys.argv)
    window = ExcelProcessor()
    window.show()
    sys.exit(app.exec_())