Is it possible to calculate only specific cell using UNO?

Creating a macro - Writing a Script - Using the API

Is it possible to calculate only specific cell using UNO?

Postby lukas1994 » 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?
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

Postby FJCC » 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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7311
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby lukas1994 » 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.
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

Postby FJCC » 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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7311
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby lukas1994 » 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.
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

Postby lukas1994 » Mon Aug 12, 2019 3:37 pm

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

Postby Villeroy » 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27227
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby lukas1994 » 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.
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

Postby lukas1994 » 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
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

Postby lukas1994 » 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
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

Postby gazelle » 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.
OOo 3.0.X on Ms Windows XP + MS Windows Vista
gazelle
 
Posts: 4
Joined: Tue Dec 09, 2008 12:08 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests