I use Apache POI and JPype together to handle some Excel files. I would like to implement a custom Excel formula function to handle an additional logic in Excel formula evaluator.
It is possible by registering a function: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/eval/FunctionEval.html
The problem is my app is written in Python and I use JPype as a wrapper for Apache POI. I tried to create Python function that implements evaluate() function (as needed in registerFunction from docs).
class CeilingMath:
def evaluate(*args: Any, row: int, col: int) -> int:
value = int(args[0])
return numpy.ceil(value)
# registering
FunctionEval = JPackage("org.apache.poi.ss.formula.eval.FunctionEval")
FunctionEval.registerFunction("CEILING.MATH", CeilingMath())
But when I try to register it I get an error:
TypeError: No matching overloads found for static org.apache.poi.ss.formula.eval.FunctionEval.registerFunction(str,CeilingMath), options are: public static void org.apache.poi.ss.formula.eval.FunctionEval.registerFunction(java.lang.String,org.apache.poi.ss.formula.functions.Function)
I understand that types don't match, but I have also no idea how to bypass that or how to create org.apache.poi.ss.formula.functions.Function instance with custom evaluate() function implementation...
[edit]
I tried to inherit from Java class:
Function = JPackage("org.apache.poi.ss.formula.functions").Function
class CeilingMath(Function):
But it doesn't work as well...
TypeError: Java classes cannot be extended in Python
I believe, the only way of doing this is writing implementations directly in Java, compiling it to JAR archive and include during JVM starts.