Chapter 25. Monitoring Sheets

Overview

Listening for Document Modifications (XModifyListener); Listening for Application Closing (XTopWindowListener); Listening for Cell Selection (XSelectionChangeListener)

Examples: Calc Modify Listener and Calc Select Listener.

The chapter looks at three kinds of listeners for spreadsheets: document modification, application closing, and cell selection listeners.

Office’s support for listeners was first described back in Chapter 4. Listening, and Other Techniques.

25.1 Listening for the User’s Modifications

A common requirement for spreadsheet programming is controlling how the user interacts with the sheet’s data. In the extreme case, this might mean preventing the user from changing anything, which is possible through the XProtectable interface discussed in 20.1.1 Read-only and Protected Viewing. But often we want to let the user edit the sheet, but monitor what is being changed.

One way of doing this is to attach a XModifyListener interface to the open document so that its modified() method will be triggered whenever a cell is changed.

Calc Modify Listener illustrates this approach:

PythonNone

ModifyListenerAdapterModifyListener

class ModifyListenerAdapter:
    def __init__(self, out_fnm: PathOrStr) -> None:
        super().__init__()
        if out_fnm:
            out_file = FileIO.get_absolute_path(out_fnm)
            _ = FileIO.make_directory(out_file)
            self._out_fnm = out_file
        else:
            self._out_fnm = ""
        self.closed = False
        loader = Lo.load_office(Lo.ConnectPipe())
        self._doc = CalcDoc(Calc.create_doc(loader))

        self._doc.set_visible()
        self._sheet = self._doc.get_sheet(0)

        # insert some data
        self._sheet.set_col(
            cell_name="A1",
            values=("Smith", 42, 58.9, -66.5, 43.4, 44.5, 45.3),
        )

        # Event handlers are defined as methods on the class.
        # However class methods are not callable by the event system.
        # The solution is to assign the method to class fields and use them to add the event callbacks.
        self._fn_on_window_closing = self.on_window_closing
        self._fn_on_modified = self.on_modified
        self._fn_on_disposing = self.on_disposing

        # Since OooDev 0.15.0 it is possible to set call backs directly on the document.
        # No deed to create a ModifyEvents object.
        # It is possible to subscribe to event for document, sheets, ranges, cells, etc.
        self._doc.add_event_modified(self._fn_on_modified)
        self._doc.add_event_modify_events_disposing(self._fn_on_disposing)

        # This is the pre 0.15.0 way of doing it.
        # pass doc to constructor, this will allow listener to be automatically attached to document.
        # self._m_events = ModifyEvents(subscriber=self._doc.component)
        # self._m_events.add_event_modified(self._fn_on_modified)
        # self._m_events.add_event_modify_events_disposing(self._fn_on_disposing)

        # close down when window closes
        self._top_win_ev = TopWindowEvents(add_window_listener=True)
        self._top_win_ev.add_event_window_closing(self._fn_on_window_closing)

    def on_window_closing(
        self, source: Any, event_args: EventArgs, *args, **kwargs
    ) -> None:
        print("Closing")
        try:
            self._doc.close_doc()
            Lo.close_office()
            self.closed = True
        except Exception as e:
            print(f"  {e}")

    def on_modified(self, source: Any, event_args: EventArgs, *args, **kwargs) -> None:
        print("Modified")
        try:
            # event = cast("EventObject", event_args.event_data)
            # doc = Lo.qi(XSpreadsheetDocument, event.Source, True)
            doc = self._doc
            addr = doc.get_selected_cell_addr()
            print(
                f"  {Calc.get_cell_str(addr=addr)} = {self._sheet.get_val(addr=addr)}"
            )
        except Exception as e:
            print(e)

    def on_disposing(self, source: Any, event_args: EventArgs, *args, **kwargs) -> None:
        print("Disposing")

Calc Modify Listener example utilizes one of two classes, ModifyListenerAdapter of modify_listener_adapter.py or ModifyListener of modify_listener.py. These classes are functionally the same. These two class are interchangeable and are for example purposes. We also seen this in 4.1 Listening to a Window.

We will focus on ModifyListenerAdapter here.

25.1.1 Listening to the Close Box

__init__() creates a ModifyListener object and then terminates, which means that the object must deal with the closing of the spreadsheet and the termination of Office.

This is done by employing another listener: an adapter for XTopWindowListener, TopWindowListener, attached to the Calc application’s close box:

