PyQt5-tablewidget全功能代码

 本文主要是为了实现以下功能：

1.新建表格，为窗口创建上下文菜单；窗口增加菜单实现打开、新建、保存、另存为的功能； 2.实现表格的新增行，插入行，删除行，新增列，插入列，删除列功能； 3.实现修改表格的列名，实现上下文菜单； 4.将新增行，插入行，删除行，新增列，插入列，删除列，修改列名功能集中到上下文菜单中。 5.实现数据库操作：sqlite3数据库与表格操作能够实现实时数据同步功能； 6.表格的新增行，插入行，删除行，新增列，插入列，删除列，修改列名的操作实施与数据库同步操作； 7.重新打开窗口需要加载上次数据； 8.关闭前要将数据保存到csv文件中； 9.关闭窗口前，完成数据的数据库保存功能。重新打开窗口需加载数据。

import sys
import csv
import sqlite3
from PyQt5.QtWidgets import QApplication, QMainWindow, QTableWidget, QTableWidgetItem, QFileDialog, QAction, QMenu, QInputDialog, QMessageBox


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.initUI()

    def initUI(self):
        # 新建表格
        self.tableWidget = QTableWidget(0, 0)
        self.setCentralWidget(self.tableWidget)
        self.createContextMenu()
        self.fileName = None
        # 菜单栏
        menubar = self.menuBar()

        fileMenu = menubar.addMenu("文件")

        newAction = QAction("新建", self)
        newAction.triggered.connect(self.newTable)
        fileMenu.addAction(newAction)

        openAction = QAction("打开", self)
        openAction.triggered.connect(self.openFile)
        fileMenu.addAction(openAction)

        saveAction = QAction("保存", self)
        saveAction.triggered.connect(self.saveFile)
        fileMenu.addAction(saveAction)

        saveAsAction = QAction("另存为", self)
        saveAsAction.triggered.connect(self.saveFileAs)
        fileMenu.addAction(saveAsAction)

        self.show()

    def createContextMenu(self):
        # 上下文菜单
        self.contextMenu = QMenu(self)

        addRowAction = QAction("新增行", self)
        addRowAction.triggered.connect(self.addRow)
        self.contextMenu.addAction(addRowAction)

        insertRowAction = QAction("插入行", self)
        insertRowAction.triggered.connect(self.insertRow)
        self.contextMenu.addAction(insertRowAction)

        deleteRowAction = QAction("删除行", self)
        deleteRowAction.triggered.connect(self.deleteRow)
        self.contextMenu.addAction(deleteRowAction)

        addColumnAction = QAction("新增列", self)
        addColumnAction.triggered.connect(self.addColumn)
        self.contextMenu.addAction(addColumnAction)

        insertColumnAction = QAction("插入列", self)
        insertColumnAction.triggered.connect(self.insertColumn)
        self.contextMenu.addAction(insertColumnAction)

        deleteColumnAction = QAction("删除列", self)
        deleteColumnAction.triggered.connect(self.deleteColumn)
        self.contextMenu.addAction(deleteColumnAction)

        modifyColumnAction = QAction("修改列名", self)
        modifyColumnAction.triggered.connect(self.modifyColumn)
        self.contextMenu.addAction(modifyColumnAction)

        self.tableWidget.setContextMenuPolicy(3)
        self.tableWidget.customContextMenuRequested.connect(self.showContextMenu)

    def showContextMenu(self, pos):
        self.contextMenu.exec_(self.tableWidget.viewport().mapToGlobal(pos))

    def newTable(self):
        result= QMessageBox.question(self, "保存文件", "是否保存改文件")
        if result == QMessageBox.Yes:
            self.saveFileAs()
        self.loadDataFromDatabase("Qtest")
        # self.tableWidget.clear()
        # self.tableWidget.setRowCount(0)
        # self.tableWidget.setColumnCount(0)

    def openFile(self):
        fileName, fileExt = QFileDialog.getOpenFileName(self, "打开文件", ".", "CSV Files (*.csv)")
        if not fileName:
            return
        try:
            self.fileName = fileName
            name = self.fileName.split('/')[-1].split(".")[0]
            table_name = "Q" + name
            self.loadDataFromDatabase(table_name)
        except:
            self.fileName = fileName
            with open(fileName, "r", newline="") as csvfile:
                reader = csv.reader(csvfile)
                data = [row for row in reader]
            self.tableWidget.setRowCount(len(data))
            self.tableWidget.setColumnCount(len(data[0]))
            for i, row in enumerate(data):
                for j, cell in enumerate(row):
                    self.tableWidget.setItem(i, j, QTableWidgetItem(cell))

    def saveFile(self):
        fileName = self.fileName
        if fileName is None:
            self.saveFileAs()
        else:
            with open(fileName, "w", newline="") as csvfile:
                writer = csv.writer(csvfile)
                for i in range(self.tableWidget.rowCount()):
                    row = []
                    for j in range(self.tableWidget.columnCount()):
                        item = self.tableWidget.item(i, j)
                        if item is not None:
                            row.append(item.text())
                        else:
                            row.append("")
                    writer.writerow(row)

            # 数据库同步操作
            self.syncToDatabase()

    def saveFileAs(self):
        fileName, fileExt = QFileDialog.getSaveFileName(self, "另存为", ".", "CSV Files (*.csv)")
        if not fileName:
            return
        self.fileName = fileName
        self.saveFile()

    def insertRow(self):
        row = self.tableWidget.currentRow()
        self.tableWidget.insertRow(row)
        self.tableWidget.setCurrentCell(row, 0)

        # 数据库同步操作
        self.syncToDatabase()

    def addRow(self):
        row = self.tableWidget.currentRow()
        self.tableWidget.insertRow(row + 1)
        self.tableWidget.setCurrentCell(row + 1, 0)

        # 数据库同步操作
        self.syncToDatabase()

    def deleteRow(self):
        row = self.tableWidget.currentRow()
        self.tableWidget.removeRow(row)

        # 数据库同步操作
        self.syncToDatabase()

    def insertColumn(self):
        col = self.tableWidget.currentColumn()
        self.tableWidget.insertColumn(col)
        self.tableWidget.setHorizontalHeaderItem(col, QTableWidgetItem("新列"))

        # 数据库同步操作
        self.syncToDatabase()

    def addColumn(self):
        col = self.tableWidget.currentColumn()
        self.tableWidget.insertColumn(col + 1)
        self.tableWidget.setHorizontalHeaderItem(col + 1, QTableWidgetItem("新列"))

        # 数据库同步操作
        self.syncToDatabase()

    def deleteColumn(self):
        col = self.tableWidget.currentColumn()
        self.tableWidget.removeColumn(col)

        # 数据库同步操作
        self.syncToDatabase()

    def modifyColumn(self):
        col = self.tableWidget.currentColumn()
        newHeaderText, ok = QInputDialog.getText(self, "修改列名", "请输入新列名")
        if ok:
            self.tableWidget.setHorizontalHeaderItem(col, QTableWidgetItem(newHeaderText))

            # 数据库同步操作
            self.syncToDatabase()

    def syncToDatabase(self):
        # 数据库同步操作
        if self.fileName is None:
            pass
        else:
            name = self.fileName.split('/')[-1].split(".")[0]
            table_name = "Q"+name
            conn = sqlite3.connect("db_table.db")
            c = conn.cursor()
            c.execute(f"DROP TABLE IF EXISTS { table_name }")
            c.execute(f"CREATE TABLE { table_name } (id INT, data TEXT)")
            for i in range(self.tableWidget.rowCount()):
                row = []
                for j in range(self.tableWidget.columnCount()):
                    item = self.tableWidget.item(i, j)
                    if item is not None:
                        row.append(item.text())
                    else:
                        row.append("")
                c.execute(f"INSERT INTO { table_name } (id, data) VALUES (?, ?)", (i, ",".join(row)))
            conn.commit()
            conn.close()

    def closeEvent(self, event):
        result = QMessageBox.question(self, "保存文件", "保存改文件吗?")
        if result == QMessageBox.Yes:
            # 保存到csv文件
            self.saveFile()
            # 保存到数据库
            if self.fileName:
                self.syncToDatabase()
        else:
            pass

    def loadDataFromDatabase(self, table_name):
        # 从数据库中读取数据
        conn = sqlite3.connect("db_table.db")
        c = conn.cursor()
        c.execute(f"SELECT * FROM {table_name} ORDER BY id")
        data = []
        for row in c.fetchall():
            data.append(row[1].split(","))
        conn.close()

        # 加载到表格中
        self.tableWidget.setRowCount(len(data))
        self.tableWidget.setColumnCount(len(data[0]))
        for i, row in enumerate(data):
            for j, cell in enumerate(row):
                self.tableWidget.setItem(i, j, QTableWidgetItem(cell))


if __name__ == '__main__':
    app = QApplication(sys.argv)
    mainWindow = MainWindow()
    mainWindow.loadDataFromDatabase("Qtest")
    sys.exit(app.exec_())