def show_em(self):
# 建立后端数据模型，需要获取行列数
mm = Mysql_manager()
with mm:
result = mm.show_db()
# print(result)
self.model = QStandardItemModel(mm.cur.rowcount, 7)
# 数据模型抬头
title = ['姓名', '部门', 'IP地址', 'MAC地址', '房间号', '交换机地址', '交换机端口']
self.model.setHorizontalHeaderLabels(title)
# 通过遍历获取数据库单元格的值。然后赋值给模型的单元格
num = 0
for i in result:
item0 = QtGui.QStandardItem(i[0])
item1 = QtGui.QStandardItem(i[1])
item2 = QtGui.QStandardItem(i[2])
item3 = QtGui.QStandardItem(i[3])
item4 = QtGui.QStandardItem(str(i[4])) # 数据库该列是整型，这里要求字符串
item5 = QtGui.QStandardItem(i[5])
item6 = QtGui.QStandardItem(str(i[6]))
self.model.setItem(num, 0, item0)
self.model.setItem(num, 1, item1)
self.model.setItem(num, 2, item2)
self.model.setItem(num, 3, item3)
self.model.setItem(num, 4, item4)
self.model.setItem(num, 5, item5)
self.model.setItem(num, 6, item6)
num += 1
self.tableView.setModel(self.model) # 视图和数据模型绑定
def export_excel(self,path):
# 数据导出为表格
# 先实例表格对象
self.wb = Workbook()
self.ws = self.wb.active
# 先写入表头
self.ws.cell(row = 1,column = 1).value = '姓名'
self.ws.cell(row = 1,column = 2).value = '部门'
self.ws.cell(row = 1,column = 3).value = 'IP地址'
self.ws.cell(row = 1,column = 4).value = 'MAC地址'
self.ws.cell(row = 1,column = 5).value = '房间号'
self.ws.cell(row = 1,column = 6).value = '交换机'
self.ws.cell(row = 1,column = 7).value = '端口'
# 从数据库读取数据
mm = Mysql_manager()
with mm:
result = mm.show_db() # 返回二元数组
num = 2 # 从第二行开始写入
for i in result:
self.ws.cell(row = num,column = 1).value = i[0]
self.ws.cell(row = num,column = 2).value = i[1]
self.ws.cell(row = num,column = 3).value = i[2]
self.ws.cell(row = num,column = 4).value = i[3]
self.ws.cell(row = num,column = 5).value = i[4]
self.ws.cell(row = num,column = 6).value = i[5]
self.ws.cell(row = num,column = 7).value = i[6]
num +=1
self.wb.save(path)
print('数据导出成功')