QT(三)excel表格的CRUD

主要操作内容在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_())


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值