I'm struggling for some time with this problem and I am not able to find any solution on-line. Closest I get is how to write Basic function that can be used in Calc's formula, but since Basic and BeanShell are completely different languages, I can't find the right syntax/procedure to achieve the same functionality in the latter language.
In Writer one can have a table (not the spreadsheet—just ordinary table), where you can press F2 over cell and enter some formula, e.g. =<C2>*<E2> to calculate product of values in C2 and E2 cells.
I wrote BeanShell method String amountInWords(String amount, String currency) which converts passed amount (e.g. 1,234.59) and currency (e.g. "USD") into words (one thousand two hundred thirty four dollars, fifty nine cents). Now I would like to hit F2 over some cell and type formula like =amountInWords(<Table2.D3>, "USD") and to see above mentioned output as the cell content. Unfortunately I get ** Expression is faulty ** message.
Can someone, please, advice me, how to make use of this method in the described manner or alternatively confirm, that this is impossible? Thank you very much in advance!
Writer table formulas are much more limited than spreadsheet formulas. There is a list of supported functions at http://libreoffice-dev.blogspot.com/2019/11/calculations-inside-of-writer-tables.html. AFAIK calling macros is not supported.
Probably what you want instead of a table is to embed a Calc spreadsheet into the Writer document at that location by going to Insert -> Object -> OLE Object. Then rewrite amountInWords as a Basic user-defined function (UDF).
If you must use BeanShell then write a Basic UDF that loads and calls the BeanShell method. Or you could create a BeanShell Calc add-in although that is more difficult, requiring XML configuration files. For add-ins, it may be easier to write in Java rather than BeanShell as there are more examples and documentation available.
Or, instead of embedding a spreadsheet, create a search-and-replace Writer macro in BeanShell that performs any needed calculations in the table. Set the macro to run on an event such as when the document is opened.