基于PyQt5实现随机抽取Excel数据

改进一下上次写的程序

使用环境：Python3.8 + openpyxl 3.0.9 + PyQt5 5.15.4 + Pandas 1.3.5

窗体GUI

由于此次所对应的excel数据格式改变，所以重新写了对应的函数【在首行添加列名】

实现代码

RdexcelFuc.py

1. 随机生成数据

def random_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 i in range(num):
            lst = list(new_DataFrame.iloc[i])  # 抽取第一行数据
            names.append("{}({} {})".format(lst[name], lst[grade], lst[gender]))
        return names
    except ValueError:
        return traceback.format_exc()

2. 新建excel

def build_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 i in range(len(names)):
            index = 'A' + str(i + 1)
            ws[index] = names[i]
        wb.save(path)  # 保存excel
        return path
    except BaseException:
        return traceback.format_exc()

3. 新建Text

def build_text(names, save_filename, dirname):
   """
   新建txt
   :param names: 随机的数据
   :param save_filename: 另存为的文件名
   :param dirname: 保存的文件路径
   :return: True/report errors
   """
   try:
       path = dirname + "/" + save_filename + ".txt"
       with open(path, "w") as f:
           for name in names:
               f.write(name + "\n")
       return path
   except BaseException:
       return traceback.format_exc()

4. 新建Excel和Text

def build_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 flag1 and flag2:
        return flag1 + "\n" + flag2
    else:
        return [flag1, flag2]

5. 判断输入数量是否超出最大行数

def is_overflow(max_rows, num):
    """
    判断输入数量是否超出最大行数
    :param max_rows: 最大行数
    :param num: 超出数量
    :return: True || False
    """
    if num > max_rows:
        return False
    else:
        return True

6. 获取最大行数和DataFrame对象

def get_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. 置顶窗口

def window_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. 判断数据有效性

