OOo/LibreOffice UNO / Java: How to get calling spreadsheet cell of a calc function?

Question

In an UNO extension for OpenOffice/LibreOffice Calc (Spreadsheet), written in Java, how can you determine the calling cell inside the implementation of a UDF (spreadsheet function)?

Remarks

• In Excel/VBA this is possible via Application.Caller
• The main motivation to get the caller is logging/tracing/debugging, i.e., view the calling cell as part of a stack trace.
• It should be possible to obtain this information, since built-in functions like "ROW()" and "COLUMN()" do have some knowledge of the calling cell.
• An application where this possibility is used (for Excel) is Obba, an object handler for spreadsheets. Here the "control panel" provides a list of (Java) exceptions including the calling cell, i.e., the cell is part of the stack trace. See the following screenshot:

This is also a feature request on the Apache OpenOffice Bugzilla

Answer 1

It looks like you want to register a listener to a spreadsheet component. To satisfy your goal, you could add the listener to the spreadsheet object it self, or to another nested object that implements an interface that supports an add.+EventListener() method.

Below is a pair (broadcaster/listener) that I can think you could use in your project: XDocumentEventBroadcaster/XDocumentEventListener

The UNO event model is explained here: https://wiki.openoffice.org/wiki/Documentation/DevGuide/ProUNO/Event_Model

Below are examples of how these listeners are used.

    ////////////////////////////////////////////////////////////////////      // Add document window listeners.      ////////////////////////////////////////////////////////////////////       System.out.println("WriterDoc: Add window listeners.");       // Example of adding a document displose listener so the application      // can know if the user manually exits the Writer window.       document.addEventListener(new XEventListener() {          public void disposing(EventObject e) {              System.out.println(                      "WriterDoc (Event Listener): The document window is closing.");          }      });       // Example of adding a window listener so the application can know      // when the document becomes initially visible (in the case of this      // implementation, we will manually set it visible below after we      // finish building it).       window.addWindowListener(new XWindowListener() {          public void windowShown(com.sun.star.lang.EventObject e) {              System.out.println(                      "WriterDoc (Window listener): The document window has become visible.");          }          public void windowHidden(com.sun.star.lang.EventObject e) { }          public void disposing(com.sun.star.lang.EventObject e) { }          public void windowResized(com.sun.star.awt.WindowEvent e) { }          public void windowMoved(com.sun.star.awt.WindowEvent e) { }      });  

Also, the service SheetCellRange supports the interface XModifyBroadcaster. Maybe you could get the desired behavior if you registered a XModifyListener object to it. The object would implement the 'modified' method, which receives an EventObject when called. I believe you can get who the caller is from the source property of the EventObject. If the source turns out to be the whole SheetCellRange, you could try to loop through all the cells you wish that be monitored, and add a XModifyListener to each. The SheetCell service also supports the XModifyBroadcaster interface .

Example of use of the XModifyBroadcaster from a CellRange: http://openoffice.2283327.n4.nabble.com/Re-How-to-get-the-XModifyBroadcaster-from-Cell-CellRange-Table-td2771959.html

Cheers!