Is there a way to mark a Sheet cell as dirty with a macro?
I have a cell that is set to a custom formula from a Calc Addin that I created. The custom formula gets is value from python code. Currently, even if the python code is changed the value of the cell does not ( unless I do a Hard Recalculate).
I am wondering if I can mark the cell as dirty from when the underlying code is changed so Calc picks it up when auto recalculate is enable.
Can you upload some sample files here?
Sample files would not be of any use right now. I am working on a custom Calc extension.
Current source here, main branch here for future reference.
The extension allows for python code to drive the results of the cell. The Python code acts like a single module so the result of the formulas are connected together.
Here is an example dump of the python code of my test sheet.
# Source code for doc: vnd.sun.star.tdoc:/2/
# Code for Cell: A1 of sheet index: 0
x = 12
# Code for Cell: A2 of sheet index: 0
y = 500
# Code for Cell: A3 of sheet index: 0
a3 = y + 10
# Code for Cell: C3 of sheet index: 0
c3 = a3 + 100
The extension also allows the code to be edited.
calculateAll() as seen in the last line of the source code below.
def dispatch(self, url: URL, args: Tuple[PropertyValue, ...]) -> None:
self._logger.debug(f"dispatch(): url={url}")
doc = CalcDoc.from_current_doc()
sheet = doc.sheets[self._sheet]
cell = sheet[self._cell]
cc = CellCache(doc) # singleton
cell_obj = cell.cell_obj
sheet_idx = sheet.sheet_index
if not cc.has_cell(cell=cell_obj, sheet_idx=sheet_idx):
self._logger.error(f"Cell {self._cell} is not in the cache.")
return
with cc.set_context(cell=cell_obj, sheet_idx=sheet_idx):
# self._edit_code() pops up the coded edit dialog and returns a boolean result.
result = self._edit_code(doc=doc, cell_obj=cell_obj)
if result:
if doc.component.isAutomaticCalculationEnabled():
doc.component.calculateAll()
Entire module source here
The issue with calculateAll() is it forces the entire sheet to be re-calculated. This works but is not efficient. I wold like to use calculate() which only re-calculates dirty cells which does not work in this case.
How could I mark the cell as dirty so calaculate() would run the custom formula and update ONLY the related cells?
There is no explicit API way to mark a cell dirty. A formula cell is broadcasted a dirty whenever one of the cells it depends on changes value or is recalculated, or if it uses a volatile function that is always recalculated it is kind of permanently dirty. There’s another way to force a formula cell to be recalculated whenever any formula cell is calculated by prefixing it with two == equal signs instead of just one. But both, volatile functions and that forced recalc, are not sufficient for your scenario because nothing in the sheet changes when the extension function’s code is edited.
However, the forced == recalc is triggered also for XCalculatable::calculate() that calculates only dirty formula cells, i.e. in your code doc.component.calculate() insteaf should do if the formula cell is marked such.
When I tried the == it get replaced by = as soon as the cell is recalculated, such as when I press the enter key after making the change. I am going to try and remove and add the formula for the cell write it back with two == to see if that work. This will be tricky because I have listeners on the cell that watch if the formula changes ( and deletes the code when the formula is removed ). I will play around some more and see what I can come up with.
Thank you for the pointers on this.
This is the solution I came up with. It seems to work.
Basically I suspend listeners and write the formula again using setFormuls() and Calc treats it like a dirty cell.
def dispatch(self, url: URL, args: Tuple[PropertyValue, ...]) -> None:
self._logger.debug(f"dispatch(): url={url}")
doc = CalcDoc.from_current_doc()
sheet = doc.sheets[self._sheet]
cell = sheet[self._cell]
cc = CellCache(doc) # singleton
cell_obj = cell.cell_obj
sheet_idx = sheet.sheet_index
if not cc.has_cell(cell=cell_obj, sheet_idx=sheet_idx):
self._logger.error(f"Cell {self._cell} is not in the cache.")
return
with cc.set_context(cell=cell_obj, sheet_idx=sheet_idx):
result = self._edit_code(doc=doc, cell_obj=cell_obj)
if result:
if doc.component.isAutomaticCalculationEnabled():
cm = CellMgr(doc) # singleton. Tracks all Code cells
# suspend the listeners for this cell
with cm.listener_context(cell.component):
s = cell.component.getFormula()
if not s:
self._logger.error(f"Cell {self._cell} has no formula.")
return
s = s.lstrip("=") # just in case there are multiple equal signs
cell.component.setFormula(f"={s}")
doc.component.calculate()
And in My log File it show cell formula being fired.
2024-06-10 02:10:00,009 - DispatchEditPY - DEBUG - Displaying dialog
2024-06-10 02:10:07,012 - DispatchEditPY - DEBUG - Dialog returned with OK
2024-06-10 02:10:07,013 - DispatchEditPY - DEBUG - Code has changed, updating ...
2024-06-10 02:10:07,013 - libre_pythonista - DEBUG - PySourceManager - update_source()
2024-06-10 02:10:07,013 - libre_pythonista - DEBUG - PySourceManager - update_source() sheet index: 0 col: 2, row: 2
2024-06-10 02:10:07,018 - libre_pythonista - DEBUG - PySourceManager - update_source() is last index updating from index 3
2024-06-10 02:10:07,018 - libre_pythonista - DEBUG - PySourceManager - update_from_index(3) Entered.
2024-06-10 02:10:07,018 - libre_pythonista - DEBUG - PySourceManager - update_from_index(3). Is last index.
2024-06-10 02:10:07,018 - libre_pythonista - DEBUG - PySourceManager - _update_item() Entered.
2024-06-10 02:10:07,018 - libre_pythonista - DEBUG - PySourceManager - _update_item() sheet index: 0 col: 2, row: 2
2024-06-10 02:10:07,018 - libre_pythonista - DEBUG - PySourceManager - _update_item() Leaving.
2024-06-10 02:10:07,019 - libre_pythonista - DEBUG - PySourceManager - update_from_index(3) Leaving.
2024-06-10 02:10:07,019 - DispatchEditPY - DEBUG - Cell Code updated for C3
2024-06-10 02:10:07,019 - libre_pythonista - DEBUG - PySourceManager - update_all() Entered.
2024-06-10 02:10:07,020 - libre_pythonista - DEBUG - PySourceManager - update_all() Leaving.
2024-06-10 02:10:07,021 - DispatchEditPY - DEBUG - Code updated
2024-06-10 02:10:07,021 - CellMgr - DEBUG - Listener context for cell: $Sheet1.$C$3
2024-06-10 02:10:07,021 - CellMgr - DEBUG - Un-subscribing listeners for cell: $Sheet1.$C$3
2024-06-10 02:10:07,023 - PyImpl - DEBUG - pyc entered
2024-06-10 02:10:07,025 - PyImpl - DEBUG - Doc UID: 2
2024-06-10 02:10:07,025 - PyImpl - DEBUG - pyc - sheet_num: arg 1
2024-06-10 02:10:07,025 - PyImpl - DEBUG - pyc - cell_address: arg $C$3
2024-06-10 02:10:07,025 - PyImpl - DEBUG - pyc -args: (510.0,)
2024-06-10 02:10:07,036 - PyImpl - DEBUG - pyc - Cell C3 has custom properties: True
2024-06-10 02:10:07,036 - PyImpl - DEBUG - Py: py cell has code
2024-06-10 02:10:07,036 - PyImpl - DEBUG - Py: py sheet_num: 1, cell_address: $C$3
2024-06-10 02:10:07,036 - PyImpl - DEBUG - pyc exiting
2024-06-10 02:10:07,037 - PyImpl - DEBUG - pyc entered
2024-06-10 02:10:07,039 - PyImpl - DEBUG - Doc UID: 2
2024-06-10 02:10:07,039 - PyImpl - DEBUG - pyc - sheet_num: arg 1
2024-06-10 02:10:07,039 - PyImpl - DEBUG - pyc - cell_address: arg $C$3
2024-06-10 02:10:07,039 - PyImpl - DEBUG - pyc -args: (510.0,)
2024-06-10 02:10:07,053 - PyImpl - DEBUG - pyc - Cell C3 has custom properties: True
2024-06-10 02:10:07,053 - PyImpl - DEBUG - Py: py cell has code
2024-06-10 02:10:07,053 - PyImpl - DEBUG - Py: py sheet_num: 1, cell_address: $C$3
2024-06-10 02:10:07,053 - PyImpl - DEBUG - pyc exiting
2024-06-10 02:10:07,054 - CellMgr - DEBUG - Subscribing to listeners for cell: $Sheet1.$C$3
I marked you suggestion and the solution seeing as it pointed me in this direction?
I alos want to note that when I put == in the formulat bar it did not stick as I mentioned above; However, when I use == in setFormula("==Myformula()) it did stick.
Doesn’t happen here. That basically means something rewrites your formula cell omitting one =.
Yes of course, a newly entered formula cell is dirty and needs to be calculated.