如何在python中将文件保存为excel时显示进度条?
问题描述
如果你能帮助我，不胜感激.将文件保存到 Excel 时，我无法显示进度条.我想要实现的是显示进度条，同时从 pandas dataframe 也从 qwidgettable 保存 excel 文件，因为它需要时间才能保存.在下载或保存 excel 文件之前，我希望该进度条关闭.我试着在网上查看，但我看不到我的查询的具体答案.到目前为止，这是我创建的编译代码.
Appreciate if you could help me. I have a trouble showing up the progress bar while saving file to excel. What I want to achieve is to show a progress bar while saving excel file from a pandas dataframe also from qwidgettable as it takes time before it saves. Until the excel file is downloaded or saved i want that progress bar to close. I tried looking over the net but I cant see specific answers to my query. So far, this is the compiled codes I have created.
import sys
from PyQt5 import QtWidgets, QtCore
import pandas as pd
import time
import psutil
class ThreadClass(QtCore.QThread):
updateProgressBar = QtCore.pyqtSignal(int)
def __init__(self, parent=None):
super(ThreadClass, self).__init__(parent)
def run(self):
while True:
val = int(psutil.cpu_percent())
time.sleep(1)
self.updateProgressBar.emit(val)
class Window(QtWidgets.QMainWindow):
def __init__(self):
super(Window, self).__init__()
self.setGeometry(50,50,500,500)
self.setWindowTitle('PyQt Tuts')
self.table()
def updateProgressBar(self, val):
self.progressBar.setValue(val)
def table(self):
self.tableWidget = QtWidgets.QTableWidget()
self.tableWidget.setGeometry(QtCore.QRect(220, 100, 411, 392))
self.tableWidget.setColumnCount(2)
self.tableWidget.setRowCount(5)
self.tableWidget.show()
item = QtWidgets.QTableWidgetItem()
item.setText("Amount")
self.tableWidget.setHorizontalHeaderItem(1, item)
records = [
['Product 1', 1000],
['Product 2', 500],
['Product 3', 600],
['Product 4', 300],
['Product 5', 800],
]
self.df = pd.DataFrame(records, columns=['Name', 'Amount'])
for r in range(5):
for c in range(2):
table_item = str(self.df.iloc[r, c])
self.tableWidget.setItem(r, c, QtWidgets.QTableWidgetItem(table_item))
self.pb_extract = QtWidgets.QPushButton(self.tableWidget)
self.pb_extract.setGeometry(QtCore.QRect(10, 200, 75, 23))
self.pb_extract.clicked.connect(self.extract)
self.pb_extract.setText("EXTRACT")
self.pb_extract.show()
def extract(self):
self.lb_downloading = QtWidgets.QLabel(self.tableWidget)
self.lb_downloading.setGeometry(QtCore.QRect(10, 270, 81, 16))
self.lb_downloading.setText("Downloading..")
self.lb_downloading.show()
self.progressBar = QtWidgets.QProgressBar(self.tableWidget)
self.progressBar.setGeometry(QtCore.QRect(10, 290, 171, 10))
self.progressBar.show()
self.threadclass = ThreadClass()
self.threadclass.start()
self.threadclass.updateProgressBar.connect(self.updateProgressBar)
self.df.to_excel('Products.xlsx', index=False)
print('Download complete!')
def run():
app = QtWidgets.QApplication(sys.argv)
app.setStyle("fusion")
w = Window()
sys.exit(app.exec_())
run()
这些代码如下所示:
我想要实现的是当我单击提取按钮时，下载进度条将关闭，直到 excel 文件完全下载/保存.
what i want to achieve is when i click the extract button, the downloading progressbar will close until the excel file fully downloaded/saved.
(PS 我只是得到 val = int(psutil.cpu_percent()) 的随机值，因为我也不知道在应用程序运行时使用什么特定代码/函数只是为了显示告诉你我有一个进度条在移动.)
(P.S i just get random values for val = int(psutil.cpu_percent()) because i also don't know what specific code/function to use while the app is running just to show to you that i have a progress bar moving.)
提前谢谢你！
解决方案
这类问题在SO里被问过无数次了，而且很多时候在评论里解释过需求说明什么情况下才有可能，在哪些情况下是不可能的.所以为了避免重复同样的事情，我将根据OP的问题在这篇文章中解释这个主题.
一个小部件通常用于显示和/或从用户那里获取信息，QProgressBar 做第一件事，也就是说，它显示进度信息，它不计算一下.
A widget in general is used to show and/or obtain information from the user, and a QProgressBar does the first thing, that is, it shows the progress information, it does not calculate it.
如果可以将任务细分为n"个子任务，则可以计算进度，因为它相当于已经完成的子任务数相对于总子任务数.
Progress can be calculated if the task can be subdivided into "n" subtasks, since it would be equivalent to the number of subtasks already done with respect to the number of total subtasks.
比如任务是上传一个N KB的文件到服务器，那么每个子任务可以有1KB的信息，所以进度是:
For example if the task is to upload a N KB file to a server, then each subtask can be 1KB of information, so the progress would be:
progress = 100 * number_of_KB_submitted/number_of_KB_of_file
另一个例子是如果你必须复制 n 个文件，那么进度是:
Another example would be if you have to copy n files, then the progress would be:
progress = 100 * number_of_copied_files / number_of_total_files
从上面可以看出，只有任务可以细分为子任务，才能计算进度，所以如果任务不能细分，就不可能计算任何进度.
From the above it is obvious that progress can only be calculated if the task can be subdivided into subtasks, so if the task cannot be subdivided then it is impossible to calculate any progress.
在将 pandas 保存在 excel 中的情况下，很明显它不能细分为n"个任务，因此无法计算其进度.
In the case of saving the pandas in an excel it is obvious that it cannot be subdivided into "n" tasks so it will be impossible to calculate its progress.
在使用 to_excel 将 pandas 保存在 excel 中的情况下，很明显它不能细分为n"个任务，因此无法计算其进度.
In the case of saving the pandas in an excel using to_excel it is obvious that it cannot be subdivided into "n" tasks so it will be impossible to calculate its progress.
在这些情况下的解决方法是显示繁忙的 QProgressBar:
A workaround in those cases is to show a busy QProgressBar:
progressbar.setRange(0, 0)
在你的情况下:
import sys
from PyQt5 import QtWidgets, QtCore
import pandas as pd
import time
import threading
class ExcelWorker(QtCore.QObject):
started = QtCore.pyqtSignal()
finished = QtCore.pyqtSignal()
def execute(self, df, filename):
threading.Thread(target=self._execute, args=(df, filename), daemon=True).start()
def _execute(self, df, filename):
self.started.emit()
df.to_excel(filename, index=False)
self.finished.emit()
class DownloaderProgressBar(QtWidgets.QWidget):
def __init__(self, parent=None):
super(DownloaderProgressBar, self).__init__(parent)
self._progressbar = QtWidgets.QProgressBar()
lay = QtWidgets.QVBoxLayout(self)
lay.addWidget(QtWidgets.QLabel(self.tr("Downloading..")))
lay.addWidget(self.progressbar)
@property
def progressbar(self):
return self._progressbar
class Window(QtWidgets.QMainWindow):
def __init__(self):
super(Window, self).__init__()
self.setGeometry(50, 50, 500, 500)
self.setWindowTitle("PyQt Tuts")
self.create_table()
self.create_progressbar()
self.create_worker()
def create_progressbar(self):
self.downloader_progressbar = DownloaderProgressBar(self.tableWidget)
self.downloader_progressbar.setGeometry(10, 270, 170, 80)
self.downloader_progressbar.hide()
def create_worker(self):
self.worker = ExcelWorker()
self.worker.started.connect(self.on_started)
self.worker.finished.connect(self.on_finished)
def create_table(self):
self.tableWidget = QtWidgets.QTableWidget()
self.tableWidget.setGeometry(QtCore.QRect(220, 100, 411, 392))
self.tableWidget.setColumnCount(2)
self.tableWidget.setRowCount(5)
self.tableWidget.show()
item = QtWidgets.QTableWidgetItem()
item.setText("Amount")
self.tableWidget.setHorizontalHeaderItem(1, item)
records = [
["Product 1", 1000],
["Product 2", 500],
["Product 3", 600],
["Product 4", 300],
["Product 5", 800],
]
self.df = pd.DataFrame(records, columns=["Name", "Amount"])
for r in range(5):
for c in range(2):
table_item = str(self.df.iloc[r, c])
self.tableWidget.setItem(r, c, QtWidgets.QTableWidgetItem(table_item))
self.pb_extract = QtWidgets.QPushButton(self.tableWidget)
self.pb_extract.setGeometry(QtCore.QRect(10, 200, 75, 23))
self.pb_extract.clicked.connect(self.extract)
self.pb_extract.setText("EXTRACT")
self.pb_extract.show()
def extract(self):
self.worker.execute(self.df.copy(), "Products.xlsx")
self.downloader_progressbar.show()
@QtCore.pyqtSlot()
def on_started(self):
self.downloader_progressbar.progressbar.setRange(0, 0)
@QtCore.pyqtSlot()
def on_finished(self):
self.downloader_progressbar.progressbar.setRange(0, 1)
def run():
app = QtWidgets.QApplication(sys.argv)
app.setStyle("fusion")
w = Window()
sys.exit(app.exec_())
run()
相关文章