界面打开excel文件读取内容在表格中展现练习

首先设定界面样式，一个点击按钮，一个路径展现，下面是数据展示

 

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

class Example(QWidget):
    def __init__(self):
        super().__init__()
        self.nrows = 0

        self.initUI()
    def initUI(self):

        self.setGeometry(100, 100, 800, 600)
        #宽度，高度，展现的宽度高度
        self.setWindowTitle('读取界面')

        self.tx = QLabel(self)
        self.tx.setText('文件路径')
        self.tx.setGeometry(130, 20, 300, 26)

        self.bt1 = QPushButton('打开文件', self)
        self.bt1.move(20, 20)

        layout = QVBoxLayout()
        # 表格
        self.table_widget = QTableWidget(20, 4)  # 行数，列数
        # self.table_widget.setGeometry(QtCore.QRect(0, 280, 801, 281))
        self.table_widget.setFixedSize(800, 530)  # 宽度，高度
        #self.table_widget.setColumnWidth(0, 55)
        # self.table_widget.setRowHeight(10, 10)
        self.table_widget.setHorizontalHeaderLabels(['序列', '姓名', '标题', '邮箱'])
        self.table_widget.setEditTriggers(QAbstractItemView.NoEditTriggers)  # 表格内容只读
        self.table_widget.setSelectionBehavior(QAbstractItemView.SelectRows)  # 点击选择整行
        self.spacerItem = QtWidgets.QSpacerItem(1, 10, QtWidgets.QSizePolicy.Minimum, QtWidgets.QSizePolicy.Expanding)
        layout.addItem(self.spacerItem)
        layout.addWidget(self.table_widget)

        self.setLayout(layout)


        self.bt1.clicked.connect(self.openfile)

        self.show()

    def openfile(self):
        fname , imgType = QFileDialog.getOpenFileName(self, '打开文件','*.xls;;All Files(*)')
        self.tx.setText(fname)

        data = xlrd.open_workbook(fname)
        # 通过表名限制
        table3 = data.sheet_by_name('Sheet2')
        # 共有多少行数据
        self.nrows = table3.nrows
        self.shuzu = {}
        i = 0
        while i < self.nrows:
            #print(table3.row_values(i))
            itema = table3.row_values(i)
            itema_zd = {i: itema}
            self.shuzu.update(itema_zd)

            i += 1
        self.nrows = len(self.shuzu)

        count = self.nrows
        # 添加数据
        for i in range(count):
            print(self.shuzu[i])
            for t in range(4):
                if self.shuzu[i][t] is None or self.shuzu[i][t] == '':
                    newItem = QTableWidgetItem('无数据')
                elif t == 0:
                    newItem = QTableWidgetItem(str(self.shuzu[i][t]))
                elif t == 4:
                    newItem = QTableWidgetItem(self.shuzu[i][t].strftime('%Y-%m-%d %H:%M:%S'))
                else:
                    newItem = QTableWidgetItem(self.shuzu[i][t])

                self.table_widget.setItem(i, t, newItem)




if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = Example()
    sys.exit(app.exec_())