[Solved] Python/Calc: Viewing intermediate results

[kiloran]

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

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?

[Charlie Young]

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

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

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

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

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

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)

[kiloran]

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!

[Charlie Young]

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.

[kiloran]

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

Here's the modified example:

Code: Select all

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.

[Charlie Young]

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!