It is not looking good from AI’s point of view. Copilot says it is not possible.
Copilot
In LibreOffice Calc, when creating a custom function (also known as a User-Defined Function or UDF), it’s not straightforward to get the reference to the cell that called the function directly within the function itself. This is because the function is designed to be stateless and not aware of the context in which it is called.
However, there is a workaround that involves passing the cell reference as an argument to the function. You can modify the function to accept additional parameters that represent the current cell’s coordinates. Here’s an example of how you might adjust your py function to accept the cell address as parameters:
Python
class Py(unohelper.Base, XPy):
# ... other parts of the class ...
def py(self, cell_address=None):
if cell_address:
# Process the cell_address which is a tuple (Sheet, Column, Row)
sheet, column, row = cell_address
# Now you can use the sheet, column, and row to get the cell value or perform other actions
# ...
else:
# Handle the case where no cell address is provided
# ...
return # the value you want to return
AI-generated code. Review and use carefully. .
To use this function in Calc, you would enter the following formula in a cell:
=PY(CELL("address"))
The CELL("address") function returns the address of the current cell, which is then passed to your py function.
Please note that this approach requires the user to manually enter the cell reference when using the function. If you’re looking for a way to automatically detect the calling cell without user intervention, this might not be possible due to the limitations of the API and the way Calc functions are evaluated.
For more advanced manipulation and if you need to get the calling cell programmatically, you might need to consider creating a macro that runs on a specific event or trigger that can access the current selection or active cell. This would be outside the scope of a simple UDF and would require a different approach to interacting with the Calc document. Remember to always test your code thoroughly to ensure it behaves as expected in all scenarios.
You
Is there a way to get the calling cell without user input?
Copilot
Unfortunately, LibreOffice Calc does not natively support retrieving the calling cell’s address from within a custom function without user input. Custom functions in Calc are designed to be stateless and are not aware of the context in which they are called.
However, there are some advanced methods that involve using macros or scripting to interact with the document and possibly retrieve the active cell or selection. These methods are more complex and typically require a deeper understanding of the LibreOffice API and macro programming.
One approach could be to use a macro that runs on specific events or triggers, which can access the current selection or active cell. This macro could then potentially pass the cell reference to your custom function. But this would be outside the scope of a simple User-Defined Function (UDF) and would require a different approach to interacting with the Calc document.
For example, you could create a service that calls Calc functions and retrieves the active cell:
Python
def get_active_cell():
# Obtain the current component
desktop = XSCRIPTCONTEXT.getDesktop()
model = desktop.getCurrentComponent()
# Check if the current component is a Calc document
if hasattr(model, "Sheets"):
sheet = model.CurrentController.ActiveSheet
cell = model.CurrentController.getSelection()
# Check if the selection is a single cell
if cell.supportsService("com.sun.star.sheet.SheetCell"):
return cell
return None
AI-generated code. Review and use carefully. .
This macro could be triggered by an event, such as selecting a cell or changing the content of a cell, and then it could interact with your py function.
Please note that implementing such a solution requires careful consideration of the document’s behavior and user experience, as it may not be as straightforward as using a built-in function. Always ensure to test your code thoroughly to make sure it behaves as expected in all scenarios. If you’re not familiar with macro programming in LibreOffice, it might be beneficial to consult the or seek assistance from the LibreOffice community forums or other experienced developers.
