{"id":943,"date":"2019-12-06T12:21:47","date_gmt":"2019-12-06T12:21:47","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=943"},"modified":"2023-01-05T10:04:52","modified_gmt":"2023-01-05T10:04:52","slug":"extending-the-excel-user-interface","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/extending-the-excel-user-interface\/","title":{"rendered":"Extending the Excel User Interface"},"content":{"rendered":"\n

You can find the code for the examples in the qtgraphics<\/em> folder of our examples repository.<\/a><\/p>\n\n\n\n

Interacting with your users<\/h3>\n\n\n\n

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<\/a> (based on the cross-platform Qt5 library). There are fast introductions<\/a> to PyQt5 and quite a lot of other literature<\/a> on the Internet, continuously being updated. Because of that, these posts look mostly at the PyQt5 features that you need to solve specific problems.<\/p>\n\n\n\n

The simplest interactions are by means of modal dialogues<\/em>, 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. <\/p>\n\n\n\n

Accessing the QApplication <\/h3>\n\n\n\n

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).<\/em><\/p>\n\n\n

\ndef get_qt_app():\n    """Returns a QApplication instance.\n    Must be called before showing any dialogs.\n    """\n    app = QApplication.instance()\n    if app is None:\n        app = QApplication([sys.executable])\n    return app\n<\/pre><\/div>\n\n\n
<\/div>\n\n\n\n
Note: Anaconda Installations of PyQt5<\/strong>

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

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<\/a>.<\/p>\n\n\n\n
Meet QMessageBox<\/h3>\n\n\n\n
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<\/em> in the repository.<\/p>\n\n\n
\nif __name__ == '__main__':\n    app = get_qt_app()\n    msgBox = QMessageBox()\n    msgBox.setText('This is a multi-line\\nmessage for display\\nto the user.')\n    msgBox.setInformativeText("And here is a detailed explanation,"\n                              " or at least the closest you will get.")\n    msgBox.setWindowTitle('TITLE')\n    msgBox.exec_()\n<\/pre><\/div>\n\n\n
<\/div>\n\n\n\n
\"A<\/figure>
\n
Here’s what the program
will display. Notice that the informative text is automatically wrapped to length<\/p>\n<\/div><\/div>\n\n\n\n
<\/div>\n\n\n\n
Making the optimizer more usable<\/h3>\n\n\n\n
In a previous post<\/a> 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.<\/p>\n\n\n\n
\"\"<\/figure><\/div>\n\n\n\n
Giving the user a choice<\/h3>\n\n\n\n
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?<\/p>\n\n\n
\n@xl_menu("sicpy.optimize")\ndef optimize4():\n    xl = xl_app()\n    # Get the initial values of the input cells\n    in_values = list(xl.Range('Inputs').Value)\n    X = np.array([x[0] for x in in_values])\n\n    orig_calc_mode = xl.Calculation\n    try:\n        # switch Excel to manual calculation\n        # and disable screen updating\n        xl.Calculation = constants.xlManual\n        xl.ScreenUpdating = False\n\n        # run the minimization routine\n        xl_obj_func = partial(obj_func, xl)\n        result = minimize(xl_obj_func, X, method='nelder-mead')\n\n        mbox = QMessageBox()\n        mbox.setIcon(QMessageBox.Information)\n        mbox.setText("Optimization results shown below."\n                     "\\nMake changes permanent?")\n        mbox.setWindowTitle("Optimization Complete")\n        mbox.setInformativeText("\\n".join([\n            "Successful:       %s" % result.success,\n            result.message,\n            "After %d iterations" % result.nit\n            ]))\n        mbox.setStandardButtons(QMessageBox.Ok | QMessageBox.Cancel)\n        yes_no = mbox.exec_()\n\n        if yes_no != QMessageBox.Ok:\n            xl.Range('Inputs').Value = in_values\n        else:\n            xl.Range('Inputs').Value = [(float(x), ) for x in result.x]\n\n    finally:\n        # restore the original calculation\n        # and screen updating mode\n        xl.ScreenUpdating = True\n        xl.Calculation = orig_calc_mode\n<\/pre><\/div>\n\n\n
Notice that line 5 saves the values of the input cells. When optimization is complete, starting at line 19 the function summarises the results<\/a> 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<\/em> method returns a value indicating which button was pressed.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The complete code can be found in the optimizer4.py<\/em> file in the examples repository.<\/p>\n\n\n\n
Next Steps<\/h2>\n\n\n\n
Continue with our next PyQt5 article, Asking the User for Input with PyQt5<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
Part 1 of our guide to creating user interfaces in Excel with PyQt5.<\/p>\n","protected":false},"author":803,"featured_media":1028,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[12,2,504],"tags":[],"class_list":["post-943","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","category-pyxll","category-user-interface"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2019\/12\/excel-qt.png","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-fd","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/943","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/users\/803"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/comments?post=943"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/943\/revisions"}],"predecessor-version":[{"id":1672,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/943\/revisions\/1672"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/1028"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=943"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=943"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=943"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}