[Bug 62275] New: vlookup function with "empty" fourth argument can not be processed.

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

[Bug 62275] New: vlookup function with "empty" fourth argument can not be processed.

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

            Bug ID: 62275
           Summary: vlookup function with "empty" fourth argument can not
                    be processed.
           Product: POI
           Version: 3.17-FINAL
          Hardware: PC
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

vlookup function with "empty" fourth argument.
ex. vlookup(A1,E1:F10,2,)


An exception is raised.
Unexpected eval type (org.apache.poi.ss.formula.eval.MissingArgEval)


Sorry for bad explanation.

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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

--- Comment #1 from Taiki Sugawara <[hidden email]> ---
I also encountered this bug.

I think it will be fixed that if we check the last argument is MissingArgEval
in following classes´╝Ü

- Var1or2ArgFunction
- Var2or3ArgFunction
- Var3or4ArgFunction

What do you think?

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

--- Comment #2 from PJ Fanning <[hidden email]> ---
Can you check with the latest nightly build?
https://poi.apache.org/download.html#nightly

I think that we might have added a fix for this already.

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

--- Comment #3 from [hidden email] ---
This bug is not fixed.

Even in the latest nightly build, it is reproduced with the following code.

CreationHelper crateHelper = wb.getCreationHelper();
FormulaEvaluator eval = crateHelper.createFormulaEvaluator();
Cell cell = row.getCell(columnIndex);
eval.evaluate(cell);

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

--- Comment #4 from PJ Fanning <[hidden email]> ---
Could you provide a full test case?

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

--- Comment #5 from [hidden email] ---
It reproduced with the following test case.

        public void test62275_bug() throws IOException {
            Workbook wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet();
            Row row = sheet.createRow(0);

            Cell cell = row.createCell(0);
            cell.setCellFormula("vlookup(A2,B1:B5,2,)");

            CreationHelper crateHelper = wb.getCreationHelper();
            FormulaEvaluator eval = crateHelper.createFormulaEvaluator();
            eval.evaluate(cell);

            wb.close();
        }

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

pj.fanning
The current code blows up because the 4th param is empty (resulting in a
MissingArgEval issue) - the code will pass is the 4th param is set to the
true (the default for this param)  
https://www.techonthenet.com/excel/formulas/vlookup.php



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html

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

Reply | Threaded
Open this post in threaded view
|

[Bug 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

PJ Fanning <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|NEW                         |RESOLVED
         Resolution|---                         |FIXED

--- Comment #6 from PJ Fanning <[hidden email]> ---
https://svn.apache.org/viewvc?view=revision&revision=1836857 was merged

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

[hidden email] changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|RESOLVED                    |REOPENED
         Resolution|FIXED                       |---
                 CC|                            |dolphin.in.the.sky.51@gmail
                   |                            |.com

--- Comment #7 from [hidden email] ---
Thank you for fixing.
but,In EXCEL, when the fourth argument of the vlookup function is "empty", it
operates in the same way as when FALSE is specified.
I suggest adding the following to resolveRangeLookupArg method of LookupUtils.

if (valEval instanceof MissingArgEval) {
    return false;
}

Thank you.

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

Dominik Stadler <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
         Resolution|---                         |FIXED
             Status|REOPENED                    |RESOLVED

--- Comment #8 from Dominik Stadler <[hidden email]> ---
This was actually already fixed some time ago via r1836857, the given test-case
and some other cases work fine now.

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

--- Comment #9 from Javen O'Neal <[hidden email]> ---
(In reply to dolphin.in.the.sky.51 from comment #7)
> In EXCEL, when the fourth argument of the vlookup function is "empty",
> it operates in the same way as when FALSE is specified.

https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
> Optionally, you can specify TRUE if you want an approximate
> match or FALSE if you want an exact match of the return
> value. If you don't specify anything, the default value will
> always be TRUE or approximate match.

In reply to Dominik Stadler from comment #8)
> This was actually already fixed some time ago via r1836857, the given
> test-case and some other cases work fine now.

>   try {
> +     isRangeLookup = LookupUtils.resolveRangeLookupArg(range_lookup, srcRowIndex, srcColumnIndex);
> + } catch(RuntimeException e) {
> +     isRangeLookup = true;
> + }

It appears that POI defaults the fourth argument to true if it's omitted.

--
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 62275] vlookup function with "empty" fourth argument can not be processed.

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=62275

--- Comment #10 from Dominik Stadler <[hidden email]> ---
At least the documentation at
https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
indicates that the default is "true", was the statement about Excel using
"false" based on actual experiments?

--
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]