Is it possible to calculate only specific cell using UNO?

[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?

[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.

[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.

[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.

[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.

[lukas1994]

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

Code: Select all   Expand viewCollapse view

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"])


[Villeroy]

You could try https://libreoffice.org/ Its Calc component has undergone several optimizations.
20 seconds does not sound too bad.

[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.

[lukas1994]

I'm now trying to use MULTIPLE.OPERATIONS() but having issues - see viewtopic.php?f=9&t=98966

[lukas1994]

I managed to improve the speed a lot by using `uno:pipe` instead of `uno:socket`. 20s -> 2s

[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.