Author Topic: IPython console in Excel  (Read 6221 times)

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 615
  • Karma: +14/-1
    • View Profile
IPython console in Excel
« on: February 23, 2013, 12:23:42 PM »
I've attached some code that shows how to launch an IPython Qt console window from Excel, attached to an IPython kernal running in Excel.

This lets you interact directly with Excel using Python, try out code, and even write new Excel functions with the pyxll.xl_func decorator interactively.

This code requires IPython 0.13, PySide and psutil.

If you add new functions, menus or macros you need to tell PyXLL to re-create the Excel wrappers and register the functions with Excel. You can do with by calling the 'pyxll_rebind' macro, which can be done with the following Python code:

xl = xl_app() # from automation example
xl.Run("pyxll_rebind")

Let me know if you try this as I'm considering adding it as an example in a future PyXLL release.

Tony
« Last Edit: March 04, 2013, 09:15:33 AM by Tony »

dyus1983

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #1 on: April 29, 2013, 12:16:47 PM »
Hi Tony,

It didn't work for the Russian 32-bit version of Excel 2010, Windows XP.
When I click PyXLL -> IPython, a new Excel window starts with a series of warnings saying:
Couldn't find '-c.xlsx'
Couldn't find 'from multiprocessing.forking import main; main()'
Couldn't find '--multiprocessing-fork.xlsx'
Couldn't find '4064.xlsx'

I use Anaconda 1.4 for Python, and besides IPython console in Excel PyXLL works fine.

Regards,
Dias

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 615
  • Karma: +14/-1
    • View Profile
Re: IPython console in Excel
« Reply #2 on: April 29, 2013, 12:52:22 PM »
Hi Dias,

ah, that's a problem with launching the child process that's used for the ipython window (the ipython kernel itself runs in the same process as excel).

Try setting 'sys.executable' to your python executable before the child process is started (you could do that in the body of the module so it's set on import), eg:

import sys
sys.executable = "C:/Python27/python.exe"

I hope this helps.
Tony

dyus1983

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #3 on: April 29, 2013, 04:48:20 PM »
Now it works! Thanks!

Dias

elakelaiset

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #4 on: July 26, 2013, 07:14:33 AM »
Hello,
First of all: Nice Job & thanks for the effort.
I tried your code and in the beginning I had the same problem like dyus1986 which disappeared after the sys.executable definition.
My problem is that my IPython interpreter seems not to share the same session with excel. I can’t access global variables defined in my functions executed in excel. So it’s like starting a normal non-connected session in iPython.
Sebastian

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 615
  • Karma: +14/-1
    • View Profile
Re: IPython console in Excel
« Reply #5 on: July 26, 2013, 12:44:59 PM »
Hi Sebastian,

by globals I'm guess you mean objects set at in your module - if so, you just need to import them from the module that your python functions are in, eg 'from my_module import *'

Best regards,
Tony

elakelaiset

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #6 on: July 29, 2013, 09:13:37 AM »
Hello Tony,
Thank you for your swift reply. By globals I mean a global variable defined in a function definition which is also accessible after execution of the function. I thought I could execute a python function in excel which creates a global variable from my excel input data. And in IPython I would like to access this global variable – so that in my understanding IPython and excel would share the same session.
Regards,
Sebastian

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 615
  • Karma: +14/-1
    • View Profile
Re: IPython console in Excel
« Reply #7 on: July 29, 2013, 10:14:09 AM »
Hi Sebastian,

I think maybe you misunderstand how globals work in python. If you have a function like this:

my_global = 5
def my_function(x):
    global my_global
    my_global += x
    return my_global

Then my_global is global to the module it's defined in, not to the entire python session. For example, if you were to create a new module then it wouldn't have 'my_global' automatically visible - you have to import it from the module it's defined in.

When you work in an IPython session that has its own set of globals local to that session. If you declare a function within that session then it will see those globals, but if you import a function from another module it will not.

You simply need to import whatever globals you want from the module where your function is defined. For example, if the above code was in a module called 'my_test_code', in IPython to see 'my_global' you would need to type 'from my_test_code import my_global'.

If you want to change what is automatically in the IPython session's variables when the kernel starts you can set 'user_ns' to something other than an empty dictionary in the _start_kernel function in the code from this thread (around line 43). For example, you could change this to be 'app.kernel.user_ns = globals()' which would set the dictionary of globals you see in IPython to be the globals dict as seen from the ipython.py module - but this would still not be the same as the globals seen by your code in the other module as explained above.

See http://docs.python.org/2/library/functions.html#globals for more information about python globals (although not very much more!).

Best regards,
Tony

gmt

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #8 on: March 24, 2014, 10:09:16 PM »
Hi Tony, this is potentially very useful, especially for a Python beginner like myself. I am on ipython 1.1.0 and I was wondering if it would be possible to make it work. I tried to look into http://stackoverflow.com/a/20610786 but this is way above me right now.
Thanks.

ks31890

  • Newbie
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #9 on: March 26, 2014, 03:46:08 AM »
Hi Tony, This is a really important piece of work. However, it seems it is not working with Canopy 1.3. If you could fix it, I am sure many users will appreciate it. Thanks a lot

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 615
  • Karma: +14/-1
    • View Profile
Re: IPython console in Excel
« Reply #10 on: March 26, 2014, 11:01:44 AM »
Thanks, we'll work on getting this working for canopy 1.3 and I'll post back here once it's done.

ks31890

  • Newbie
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #11 on: March 27, 2014, 02:09:01 PM »
Tony, please also consider adding it in the future PyXLL releases. This should be part of the product. Thanks a lot

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 615
  • Karma: +14/-1
    • View Profile
Re: IPython console in Excel
« Reply #12 on: March 27, 2014, 05:11:08 PM »
Hi,

I've added new versions of this code to the pyxll-examples github repo:

https://github.com/pyxll/pyxll-examples/tree/master/ipython

There are two versions, one for IPython 0.13 and another for the newer IPython 1.2 that uses the new IPython API embed_kernel function.

Best regards,
Tony

gmt

  • Newbie
  • *
  • Posts: 8
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #13 on: March 28, 2014, 10:10:26 AM »
Hi Tony, I am trying it and yes, just doing 'from my_module import *' from the IPython console gives me all the variables in that module. This is what I needed and I will use it as a very convenient debugger.
Thanks a lot.

ks31890

  • Newbie
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: IPython console in Excel
« Reply #14 on: March 28, 2014, 08:30:12 PM »
Tony, it works great. Thank you so much. Now I can ask my boss to buy me a license :)