改进一下上次写的程序
使用环境：Python3.8 + openpyxl 3.0.9 + PyQt5 5.15.4 + Pandas 1.3.5
窗体GUI
由于此次所对应的excel数据格式改变，所以重新写了对应的函数【在首行添加列名】
实现代码
RdexcelFuc.py
1. 随机生成数据
defrandom_dateframe(data, num):"""
随机生成数据
:param data: DataFrame对象
:param num: 随机生成的数量
:return: 随机生成的数据(list) || error report
data.sample(n=num,random_state=None)
:parameter data: 数据帧对象
:parameter n: 随机数量
:parameter radom_state: None=不重复的的数 1=会生成重复的数
"""
new_DataFrame= data.sample(n=num, random_state=None)# 随机抽取数据try:
title=list(new_DataFrame)# 获取列名
gender= title.index("性别")# 获取下标
grade= title.index("年级")
name= title.index("姓名")
names=[]# 存储随机结果的列表for iinrange(num):
lst=list(new_DataFrame.iloc[i])# 抽取第一行数据
names.append("{}({} {})".format(lst[name], lst[grade], lst[gender]))return namesexcept ValueError:return traceback.format_exc()2. 新建excel
defbuild_excel(names, save_filename, dirname):"""
新建excel
:param names: 随机的数据
:param save_filename: 另存为的文件名
:param dirname: 保存的文件路径
:return: 导出文件的路径
"""try:"""
desktop = os.path.join(os.path.expanduser("~"), "Desktop\\") + filename + ".xlsx"
os.path.expanduser("~"): 或许系统中~的路径
os.path.join(): 将Desktop和系统路径组成新路径
"""
path= dirname+"/"+ save_filename+".xlsx"
wb= openpyxl.Workbook()# 新建excel
ws= wb.worksheets[0]# 选择工作表for iinrange(len(names)):
index='A'+str(i+1)
ws[index]= names[i]
wb.save(path)# 保存excelreturn pathexcept BaseException:return traceback.format_exc()3. 新建Text
defbuild_text(names, save_filename, dirname):"""
新建txt
:param names: 随机的数据
:param save_filename: 另存为的文件名
:param dirname: 保存的文件路径
:return: True/report errors
"""try:
path= dirname+"/"+ save_filename+".txt"withopen(path,"w")as f:for namein names:
f.write(name+"\n")return pathexcept BaseException:return traceback.format_exc()4. 新建Excel和Text
defbuild_both(names, save_filename, dirname):"""
新建excel和text
:param names: 随机的数据
:param save_filename: 另存为的文件名
:param dirname: 保存的文件路径
:return: True/False
"""
flag1= build_text(names, save_filename, dirname)
flag2= build_excel(names, save_filename, dirname)if flag1and flag2:return flag1+"\n"+ flag2else:return[flag1, flag2]5. 判断输入数量是否超出最大行数
defis_overflow(max_rows, num):"""
判断输入数量是否超出最大行数
:param max_rows: 最大行数
:param num: 超出数量
:return: True || False
"""if num> max_rows:returnFalseelse:returnTrue6. 获取最大行数和DataFrame对象
defget_rows_data(filename):"""
获取最大行数和DataFrame对象
:param filename: 文件路径
:return: 最大行数 and DataFrame对象
"""
data= pd.read_excel(filename)
max_rows= data.shape[0]return max_rows, data《Rdexcel.py》
1. 置顶窗口
defwindow_ontop(self, flag=0):"""
设置窗口置顶
:param flag: 0/1
:return: None
"""if flag==0:
MainWindow.setWindowFlags(QtCore.Qt.WindowStaysOnTopHint)# 设置窗口置顶else:
MainWindow.setWindowFlags(QtCore.Qt.Widget)# 取消置顶
MainWindow.showNormal()# 回到窗口的原始尺寸# 百度上部分方法使用self.show() 会使窗口消失不可见2. 判断数据有效性
defvalid_data(self):"""
判断数据有效性
:return: 保存的路径, excel文件的路径, 输入的数, 自定义文件名
"""
filename= self.lineEdit.text()# 获取选择数据表的路径
dirname= self.lineEdit_4.text()# 获取选择的文件夹路径
num= self.lineEdit_2.text()# 获取输入的数量
save_filename= self.lineEdit_3.text()# 自定义文件名ifnot filename:
self.information("请选择数据表！")elifnot dirname:
self.information("请选择文件夹！")elifnot num:
self.information("请输入数量！")elifnot save_filename:
self.information("请输入另存为文件名！")else:return dirname, filename,int(num), save_filenamereturn[False,False,False,False]实现的效果
Pyinstaller打包命令
pyinstaller -w -F -p C:\...Python\Python38\Lib\site-packages -i favicon.ico Rdexcel.py RdexcelFuc.py基础总结
在实现编写置顶功能的时候，如果取消置顶会出现窗口缩小化不可见，在添加self.show()时，窗口会消失；
添加MainWindow.showNormal()会出现窗口闪烁文件下载
https://download.csdn.net/download/weixin_45564816/85063982完整函数
# Rdexcel.py#!/usr/bin/python# -*- coding: UTF-8 -*-import sysimport RdexcelFucimport tracebackfrom PyQt5import QtCore, QtGui, QtWidgetsfrom PyQt5.QtWidgetsimport QMessageBoxfrom PyQt5.QtWidgetsimport QWidget, QFileDialogfrom PyQt5.QtCoreimport QRegExpfrom PyQt5.QtGuiimport QIntValidator, QRegExpValidator# 导入整数校验器和正则校验器classUi_MainWindow(QWidget):defsetupUi(self, MainWindow):
MainWindow.setObjectName("MainWindow")
MainWindow.setWindowFlags(QtCore.Qt.WindowStaysOnTopHint)# 设置窗口处于置顶状态
MainWindow.resize(451,315)
icon= QtGui.QIcon()
icon.addPixmap(QtGui.QPixmap("other.png"), QtGui.QIcon.Normal, QtGui.QIcon.Off)
MainWindow.setWindowIcon(icon)
self.centralwidget= QtWidgets.QWidget(MainWindow)
self.centralwidget.setObjectName("centralwidget")
self.gridLayout_2= QtWidgets.QGridLayout(self.centralwidget)
self.gridLayout_2.setObjectName("gridLayout_2")
self.label_6= QtWidgets.QLabel(self.centralwidget)
font= QtGui.QFont()
font.setFamily("Agency FB")
font.setPointSize(11)
self.label_6.setFont(font)
self.label_6.setObjectName("label_6")
self.gridLayout_2.addWidget(self.label_6,4,0,1,3)
self.lineEdit_4= QtWidgets.QLineEdit(self.centralwidget)
self.lineEdit_4.setReadOnly(True)
self.lineEdit_4.setObjectName("lineEdit_4")
self.gridLayout_2.addWidget(self.lineEdit_4,2,2,1,2)
self.gridLayout= QtWidgets.QGridLayout()
self.gridLayout.setObjectName("gridLayout")
self.radioButton_2= QtWidgets.QRadioButton(self.centralwidget)
font= QtGui.QFont()
font.setFamily("宋体")
font.setPointSize(9)
self.radioButton_2.setFont(font)
self.radioButton_2.setObjectName("radioButton_2")
self.gridLayout.addWidget(self.radioButton_2,0,4,1,1)
self.label_3= QtWidgets.QLabel(self.centralwidget)
self.label_3.setObjectName("label_3")
self.gridLayout.addWidget(self.label_3,0,2,1,1)
self.radioButton_3= QtWidgets.QRadioButton(self.centralwidget)
self.radioButton_3.setObjectName("radioButton_3")
self.gridLayout.addWidget(self.radioButton_3,0,5,1,1)
self.radioButton= QtWidgets.QRadioButton(self.centralwidget)
font= QtGui.QFont()
font.setFamily("宋体")
font.setPointSize(9)
self.radioButton.setFont(font)
self.radioButton.setChecked(True)
self.radioButton.setObjectName("radioButton")
self.gridLayout.addWidget(self.radioButton,0,3,1,1)
self.lineEdit_2= QtWidgets.QLineEdit(self.centralwidget)# pint = QIntValidator(self) 实例化整形验证器# pint.setRange(0, 9999999) 设置范围# self.lineEdit_2.setValidator(pint)
reg= QRegExp('[0-9]+')# 设置正则表达式
preg= QRegExpValidator(self)# 自定义校验器
preg.setRegExp(reg)# 设置属性
self.lineEdit_2.setValidator(preg)
self.lineEdit_2.setObjectName("lineEdit_2")
self.gridLayout.addWidget(self.lineEdit_2,0,1,1,1)
self.label_4= QtWidgets.QLabel(self.centralwidget)
font= QtGui.QFont()
font.setFamily("Agency FB")
font.setPointSize(11)
self.label_4.setFont(font)
self.label_4.setObjectName("label_4")
self.gridLayout.addWidget(self.label_4,0,0,1,1)
self.gridLayout_2.addLayout(self.gridLayout,3,0,1,5)
self.pushButton_2= QtWidgets.QPushButton(self.centralwidget)
self.pushButton_2.setObjectName("pushButton_2")
self.gridLayout_2.addWidget(self.pushButton_2,4,4,1,1)
self.lineEdit= QtWidgets.QLineEdit(self.centralwidget)
self.lineEdit.setReadOnly(True)
self.lineEdit.setObjectName("lineEdit")
self.gridLayout_2.addWidget(self.lineEdit,1,2,1,2)
self.label_7= QtWidgets.QLabel(self.centralwidget)
font= QtGui.QFont()
font.setFamily("Agency FB")
font.setPointSize(11)
self.label_7.setFont(font)
self.label_7.setObjectName("label_7")
self.gridLayout_2.addWidget(self.label_7,2,0,1,2)
self.lineEdit_3= QtWidgets.QLineEdit(self.centralwidget)
reg3= QRegExp('[^?*、\\\/\"\"<>|]+')# 设置正则表达式
preg3= QRegExpValidator(self)# 自定义校验器
preg3.setRegExp(reg3)# 设置属性
self.lineEdit_3.setValidator(preg3)
self.lineEdit_3.setObjectName("lineEdit_3")
self.gridLayout_2.addWidget(self.lineEdit_3,4,3,1,1)
self.pushButton= QtWidgets.QPushButton(self.centralwidget)
self.pushButton.setObjectName("pushButton")
self.gridLayout_2.addWidget(self.pushButton,1,4,1,1)
self.label= QtWidgets.QLabel(self.centralwidget)
font= QtGui.QFont()
font.setFamily("Agency FB")
font.setPointSize(18)
font.setBold(True)
font.setWeight(75)
self.label.setFont(font)
self.label.setAlignment(QtCore.Qt.AlignCenter)
self.label.setObjectName("label")
self.gridLayout_2.addWidget(self.label,0,1,1,3)
self.label_2= QtWidgets.QLabel(self.centralwidget)
font= QtGui.QFont()
font.setFamily("Agency FB")
font.setPointSize(11)
font.setBold(False)
font.setWeight(50)
self.label_2.setFont(font)
self.label_2.setObjectName("label_2")
self.gridLayout_2.addWidget(self.label_2,1,0,1,2)
self.pushButton_3= QtWidgets.QPushButton(self.centralwidget)
self.pushButton_3.setObjectName("pushButton_3")
self.gridLayout_2.addWidget(self.pushButton_3,2,4,1,1)
self.label_5= QtWidgets.QLabel(self.centralwidget)
self.label_5.setText("")
self.label_5.setObjectName("label_5")
self.gridLayout_2.addWidget(self.label_5,0,0,1,1)
self.textEdit= QtWidgets.QTextEdit(self.centralwidget)
self.textEdit.setObjectName("textEdit")
self.gridLayout_2.addWidget(self.textEdit,5,0,1,5)
self.checkBox= QtWidgets.QCheckBox(self.centralwidget)
self.checkBox.setChecked(True)
self.checkBox.setObjectName("checkBox")
self.gridLayout_2.addWidget(self.checkBox,0,4,1,1)
MainWindow.setCentralWidget(self.centralwidget)
self.menubar= QtWidgets.QMenuBar(MainWindow)
self.menubar.setGeometry(QtCore.QRect(0,0,451,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.pushButton.clicked.connect(self.open_file)# 绑定“选择数据表”控件事件
self.pushButton_3.clicked.connect(self.open_dir)# 绑定“选择文件夹”控件事件
self.checkBox.stateChanged.connect(self.check_state)# 绑定“置顶”控件事件
self.pushButton_2.clicked.connect(self.rdfuc)# 绑定“开始生成”控件事件
QtCore.QMetaObject.connectSlotsByName(MainWindow)defretranslateUi(self, MainWindow):
_translate= QtCore.QCoreApplication.translate
MainWindow.setWindowTitle(_translate("MainWindow","Rdexcel"))
self.label_6.setText(_translate("MainWindow","另存为文件名(不含后缀)"))
self.radioButton_2.setText(_translate("MainWindow","Text"))
self.label_3.setText(_translate("MainWindow","选择生成类型"))
self.radioButton_3.setText(_translate("MainWindow","Both"))
self.radioButton.setText(_translate("MainWindow","Excel"))
self.label_4.setText(_translate("MainWindow","输入数量"))
self.pushButton_2.setText(_translate("MainWindow","开始生成"))
self.label_7.setText(_translate("MainWindow","保存文件的路径"))
self.pushButton.setText(_translate("MainWindow","选择数据表"))
self.label.setText(_translate("MainWindow","随机生成数据建表"))
self