How to read result of HYPERLINK() function in POI

Question

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?

Answer 1

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?