Hyperlink() special case

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

Hyperlink() special case

Greg Woolsey
This is a unique (as far as I can see) Excel function, in that the
evaluation result is one thing, but the function has a second value
required for implementation in a client.

The first argument to the function is the target URL, the second argument
is optional display text.

The current implementation returns the display text, if present, as the
function value.  This is correct, as that's the value displayed, but if you
want to actually implement a link, one needs the URL argument also.

There currently is no way I can see to easily get that. I see 5 years ago
it was possible to override POI function implementations, which allowed a
hacky way to get at the value [1].  This is not available in 3.17 or 4.0.x.

I'm not finding a quick or simple-enough-to-be-satisfactorily-stable way to
get at that first arg when two are present in a cell formula.  Especially
if the argument is built from a complex formula, as is often the case with
URLs, as sanitization and escaping are needed.

I'd be satisfied with a way to override a function implementation for a
given evaluation run only, or even parse the formula to find function
objects and their arguments.  However, the process to get from a Ptg[] to a
function call and arguments involves a complex evaluation loop in
OperationEvaluationContext, and I'm not excited about messing with that.

For now I think I'll have to live with parsing the formula string to
extract the argument text and evaluating it to get the URL.

For a future POI release, two ideas I've had so far, worth discussing, are:

A.
Hyperlink function evaluation returns subclasses of the input ValueEval
that references the "other" relevant value.  Could be used by other
functions with similar side effect requirements, if there are any.

B.
Add a default "ValueEval get|setOtherValue()" methods to the ValueEval
interface that do nothing and return null, and override it in implementing
classes. Hyperlink and other functions would set the "Other" value as
needed.

I'm not in love with either solution, but that's what I've got so far.

Greg

[1] https://stackoverflow.com/questions/15757243
Reply | Threaded
Open this post in threaded view
|

Re: Hyperlink() special case

Nick Burch-2
On Thu, 29 Nov 2018, Greg Woolsey wrote:
> The first argument to the function is the target URL, the second argument
> is optional display text.
>
> The current implementation returns the display text, if present, as the
> function value.  This is correct, as that's the value displayed, but if you
> want to actually implement a link, one needs the URL argument also.

I'd lean towards one of:
  * ThreadLocal that the hyperlink function checks (with helpful setter and
    clearer on it) to toggle between returning the text or link
  * setter on FormulaEvaluator that lets you toggle, then pass that
    somehow to the hyperlink function
  * subclass of FormulaEvaluator with extra evaluateCell method that
    returns the link, plus similar to above way to pass flag to function

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Hyperlink() special case

pj.fanning
How about `public final class HyplerlinkEval implements HyperlinkValueEval,
StringValueEval` where HyperlinkValueEval has a `Hyperlink
getHyperlinkValue()` method? This way you can treat the result as a string
and get the current behaviour (to maintain compatibility) or you can treat
the result as a Hyperlink and get access to its link and its display text.



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Hyperlink() special case

Greg Woolsey
In reply to this post by Nick Burch-2
Thanks for the thoughts.  I like the idea of handling behavior modification
through the FormulaEvaluator, as those can be discarded and recreated, and
are tied to a specific workbook.  I don't like ThreadLocal options, even
though POI is not at all Thread-safe, as it is always harder to debug and
maintain. Especially if one thread is handling multiple workbooks, it could
be confusing to see side effects.

Fresh eyes this morning show me that Function.evaluate() is only called
from 3 places - OperationEvaluationFactory, and two implementations of
Function that need a variation on recursion (SUBTOTAL and conditional
formatting ABOVE_AVERAGE).

Further, OperationEvaluationFactory calls it from a static method that
has OperationEvaluationContext as a parameter. That context class already
has a flag for single vs. multiple results.  Perhaps that's the right place
to store additional flags, like one for identifying which return value to
use for HYPERLINK.

The interface for Function would change to add OperationEvaluationContext
as a parameter to evaluate().  The recursive calls would then have this
value available to pass to their child function calls, and only the
instantiation path for the standard factory call would need adjusting to
handle evaluation flags of any sort.

The interface could then have a default method with the current signature,
and call the updated method with a null context.

Only functions that actually need or care about flags would be affected
then, and could be coded to handle a null context by invoking the existing
behavior, only adjusting evaluation in the presence of their specific flag
in a non-null context.

Work remains to figure out what calling path makes the most sense for
actually setting a flag in the context, but I would anticipate only
implementing it in one path, perhaps as you suggest through the
FormulaEvaluator via instance specific getter/setter methods for hard-coded
or parameterized flags.


On Thu, Nov 29, 2018 at 11:51 PM Nick Burch <[hidden email]> wrote:

> On Thu, 29 Nov 2018, Greg Woolsey wrote:
> > The first argument to the function is the target URL, the second argument
> > is optional display text.
> >
> > The current implementation returns the display text, if present, as the
> > function value.  This is correct, as that's the value displayed, but if
> you
> > want to actually implement a link, one needs the URL argument also.
>
> I'd lean towards one of:
>   * ThreadLocal that the hyperlink function checks (with helpful setter and
>     clearer on it) to toggle between returning the text or link
>   * setter on FormulaEvaluator that lets you toggle, then pass that
>     somehow to the hyperlink function
>   * subclass of FormulaEvaluator with extra evaluateCell method that
>     returns the link, plus similar to above way to pass flag to function
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Hyperlink() special case

Greg Woolsey
In reply to this post by pj.fanning
Just saw this - Gmail puts all emails from Yahoo in spam for some reason I
haven't investigated.

I like this, it was one of my first thoughts, but the display value
parameter is not always a String - it can be anything, including an error
result, in my testing.

So there would need to be HyperlinkEval instances for each type of existing
Eval class.  Possible, but a bit to maintain.  Advantage is the ability to
use type checks to see what the cell value is, rather than looking at the
formula string.  Probably less to maintain and less invasive than adding to
the evaluation pathway.

I'll add it to the issue and hopefully be able to play with it in a month
or so after wrapping up my current project.

On Fri, Nov 30, 2018 at 12:28 AM pj.fanning <[hidden email]> wrote:

> How about `public final class HyplerlinkEval implements HyperlinkValueEval,
> StringValueEval` where HyperlinkValueEval has a `Hyperlink
> getHyperlinkValue()` method? This way you can treat the result as a string
> and get the current behaviour (to maintain compatibility) or you can treat
> the result as a Hyperlink and get access to its link and its display text.
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>