When a task takes more than a second or two it can be helpful to display something to the user to show them that progress is still being made. If you don’t it can just appear that the Excel is hung because it’s unresponsive until your task is complete.
Python has a variety of UI toolkits that can be used for a progress indicator. They all work mostly in the same way. You create the dialog with the progress indicator, periodically set the progress, and then close the window when your task is complete.
This blog will show you how to display a progress indicator using the pywin32 package. The pywin32 package is sometimes overlooked for building user interface components because it’s not as modern as some other toolkits. If you don’t need a fancy user interface though, it can be good choice as you probably already have it installed! The packages in pywin32 wrap the underlying WIN32 APIs, so it can seem a bit ‘low level’, but it includes a lot of demos to help get you started. Take a look in site-packages/pythonwin/pywin/Demos.
First off, install the pywin32 package if you don’t have it already.
pip install pywin32
This actually installs a number of packages: pythonwin, pywin, win32ui, win32api, win32com and others.
In the demos folder mentioned earlier (site-packages/pythonwin/pywin/Demos) you’ll find a file called progressbar.py. Exactly what we need! With very little modification we can get this working in Excel and give our users that progress feedback during our long running task.
Here’s the code from the pythonwin example:
from pywin.mfc import dialog
import win32ui
import win32con
def MakeDlgTemplate():
style = (win32con.DS_MODALFRAME |
win32con.WS_POPUP |
win32con.WS_VISIBLE |
win32con.WS_CAPTION |
win32con.WS_SYSMENU |
win32con.DS_SETFONT)
cs = (win32con.WS_CHILD |
win32con.WS_VISIBLE)
w = 215
h = 36
dlg = [["Progress bar control example",
(0, 0, w, h),
style,
None,
(8, "MS Sans Serif")],
]
s = win32con.WS_TABSTOP | cs
dlg.append([128,
"Tick",
win32con.IDOK,
(10, h - 18, 50, 14), s | win32con.BS_DEFPUSHBUTTON])
dlg.append([128,
"Cancel",
win32con.IDCANCEL,
(w - 60, h - 18, 50, 14), s | win32con.BS_PUSHBUTTON])
return dlg
class TestDialog(dialog.Dialog):
def OnInitDialog(self):
rc = dialog.Dialog.OnInitDialog(self)
self.pbar = win32ui.CreateProgressCtrl()
self.pbar.CreateWindow (win32con.WS_CHILD |
win32con.WS_VISIBLE,
(10, 10, 310, 24),
self, 1001)
# self.pbar.SetStep (5)
self.progress = 0
self.pincr = 5
return rc
def OnOK(self):
# NB: StepIt wraps at the end if you increment past the upper limit!
# self.pbar.StepIt()
self.progress = self.progress + self.pincr
if self.progress > 100:
self.progress = 100
if self.progress <= 100:
self.pbar.SetPos(self.progress)
def demo(modal = 0):
d = TestDialog (MakeDlgTemplate())
if modal:
d.DoModal()
else:
d.CreateWindow ()
if __name__=='__main__':
demo(1)
This is pretty much what we need. It creates a dialog box containing a progress bar with two buttons, one to increment the progress and another to cancel the dialog box.
We need to make a slight change so that we can increment the progress bar ourselves without the button, and close it after we’re done with it. We’ll remove the OnOK method and add a “progress” property to set the current progress, and add a “close” method to close the dialog. Those changes look like this:
class TestDialog(dialog.Dialog):
def OnInitDialog(self):
rc = dialog.Dialog.OnInitDialog(self)
self.pbar = win32ui.CreateProgressCtrl()
self.pbar.CreateWindow (win32con.WS_CHILD |
win32con.WS_VISIBLE,
(10, 10, 310, 24),
self, 1001)
return rc
def set_progress(self, progress):
self.pbar.SetPos(progress)
def close(self):
self.PostMessage(win32con.WM_CLOSE, 0, 0)
Now all we need is a way to create this dialog and update it during a long running task.
For that we’ll use a PyXLL menu item to run a task in a background thread. As the task runs, we’ll set “progress” on the dialog so the user can see something’s going on. When it’s finished we’ll close the dialog with the new “close” method we added above.
Our long running task looks like this, although in a real case the time.sleep calls would be replaced with actually doing something!
def long_running_task(progress_dialog):
try:
for i in range(100):
time.sleep(0.05)
progress_dialog.set_progress(i)
finally:
progress_dialog.close()
To call our “long_running_task” from Excel as a menu function we use the @xl_menu decorator. This doesn’t have to be a menu function, it could be a macro or ribbon action and work in exactly the same way.
Our menu function will create the progress dialog, start a background thread to run “long_running_task” and then wait for it to complete.
@xl_menu("Progress Bar Demo")
def progress_bar_demo():
d = TestDialog (MakeDlgTemplate())
# start a background thread to do some work
thread = threading.Thread(target=long_running_task, args=(d,))
thread.start()
# show the dialog
d.DoModal()
# wait for the thread to finish
thread.join()
And there we have it! A super-simple progress indicator in just a few lines of code. To make it look as you want (you will probably want to remove the buttons since they no longer do anything) you can update the MakeDlgTemplate function taken from the pythonwin example code.
Here’s the full code, but be sure to checkout the original pythonwin example too.
from pyxll import xl_menu
from pywin.mfc import dialog
import time
import win32ui
import win32con
import threading
def MakeDlgTemplate():
style = (win32con.DS_MODALFRAME |
win32con.WS_POPUP |
win32con.WS_VISIBLE |
win32con.WS_CAPTION |
win32con.WS_SYSMENU |
win32con.DS_SETFONT)
cs = (win32con.WS_CHILD |
win32con.WS_VISIBLE)
w = 215
h = 36
dlg = [["Progress bar control example",
(0, 0, w, h),
style,
None,
(8, "MS Sans Serif")],
]
s = win32con.WS_TABSTOP | cs
return dlg
class TestDialog(dialog.Dialog):
def OnInitDialog(self):
rc = dialog.Dialog.OnInitDialog(self)
self.pbar = win32ui.CreateProgressCtrl()
self.pbar.CreateWindow (win32con.WS_CHILD |
win32con.WS_VISIBLE,
(10, 10, 310, 24),
self, 1001)
self.pbar.SetRange(1, 100)
return rc
def set_progress(self, progress):
self.pbar.SetPos(progress)
def close(self):
self.PostMessage(win32con.WM_CLOSE, 0, 0)
def long_running_task(progress_dialog):
try:
for i in range(100):
time.sleep(0.05)
progress_dialog.set_progress(i)
finally:
progress_dialog.close()
@xl_menu("Progress Bar Demo")
def progress_bar_demo():
d = TestDialog (MakeDlgTemplate())
# start a background thread to do some work
thread = threading.Thread(target=long_running_task, args=(d,))
thread.start()
# show the dialog
d.DoModal()
# wait for the thread to finish
thread.join()