pyqt5：openpyxl 读取 Excel文件，显示在 QTableWidget 中

pip install openpyxl
openpyxl-3.1.2-py2.py3-none-any.whl (249 kB)
et_xmlfile-1.1.0-py3-none-any.whl (4.7 kB)
摘要：A Python library to read/write Excel 2010 xlsx/xlsm files

pip install pyqt5; 
pip install pyqt5-tools; 

编写 openpyxl_pyqt5.py 如下

# -*- coding: utf-8 -*-
import os
import sys
from PyQt5.QtWidgets import QApplication, QMainWindow, QFileDialog, QMessageBox
from PyQt5.QtWidgets import QTableWidget, QTableWidgetItem
from openpyxl import load_workbook
 
class ExcelReader(QMainWindow):
    def __init__(self):
        super().__init__()
 
        # 设置窗口属性
        self.setWindowTitle("Excel Reader")
        self.setGeometry(100, 100, 800, 600)
 
        # 选择.xlsx文件名
        file_name, _ = QFileDialog.getOpenFileName(self, "Open Excel File", "", "xlsx Files (*.xlsx)")
        if file_name:
            self.file_path = file_name
        else:
            sys.exit(1)
        if not os.path.exists(self.file_path):
            print(f'Error: {self.file_path} not found.')
            sys.exit(2)
    
        # 加载Excel文件
        book = load_workbook(filename=self.file_path)
        sheet = book.active
        nrows = sheet.max_row    # 取最大行数
        ncols = sheet.max_column # 取最大列数
        if ncols > 26:
            print(f" columns: {ncols} > 26 !")
            ncols = 26
 
        # 创建表格控件
        self.table = QTableWidget()
        self.table.setRowCount(nrows) # 设置行数
        self.table.setColumnCount(ncols)  # 设置列数
        headers = tuple("ABCDEFGHIJKLMNOPQRSTUVWXYZ")
        self.table.setHorizontalHeaderLabels(headers[0:ncols])
        self.setCentralWidget(self.table)
 
        # 遍历Excel数据并插入到表格控件中
        row = 0
        for row_data in sheet.iter_rows():
            col = 0
            for cell in row_data:
                v = cell.value
                t = cell.data_type
                if v is None:
                    item = QTableWidgetItem('')
                elif t in ('s','t'): # str, text
                    item = QTableWidgetItem(v)
                elif t =='n': # number
                    if v%1 ==0: # int
                        item = QTableWidgetItem("%d" %v)
                    else: # float
                        item = QTableWidgetItem("%.4f" %v)
                elif t =='d': # date
                    item = QTableWidgetItem(v.strftime('%Y-%m-%d'))
                else:
                    item = QTableWidgetItem(str(v))
                self.table.setItem(row, col, item)
                col += 1
            row += 1
        #
 
if __name__ == "__main__":
    app = QApplication(sys.argv)
    reader = ExcelReader()
    reader.show()
    sys.exit(app.exec_())

运行 python openpyxl_pyqt5.py