Python之PyQt5实战（三）源码

main.py

import sys
from datetime import datetime

import MySQLdb
import xlrd
from PyQt5.QtGui import QStandardItemModel
from PyQt5.QtWidgets import QApplication, QMainWindow, QFileDialog, QMessageBox
from PyQt5 import QtGui
from xlrd import xldate_as_tuple

from demomain import Ui_MainWindow

class Main(QMainWindow, Ui_MainWindow):
    def __init__(self):

        super(Main,self).__init__()
        self.setupUi(self)

        self.line_tbl_name.setPlaceholderText("请填写需创建的表名")
        self.btn_find_file.clicked.connect(self.openFile)
        self.btn_create_tbl.clicked.connect(self.createTable)
        self.btn_imp_data.clicked.connect(self.insert_data)

    def openFile(self):
        try:
            fname,ftype = QFileDialog.getOpenFileName(self, 'Open file', '/','Excel files(*.xlsx)')
            self.line_find_file.setText(fname)
            self.readExcel(fname)
        except FileNotFoundError as e:
            self.echo(value='请选择文件……')

    def readExcel(self, fname):
        path = fname
        data = xlrd.open_workbook(path) #获取文件地址
        table = data.sheet_by_index(0) #因为是测试工具我这里默认访问第一个sheet,愿意继续研究的同学可以在这里加个小细节，用于选择访问哪个一个sheet
        total_num = table.nrows - 1
        total_col = table.ncols

        colnames = table.row_values(0)
        hearder = [str(i) for i in colnames]
#在table做一个表头，方便之后创建数据库表结构的时候直接获取表头字段。
        self.model = QStandardItemModel(total_num, total_col)
        self.model.setHorizontalHeaderLabels(hearder)
#设置excel中内容的列和行
        for row in range(1, total_num + 1):
            for column in range(total_col):
                cell = table.cell(row, column).value
                self.model.setItem(row - 1, column, QtGui.QStandardItem(str(cell)))
#做一个循环将这个列和行中的内容读出来
        self.tableView.setModel(self.model) # 最后在tableview里面set一下就可以预览啦。

    def createTable(self):
        try:
            path = self.line_find_file.text() #获取介绍图中4的名字
            data = xlrd.open_workbook(path)
            table = data.sheet_by_index(0)
            colnames =  table.row_values(0)
            table_name = self.line_tbl_name.text()
            sql='create table '+table_name+' ('+','.join([str(i+' varchar(100)') for i in colnames])+ ')' #默认类型均为varchar(100),后续可开发修改字段类型
            self.my_database(sql,table_name)#连接数据库
        except FileNotFoundError as e:
            pass

    def my_database(self, sql, table_name):
        value = ''
        db = MySQLdb.connect("localhost", "root", "root", "test", charset='utf8')
        cursor = db.cursor()

        try:
            cursor.execute(sql)
            value = table_name + '创建成功！'
            self.echo(value)
        except:
            self.echo(value='建表失败！查看表名是否重复！')

        db.commit()
        cursor.close()
        db.close()

    def insert_data(self):
        try:
            path = self.line_find_file.text()
            data = xlrd.open_workbook(path)
            table = data.sheet_by_index(0)
            table_name = self.line_tbl_name.text()
            total_num = table.nrows
            total_col = table.ncols

            db = MySQLdb.connect("localhost", "root", "root", "test", charset='utf8')
            cursor = db.cursor()

            for i in range(1, total_num):  # 除了第一行剩下的数据
                row_content = []
                sql = self.concat_head(table, table_name)
                for j in range(total_col):
                    cell = table.cell(i, j).value
                    ctype = table.cell(i, j).ctype
                    if ctype == 2 and cell % 1 == 0:  # 如果是整形
                        cell = int(cell)
                    elif ctype == 3:  # 转成datetime对象
                        date = datetime(*xldate_as_tuple(cell, 0))
                        cell = "'" + date.strftime('%Y-%d-%m %H:%M:%S') + "'"
                    elif ctype == 4:
                        cell = True if cell == 1 else False
                    else:
                        cell = "'" + cell + "'"
                    row_content.append(cell)
                sql += "(" + ','.join(str(element) for element in row_content) + ");"
                try:
                    cursor.execute(sql)  # insert语句
                except:
                    db.rollback()
            db.commit()
            cursor.close()
            db.close()
            self.echo(value='数据插入成功！')
        except:
            self.echo(value='数据插入失败！')

    def concat_head (self,table,table_name):
        colnames =  table.row_values(0) #第一行拼接'insert into table_name (,,,) values'
        str1 = 'insert into '+table_name+' ('+','.join([str(i) for i in colnames])+ ') values'
        return str1

    def echo(self,value):
        if value == False:
            QMessageBox.information(self, "抱歉", "功能暂未开放……敬请期待！", QMessageBox.Yes)
        else:
            QMessageBox.information(self, "结果", "{}\n".format(value), QMessageBox.Yes)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    Main = Main()
    Main.show()
    sys.exit(app.exec_())

后续同步更新