Is it possible to calculate only specific cell using UNO?
Is it possible to calculate only specific cell using UNO?
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
Re: Is it possible to calculate only specific cell using UNO
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Is it possible to calculate only specific cell using UNO
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.
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
Re: Is it possible to calculate only specific cell using UNO
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Is it possible to calculate only specific cell using UNO
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.
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
Re: Is it possible to calculate only specific cell using UNO
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
Re: Is it possible to calculate only specific cell using UNO
You could try https://libreoffice.org/ Its Calc component has undergone several optimizations.
20 seconds does not sound too bad.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Is it possible to calculate only specific cell using UNO
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.
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
Re: Is it possible to calculate only specific cell using UNO
I'm now trying to use MULTIPLE.OPERATIONS() but having issues - see viewtopic.php?f=9&t=98966
OpenOffice 3.1 on MacOS
Re: Is it possible to calculate only specific cell using UNO
I managed to improve the speed a lot by using `uno:pipe` instead of `uno:socket`. 20s -> 2s
OpenOffice 3.1 on MacOS
Re: Is it possible to calculate only specific cell using UNO
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.
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