How to tell when recalculate is done in Calc?

English
,
#1

How to tell when recalculate is done in Calc?

I need to take action recalculate of a sheet is done.
The action can’t be done while calculating is taking place because it modifies a formula on the sheet.
When I try and modify a formula while recalculating is taking place it leads to LibreOffice crashing totally.

What I am trying to accomplish

I have a custom calc formula that set a formula array based upon a named range in the sheet.
In short my custom formula is aware when the named range data changes.
When the range data changes my custom formula updates the data in the formula array.
So far so good.

The issue is when data is added to the named range my formula recognizes the new data.
However, trying to set the formula array (array size has changed) to match the new data totally fails while recalculation is taking place, to the point that LibeOffice crashes.
I can update the formula array manually (by running my update code in APSO Console) and it works fine, but can’t update while recalculation is taking place.

My choices seem to be:

  1. Stop the recalculation and make the changes … I don’t think it is possible to stop recalculation
  2. Wait for recalculation to be done and then trigger the updates (this makes the most sense)

I do know how to determine when a recalculation has finished. Is this Possible?

Sheets have a Formulas Calculated event but it fires before recalculation and not after.

I am working with python if it matters.

What I am trying to accomplish is very similar to this clip for excel.

#2

… file a bug report on crash?

#3

Where is that documented?

#4

I don’t know where Formulas Calculated is at the start of calculation might be documented. I have not seen the documentation on this yet.


However, I have tested this thousands of times at this point. The extension I am writing relies on this heavily.

#5

This python code example shows that the Formulas Calculated event is actually OnCalculate (note not OnCalculated, past tense). However, I still was not able to find any documentation on it.


MRI show the following events for a sheet

OnFocus
OnUnFocus
OnSelect
OnDoubleClick
OnRightClick
OnChange
OnCalculate
#6

Yes there are the events [onChange, OnCalculate] …
Both are triggered … after the Change| after the Recalculation

#7

events = cast("XNameReplace", sheet.component.getEvents()) # type: ignore

OK, this is far beyond my skills.

#8

It matters not in this case. The example is with the purpose of pointing out that Formulas Calculated maps to OnCalculate

Here is a simplified version of the method for better understanding.

def set_sheet_calculate_event(sheet: Any):
	# get the event which is a XNameReplace implementation
    events = sheet.getEvents()  # events will be a XNameReplace implementation
    # check first if the sheet events contains the OnCalculate attribute, This should always be True
    if events.hasByName("OnCalculate"):
        try:
	        # new_script will contain a string similar to:
	        # "vnd.sun.star.script:myextension.oxt|python|scripts|share_event.py$formulas_calc?language=Python&location=user:uno_packages"
            new_script = _get_new_script()
            try:
	            # set the properties, Script and EventType
                dict_props = {}
                dict_props["Script"] = new_script
                dict_props["EventType"] = "Script"
                data = Props.make_props(**dict_props) # makes tuple of PropertyValue
	            # use uno to invoke or it does not seem to work otherwise
                uno_data = uno.Any("[]com.sun.star.beans.PropertyValue", data)
                uno.invoke(events, "replaceByName", ("OnCalculate", uno_data))
            except Exception:
                log.exception(f"set_doc_sheets_calculate_event() - Sheet {sheet.name}")

        except Exception:
            log.exception(f"set_sheet_calculate_event() - Sheet {sheet.name}")
    else:
        log.debug(f"Sheet {sheet.name} does not have OnCalculate event")
#9

The online help for Sheet Events mentions

Formulas calculated
Cells in sheet have been recalculated.

but that is wrong. The OnCalculate event is fired before calculation, in fact already when a DataChanged notification is handled that may result in calculation.

There can’t be a reliable “when everything is calculated” event because there may be partial calculations if they are sufficient to display all results in a view, and subsequent (partial) calculations may occur in idle phases or when scrolling; unless everything is recalculated at once by Shift+Ctrl+F9 Hard-Recalc-All.

What you can do is to disable automatic calculation before modifying cell contents and re-enable afterwards. You should do that anyway because modifying cell content while calculation runs is a no-no and as you experienced might even lead to crashes. As Mike said, worth a bug report. As a workaround, disabling multi-threaded calculation may help. But if your code runs in a separate detached from main thread it may be more complicated.

3 Likes
#10

For reference, Excel has an event Application.AfterCalculate, property CalculationState, method CalculateUntilAsyncQueriesDone.

#11

Ok, we could fire such event if the queue was actually emptied, but it might be that would be much later or several OnCalculate would be fired before one OnWhenCalculated (or some such) even occurs.

#12

I think this would be great.
The property, method, and event I mentioned in my previous post complement each other nicely:

  • Property: check if there are currently any calculations
  • Method: wait (synchronously) for the calculations to complete
  • Event: wait asynchronously.
1 Like
#13

I found a way that works. I was getting a bit desperate so I just started trying stuff.


My solution is to start another thread and update the array formula from there.


Short Version, when my custom function is called it check to see if the array formula need to be updated, If the underlying data size has chaged the the array needs to be update to reflect the new size. Making the change while in the same thread that the current custom function is running on seems to result in LibreOffice Crashing. My solution is to start another thread (in python) and update the array in the new thread. I do not wait for the thread to finish (no t.join()).


So far it seems to be working great. I have only tested on Linux so far.

#14

I is also working fine on Windows 10, LO 2024.2.5.2

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.