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

[Karim]

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 = desktop.loadComponentFromURL("file:///c:"+filepath+".doc" 
,"_blank", 0, (prop,))

prop = PropertyValue()
prop.Name='Hidden'
#prop.Value=True
prop.Value=False
#inProps = PropertyValue( "Hidden" , 0 , True, 0 )
doc = desktop.loadComponentFromURL('private:factory/swriter','_blank', 
0, (prop,))
#doc = desktop.loadComponentFromURL('private:factory/swriter','_blank', 
0, ())

text = doc.Text
cursor = text.createTextCursor()
text.insertString(cursor, 'The first line in the newly created text 
document.\n', 0)
text.insertString(cursor, 'Now we are in the second line\n' , 0)

-------------------------------------------------------------------------------

Functions example:

def search_text(component=None, text=None):
     """
     Generator function to seek for some text in a document.

     @param component  the component where the text is seeked.
     @param text       the text to be searched.

     @return   the found occurence one at a time per generator call.
     """
     # Creation of a descriptor from a document w/ search capabilities.
     descriptor = component.createSearchDescriptor()
     descriptor.SearchString        = text
     descriptor.SearchWords         = True
     descriptor.SearchCaseSensitive = False

     # Get the first match.
     found = component.findFirst(descriptor)

     while found:
         yield found
         found = component.findNext(found.End, descriptor)

def search_replace_text(component=None, search=None, replace=None):
     """
     Seek and replace text occurrence in a text component.

     @param component  the component where the text is seeked.
     @param search     the text to be searched.
     @param replace    the text to be replaced.
     """
     for found in search_text(component=doc, text=search):
         found.setString(replace)


def insert_table_rows(table=None, rows=None, index=1):
     """
     Insert multiple rows in a table object at .

     @param table   the component where the text is seeked.
     @param rows    the data array (tuples of tuples) .
     @param index   the index position of the insertion.
     """"
     try:
         table.RepeatHeadline = True
         table.HeaderRowCount = 1

         # Get the DataArray.
         data = list(table.DataArray)

         # Add to it the data rows.
         for element, i in enumerate(rows, index):
             data.insert(element, i)

         # Resize the table to prepare data adding.
         table_rows  = table.Rows

         # Add #rows starting at the NumRows given by index position.
         table_rows.insertByIndex(table_rows.Count, len(rows))



Cheers
Karim

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20120706/503b318e/attachment.html>