Apache Poi can evaluate and return results of functions in formulas. However for the special function HYPERLINK(), it only returns the "display value", not the actual calculated hyperlink value.
I have an Excel file which contains complex computed hyperlinks which combine results from a number of different fields in the workbook, thus it would be nice to be able to read the resulting URL for the hyperlink, however with default formula evaluation I only get the "display value", not the actual URL.
Is there a way I can compute the formula in a way so I can read the actual URL?
Found a way, but I would probably call it "ugly workaround":
If you try to re-implement the "Hyperlink" function implementation in Apache Poi with WorkbookEvaluator.registerFunction("HYPERLINK", func) you get an error that the built-in function cannot be overwritten.
After digging into Poi a bit more, I found that I can access the list of builtin-functions by putting a class into the "org.apache.poi.ss.formula.eval" package:
package org.apache.poi.ss.formula.eval;
public class BuiltinFunctionsOverloader {
public static void replaceBuiltinFunction(int index, Function function) {
FunctionEval.functions[index] = function;
}
}
Then I can use this override a function, e.g. Hyperlink has index 359:
BuiltinFunctionsOverloader.replaceBuiltinFunction(359, func);
With a function implementation as follows, I now get the URL-value instead of the display-value:
Function func = new Function2Arg() {
@Override
public final ValueEval evaluate(ValueEval[] largs, int srcRowIndex, int srcColumnIndex) {
switch (largs.length) {
case 1:
return evaluate(srcRowIndex, srcColumnIndex, largs[0]);
case 2:
return evaluate(srcRowIndex, srcColumnIndex, largs[0], largs[1]);
}
return ErrorEval.VALUE_INVALID;
}
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
return arg0;
}
@Override
public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
return arg0;
}
};
Ugly, but at least does not require me to patch POI.
Anybody knows of a more "official" way of doing this?