Is it possible to calculate only specific cell using UNO?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
lukas1994
Posts: 14
Joined: Mon Aug 12, 2019 1:38 pm

Is it possible to calculate only specific cell using UNO?

Post by lukas1994 »

I need to re-calculate a spreadsheet for many inputs and calculate() and calculateAll() are rather slow. Is it possible to only recalculate specific cells?
OpenOffice 3.1 on MacOS
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Is it possible to calculate only specific cell using UNO

Post by FJCC »

The function calculate() only affects cells whose precedents have changed. Calculating less than that would leave you with cells whose value is not correct. That seems like a bad idea.If you tell us more about what you are trying to do, someone may have a specific suggestion.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
lukas1994
Posts: 14
Joined: Mon Aug 12, 2019 1:38 pm

Re: Is it possible to calculate only specific cell using UNO

Post by lukas1994 »

I just want to speed up the computation. Right now, re-calculating my spreadsheet 100 times takes 20s. Another approach would be to preprocess the spreadsheet and remove all cells that I don't need.

I have a set of input cells and a set of output cells. These are the only cells I care about. There are probably many more cells that don't need to get computed.
OpenOffice 3.1 on MacOS
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Is it possible to calculate only specific cell using UNO

Post by FJCC »

Recalculating 100 times should not be necessary unless you are doing an iterative calculation. Can you show your code and upload a small files demonstrating what you are doing? To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
lukas1994
Posts: 14
Joined: Mon Aug 12, 2019 1:38 pm

Re: Is it possible to calculate only specific cell using UNO

Post by lukas1994 »

Ohh sorry. I’m changing the inputs before every recalculation of course. Can upload the code later if that helps.

Is there a way to get all transitive dependencies of a set of cells? Then I could remove all formulas in the other cells.
OpenOffice 3.1 on MacOS
lukas1994
Posts: 14
Joined: Mon Aug 12, 2019 1:38 pm

Re: Is it possible to calculate only specific cell using UNO

Post by lukas1994 »

Basically I'm running some kind of Monte Carlo simulation:

Code: Select all

import uno
import os
import random

file_name = "/app/fund.xls"
lock_file = "/app/.~lock.fund.xls#"
# needed because we get the /app directory from the local machine
os.remove(lock_file)

# API examples: https://wiki.openoffice.org/wiki/Python-Calc
local = uno.getComponentContext()
resolver = local.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", local)
context = resolver.resolve("uno:socket,host=0.0.0.0,port=8100;urp;StarOffice.ServiceManager")
remoteContext = context.getPropertyValue("DefaultContext")
desktop = context.createInstanceWithContext("com.sun.star.frame.Desktop", remoteContext)
document = desktop.loadComponentFromURL(uno.systemPathToFileUrl(file_name), "_blank", 0, ())
sheet = document.getSheets().getByIndex(1)

def run(inputs, outputs):
	res = []
	for _ in range(100):
		for cell, inp in inputs.items():
			value = random.gauss(inp[0], inp[1])
			sheet.getCellRangeByName(cell).setValue(value)
		document.calculate()
		out = []
		for cell in outputs:
			out.append(sheet.getCellRangeByName(cell).getValue())
		res.append(out)
	return res

run({"A1": [100, 10]}, ["B1"])
OpenOffice 3.1 on MacOS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is it possible to calculate only specific cell using UNO

Post by Villeroy »

You could try https://libreoffice.org/ Its Calc component has undergone several optimizations.
20 seconds does not sound too bad.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
lukas1994
Posts: 14
Joined: Mon Aug 12, 2019 1:38 pm

Re: Is it possible to calculate only specific cell using UNO

Post by lukas1994 »

I'm using the `libreoffice-common` package on Linux.
I actually just tested it on a very simple spreadsheet with just one formula and it also took 20sec. So it looks like it's mainly the overhead of uno/openoffice/... and not the computation itself.

Do you have any other ideas how to speed things up? Maybe running multiple threads and each thread opens the sheet once in a separate window?
Maybe OpenOffice and Uno is just not the right solution. I just found that Python libraries like https://github.com/vinci1it2000/formulas fail when loading more complex sheets since some formulas are not implemented.
OpenOffice 3.1 on MacOS
lukas1994
Posts: 14
Joined: Mon Aug 12, 2019 1:38 pm

Re: Is it possible to calculate only specific cell using UNO

Post by lukas1994 »

I'm now trying to use MULTIPLE.OPERATIONS() but having issues - see viewtopic.php?f=9&t=98966
OpenOffice 3.1 on MacOS
lukas1994
Posts: 14
Joined: Mon Aug 12, 2019 1:38 pm

Re: Is it possible to calculate only specific cell using UNO

Post by lukas1994 »

I managed to improve the speed a lot by using `uno:pipe` instead of `uno:socket`. 20s -> 2s
OpenOffice 3.1 on MacOS
gazelle
Posts: 4
Joined: Tue Dec 09, 2008 12:08 am

Re: Is it possible to calculate only specific cell using UNO

Post by gazelle »

My solution for this was implemented in Visual Basic, but you should be able to adapt it to python. The method takes less than 1 sec and goes like this:
1. Get the formula of the cell
2. Append a BLANK to that string
3. Store it back into the cell
You may have to make it your active cell first if I remember correctly.
OOo 3.0.X on Ms Windows XP + MS Windows Vista
Post Reply