# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'roster.ui'
#
# Created by: PyQt5 UI code generator 5.15.1
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.

import sys
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *

import xlrd
import pandas as pd
import numpy as np


class Ui_MainWindow(QMainWindow):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(1097, 558)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
        self.tableWidget.setGeometry(QtCore.QRect(20, 50, 491, 391))
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setColumnCount(0)
        self.tableWidget.setRowCount(0)
        self.tableWidget_2 = QtWidgets.QTableWidget(self.centralwidget)
        self.tableWidget_2.setGeometry(QtCore.QRect(580, 50, 491, 391))
        self.tableWidget_2.setObjectName("tableWidget_2")
        self.tableWidget_2.setColumnCount(0)
        self.tableWidget_2.setRowCount(0)
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(20, 10, 93, 28))
        self.pushButton.setObjectName("pushButton")
        self.textEdit = QtWidgets.QTextEdit(self.centralwidget)
        self.textEdit.setGeometry(QtCore.QRect(140, 460, 151, 31))
        self.textEdit.setObjectName("textEdit")
        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setGeometry(QtCore.QRect(20, 460, 111, 41))
        self.label.setObjectName("label")
        #self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        #self.pushButton_2.setGeometry(QtCore.QRect(980, 460, 93, 28))
        #self.pushButton_2.setObjectName("pushButton_2")
        self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_3.setGeometry(QtCore.QRect(530, 190, 41, 111))
        self.pushButton_3.setObjectName("pushButton_3")
        self.label_2 = QtWidgets.QLabel(self.centralwidget)
        self.label_2.setGeometry(QtCore.QRect(490, 470, 171, 16))
        self.label_2.setObjectName("label_2")
        self.label_3 = QtWidgets.QLabel(self.centralwidget)
        self.label_3.setGeometry(QtCore.QRect(580, 20, 72, 15))
        self.label_3.setObjectName("label_3")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 1097, 26))
        self.menubar.setObjectName("menubar")
        self.menu = QtWidgets.QMenu(self.menubar)
        self.menu.setObjectName("menu")
        self.menu_2 = QtWidgets.QMenu(self.menubar)
        self.menu_2.setObjectName("menu_2")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.action = QtWidgets.QAction(MainWindow)
        self.action.setObjectName("action")
        self.action_2 = QtWidgets.QAction(MainWindow)
        self.action_2.setObjectName("action_2")
        self.action_3 = QtWidgets.QAction(MainWindow)
        self.action_3.setObjectName("action_3")
        self.menu.addAction(self.action)
        self.menu_2.addAction(self.action_2)
        self.menu_2.addAction(self.action_3)
        self.menubar.addAction(self.menu.menuAction())
        self.menubar.addAction(self.menu_2.menuAction())

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

        # 导入数据（页面端）
        self.pushButton.clicked.connect(self.openfile)
        self.pushButton.clicked.connect(self.create_table_show)

        # 导入数据（菜单栏）
        self.action.triggered.connect(self.openfile)
        self.action.triggered.connect(self.create_table_show)

        # 转换
        self.pushButton_3.clicked.connect(self.convertRoster)
        #self.pushButton_3.clicked.connect(self.create_convert_table_show)

        # 保存输出
        #self.pushButton_2.clicked.connect(self.slot_btn_saveFile)

        # 帮助
        self.action_2.triggered.connect(self.Help)

        # 关于
        self.action_3.triggered.connect(self.AboutUS)



    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MyRoster名单转换程序"))
        self.pushButton.setText(_translate("MainWindow", "导入数据"))
        self.label.setText(_translate("MainWindow", "请输入转换列数："))
        #self.pushButton_2.setText(_translate("MainWindow", "保存数据"))
        self.pushButton_3.setText(_translate("MainWindow", "转\n\n换"))
        self.label_2.setText(_translate("MainWindow", "MyRoster V1.0"))
        self.label_3.setText(_translate("MainWindow", "效果预览"))
        self.menu.setTitle(_translate("MainWindow", "新建"))
        self.menu_2.setTitle(_translate("MainWindow", "帮助"))
        self.action.setText(_translate("MainWindow", "导入数据"))
        self.action_2.setText(_translate("MainWindow", "使用说明"))
        self.action_3.setText(_translate("MainWindow", "关于"))

    def openfile(self):
        print("111")
        ###获取路径===================================================================
        try:
            openfile_name = QFileDialog.getOpenFileName(self, '选择文件', '', 'Excel files(*.xlsx , *.xls);;CSV(*.csv)')

            # print(openfile_name)
            global path_openfile_name

            ###获取路径====================================================================

            path_openfile_name = openfile_name[0]

        except Exception as e:
            print('err', e)

    def create_table_show(self):
        print("222")
        try:
            ###===========读取表格，转换表格，===========================================
            if len(path_openfile_name) > 0:
                input_table = pd.read_excel(path_openfile_name)
                # print(input_table)
                input_table_rows = input_table.shape[0]
                input_table_colunms = input_table.shape[1]
                # print(input_table_rows)
                # print(input_table_colunms)
                input_table_header = input_table.columns.values.tolist()
                # print(input_table_header)

                ###===========读取表格，转换表格，============================================
                ###======================给tablewidget设置行列表头============================

                self.tableWidget.setColumnCount(input_table_colunms)
                self.tableWidget.setRowCount(input_table_rows)
                self.tableWidget.setHorizontalHeaderLabels(input_table_header)

                ###======================给tablewidget设置行列表头============================

                ###================遍历表格每个元素，同时添加到tablewidget中========================
                for i in range(input_table_rows):
                    input_table_rows_values = input_table.iloc[[i]]
                    # print(input_table_rows_values)
                    input_table_rows_values_array = np.array(input_table_rows_values)
                    input_table_rows_values_list = input_table_rows_values_array.tolist()[0]
                    #print(input_table_rows_values_list)
                    for j in range(input_table_colunms):
                        input_table_items_list = input_table_rows_values_list[j]
                        #print(input_table_items_list)
                        #print(type(input_table_items_list))
                        # print(input_table_items_list)
                        # print(type(input_table_items_list))

                        ###==============将遍历的元素添加到tablewidget中并显示=======================

                        input_table_items = str(input_table_items_list)
                        #print(input_table_items)
                        #print(type(input_table_items))
                        newItem = QTableWidgetItem(input_table_items)
                        newItem.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)

                        self.tableWidget.setItem(i, j, newItem)

            ###================遍历表格每个元素，同时添加到tablewidget中========================
            else:
                self.centralWidget.show()
        except Exception as e:
            print("err2 ", e)

    def convertRoster(self):
        col_nums = self.textEdit.toPlainText()
        global path_convertedfile_name
        try:
            if len(path_openfile_name) > 0:
                if len(col_nums) > 0:
                    data = pd.read_excel(path_openfile_name, header=0)  # 打开xls文件

                    out_data = []
                    #print(len(data))
                    for i in range(len(data)):
                        #print(data.values[i][0])
                        #print(data.values[i][1])
                        out_data.append(str(data.values[i][0]).replace("_", ".") + '\n' + str(data.values[i][1]))

                    #print(out_data)
                    #print(len(out_data))

                    data_dic = {}
                    for i in range(int(col_nums)):
                        data_dic[str(i)]=[]

                    print(data_dic)

                    count = 0
                    for i in out_data:
                        data_dic[str(count % int(col_nums))].append(i)
                        count += 1

                    max_len = len(data_dic["0"])
                    for i in data_dic:
                        if len(data_dic[i]) < max_len:
                            data_dic[i].append("")
                    '''
                    if int(col_nums) > 1:
                        for i in range
                        if len(data_dic["0"]) > len(data_dic[str(int(col_nums) - 1)]):
                            for i in range(len(data_dic["0"]) - len(data_dic[str(int(col_nums) - 1)])):
                                data_dic[str(int(col_nums) - 1)].append("")
                    '''



                    #styles = [dict(selector="th", props=[("text-align", "center")])]
                    df = pd.DataFrame(data_dic)
                    #df.style.set_table_styles(styles)
                    df.to_excel('output.xlsx', header=None, index=False)
                    print("Writing Done!")
                    print(path_openfile_name)
                    path_convertedfile_name = "./output.xlsx"
                    self.create_convert_table_show()
                    QMessageBox.warning(self,
                                        "转换成功",
                                        "转换成功，文件保存在output.xlsx！",
                                        QMessageBox.Yes)
                else:
                    QMessageBox.warning(self,
                                        "转换错误",
                                        "请设置转换后的列数！",
                                        QMessageBox.Yes)
            else:
                QMessageBox.warning(self,
                                    "转换错误",
                                    "请先导入原始数据！",
                                    QMessageBox.Yes)
        except Exception as e:
            print("err1", e)
            QMessageBox.warning(self,
                                "转换错误",
                                str(e),
                                QMessageBox.Yes)

    def create_convert_table_show(self):

        try:
            if len(path_convertedfile_name) > 0:
                input_table = pd.read_excel(path_convertedfile_name)
                # print(input_table)
                input_table_rows = input_table.shape[0]
                input_table_colunms = input_table.shape[1]
                # print(input_table_rows)
                # print(input_table_colunms)
                input_table_header = input_table.columns.values.tolist()
                # print(input_table_header)

                ###===========读取表格，转换表格，============================================
                ###======================给tablewidget设置行列表头============================

                self.tableWidget_2.setColumnCount(input_table_colunms)
                self.tableWidget_2.setRowCount(input_table_rows)
                #self.tableWidget_2.setHorizontalHeaderLabels(input_table_header)

                ###======================给tablewidget设置行列表头============================

                ###================遍历表格每个元素，同时添加到tablewidget中========================
                for i in range(input_table_rows):
                    input_table_rows_values = input_table.iloc[[i]]
                    # print(input_table_rows_values)
                    input_table_rows_values_array = np.array(input_table_rows_values)
                    input_table_rows_values_list = input_table_rows_values_array.tolist()[0]
                    #print(input_table_rows_values_list)
                    for j in range(input_table_colunms):
                        input_table_items_list = input_table_rows_values_list[j]
                        # print(input_table_items_list)
                        # print(type(input_table_items_list))
                        # print(input_table_items_list)
                        # print(type(input_table_items_list))

                        ###==============将遍历的元素添加到tablewidget中并显示=======================

                        input_table_items = str(input_table_items_list)
                        # print(input_table_items)
                        # print(type(input_table_items))
                        newItem = QTableWidgetItem(input_table_items)
                        newItem.setTextAlignment(Qt.AlignHCenter | Qt.AlignVCenter)

                        self.tableWidget_2.setItem(i, j, newItem)

                ###================遍历表格每个元素，同时添加到tablewidget中========================
            else:
                self.centralWidget.show()
        except Exception as e:
            print("err2", e)

    def Help(self):
        try:
            QMessageBox.about(self,
                                "帮助",
                                '''使用说明：\n使用前请先将原始数据文件中的所有“.”替换为“_”!\n使用时请先点击导入数据，选择要转换的原始名单文件，然后根据自身需求设置转换后的文件的列数，最后点击转换按钮即可。转换完的文件将保存在程序同目录下的output.xlsx文件中，请及时另存到别处，以免数据被覆盖！\n*注意，转换时请关闭output.xlsx文件！''')
        except Exception as e:
            print('err ',e)

    def AboutUS(self):
        try:
            QMessageBox.about(self,
                                "关于",
                                '''MyRoster名单转换工具为一款开源软件。\n项目地址为：\nhttps://github.com/h3h3da/MyRoster''')
        except Exception as e:
            print('err ',e)



    '''
    def save_data(self):
        fileName2, ok2 = QFileDialog.getSaveFileName(self,
                                                     "文件保存",
                                                     "/",
                                                     "All Files (*);;Excel files(*.xlsx , *.xls)")
                                                     
    def slot_btn_saveFile(self):
        fileName_choose, filetype = QFileDialog.getSaveFileName(self,
                                                                "文件保存",
                                                                "./",  # 起始路径
                                                                "All Files (*);;Text Files (*.txt)")  # 设置文件扩展名过滤,用双分号间隔

        if fileName_choose == "":
            print("\n取消选择")
            return

        print("\n你选择要保存的文件为:")
        print(fileName_choose)
        print("文件筛选器类型: ", filetype)
    '''


if __name__ == '__main__':
    app = QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()

    sys.exit(app.exec_())
