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"))  # 設(shè)置窗口的圖標(biāo)，icon.ico是圖標(biāo)文件
        self.setWindowTitle("Excel字符截取工具")
        self.setGeometry(100, 100, 800, 500)
        self.setStyleSheet("background-color: #f0f0f0;")  # 設(shè)置背景顏色

        self.layout = QVBoxLayout()

        # 設(shè)置字體
        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)

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

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

        # 截取方式標(biāo)簽
        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)

        # 進(jìn)度條
        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()

        # 立即截取和導(dǎo)出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("導(dǎo)出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 = []  # 存儲列的復(fù)選框

        # 連接單選框，選擇時(shí)啟用/禁用相應(yīng)的輸入框
        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])

            # 設(shè)置列名
            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)))

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

    def update_column_checkboxes(self):
        """更新列選擇復(fù)選框"""
        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風(fēng)格的列名 (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):
        """檢查至少選中了一個列，并啟用應(yīng)用按鈕，同時(shí)高亮顯示選中的列"""
        if any(checkbox.isChecked() for checkbox in self.column_checkboxes):
            self.apply_button.setEnabled(True)
        else:
            self.apply_button.setEnabled(False)

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

        # 遍歷所有的復(fù)選框，根據(jù)選中狀態(tài)高亮顯示相應(yīng)列
        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):
        """恢復(fù)列的默認(rèn)背景色"""
        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ù)選擇的截取方式，啟用相應(yīng)的輸入框"""
        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ù)輸入值立即應(yīng)用截取操作"""
        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è)置進(jìn)度條最大值為選中的列數(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:]

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

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

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

    def apply_custom_range_cut(self, start, end):
        """應(yīng)用自定義截取范圍"""
        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())  # 設(shè)置與主窗口一致的圖標(biāo)
        msg.setText(message)
        msg.setWindowTitle("錯誤")
        msg.exec_()

    def show_success_message(self, message):
        """彈出成功提示框"""
        msg = QMessageBox()
        msg.setIcon(QMessageBox.Information)
        msg.setWindowIcon(self.windowIcon())  # 設(shè)置與主窗口一致的圖標(biāo)
        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):
        """導(dǎo)出當(dāng)前數(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ù)導(dǎo)出成功！")


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