[Solved] Python/Calc: Viewing intermediate results

Keyboard macros or custom scripts

[Solved] Python/Calc: Viewing intermediate results

Postby kiloran » Thu Jul 25, 2013 10:41 am

I'm developing a Python macro embedded in a Calc document to scrape data from various web pages and paste some results in cells in the spreadsheet.

It works, but when I start the macro, I have to wait until the macro has finished before I can see the results. Here's a very simple script to demonstrate the problem:
Code: Select all   Expand viewCollapse view
import time

def myTest3():   
   oDoc = XSCRIPTCONTEXT.getDocument()
   oSheets = oDoc.getSheets()
   mySheet = oSheets.getByName("Sheet1")
   
   mySheet.getColumns().removeByIndex(0, 1)
   
   for i in range(0,5):
      mySheet.getCellByPosition(0,i).Value = i
      time.sleep(1)


I would like to see the updated cells as they are updated, rather than wait until the end of the script. A similar Excel VBA macro allows me to see the cells as they update, as does an OpenOffice Basic macro

Any ideas?
Last edited by kiloran on Sun Jul 28, 2013 11:40 am, edited 1 time in total.
--kiloran
LibreOffice 4.0.3.3 on Windows 7
kiloran
 
Posts: 10
Joined: Sun Oct 14, 2012 2:06 pm

Re: Python/Calc: Viewing intermediate results

Postby Charlie Young » Thu Jul 25, 2013 6:53 pm

I've found a solution which may be a bit radical, but it works, and also provides an opportunity to discuss some issues with Python in version 4 which I was looking at yesterday.

What we're doing here is sending our changes to the sheet through a socket connection.

In the python scripts folder we want SlowDown.py

Code: Select all   Expand viewCollapse view
import subprocess

def SlowDown(*dummy):
    subprocess.Popen(["C:\\Program Files\\OpenOffice 4\\program\\python-core-2.7.5\\bin\\python.exe","C:\\Documents and Settings\\Charlie\\Application Data\\OpenOffice\\4\\user\\Scripts\\python\\SlowItDown.py"],creationflags=0x08000000,shell = False)

g_exportedScripts = SlowDown,


The purpose of the Windows specific

Code: Select all   Expand viewCollapse view
creationflags=0x08000000


is to suppress the creation of a pop-up window for the process.


The important thing to notice here is that python is being started, not directly from the program folder, but from

Code: Select all   Expand viewCollapse view
C:\Program Files\OpenOffice 4\program\python-core-2.7.5\bin\


Now, I found that when starting from this folder that the uno modules weren't found. One solution I found (maybe there's something easier), is to set the environment variable PYTHONPATH

Code: Select all   Expand viewCollapse view
PYTHONPATH=C:\Program Files\OpenOffice 4\program\


I just configured my system to startup with this. Other folders can be added in the usual fashion by separating them with semicolons.

So, slowdown launches SlowIItDown.py, which above is also in the scripts folder, but could be put elsewhere. It contains myTest3 in a modified form.

Code: Select all   Expand viewCollapse view
import socket  # only needed on win32-OOo3.0.0
import uno
import unohelper
import time
import subprocess

def myTest3(desktop,DelayTime):
    oDoc = desktop.getCurrentComponent()
    oSheets = oDoc.getSheets()
    mySheet = oSheets.getByName("Sheet1")
    mySheet.getColumns().removeByIndex(0,1)
    for i in range(0,5):
        oCell = mySheet.getCellByPosition(0,i)
        oCell.setValue(i)
        time.sleep(DelayTime)
     
       

subprocess.Popen(["soffice","-invisible","-accept=socket,host=localhost,port=2083;urp;StarOffice.ServiceManager"],creationflags=0x08000000,shell = False)

time.sleep(2)

# get the uno component context from the PyUNO runtime
localContext = uno.getComponentContext()

# create the UnoUrlResolver
resolver = localContext.ServiceManager.createInstanceWithContext(
            "com.sun.star.bridge.UnoUrlResolver", localContext )

# connect to the running office
ctx = resolver.resolve( "uno:socket,host=localhost,port=2083;urp;StarOffice.ComponentContext" )
smgr = ctx.ServiceManager
desktop = smgr.createInstanceWithContext( "com.sun.star.frame.Desktop",ctx)
myTest3(desktop,1)
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1446
Joined: Fri May 14, 2010 1:07 am

Re: Python/Calc: Viewing intermediate results

Postby kiloran » Thu Jul 25, 2013 9:18 pm

Charlie, first of all I have to thank you for the effort you must have put in to this. Much appreciated.

I have not yet tried your proposal, but I can see a potential problem.

I am creating a spreadsheet with embedded macros, to be used by a community who have different implementations (various flavours of Windows, or Linux, and various versions of OpenOffice or LibreOffice). At the moment, the spreadsheet uses Basic macros and seems to work OK, though at glacial speeds for web-scraping (hence my move to Python).

I think that the hard-coded paths such as C:\\Program Files\\OpenOffice 4\\program\\python-core-2.7.5\\bin\\python.exe will therefore make your proposal unworkable in this case. Sorry!
--kiloran
LibreOffice 4.0.3.3 on Windows 7
kiloran
 
Posts: 10
Joined: Sun Oct 14, 2012 2:06 pm

Re: Python/Calc: Viewing intermediate results

Postby Charlie Young » Thu Jul 25, 2013 9:24 pm

kiloran wrote:I think that the hard-coded paths such as C:\\Program Files\\OpenOffice 4\\program\\python-core-2.7.5\\bin\\python.exe will therefore make your proposal unworkable in this case. Sorry!


It's not clear what all your issues might be, but Python can identify the OS it's running on, which might help you find the appropriate paths.
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1446
Joined: Fri May 14, 2010 1:07 am

Re: Python/Calc: Viewing intermediate results

Postby kiloran » Sun Jul 28, 2013 11:39 am

I found a solution, rather unexpectedly, by updating the Status Bar in Calc.

Here's the modified example:
Code: Select all   Expand viewCollapse view
import time

def myTest3():   
   oDoc = XSCRIPTCONTEXT.getDocument()
   oSheets = oDoc.getSheets()
   mySheet = oSheets.getByName("Sheet1")
   
   # set up Status Indicator
   Controller = oDoc.getCurrentController()
   DocFrame = Controller.getFrame()
   oStatusIndicator = DocFrame.createStatusIndicator()
   oStatusIndicator.start( "", 1 )
   
   mySheet.getColumns().removeByIndex(0, 1)
   
   oStatusIndicator.setText( "Process started" )
   
   for i in range(0,5):
      oStatusIndicator.setText( "Processing: " + str(i) )
      mySheet.getCellByPosition(0,i).Value = i
      time.sleep(1)

   oStatusIndicator.setText( "Process finished" )


It may not be elegant, and I don't have a real understanding of how it works, but it achieves exactly what I want.
--kiloran
LibreOffice 4.0.3.3 on Windows 7
kiloran
 
Posts: 10
Joined: Sun Oct 14, 2012 2:06 pm

Re: [Solved] Python/Calc: Viewing intermediate results

Postby Charlie Young » Sun Jul 28, 2013 2:55 pm

Very good, and elegant enough.

I had come to the conclusion that Python macros maintain a buffer of some sort, and don't flush it until the macro returns. I tried various tricks to force this to happen, but the only thing that worked at all was to pop up a message box for each i, and that obviously wasn't a solution to your problem. Apparently Python is kind enough to update a status indicator as desired.

I think this is a valuable find!
Apache OpenOffice 4.0.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1446
Joined: Fri May 14, 2010 1:07 am


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 3 guests