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_())
后续同步更新