PythonNone

# in modify_listener_adapter.py
# close down when window closes
def __init__(self, out_fnm: PathOrStr) -> None:
    # ... other code
    self._fn_on_window_closing = self.on_window_closing
    self._top_win_ev = TopWindowEvents(add_window_listener=True)
    self._top_win_ev.add_event_window_closing(self._fn_on_window_closing)
    # ... other code

XTopWindowListener was described in 4.1 Listening to a Window, but TopWindowListener is an OooDev support class in the adapter namespace.

XTopWindowListener defines eight methods, called when the application window is in different states: opened, activated, deactivated, minimized, normalized, closing, closed, and disposed. TopWindowListener supplies empty implementations for those methods:

PythonNone

class TopWindowListener(AdapterBase, XTopWindowListener):

    def __init__(
        self, trigger_args: GenericArgs | None = None, add_listener: bool = True
    ) -> None:
        super().__init__(trigger_args=trigger_args)
        if add_listener:
            self._tk = mLo.Lo.create_instance_mcf(
                XExtendedToolkit, "com.sun.star.awt.Toolkit", raise_err=True
            )
            if self._tk is not None:
                self._tk.addTopWindowListener(self)

    def windowOpened(self, event: EventObject) -> None:
        self._trigger_event("windowOpened", event)

    def windowActivated(self, event: EventObject) -> None:
        self._trigger_event("windowActivated", event)

    def windowDeactivated(self, event: EventObject) -> None:
        """Is invoked when a window is deactivated."""
        self._trigger_event("windowDeactivated", event)

    def windowMinimized(self, event: EventObject) -> None:
        self._trigger_event("windowMinimized", event)

    def windowNormalized(self, event: EventObject) -> None:
        self._trigger_event("windowNormalized", event)

    def windowClosing(self, event: EventObject) -> None:
        self._trigger_event("windowClosing", event)

    def windowClosed(self, event: EventObject) -> None:
        self._trigger_event("windowClosed", event)

    def disposing(self, event: EventObject) -> None:
        self._trigger_event("disposing", event)

TopWindowEvents is a class that can subscribes to the events generated by TopWindowListener, and contains methods for each of the eight events. TopWindowEvents then can be used to subscribe to call back methods in a more pythonic way. TopWindowEvents can be used independently or inherited to extend a class that needs to provide event callbacks for the eight events.

modify_listener_adapter.py subscribes to windowClosing(), and ignores the other methods. windowClosing() is triggered when the application’s close box is clicked, and it responds by closing the document and Office:

PythonNone

# in modify_listener_adapter.py
def on_window_closing(self, source: Any, event_args: EventArgs, *args, **kwargs) -> None:
    print("Closing")
    try:
        Lo.close_doc(self._doc)
        Lo.close_office()
        self.closed = True
    except Exception as e:
        print(f"  {e}")

25.1.2 Listening for Modifications

ModifyListener is notified of document changes by attaching itself to the document’s XModifyBroadcaster:

PythonNone

# in ModifyListener class
def __init__(self, trigger_args: GenericArgs | None = None, doc: XComponent | None = None) -> None:
    super().__init__(trigger_args=trigger_args)
    self._doc = CalcDoc(Calc.create_doc(loader))
    # ... other code

    mb = self._doc.qi(XModifyBroadcaster, True)
    mb.addModifyListener(self)

modify_listener_adapter.py has a built in ModifyEvents.

PythonNone

# in modify_listener_adapter.py
def __init__(self, out_fnm: PathOrStr) -> None:
    # ... other code
    self._fn_on_modified = self.on_modified
    self._doc.add_event_modified(self._fn_on_modified)

    # ... other code

def on_modified(self, source: Any, event_args: EventArgs, *args, **kwargs) -> None:
    print("Modified")
    try:
        # event = cast("EventObject", event_args.event_data)
        # doc = Lo.qi(XSpreadsheetDocument, event.Source, True)
        doc = self._doc
        addr = doc.get_selected_cell_addr()
        print(
            f"  {Calc.get_cell_str(addr=addr)} = {self._sheet.get_val(addr=addr)}"
        )
    except Exception as e:
        print(e)

An EventArgs object arriving at modified() has an event_data property that is an EventObject with a Source field of type XInterface. Every Office interface inherits XInterface so it’s difficult to know what the source really is. The simplest solution is to print the names of the source’s supported services, by calling Info.show_services(), as seen in the commented-out code above.

