主要操作内容在class Crud中
# -*- coding: utf-8 -*-
# Form implementation generated from reading ui file 'excelwork.ui'
#
# Created by: PyQt5 UI code generator 5.13.0
#
# WARNING! All changes made in this file will be lost!
from PyQt5.QtCore import pyqtSignal
from xlutils.copy import copy
import sys
from PyQt5 import QtCore, QtWidgets,QtGui
from PyQt5.QtWidgets import QApplication, QMainWindow, QMessageBox
import xlrd,xlwt
import re
import os
import time
def getBook(path):
data = xlrd.open_workbook(path, formatting_info=True)
return copy(wb=data),data.sheets()[0]
def initExcel(path):
mkdirlambda = lambda x: os.makedirs(x) if not os.path.exists(x) else True
mkdirlambda('./excelwork')
book = xlwt.Workbook() # 创建一个excel对象
# sheet = data.sheets()[0]
sheet = book.add_sheet('Sheet1', cell_overwrite_ok=True) # 添加一个sheet页
title = ['序号', '时间', '分类','金额']
sheet.write(0, 0, title[0])
sheet.write(0, 1, title[1])
sheet.write(0, 2, title[2])
sheet.write(0, 3, title[3])
book.save(path)
def getMaxrow(path):
xlrd.Book.encoding = "utf-8"
data = xlrd.open_workbook(path)
table = data.sheets()[0]
return table.nrows
def SortDatalist(datalist,datalabel=0):
if (len(datalist) == 1):
return datalist
label = int(datalist[int(len(datalist)/2)][3])
leftlist = []
rightlist = []
for index,data in enumerate(datalist):
if(int(data[3])>label):
rightlist.append(data)
elif(int(data[3])<=label and index!=int(len(datalist)/2)):
leftlist.append(data)
if(len(leftlist)==0):
leftlist.append(datalist[int(len(datalist)/2)])
elif(len(rightlist)==0):
rightlist.append(datalist[int(len(datalist) / 2)])
else:
leftlist.append(datalist[int(len(datalist) / 2)])
leftlist = SortDatalist(leftlist,datalabel)
rightlist = SortDatalist(rightlist,datalabel)
if(datalabel==1):
return rightlist + leftlist
return leftlist + rightlist
class Crud(QMainWindow):
def __init__(self):
super(Crud, self).__init__()
self.setupUi(self)
self.radioButton_3.setChecked(True)
self.path = os.path.join('./excelwork','data.xls')
# book为写数据,table为读数据使用
# 初始化excel表格
try:
self.book,self.table = getBook(self.path)
except Exception as e:
initExcel(self.path)
self.book,self.table = getBook(self.path)
# 事件
self.pushButton.clicked.connect(self.addData)
self.pushButton_2.clicked.connect(self.deleteData)
self.pushButton_3.clicked.connect(self.updataData)
self.pushButton_4.clicked.connect(self.selectData)
def addData(self):
try:
row = getMaxrow(self.path)
sheet = self.book.get_sheet(0)
sheet.write(row,0,row)
sheet.write(row,1,getTime(time.time()))
sheet.write(row,2,self.comboBox.currentText())
sheet.write(row,3,self.lineEdit.text())
self.book.save(self.path)
except Exception as e:
print(e)
def deleteData(self):
datalist = []
for row in range(self.table.nrows):
if(row!=0):
if(int(self.table.row_values(row)[0])!=int(self.lineEdit_2.text())):
datalist.append(self.table.row_values(row))
try:
initExcel(self.path)
self.book, self.table = getBook(self.path)
sheet = self.book.get_sheet(0)
for index,data in enumerate(datalist):
row = index + 1
sheet.write(row, 0, data[0])
sheet.write(row, 1, data[1])
sheet.write(row, 2, data[2])
sheet.write(row, 3, data[3])
print(data)
self.book.save(self.path)
except Exception as e:
print(e)
def updataData(self):
label = 0
for row in range(self.table.nrows):
if (row != 0):
if (int(self.table.row_values(row)[0]) == int(self.lineEdit_4.text())):
label = row
break
sheet = self.book.get_sheet(0)
sheet.write(label,1,getTime(time.time()))
sheet.write(label,2,self.comboBox_2.currentText())
sheet.write(label,3,self.lineEdit_3.text())
self.book.save(self.path)
def selectData(self):
try:
self.textEdit.setText("")
label = 0
datalist = []
for row in range(self.table.nrows):
if(row!=0):
content = "序号:" + str(int(self.table.row_values(row)[0])) +" | 时间:"+ self.table.row_values(row)[1] +" | 类别:"+ self.table.row_values(row)[2] +" | 金额:"+ self.table.row_values(row)[3] + " 元"
if(self.radioButton_3.isChecked()):
self.textEdit.append(content)
label = 2
datalist.append(self.table.row_values(row))
if(label == 2):
return
else:
if(self.radioButton_2.isChecked()):
label = 1
datalist = SortDatalist(datalist,label)
for data in datalist:
content = "序号:" + str(int(data[0])) +" | 时间:"+ data[1] +" | 类别:"+ data[2] +" | 金额:"+ data[3] + " 元"
self.textEdit.append(content)
except Exception as e:
print(e)
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(800, 600)
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.tabWidget = QtWidgets.QTabWidget(self.centralwidget)
self.tabWidget.setGeometry(QtCore.QRect(10, 0, 781, 551))
self.tabWidget.setObjectName("tabWidget")
self.tab = QtWidgets.QWidget()
self.tab.setObjectName("tab")
self.pushButton = QtWidgets.QPushButton(self.tab)
self.pushButton.setGeometry(QtCore.QRect(340, 312, 75, 31))
self.pushButton.setObjectName("pushButton")
self.lineEdit = QtWidgets.QLineEdit(self.tab)
self.lineEdit.setGeometry(QtCore.QRect(350, 200, 101, 31))
self.lineEdit.setObjectName("lineEdit")
self.comboBox = QtWidgets.QComboBox(self.tab)
self.comboBox.setGeometry(QtCore.QRect(350, 110, 69, 31))
self.comboBox.setObjectName("comboBox")
self.comboBox.addItem("")
self.comboBox.addItem("")
self.comboBox.addItem("")
self.label = QtWidgets.QLabel(self.tab)
self.label.setGeometry(QtCore.QRect(310, 120, 54, 12))
self.label.setObjectName("label")
self.label_2 = QtWidgets.QLabel(self.tab)
self.label_2.setGeometry(QtCore.QRect(310, 210, 54, 12))
self.label_2.setObjectName("label_2")
self.tabWidget.addTab(self.tab, "")
self.tab_3 = QtWidgets.QWidget()
self.tab_3.setObjectName("tab_3")
self.lineEdit_2 = QtWidgets.QLineEdit(self.tab_3)
self.lineEdit_2.setGeometry(QtCore.QRect(360, 170, 71, 31))
self.lineEdit_2.setObjectName("lineEdit_2")
self.label_3 = QtWidgets.QLabel(self.tab_3)
self.label_3.setGeometry(QtCore.QRect(310, 180, 54, 12))
self.label_3.setObjectName("label_3")
self.pushButton_2 = QtWidgets.QPushButton(self.tab_3)
self.pushButton_2.setGeometry(QtCore.QRect(330, 250, 75, 31))
self.pushButton_2.setObjectName("pushButton_2")
self.tabWidget.addTab(self.tab_3, "")
self.tab_4 = QtWidgets.QWidget()
self.tab_4.setObjectName("tab_4")
self.label_4 = QtWidgets.QLabel(self.tab_4)
self.label_4.setGeometry(QtCore.QRect(310, 140, 54, 12))
self.label_4.setObjectName("label_4")
self.lineEdit_3 = QtWidgets.QLineEdit(self.tab_4)
self.lineEdit_3.setGeometry(QtCore.QRect(350, 220, 101, 31))
self.lineEdit_3.setObjectName("lineEdit_3")
self.pushButton_3 = QtWidgets.QPushButton(self.tab_4)
self.pushButton_3.setGeometry(QtCore.QRect(340, 332, 75, 31))
self.pushButton_3.setObjectName("pushButton_3")
self.label_5 = QtWidgets.QLabel(self.tab_4)
self.label_5.setGeometry(QtCore.QRect(310, 230, 54, 12))
self.label_5.setObjectName("label_5")
self.comboBox_2 = QtWidgets.QComboBox(self.tab_4)
self.comboBox_2.setGeometry(QtCore.QRect(350, 130, 69, 31))
self.comboBox_2.setObjectName("comboBox_2")
self.comboBox_2.addItem("")
self.comboBox_2.addItem("")
self.comboBox_2.addItem("")
self.lineEdit_4 = QtWidgets.QLineEdit(self.tab_4)
self.lineEdit_4.setGeometry(QtCore.QRect(350, 50, 61, 31))
self.lineEdit_4.setText("")
self.lineEdit_4.setObjectName("lineEdit_4")
self.label_6 = QtWidgets.QLabel(self.tab_4)
self.label_6.setGeometry(QtCore.QRect(310, 60, 54, 12))
self.label_6.setObjectName("label_6")
self.tabWidget.addTab(self.tab_4, "")
self.tab_2 = QtWidgets.QWidget()
self.tab_2.setObjectName("tab_2")
self.textEdit = QtWidgets.QTextEdit(self.tab_2)
self.textEdit.setGeometry(QtCore.QRect(20, 20, 741, 291))
self.textEdit.setObjectName("textEdit")
self.label_7 = QtWidgets.QLabel(self.tab_2)
self.label_7.setGeometry(QtCore.QRect(30, 360, 54, 12))
self.label_7.setObjectName("label_7")
self.comboBox_3 = QtWidgets.QComboBox(self.tab_2)
self.comboBox_3.setGeometry(QtCore.QRect(70, 350, 69, 31))
self.comboBox_3.setObjectName("comboBox_3")
self.comboBox_3.addItem("")
self.comboBox_3.addItem("")
self.comboBox_3.addItem("")
self.radioButton = QtWidgets.QRadioButton(self.tab_2)
self.radioButton.setGeometry(QtCore.QRect(250, 360, 89, 16))
self.radioButton.setObjectName("radioButton")
self.radioButton_2 = QtWidgets.QRadioButton(self.tab_2)
self.radioButton_2.setGeometry(QtCore.QRect(380, 360, 89, 16))
self.radioButton_2.setObjectName("radioButton_2")
self.radioButton_3 = QtWidgets.QRadioButton(self.tab_2)
self.radioButton_3.setGeometry(QtCore.QRect(500, 360, 89, 16))
self.radioButton_3.setObjectName("radioButton_3")
self.pushButton_4 = QtWidgets.QPushButton(self.tab_2)
self.pushButton_4.setGeometry(QtCore.QRect(310, 420, 81, 41))
self.pushButton_4.setObjectName("pushButton_4")
self.tabWidget.addTab(self.tab_2, "")
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 23))
self.menubar.setObjectName("menubar")
MainWindow.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.retranslateUi(MainWindow)
self.tabWidget.setCurrentIndex(3)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.pushButton.setText(_translate("MainWindow", "增加"))
self.comboBox.setItemText(0, _translate("MainWindow", "吃饭"))
self.comboBox.setItemText(1, _translate("MainWindow", "喝酒"))
self.comboBox.setItemText(2, _translate("MainWindow", "娱乐"))
self.label.setText(_translate("MainWindow", "类别:"))
self.label_2.setText(_translate("MainWindow", "金额:"))
self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab), _translate("MainWindow", "增加"))
self.label_3.setText(_translate("MainWindow", "删除id:"))
self.pushButton_2.setText(_translate("MainWindow", "删除"))
self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_3), _translate("MainWindow", "删除"))
self.label_4.setText(_translate("MainWindow", "类别:"))
self.pushButton_3.setText(_translate("MainWindow", "修改"))
self.label_5.setText(_translate("MainWindow", "金额:"))
self.comboBox_2.setItemText(0, _translate("MainWindow", "吃饭"))
self.comboBox_2.setItemText(1, _translate("MainWindow", "喝酒"))
self.comboBox_2.setItemText(2, _translate("MainWindow", "娱乐"))
self.label_6.setText(_translate("MainWindow", "修改id:"))
self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_4), _translate("MainWindow", "修改"))
self.label_7.setText(_translate("MainWindow", "类别:"))
self.comboBox_3.setItemText(0, _translate("MainWindow", "吃饭"))
self.comboBox_3.setItemText(1, _translate("MainWindow", "喝酒"))
self.comboBox_3.setItemText(2, _translate("MainWindow", "娱乐"))
self.radioButton.setText(_translate("MainWindow", "金额正序"))
self.radioButton_2.setText(_translate("MainWindow", "金额逆序"))
self.radioButton_3.setText(_translate("MainWindow", "默认"))
self.pushButton_4.setText(_translate("MainWindow", "查询"))
self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_2), _translate("MainWindow", "查询"))
def closeEvent(self, event):
reply = QMessageBox.question(self, '确认', '确认退出吗?', QMessageBox.Yes | QMessageBox.No)
if reply == QMessageBox.Yes:
event.accept()
else:
event.ignore()
class Excelwork(object):
def setupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.resize(268, 633)
self.centralwidget = QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.pushButton = QtWidgets.QPushButton(self.centralwidget)
self.pushButton.setGeometry(QtCore.QRect(90, 140, 75, 31))
self.pushButton.setObjectName("pushButton")
self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
self.pushButton_2.setGeometry(QtCore.QRect(90, 190, 75, 31))
self.pushButton_2.setObjectName("pushButton_2")
self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
self.pushButton_3.setGeometry(QtCore.QRect(90, 240, 75, 31))
self.pushButton_3.setObjectName("pushButton_3")
self.pushButton_4 = QtWidgets.QPushButton(self.centralwidget)
self.pushButton_4.setGeometry(QtCore.QRect(90, 290, 75, 31))
self.pushButton_4.setObjectName("pushButton_4")
self.label = QtWidgets.QLabel(self.centralwidget)
self.label.setGeometry(QtCore.QRect(20, 59, 241, 41))
font = QtGui.QFont()
font.setFamily("Aharoni")
font.setPointSize(20)
font.setBold(True)
font.setWeight(75)
self.label.setFont(font)
self.label.setObjectName("label")
MainWindow.setCentralWidget(self.centralwidget)
self.menubar = QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0, 0, 268, 23))
self.menubar.setObjectName("menubar")
MainWindow.setMenuBar(self.menubar)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
self.retranslateUi(MainWindow)
QtCore.QMetaObject.connectSlotsByName(MainWindow)
def retranslateUi(self, MainWindow):
_translate = QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
self.pushButton.setText(_translate("MainWindow", "增加"))
self.pushButton_2.setText(_translate("MainWindow", "删除"))
self.pushButton_3.setText(_translate("MainWindow", "修改"))
self.pushButton_4.setText(_translate("MainWindow", "查询"))
self.label.setText(_translate("MainWindow", "excel表格数据处理"))
class ResultApp(Excelwork,QMainWindow):
def __init__(self):
super(ResultApp, self).__init__()
self.setupUi(self)
self.crud = Crud()
self.crud.hide()
# 事件
self.pushButton.clicked.connect(self.Addcontent)
self.pushButton_2.clicked.connect(self.Deletecontent)
self.pushButton_3.clicked.connect(self.Updatacontent)
self.pushButton_4.clicked.connect(self.Selectcontent)
def Addcontent(self):
try:
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_2))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_3))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_4))
self.crud.tabWidget.addTab(self.crud.tab, "增加")
self.crud.show()
except Exception as e:
print(e)
def Deletecontent(self):
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_2))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_3))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_4))
self.crud.tabWidget.addTab(self.crud.tab_3, "删除")
self.crud.show()
def Updatacontent(self):
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_2))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_3))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_4))
self.crud.tabWidget.addTab(self.crud.tab_4, "更新")
self.crud.show()
def Selectcontent(self):
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_2))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_3))
self.crud.tabWidget.removeTab(self.crud.tabWidget.indexOf(self.crud.tab_4))
self.crud.tabWidget.addTab(self.crud.tab_2, "查询")
self.crud.show()
def closeEvent(self, event):
reply = QMessageBox.question(self, '确认', '确认退出吗?', QMessageBox.Yes | QMessageBox.No)
if reply == QMessageBox.Yes:
event.accept()
else:
event.ignore()
# 时间戳转时间
def getTime(timelabel):
time_local = time.localtime(timelabel)
return time.strftime("%Y/%m/%d %H:%M",time_local)
if __name__ == '__main__':
app = QApplication(sys.argv)
resultapp = ResultApp()
resultapp.show()
sys.exit(app.exec_())