Re: Which way is best to execute a Python script in Excel?
Thanks again Emile, I'll try out some examples. I found this one: http://showmedo.com/videotutorials/video?name=2190050fromSeriesID=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? -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
On 07/07/2012 11:05 AM, Maurizio Spadaccino wrote: Thanks again Emile, I'll try out some examples. I found this one: http://showmedo.com/videotutorials/video?name=2190050fromSeriesID=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? 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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=2190050fromSeriesID=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
--
http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
On 06/07/2012 1:09 AM, Terry Reedy wrote: 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 You might be interested in pyspread (http://manns.github.com/pyspread/). It is no longer maintained for Windows. Colin W. -- http://mail.python.org/mailman/listinfo/python-list
Which way is best to execute a Python script in Excel?
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? Thanks for you help Maurizio -- Maurizio www.mauriziospadaccino.com --- If you can avoid printing this e-mail, you are only doing good for our planet. -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
Le 05/07/2012 09:22, Maurizio Spadaccino a écrit : 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? 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. 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. Cheers Karim -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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... -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: Which way is best to execute a Python script in Excel?
Le 05/07/2012 23:20, Terry Reedy a écrit :
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.
Hi Terry,
You are right it was the warrior path at the beginning to be able to
produce some code but it is working pretty well.
I add to gather many pieces and examples. In fact, you should take the
OOo Basic langage example (API) and convert it
(sometimes for python some cases have been changed but mainly you have
to use ones from original API namely OOo Basic
language). They only did python wrapper around the native API.
An excellent link to derived all code example to python:
http://www.pitonyak.org/AndrewMacro.sxw.
On latest ubuntu you get no error if you are importing:
import uno
import unohelper
BUT IN OTHER ENVIRONMENT if the version of python is different from the
one embedded inside OpenOffice (LibreOffice) namely 2.6.X
You are obliged to recompile a OOo version with you python version. BUT
there is a way to hack this. I did it, just do the following:
LD_LIBRARY_PATH=:/usr/lib/openoffice.org/ure/lib:/usr/lib/openoffice.org/basis3.3/program
PYTHONPATH=:/usr/lib/openoffice.org3/basis-link/program/
UNO_PATH=/usr/lib/openoffice.org/basis3.3/program
URE_BOOTSTRAP=vnd.sun.star.pathname:/usr/lib/openoffice.org/basis3.3/program/fundamentalbasisrc
Jut set these env variables according to your OOo installation and you
will get no error by importing uno and unohelper.
One article to begin with:
http://www.linuxjournal.com/content/starting-stopping-and-connecting-openoffice-python
And to finish basic code to get started and some functions I adapted
from Andrew 's Macro book in python to search string and update a table
inside writer document (work or MSW ord or SW format):
Start up code
---
import uno
import unohelper
# 1. Get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()
# 2. Create the UnoUrlResolver
resolver =
localContext.ServiceManager.createInstanceWithContext(com.sun.star.bridge.UnoUrlResolver,
localContext )
# 3. Get the central desktop object
smgr = resolver.resolve(
uno:socket,host=localhost,port=2002;urp;StarOffice.ServiceManager )
# 4. Declare the ServiceManager
remoteContext = smgr.getPropertyValue( DefaultContext )
# 5. Get the central desktop object
desktop = smgr.createInstanceWithContext(
com.sun.star.frame.Desktop,remoteContext)
!!! Document text creation !!!
# open a writer document
#doc = desktop.loadComponentFromURL('private:factory/swriter','_blank',
0, ())
#document =
Re: Which way is best to execute a Python script in Excel?
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 -- http://mail.python.org/mailman/listinfo/python-list
