Spreadsheet can't handle hyperlinks containing formulas with structured references (table relative references)

[WoozyG]

Description

DefaultHyperlinkCellClickHandler.getFirstArgumentFromFormula(Cell) looks for formula results to be of type LazyRefEval but doesn't handle anything in the AreaEval tree. Structured reference formulas return LazyAreaEval even when it's a single cell area.

The method then sees no address, but tries to navigate there as an external URL anyway.

Expected outcome

Clicking a HYPERLINK() formula cell containing a value derived from a formula containing structured references should work like any other link click, and navigate to the evaluated value as a URL.

This is easy to do by adding another case to the method. I've been doing it this way for years in Vaadin 8 with POI 3.18.

	        if (value instanceof AreaEval) {
	        	AreaEval base = (AreaEval) value;
	        	// assume a single cell ref
				value = base.getRelativeValue(0,0);
	        } 

Yes, this has a comment mentioning it just uses the first cell's value, if it truly is a multi-cell area, but I've not found a case where that's true anyway, POI just uses the AreaEval interface for the single result.

Would be nice to see this in V24, but since it's already at alpha 9, I know that's unlikely. I can handle it in a custom subclass for now.

Minimal reproducible example

This attached workbook has the issue for me. POI evaluates it to a LazyAreaEval.

hyperlink_bug.xlsx

Steps to reproduce

see minimal example.

Environment

Vaadin version(s): 24.3.6 OS: N/A

Browsers

Issue is not browser related

[WoozyG]

Further, LazyRefEval is a concrete final class. That should be abstracted as far as possible as well. Similarly, AreaEval can be abstracted. I think this should be the final code to handle all cases returning a valid value that eventually resolves to text:

        if (value instanceof RefEval) {
            var refEvalValue = (RefEval) value;
            value = refEvalValue
                    .getInnerValueEval(refEvalValue.getFirstSheetIndex());
        }
        if (value instanceof TwoDEval) {
        	TwoDEval base = (TwoDEval) value;
        	// assume a single cell ref
			value = base.getValue(0,0);
        } 

        if (value instanceof StringEval) {
            return ((StringEval) value).getStringValue();
        }