In this case, the Source field is supported by the SpreadsheetDocument service, which means that it can be converted into an XSpreadsheetDocument interface. Lots of useful things can be accessed through this interface, but that’s also commented-out because self._doc field points to the doc.

25.1.3 Examining the Changed Cell (or Cells)

While modified() is being executed, the modified cell in the document is still selected (or active), and so can be retrieved:

PythonNone

# in modify_listener_adapter.py
doc = self._doc
addr = doc.get_selected_cell_addr()

Calc.get_selected_cell_addr() needs the XModel interface for the document so that XModel.getCurrentSelection() can be called. It also has to handle the possibility that a cell range is currently selected rather than a single cell:

PythonNone

# in Calc class
@classmethod
def get_selected_cell_addr(cls, doc: XSpreadsheetDocument) -> CellAddress:
    cr_addr = cls.get_selected_addr(doc=doc)
    if cls.is_single_cell_range(cr_addr):
        sheet = cls.get_active_sheet(doc)
        cell = cls.get_cell(sheet=sheet, col=cr_addr.StartColumn, row=cr_addr.StartRow)
        return cls.get_cell_address(cell)
    else:
        raise CellError("Selected address is not a single cell")

@overload
@classmethod
def get_selected_addr(cls, doc: XSpreadsheetDocument) -> CellRangeAddress:
    model = Lo.qi(XModel, doc)
    return cls.get_selected_addr(model)


@overload
@classmethod
def get_selected_addr(cls, model: XModel) -> CellRangeAddress:
    ra = Lo.qi(XCellRangeAddressable, model.getCurrentSelection(), raise_err=True)
    return ra.getRangeAddress()

See also

• Calc.get_selected_cell_addr()

• Calc.get_selected_addr()

Calc.get_selected_cell_addr() utilizes Calc.get_selected_addr(), which returns the address of the selected cell range. Calc.get_selected_cell_addr() examines this cell range to see if it’s really just a single cell by calling Calc.is_single_cell_range():

PythonNone

# in Calc class
@staticmethod
def is_single_cell_range(cr_addr: CellRangeAddress) -> bool:
    return cr_addr.StartColumn == cr_addr.EndColumn and cr_addr.StartRow == cr_addr.EndRow

If the cell range is referencing a cell then the cell range address position is used to directly access the cell in the sheet:

PythonNone

# in Calc.get_selected_cell_addr()
sheet = cls.get_active_sheet(doc)
cell = cls.get_cell(sheet=sheet, col=cr_addr.StartColumn, row=cr_addr.StartRow)

This requires the current active sheet, which is obtained through Calc.get_active_sheet().

25.1.4 Problems with the modified() Method

After all this coding, the bad news is that modified() is still lacking in functionality.

One minor problem is that modified() is called twice when the user finishes editing a cell. This occurs when the user presses enter, or tab, or an arrow key, and for unknown reasons. It could be fixed with some judicious hacking: i.e. by using a counter to control when the code is executed.

A more important concern is that modified() only has access to the new value in the cell, but doesn’t know what was overwritten, which would be very useful for implementing data validation. This led to investigation of another form of listening, based on cell selection, which is described next.

25.2 Listening for Cell Selections

Listening to cell selections on the sheet has the drawback of generating a lot of events, but this abundance of data turns out to be useful; It can be used to report more about cell modifications.

The Calc Select Listener example is similar to Calc Modify Listener except that it uses SelectionChangeEvents rather than|modify_events|:

PythonNone

# in select_listener.py
class SelectionListener:
    def __init__(self) -> None:
        super().__init__()
        self.closed = False
        loader = Lo.load_office(Lo.ConnectSocket())
        self._doc = CalcDoc(Calc.create_doc(loader))

        self._doc.set_visible()
        self._sheet = self._doc.get_sheet(0)

        self._curr_addr = self._doc.get_selected_cell_addr()
        self._curr_val = self._get_cell_float(self._curr_addr)  # may be None

        self._attach_listener()

        # insert some data
        self._sheet.set_col(
            values=("Smith", 42, 58.9, -66.5, 43.4, 44.5, 45.3),
            cell_name="A1",
        )

