Page 1 of 1

### Is it possible to calculate only specific cell using UNO?

Posted: Mon Aug 12, 2019 1:40 pm
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?

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

Posted: Mon Aug 12, 2019 2:27 pm
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.

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

Posted: Mon Aug 12, 2019 2:37 pm
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.

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

Posted: Mon Aug 12, 2019 3:17 pm
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.

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

Posted: Mon Aug 12, 2019 3:23 pm
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.

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

Posted: Mon Aug 12, 2019 3:37 pm
Basically I'm running some kind of Monte Carlo simulation:
Code: Select all   Expand viewCollapse view
`import unoimport osimport randomfile_name = "/app/fund.xls"lock_file = "/app/.~lock.fund.xls#"# needed because we get the /app directory from the local machineos.remove(lock_file)# API examples: https://wiki.openoffice.org/wiki/Python-Calclocal = 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 resrun({"A1": [100, 10]}, ["B1"])`

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

Posted: Mon Aug 12, 2019 3:58 pm
You could try https://libreoffice.org/ Its Calc component has undergone several optimizations.
20 seconds does not sound too bad.

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

Posted: Mon Aug 12, 2019 5:13 pm
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.

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

Posted: Tue Aug 13, 2019 10:55 am
I'm now trying to use MULTIPLE.OPERATIONS() but having issues - see viewtopic.php?f=9&t=98966

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

Posted: Tue Aug 13, 2019 5:29 pm
I managed to improve the speed a lot by using `uno:pipe` instead of `uno:socket`. 20s -> 2s

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

Posted: Fri Oct 04, 2019 6:06 pm
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.