Re: Which way is best to execute a Python script in Excel?

[Thomas Jollans]

On 07/05/2012 09:26 AM, Karim wrote:
> Look at PyUNO from OpenOffice very large API:
> http://www.openoffice.org/api/docs
>
> I use to create all my documention (Excell, Writer, etc...) on this API.

Note that this API is for OpenOffice, not Microsoft Excel. However, as
you probably know, you can open most Excel files in OpenOffice's Calc.

I urge you to consider LibreOffice, a fork of OpenOffice that is now
broadly considered its successor. Its API can also be used in Python
http://api.libreoffice.org/

If you want to program Microsoft Office, your only option is, as far as
I know, VBA, but it may be possible to call Python from VBA using the
Windows Script Host. I assume that the Windows Script Control uses this,
but I doubt that's much easier to use than the standard WScript API.
(then again, I wouldn't know)

All in all, if you're going to script Microsoft office, by far the
easiest way is to simply stick to VBA and forget about Python in this
particular environment. If it's an option, try LibreOffice.

Thomas

[Emile van Sebille]

On 7/5/2012 12:22 AM Maurizio Spadaccino said...
> Hi all
>
> I'm new to Python but soon after a few days of studying its features I
> find it my favourite mean of programming scripts to allow for data
> storing and mining. My idea would be to inplement python scripts from
> inside an excel sheet that would store and fetch data from a Mysql
> database.

Look again at the com interface -- I've created excel commands
implemented entirely in python this way and it will do everything you're
looking for. Just start with some of the examples and extend from there.

See http://oreilly.com/catalog/pythonwin32/chapter/ch12.html

I did this ten years ago so things have likely changed, but the bits of
the python script that set up com then looked like this:


class fenxUtilities:
_public_methods_ = [ 'FirstPartInsp' ]
_reg_progid_ = "fenxDCom.Util"
_reg_clsid_ = "{3EAD7AB4-2978-4360-8F7D-33FB36E9E146}"
def FirstPartInsp(self,nomDiam, numFlutes, nomOAL, nomLOC):
return EMSpecs(nomDiam, numFlutes, nomOAL, nomLOC).retvals


if __name__=='__main__':
print "Registering COM server..."
import win32com.server.register
win32com.server.register.UseCommandLine(fenxUtilities)


HTH,

Emile




> So i need the script to be launched, say, by pressing a button
> in excel and, for instance, retrieve immediately data from the mysql
> table. For what I found in the net, it seems there are three ways to
> control python from excel:
> 1) run my python script via shell using the Run command in VBA, which
> seems to me the easiest (but maybe slower?) way;
> 2) creating a COM server, for which I need more training since it doesnt
> appear that easy;
> 3) via Microsoft Script Control, for which I saw some example around
> where It looks like I can 'simulate' a python shell via the
> 'executeStatement' command.
>
> What would you suggest would be the more performing way to achieve my
> goals as described above?
>
> Thanks for you help
> Maurizio
>
>
> --
> Maurizio
>
>
> www.mauriziospadaccino.com <http://www.mauriziospadaccino.com>
> ---------------
> If you can avoid printing this e-mail, you are only doing good for our
> planet.
>
>

[Mark Shroyer]

On Thu, Jul 05, 2012 at 03:22:01AM -0400, Maurizio Spadaccino wrote:
> Hi all
>
> I'm new to Python but soon after a few days of studying its features I
> find it my favourite mean of programming scripts to allow for data
> storing and mining. My idea would be to inplement python scripts from
> inside an excel sheet that would store and fetch data from a Mysql

> database. So i need the script to be launched, say, by pressing a

> button in excel and, for instance, retrieve immediately data from the
> mysql table. For what I found in the net, it seems there are three
> ways to control python from excel: 1) run my python script via shell
> using the Run command in VBA, which seems to me the easiest (but maybe
> slower?) way; 2) creating a COM server, for which I need more training
> since it doesnt appear that easy; 3) via Microsoft Script Control, for
> which I saw some example around where It looks like I can 'simulate' a
> python shell via the 'executeStatement' command.
>
> What would you suggest would be the more performing way to achieve my
> goals as described above?

One thing you could check out is IronSpread: http://www.ironspread.com/

