How to read result of HYPERLINK() function in POI

2019-07-09 00:41发布

问题:

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?

回答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?