select_listener.py also keeps track of variables holding the address of the currently selected cell (self.curr_addr) and its numerical value (self.curr_val). If the cell doesn’t contain a float then self.curr_val is assigned None. self.curr_addr and self.curr_val are initialized after the document is first created, and are updated whenever the user changes a cell.

_attach_listener() is called to attach the listener to the document:

PythonNone

# in select_listener.py
def _attach_listener(self) -> None:
    # Event handlers are defined as methods on the class.
    # However class methods are not callable by the event system.
    # The solution is to assign the method to class fields and use them to add the event callbacks.
    self._fn_on_window_closing = self.on_window_closing
    self._on_selection_changed = self.on_selection_changed
    self._on_disposing = self.on_disposing

    # close down when window closes
    self._twe = TopWindowEvents(add_window_listener=True)
    self._twe.add_event_window_closing(self._fn_on_window_closing)

    # pass doc to constructor, this will allow listener events to be automatically attached to document.
    self._sel_events = SelectionChangeEvents(doc=self._doc.component)
    self._sel_events.add_event_selection_changed(self._on_selection_changed)
    self._sel_events.add_event_selection_change_events_disposing(self._on_disposing)

The current document is passed to SelectionChangeEvents which handles setting up the XSelectionSupplier.

on_selection_changed() listens for three kinds of changes in the sheet:

1. it reports when the selected cell changes by printing the name of the previous cell and the newly selected one;

2. it reports whether the cell that has just lost focus now has a value different from when it was selected;

3. it reports if the newly selected cell contains a numerical value.

For example, Fig. 209 shows the initial sheet of data created by select_listener.py:

Fig. 209 :Calc Select Listener Sheet Data.

Note that the selected cell when the sheet is first created is A1.

If the user carries out the following operations:

• click in cell B2

• click in cell A4

• click in A5

• change A5 to 4 and press tab

then the sheet will end up looking like Fig. 210, with B5 being the selected cell.

Fig. 210 :Calc Select Listener Modified Sheet.

During these changes, on_selection_changed() will report:

A2 value: 42.0
A3 value: 58.9
A4 value: -66.5
A5 value: 43.4
A5 value: 43.4
A5 has changed from 43.40 to 4.00

The “value” lines state the value of a cell when it’s first selected, and the “changed” lines report whether the cell was left changed when the focus moved to another cell.

The output from on_selection_changed() shown above shows how the user moved around the spreadsheet, and changed the A5 cell’s contents from 43.4 to 4.

on_selection_changed() is defined as:

PythonNone

# in select_listener.py
def on_selection_changed(
    self, source: Any, event_args: EventArgs, *args, **kwargs
) -> None:
    event = cast("EventObject", event_args.event_data)
    ctrl = Lo.qi(XController, event.Source)
    if ctrl is None:
        print("No ctrl for event source")
        return

    addr = self._doc.get_selected_cell_addr()
    if addr is None:
        return
    try:
        # better to wrap in try block.
        # otherwise errors crashes office
        if not Calc.is_equal_addresses(addr, self._curr_addr):
            flt = self._get_cell_float(self._curr_addr)
            if flt is not None:
                if self._curr_val is None:  # so previously stored value was null
                    print(
                        f"{Calc.get_cell_str(self._curr_addr)} new value: {flt:.2f}"
                    )
                else:
                    if self._curr_val != flt:
                        print(
                            f"{Calc.get_cell_str(self._curr_addr)} has changed from {self._curr_val:.2f} to {flt:.2f}"
                        )

        # update current address and value
        self._curr_addr = addr
        self._curr_val = self._get_cell_float(addr)
        if self._curr_val is not None:
            print(f"{Calc.get_cell_str(self._curr_addr)} value: {self._curr_val}")

except Exception as e:
    print(e)

on_selection_changed() is called whenever the user selects a new cell. The address of this new cell is obtained by Calc.get_selected_cell_addr(), which returns null if the user has selected a cell range.

If the new selection is a cell then a series of comparisons are carried out between the previously selected cell address and value (stored in self.curr_addr and self.curr_val) and the new address and its possible numerical value (stored in addr and flt). At the end of the method the current address and value are updated with the new ones.

XSelectionChangeListener shares a similar problem to XModifyListener in that a single user selection triggers multiple calls to selectionChanged(). Clicking once inside a cell causes four calls, and an arrow key press may trigger two calls depending on how it’s entered from the keyboard.