Author Topic: Toggle between GUI and excel with Pyxll  (Read 966 times)

kasa

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Toggle between GUI and excel with Pyxll
« on: October 04, 2014, 06:05:45 PM »
Hi Tony,

I have an menu function in python, which is exposed to excel via Pyxll. When the menu is clicked, it displays a GUI. The GUI works fine. The problem is that when the GUI is running, the excel application goes into a wait state ( cannot click on any cell). Once the GUI is closed, the control goes back to excel.

This is the current behavior. Desired behavior is that the GUI and excel application co-exist. i.e. it should be possible to run GUI and at same time access excel application. I understand that this can be done via threading. i tried pyxll.async_call(function) and then called the UI in the function 'func', but it doesnt work.

Request you to please help me with this.

Thank you .



Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 584
  • Karma: +14/-1
    • View Profile
Re: Toggle between GUI and excel with Pyxll
« Reply #1 on: October 04, 2014, 07:03:41 PM »
Hi,

you need your GUI event loop to run as part of the main windows event loop. The pyxll.async_call makes your function get called after the calling function has finished once the windows event loop is resumed; this is how it is possible to write back to Excel from a UDF as normally writes back to the sheet would cause an error during calculation.

You could start your event loop in another thread using the threading module, but a better solution is to somehow inject your GUI's event loop into Excel's event loop.

Here is some code that does exactly that for PySide (Qt). Hopefully that is what you have written your GUI in, but if not then you may be able to take inspiration from it and write something similar for your GUI toolkit.

Code: [Select]
from PySide import QtGui, QtCore

def get_qt_app():
    """
    returns the global QtGui.QApplication instance and starts
    the event loop if necessary.
    """
    app = QtCore.QCoreApplication.instance()
    if app is None:
        # create a new application
        app = QtGui.QApplication([])

        # use timer to process events periodically
        processing_events = {}
        def qt_timer_callback(timer_id, time):
            if timer_id in processing_events:
                return
            processing_events[timer_id] = True
            try:
                app = QtCore.QCoreApplication.instance()
                if app is not None:
                    app.processEvents(QtCore.QEventLoop.AllEvents, 300)
            finally:
                del processing_events[timer_id]

        timer.set_timer(100, qt_timer_callback)

    return app

Also if you are creating a modal dialog then that will run its own event loop and so will always block. This will only work for modeless dialogs.

Another option would be to use multiprocessing and run your GUI as a child process and communicate back to Excel with queues or something like that. That way there would be no conflict with the event loops and so everything would work fine.

Best regards,
Tony

kasa

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Toggle between GUI and excel with Pyxll
« Reply #2 on: October 05, 2014, 06:22:58 PM »
Thank you very much !!!!  :)

Now, the Excel isnt blocked ( i can click on any cell) , however, i cannot write into any cell. Can you please suggest where i am going wrong.

 MY Code:
@xl_menu("run_ui",menu="run_ui", menu_order=3)
def run_ui():

 form = UI.AppForm()  # UI is another script and APPForm is the UI Class which has all the UI
 form.show()
 app = get_qt_app()   # the function which you gave
 status = app.exec_()
 return

Warm Regards,
Kasa
« Last Edit: October 05, 2014, 11:14:22 PM by kasa »

kasa

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Toggle between GUI and excel with Pyxll
« Reply #3 on: October 05, 2014, 11:59:51 PM »
More Info: UI.APPForm is a class inherited from QMainWindow

I am a newbie to PYQT and not sure if its a modal or non-modal and whether it can be called from the Pyxll function as event loop inside excel event loop

Any help or suggestion is appreciated.

Thank you.
Kasa

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 584
  • Karma: +14/-1
    • View Profile
Re: Toggle between GUI and excel with Pyxll
« Reply #4 on: October 06, 2014, 10:26:14 AM »
Hi,

QMainWindow is not modal, but it's reasonable for a class derived from that to capture all input as it expects to be the root window in the application - so maybe that is why when it's open Excel doesn't see any key presses (if that's what is happening).

You could try with a small test class, rather than jumping in with a whole pre-written app that may be doing lots of things that could upset Excel - or if you are trying to work with a large existing app framework or something, maybe doing it in another process would be a better solution. You can still write back to excel from another process using win32com (eg xl = win32com.client.Dispatch("Excel.Application"), instead of using pyxll.get_active_object which only works from inside the excel process - something for me to look at for a future release :) ).

Best regards,
Tony


 

kasa

  • Newbie
  • *
  • Posts: 11
  • Karma: +0/-0
    • View Profile
Re: Toggle between GUI and excel with Pyxll
« Reply #5 on: October 08, 2014, 12:52:24 AM »
Hi Tony,

I have tried with a very simple code.

#### GUI testUI ###########
from PyQt4.Qt import * 
class myLabel(QLabel):
    def __init__(self):
        QLabel.__init__(self)
        self.label = QLabel("Hello world")

##########PYXLL menu function##########
        form = testUI.myLabel()
        form.show()
        app = get_qt_app()
        status = app.exec_()

Even in this case, i was not able edit the excel sheet with GUI running.

I will try creating a new process for GUI and see if that works.

Please let me know if you can think of something else which might work.

Thanks,
Kasa

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 584
  • Karma: +14/-1
    • View Profile
Re: Toggle between GUI and excel with Pyxll
« Reply #6 on: October 08, 2014, 08:48:13 AM »
Hi Kasa,

ah ok, now I see what you're doing. Because Qt's loop is already running in the main windows loop, there is no need to call "exec_" on the app. It's that call that will be blocking the Excel process. If you remove that line then the function will return control to Excel, but the Qt main loop will continue to run.

You may want to keep track of your top level window in a global dictionary or module variable so you can find it again later, and also in case it gets closed once there are no references left to it (not sure if that's strictly necessary, but I would try it anyway) - e.g.:

Code: [Select]
form = None
...
        global form  # use a global so it keeps a reference after the function returns
        app = get_qt_app()  # create the app before creating any windows
        form = testUI.myForm()
        form.show()
        # status = app.exec_()  # no need to run exec_, windows loop is already running

Best regards,
Tony