[Bug 62969] New: HYPERLINK() function needs a way to return link reference when display label is also specified

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 62969] New: HYPERLINK() function needs a way to return link reference when display label is also specified

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62969

            Bug ID: 62969
           Summary: HYPERLINK() function needs a way to return link
                    reference when display label is also specified
           Product: POI
           Version: 4.0.x-dev
          Hardware: All
                OS: All
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

This function has special behavior in Excel.  It is dynamically displayed as a
link, using the first argument as the target and the second, optional one as
the display text.  If there is only one argument, the link address is used for
display.

The POI function currently returns the displayed value, which is correct in
most cases, as that's what the user sees as the cell value.

However, if one wants to display the cell contents in some interactive format,
there is currently no way to get the address from the formula in the presence
of a defined display label, other than to read the raw formula string, parse it
for the first function argument, and then evaluate that string as a formula
value.

While that is possible, it is fragile and requires proper handling of the
complex formula syntax in the raw value.

This issue is to track discussion from the related POI Users email list thread,
and document design decisions and implementation.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62969] HYPERLINK() function needs a way to return link reference when display label is also specified

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62969

--- Comment #1 from Greg Woolsey <[hidden email]> ---
Current thinking involves augmenting FormulaEvaluator with a flag (or facility
for multiple flags, perhaps an EnumMap?) to control which value is returned by
the function in that invocation/evaluation.

The Formula interface would take the OperationEvaluationContext in which it is
being called, which would have access to the flags via the WorkbookEvaluator.
WorkbookEvaluator appears to be the only class that ever constructs
OperationEvaluationContext objects and uses them to evaluate functions.

This would enable a given cell to be evaluated in the context of cached results
in two different ways based on the state of the flags for the current
FormulaEvaluator.

Testing will be needed to see if the cache needs to be cleared when flags
change, in order to properly re-process the formula.

Also, this could have side effects for other cells whose formulas reference the
cell with the HYPERLINK() formula call - we would want dependent cell
calculations to continue to always use the display label result.

Perhaps that means the cache mechanism needs to know about the presence or
absence of flags also, and skip cache reads and writes when flags are set.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62969] HYPERLINK() function needs a way to return link reference when display label is also specified

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62969

--- Comment #2 from Greg Woolsey <[hidden email]> ---
Testing in Excel 2016/Office 365 shows that a cell with a formula containing
the HYPERLINK() function anywhere in the formula converts the entire cell value
to a link.

If there is additional formula contents before or after the function call,
those results are appended to BOTH the address and the display label. In my
opinion, that's never going to be useful to anyone, and may be a case we don't
need to handle, at least not at first.

In practice, I think POI only needs to handle cases where the cell formula is
just a single call to HYPERLINK(), regardless of the complexity of the argument
formulas.

This may mean the only useful use case is to access the address from the Cell
object, similar to accessing hyperlink relations currently.

Cell could have these methods:

public boolean isHyperlinkFormula();
public String getHyperlinkFormulaAddress();

The first would just check if the formula starts with "HYPERLINK(" case
insensitive.

The second would set up a new FormulaEvaluator with the proper flag set, and
evaluate the cell formula in that context.

In the future, if performance with lots of link function calls is an issue,
perhaps we could investigate ways to reuse the same FormulaEvaluator, but for
safety I'd avoid it if possible, so we don't mess with related/dependent cell
values expecting the display text result.

I like this, as it could reduce the public API impact to just these method
calls, and avoid misuse of the flag, which could result in more bug reports.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62969] HYPERLINK() function needs a way to return link reference when display label is also specified

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62969

--- Comment #3 from Dominik Stadler <[hidden email]> ---
Created attachment 36288
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=36288&action=edit
Workaround by overloading FunctionEval.functions

I had the same problem some time ago and used a workaround by putting a class
into the org.apache.poi.ss.formula.eval package and accessing the protected
FunctionEval.functions-array this way, see the attached sample code.

// MyHyperlink returns the URL Value instead of the normal text-value returned
usually by the Hyperlink function
Function func = new MyHyperlink();

BuiltinFunctionsOverloader.replaceBuiltinFunction(359, func);


Maybe we can make it possible to override functions this way in FunctionEval
natively via a static FunctionEval.replaceBuiltinFunction() and provide the
alternative implementation of Hyperlink together with sample code? This would
spare us from breaking existing interfaces.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [Bug 62969] HYPERLINK() function needs a way to return link reference when display label is also specified

Greg Woolsey
That replacement method is no longer available, and the maps appear to be
static, so making a change would affect all threads, so I don't like that
option.  Perhaps a separate override map local to an evaluator instance
would be a possibility, but then it would need to be available in the
proper function lookup context, which may or may not be a simple option.

Good thought though, about allowing function customization, especially if
there are other functions that have side effects or multiple possible
values needed to implement Excel behavior.

On Sat, Dec 1, 2018 at 12:54 AM <[hidden email]> wrote:

> https://bz.apache.org/bugzilla/show_bug.cgi?id=62969
>
> --- Comment #3 from Dominik Stadler <[hidden email]> ---
> Created attachment 36288
>   --> https://bz.apache.org/bugzilla/attachment.cgi?id=36288&action=edit
> Workaround by overloading FunctionEval.functions
>
> I had the same problem some time ago and used a workaround by putting a
> class
> into the org.apache.poi.ss.formula.eval package and accessing the protected
> FunctionEval.functions-array this way, see the attached sample code.
>
> // MyHyperlink returns the URL Value instead of the normal text-value
> returned
> usually by the Hyperlink function
> Function func = new MyHyperlink();
>
> BuiltinFunctionsOverloader.replaceBuiltinFunction(359, func);
>
>
> Maybe we can make it possible to override functions this way in
> FunctionEval
> natively via a static FunctionEval.replaceBuiltinFunction() and provide the
> alternative implementation of Hyperlink together with sample code? This
> would
> spare us from breaking existing interfaces.
>
> --
> You are receiving this mail because:
> You are the assignee for the bug.
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

[Bug 62969] HYPERLINK() function needs a way to return link reference when display label is also specified

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62969

--- Comment #4 from Greg Woolsey <[hidden email]> ---
PJ Fanning suggested another path, creating Hyperlink*Eval classes that extend
the existing Eval classes, and implement an additional interface to provide

String getAddress();

that way eval type checks will still pass, but an additional check can be made
to see if the result is a hyperlink, and if so, get the target address.

I like it - requires maintaining twice as many eval classes, but there aren't
that many, and they don't change.

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 62969] HYPERLINK() function needs a way to return link reference when display label is also specified

Bugzilla from bugzilla@apache.org
In reply to this post by Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=62969

--- Comment #5 from PJ Fanning <[hidden email]> ---
My suggestion was to have `public Hyperlink getHyperlink()` which I think is
better than `public String getAddress()` because Hyperlink as all the Hyperlink
data (URL, display name, etc).

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]