def valid_data(self):
    """
    判断数据有效性
    :return: 保存的路径, excel文件的路径, 输入的数, 自定义文件名
    """
    filename = self.lineEdit.text()  # 获取选择数据表的路径
    dirname = self.lineEdit_4.text()  # 获取选择的文件夹路径
    num = self.lineEdit_2.text()  # 获取输入的数量
    save_filename = self.lineEdit_3.text()  # 自定义文件名
    if not filename:
        self.information("请选择数据表！")
    elif not dirname:
        self.information("请选择文件夹！")
    elif not num:
        self.information("请输入数量！")
    elif not save_filename:
        self.information("请输入另存为文件名！")
    else:
        return dirname, filename, int(num), save_filename
    return [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 sys
import RdexcelFuc
import traceback
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QMessageBox
from PyQt5.QtWidgets import QWidget, QFileDialog
from PyQt5.QtCore import QRegExp
from PyQt5.QtGui import QIntValidator, QRegExpValidator  # 导入整数校验器和正则校验器


class Ui_MainWindow(QWidget):
    def setupUi(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)

    def retranslateUi(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.label_2.setText(_translate("MainWindow", "Excel数据表路径"))
        self.pushButton_3.setText(_translate("MainWindow", "选择文件夹"))
        self.checkBox.setText(_translate("MainWindow", "置顶"))

    def open_file(self):
        """
        打开文件
        :return: None
        QFileDialog.getOpenFileName(self, 文件选择框标题, 打开的默认路径, 过滤文件后缀)
        """
        self.window_ontop(1)
        self.lineEdit.setText(QFileDialog.getOpenFileName(self, "选择文件", "/", "Excel Files(*.xlsx)")[0])
        self.window_ontop(0)

    def open_dir(self):
        """
        打开文件夹
        :return: None
        QFileDialog.getExistingDirectory(self,文件选择框标题,打开的默认路径)
        """
        self.window_ontop(1)
        self.lineEdit_4.setText(QFileDialog.getExistingDirectory(self, "选择文件夹", "/"))
        self.window_ontop(1)

    def window_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() 会使窗口消失不可见

    def check_state(self):
        """
        根据复选框设置窗口属性
        :return:None
        """
        if self.checkBox.isChecked():
            self.window_ontop(0)
        else:
            self.window_ontop(1)

    def information(self, t):
        """
        弹出提示窗口
        :param t: 自定义消息
        :return: None
        """
        self.window_ontop(1)
        QMessageBox.information(self, "提示", t, QMessageBox.Yes, QMessageBox.Yes)
        self.window_ontop(0)

    def valid_data(self):
        """
        判断数据有效性
        :return: 保存的路径, excel文件的路径, 输入的数, 自定义文件名
        """
        filename = self.lineEdit.text()  # 获取选择数据表的路径
        dirname = self.lineEdit_4.text()  # 获取选择的文件夹路径
        num = self.lineEdit_2.text()  # 获取输入的数量
        save_filename = self.lineEdit_3.text()  # 自定义文件名
        if not filename:
            self.information("请选择数据表！")
        elif not dirname:
            self.information("请选择文件夹！")
        elif not num:
            self.information("请输入数量！")
        elif not save_filename:
            self.information("请输入另存为文件名！")
        else:
            return dirname, filename, int(num), save_filename
        return [False, False, False, False]

    def build_file(self, names, dirname, save_filename):
        """
        建立文件
        :param names: 随机身材的数据(list)
        :param dirname:  文件夹路径
        :param save_filename:  另存为的文件名
        :return: None
        """
        try:
            radio_text = ""
            if self.radioButton.isChecked():
                flag = RdexcelFuc.build_excel(names, save_filename, dirname)  # 导出excel
                radio_text = self.radioButton.text()
            elif self.radioButton_2.isChecked():
                flag = RdexcelFuc.build_text(names, save_filename, dirname)  # 导出Text
                radio_text = self.radioButton_2.text()
            elif self.radioButton_3.isChecked():
                flag = RdexcelFuc.build_both(names, save_filename, dirname)  # # 导出excel和Text
                radio_text = self.radioButton_3.text()

            if radio_text == "Both":
                self.textEdit.append("准备导出的文件类型为：Excel and Text")
            else:
                self.textEdit.append("准备导出的文件类型为：" + radio_text)

            if not isinstance(radio_text, list):
                self.textEdit.append("导出文件成功，导出文件路径为：" + flag)
            else:
                self.textEdit.append("导出文件成功，导出文件路径为：" + "\n")
                for i in flag:
                    self.textEdit.append(i + "\n")
        except BaseException:
            self.textEdit.append("build_file error\n" + traceback.format_exc() + "\n")

    def rdfuc(self):
        """
        程序主函数
        :return: None
        """
        try:
            self.textEdit.clear()  # 清除多行文本框内容
            dirname, filename, num, save_filename = self.valid_data()  # 判断数据的有效性
            if dirname:
                max_rows, data = RdexcelFuc.get_rows_data(filename)  # 获取文件最大行数和DataFrame对象
                if not RdexcelFuc.is_overflow(max_rows, num):
                    self.information("输入的数太大，超过了文件的最大行！")
                else:
                    names = RdexcelFuc.random_dateframe(data, num)
                    if isinstance(names, list):
                        for i in range(num):
                            self.textEdit.append("随机生成的第" + str(i + 1) + "个字符串为：" + names[i] + "\n")
                        self.build_file(names, dirname, save_filename)
                    else:
                        self.information("请按照规则定义excel数据表头！")
        except BaseException:
            self.textEdit.append("rdfuc error\n" + traceback.format_exc() + "\n")


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

# RdexcelFuc.py
import pandas as pd
import openpyxl
import traceback


def random_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 i in range(num):
            lst = list(new_DataFrame.iloc[i])  # 抽取第一行数据
            names.append("{}({} {})".format(lst[name], lst[grade], lst[gender]))
        return names
    except ValueError:
        return traceback.format_exc()


def build_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 i in range(len(names)):
            index = 'A' + str(i + 1)
            ws[index] = names[i]
        wb.save(path)  # 保存excel
        return path
    except BaseException:
        return traceback.format_exc()


def build_text(names, save_filename, dirname):
    """
    新建txt
    :param names: 随机的数据
    :param save_filename: 另存为的文件名
    :param dirname: 保存的文件路径
    :return: True/report errors
    """
    try:
        path = dirname + "/" + save_filename + ".txt"
        with open(path, "w") as f:
            for name in names:
                f.write(name + "\n")
        return path
    except BaseException:
        return traceback.format_exc()


def build_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 flag1 and flag2:
        return flag1 + "\n" + flag2
    else:
        return [flag1, flag2]


def is_overflow(max_rows, num):
    """
    判断输入数量是否超出最大行数
    :param max_rows: 最大行数
    :param num: 超出数量
    :return: True || False
    """
    if num > max_rows:
        return False
    else:
        return True


def get_rows_data(filename):
    """
    获取最大行数和DataFrame对象
    :param filename: 文件路径
    :return: 最大行数 and DataFrame对象
    """
    data = pd.read_excel(filename)
    max_rows = data.shape[0]
    return max_rows, data