Extending the Excel User Interface

You can find the code for the examples in the qtgraphics folder of our examples repository.

Interacting with your users

Python has great support for building user interface, or GUI, tools. With PyXLL, these can be used from within Excel. In this and the next few posts we’ll be building Excel user interfaces using the popular PyQt5 package (based on the cross-platform Qt5 library). There are fast introductions to PyQt5 and quite a lot of other literature on the Internet, continuously being updated. Because of that, these posts look mostly at the PyQt5 features that you need to solve specific problems.

The simplest interactions are by means of modal dialogues, which suspend all other operations in the program until they are dismissed. You can do quite a lot just with such dialogues. To do anything at all in Qt you must first create a QApplication object.

Accessing the QApplication

You want to be sure that you don’t create more than one QApplication, so first try and access the current instance. If none exists, then create one. This makes a relatively simple function. (In this and other snippets we omit the necessary imports, which are included in the complete programs in the examples repository).

def get_qt_app():
    """Returns a QApplication instance.
    Must be called before showing any dialogs.
    """
    app = QApplication.instance()
    if app is None:
        app = QApplication([sys.executable])
    return app

Note: Anaconda Installations of PyQt5

If you are using Anaconda and have installed PyQt5 using conda you may come across this error:
PyQt: Could not find or load the Qt platform plugin “windows”

To fix this you need to tell it where to look by setting the QT_PLUGIN_PATH environment variable. This can be done in your pyxll.cfg file, for example:

[ENVIRONMENT]
QT_PLUGIN_PATH = C:\Users\xxx\Anaconda\envs\pyxll\Library\plugins

For more information please see this FAQ article.

Meet QMessageBox

Qt5 has a family of built-in dialogues for various purposes. The simplest, the QMessageBox, displays a (possibly multi-line) message to the user and requires acknowledgement. Here’s a program that shows a simple use of QMessageBox. The complete code is file qt_ex1.py in the repository.

if __name__ == '__main__':
    app = get_qt_app()
    msgBox = QMessageBox()
    msgBox.setText('This is a multi-line\nmessage for display\nto the user.')
    msgBox.setInformativeText("And here is a detailed explanation,"
                              " or at least the closest you will get.")
    msgBox.setWindowTitle('TITLE')
    msgBox.exec_()

Here’s what the program
will display. Notice that the informative text is automatically wrapped to length

Making the optimizer more usable

In a previous post I explained how to use PyXLL to tackle a broad range of optimization problems with Excel. The short animation showed a PyXLL-driven goal seek. Excel’s “undo” feature couldn’t reverse the changes to the input values after an optimization, though. You need that ability to be able to ask “what if” questions of the data.

Giving the user a choice

Here’s a modified version of the final optimization function from the last blog post. It displays the results of the optimization and asks the user whether to keep the optimization result or to revert the inputs to their original state?

@xl_menu("sicpy.optimize")
def optimize4():
    xl = xl_app()
    # Get the initial values of the input cells
    in_values = list(xl.Range('Inputs').Value)
    X = np.array([x[0] for x in in_values])

    orig_calc_mode = xl.Calculation
    try:
        # switch Excel to manual calculation
        # and disable screen updating
        xl.Calculation = constants.xlManual
        xl.ScreenUpdating = False

        # run the minimization routine
        xl_obj_func = partial(obj_func, xl)
        result = minimize(xl_obj_func, X, method='nelder-mead')

        mbox = QMessageBox()
        mbox.setIcon(QMessageBox.Information)
        mbox.setText("Optimization results shown below."
                     "\nMake changes permanent?")
        mbox.setWindowTitle("Optimization Complete")
        mbox.setInformativeText("\n".join([
            "Successful:       %s" % result.success,
            result.message,
            "After %d iterations" % result.nit
            ]))
        mbox.setStandardButtons(QMessageBox.Ok | QMessageBox.Cancel)
        yes_no = mbox.exec_()

        if yes_no != QMessageBox.Ok:
            xl.Range('Inputs').Value = in_values
        else:
            xl.Range('Inputs').Value = [(float(x), ) for x in result.x]

    finally:
        # restore the original calculation
        # and screen updating mode
        xl.ScreenUpdating = True
        xl.Calculation = orig_calc_mode

Notice that line 5 saves the values of the input cells. When optimization is complete, starting at line 19 the function summarises the results in a QMessageBox and asks the user whether or not to make the changes permanent. Note how we configured QMessageBox with two buttons. Its _exec method returns a value indicating which button was pressed.

Rather than rely on keyboard shortcuts I added this optimizer as a menu command. I also fixed a small bug in the original. This introduced slight errors by leaving the objective function’s input set to the last evaluation’s.

The complete code can be found in the optimizer4.py file in the examples repository.

Next Steps

Continue with our next PyQt5 article, Asking the User for Input with PyQt5.