(Despite the name, it's based on CPython rather than IronPython.)

It presents a simplified interface for communicating with Excel compared
to what you get with win32com / plain OLE automation. I'm a little hazy
on whether the Python scripts created with this plugin are saved within
the Excel file or somewhere on the local system, though...

[Terry Reedy]

On 7/5/2012 5:12 AM, Thomas Jollans wrote:
> On 07/05/2012 09:26 AM, Karim wrote:
>> Look at PyUNO from OpenOffice very large API:
>> http://www.openoffice.org/api/docs
>>
>> I use to create all my documention (Excell, Writer, etc...) on this API.
>
> Note that this API is for OpenOffice, not Microsoft Excel. However, as
> you probably know, you can open most Excel files in OpenOffice's Calc.
>
> I urge you to consider LibreOffice, a fork of OpenOffice that is now
> broadly considered its successor. Its API can also be used in Python
> http://api.libreoffice.org/

Can you explain or point to a document that explains how to actually do
that? (use the LibreOffice api from Python?)

The only mention of Python on that page is under examples. (and there is
no mention of python in the installation guide, even as an option, nor
in the development tools doc, ).

On the example page, there is only one example and unlike all the other
sections, no 'Additional information' linking to a 'Python Language
binding'.

In toolpanel.py of the example, there is

import uno
import unohelper

from com.sun.star.ui import XUIElementFactory
from com.sun.star.ui import XUIElement
from com.sun.star.ui.UIElementType import TOOLPANEL as unoTOOLPANEL
from com.sun.star.ui import XToolPanel

but where are the python-importable modules and com package and their
docs and how does one get them? There are not in the example directory.

The IDL reference only covers the com.sun.star stuff.

---
Looking with Google, I see that some linux distros include
LibreOffice-PyUno in thier package managers. Not helpful for Windows.

The LibreOffice installation includes a python2.6.1 installation under
LO.../program/ with pyuno pre-installed. No doc that I could see.
However, when running it

>>> import com.sun.star.ui
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ImportError: No module named com.sun.star.ui

Oh, there is a trick to it
>>> import uno
>>> import unohelper
>>> import com.sun.star.ui
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "uno.py", line 263, in _uno_import
return _g_delegatee( name, *optargs, **kwargs )
ImportError: No module named com.sun.star.ui
>>> from com.sun.star.ui import XToolPanel

So import uno and unohelper and one can import objects from the
non-existent com.sun.star.ui module. Still, a lot more is needed to
understand even the example.

--
Terry Jan Reedy

[Karim]

Le 06/07/2012 07:09, Terry Reedy a écrit :
>
> On 7/5/2012 10:30 PM, Karim wrote:
>
>> An excellent link to derived all code example to python:
>> http://www.pitonyak.org/AndrewMacro.sxw.
>
> Even though he only writes in OOBasic, you are right that he explains
> the basic concepts needed for accessing the api from any language. He
> is also honest. Writing non-api code is relatively easy; accessing the
> OO/LO api is harder. I made a start. When I get further, I will look
> at the examples that are close to some things I want to do. I will
> also study your Python examples. Thanks for the help.
>
> Terry
>
>
NP you are welcome to ask me any question.


Cheers
Karim

[Terry Reedy]

[Maurizio Spadaccino]

Hi Emile
Thanks for the reply. Could you provide me a more detailed 'how-to' tutorial on implementing a VBA macro that calls a script or a function from python, or tell me where on the web I can find it? The OReilly chapter seems a bit hard for me at this stage? I dont know, for example, where i should tell the macro where to locate the script...
Maurizio

[Emile van Sebille]

On 7/6/2012 1:31 AM Maurizio Spadaccino said...

> Could you provide me a more detailed 'how-to' tutorial on implementing a VBA macro that calls a script or a function from python, or tell me where on the web I can find it? The OReilly chapter seems a bit hard for me at this stage?

I'm not going to write a how-to, but the relevant bits from the VBA code
look like this:

Set fpiUtils = CreateObject("fenxDCom.Util")
response = fpiUtils.FirstPartInsp(nomDiam, numFlutes, nomOAL, nomLOC)

> I dont know, for example, where i should tell the macro where to locate the script...

Registering the com server does that for you (the __name__ == __main__
part of the python script)

Again, get one of the examples working and move out from there.

Emile

[Colin J. Williams]

You might be interested in pyspread
(http://manns.github.com/pyspread/).

It is no longer maintained for Windows.

Colin W.

[Maurizio Spadaccino]

Thanks again Emile, I'll try out some examples. I found this one: http://showmedo.com/videotutorials/video?name=2190050&fromSeriesID=219
quite enlightning.
One last doubt is: say the python code gets used by more Excel Users (different pc), can I include in some way a dinamic generation of the id in order to allow each pc to register is own COM server or do I have to hard code the id on each machine specifically?

[Thomas Jollans]

It sounds like you're misunderstanding what's going on: COM is not
network transparent by nature. Separate PCs are completely separate
unless you explicitly create a connection.

Apart from that, it looks like this is a UUID. See
http://docs.python.org/library/uuid.html

[Emile van Sebille]

On 7/7/2012 2:05 AM Maurizio Spadaccino said...

> Thanks again Emile, I'll try out some examples. I found this one: http://showmedo.com/videotutorials/video?name=2190050&fromSeriesID=219
> quite enlightning.
> One last doubt is: say the python code gets used by more Excel Users (different pc), can I include in some way a dinamic generation of the id in order to allow each pc to register is own COM server or do I have to hard code the id on each machine specifically?
>

Here's how to generate an ID, but it's likely not to occur on other
machines.

>>> import pythoncom; print pythoncom.CreateGuid()
{CE571F2A-6BD8-4A8D-9482-4EC02FAC171E}

There's an interesting perspective on uniqueness of guid's at

http://betterexplained.com/articles/the-quick-guide-to-guids/

So, create it once and hardcode it in. If you provide new features in a
subsequent version you may want to issue a new guid, particularly to
develop a new version while still running the old on the same machine.

Each user will then need to have python and pythonwin installed, then
run your com server to execute the com server registration piece in the
"if __name__ == '__main__'" part of things.

Emile