import sys
import os
from PyQt5.QtWidgets import *
import openpyxl
from PyQt5 import *
from PyQt5.QtGui import QIcon

class UI(QMainWindow):
    def __init__(self):
        super().__init__()

        self.initUI()

    def initUI(self):
        self.resize(400, 400)
        self.move(500, 100)
        self.setWindowTitle('Test')

        self.table = QTableWidget()
        self.table.setSelectionBehavior(QAbstractItemView.SelectRows) #设置为单行选中
        self.table.setSelectionMode(QAbstractItemView.SingleSelection) #每次只能选中一行
        self.table.setEditTriggers(QAbstractItemView.NoEditTriggers) #表格不能编辑
        self.table.itemDoubleClicked.connect(self.doubleClickEvent)
        layout = QHBoxLayout()
        layout.addWidget(self.table)
        # 可以根据窗口大小来自适应显示表项
        self.table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.setLayout(layout)
        self.setCentralWidget(self.table)
        self.statusBar()

        menu = self.menuBar()
        fileMenu = menu.addMenu("&File")

        saveAction = QAction(QIcon('save.png'), '&Save', self)
        saveAction.setShortcut('Ctrl+S')
        saveAction.setStatusTip('Save as')
        saveAction.triggered.connect(self.save)
        fileMenu.addAction(saveAction)

        openAction = QAction(QIcon("open.png"), "&Open", self)
        openAction.setShortcut('Ctrl+O')
        openAction.setStatusTip("Open a file")
        openAction.triggered.connect(self.open)
        fileMenu.addAction(openAction)

        self.show()
        self.center()

    def center(self): #将主窗口放在屏幕的中心
        qr = self.frameGeometry()  # 获得当前组件的特定几何图形
        cp = QDesktopWidget().availableGeometry().center()  # 计算出显示器的绝对值，并获得中心点
        qr.moveCenter(cp)  # 将获得的几何图形的中心点移动到屏幕中心
        self.move(qr.topLeft())  # 将当前组件的左上顶角移动到几何图形的坐上顶角上

    def save(self):
        filename, fileType= QFileDialog.getSaveFileName(self,
                                    "Save As",
                                    "C:\\Users\\Administrator\\Desktop",
                                    "Excel Files (*.xlsx)")
        if (filename == ""):
            return None

        wb = openpyxl.Workbook()
        sheet = wb["Sheet"]


        rows = self.table.rowCount()
        cols = self.table.columnCount()
        for i in range(1, cols + 1):
            sheet.cell(1, i).value = str(self.talbeHeadList[i - 1])
        sheet.freeze_panes = "A2"
        for i in range(2, rows + 1):
            for j in range(1, cols + 1):
                if (self.table.item(i - 1, j - 1) != None):
                    item = self.table.item(i - 1, j - 1)
                    sheet.cell(i, j).value = item.text()

        wb.save(filename)

    def open(self):
        fileName, filetype = QFileDialog.getOpenFileName(self,
                                                         "Open", #弹窗的标题
                                                         "C:\\Users\\Administrator\\Desktop", #默认的文件路径
                                                         "Excel Files (*.xlsx)")  # 设置文件扩展名过滤,注意用双分号间隔
        # print(fileName)
        if (fileName == ""):
            return None #处理未选择文件的情况
        else:
            self.loadExcelData(fileName) #加载Excel文件中的数据

    def loadExcelData(self, filePath): #加载Excel文件中的数据
        # wb = openpyxl.load_workbook("C:\\Users\\Administrator\\Desktop\\Flight\\Test.xlsx")
        wb = openpyxl.load_workbook(filePath)
        sheet1 = wb.active
        rows = sheet1.max_row - sheet1.min_row + 1
        cols = sheet1.max_column - sheet1.min_column + 1
        self.table.setColumnCount(cols)
        self.table.setRowCount(rows - 1)
        self.table.setSortingEnabled(True)
        self.table.resizeColumnsToContents() #将行与列的宽度高度与文本内容的宽高相匹配
        self.table.resizeRowsToContents() #将行与列的宽度高度与文本内容的宽高相匹配

        self.talbeHeadList = [] #设置表头
        for i in range(1, cols + 1):
            self.talbeHeadList.append(str(sheet1.cell(1, i).value))
        self.table.setHorizontalHeaderLabels(self.talbeHeadList)

        for i in range(2, rows + 1):
            for j in range(1, cols + 1):
                item = QTableWidgetItem(str(sheet1.cell(i, j).value))
                item.setTextAlignment(0x0004 | 0x0080)
                self.table.setItem(i - 2, j - 1, item)

    def doubleClickEvent(self):
        self.hide() #隐藏前一个窗口，不然会闪退
        self.clientInfo = ClientInfoWindows()

class ClientInfoWindows(QWidget):
    def __init__(self):
        super().__init__()
        self.init()

    def init(self):
        self.window = QWidget()
        self.resize(400, 400)
        self.center()
        self.window.show()

    def center(self):
        qr = self.frameGeometry()  # 获得当前组件的特定几何图形
        cp = QDesktopWidget().availableGeometry().center()  # 计算出显示器的绝对值，并获得中心点
        qr.moveCenter(cp)  # 将获得的几何图形的中心点移动到屏幕中心
        self.move(qr.topLeft())  # 将当前组件的左上顶角移动到几何图形的坐上顶角上

if __name__ == '__main__':
    app = QApplication(sys.argv)
    u = UI()
    sys.exit(app.exec_())