You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2011/04/05 18:06:36 UTC
DO NOT REPLY [Bug 51024] New: VLOOKUP/HLOOKUP default behaviour
https://issues.apache.org/bugzilla/show_bug.cgi?id=51024
Summary: VLOOKUP/HLOOKUP default behaviour
Product: POI
Version: unspecified
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: minor
Priority: P2
Component: POI Overall
AssignedTo: dev@poi.apache.org
ReportedBy: thomas.themel@mirai-solutions.com
Hi,
There seems to be a problem with "empty" arguments in Excel formulae, at least
for VLOOKUP and HLOOKUP. Excel accepts formulae of the form
"=VLOOKUP(B2;B5:C7;2;)" as equivalent to "=VLOOKUP(B2;B5:C6;2)", but POI fails
to evaluate this field with "Unexpected eval type
(org.apache.poi.ss.formula.eval.MissingArgEval)".
Probably Var3or4ArgFunction and its cousins should check for the last argument
to be of type MissingArgEval as well?
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 51024] VLOOKUP/HLOOKUP default behaviour
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=51024
Giuseppe Nespolino <g....@gmail.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|RESOLVED |REOPENED
Resolution|WORKSFORME |---
OS|Windows XP |All
--- Comment #2 from Giuseppe Nespolino <g....@gmail.com> ---
I am experiencing this bug in poi-3.9
It happens to me with an xlsx file, so maybe the test case should consider XSSF
workbook.
The formula contains VLOOKUP(refereceArg;range;2;) so the parser believes it is
a 4 args formula and when calling LookupUtils.resolveRangeLookupArg with a
MissingArgEval it throws a runtime exception.
I believe it is enough to add:
if (valEval instanceof MissingArgEval) {
return true; /* true is default is last arg is missing */
}
in LookupUtils.resolveRangeLookupArg to solve this bug, or maybe it breaks
something and it should be addressed at parser level or even at
Var3or4ArgFunction, making args.length be 3 if last one is an instance of
MissingArgEval.
If needed i can attach an xlsx file and some code to reproduce the bug
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 51024] VLOOKUP/HLOOKUP default behaviour
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=51024
Nick Burch <ni...@alfresco.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |WORKSFORME
--- Comment #1 from Nick Burch <ni...@alfresco.com> 2011-04-08 11:25:21 EDT ---
I've just added a unit test for this, which passes just fine:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r1 = s.createRow(0);
HSSFRow r2 = s.createRow(1);
r1.createCell(0).setCellValue("v A1");
r2.createCell(0).setCellValue("v A2");
r1.createCell(1).setCellValue("v B1");
HSSFCell c = r1.createCell(4);
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
c.setCellFormula("VLOOKUP(\"v A1\", A1:B2, 1)");
assertEquals("v A1", eval.evaluate(c).getStringValue());
c.setCellFormula("VLOOKUP(\"v A1\", A1:B2, 1, 1)");
assertEquals("v A1", eval.evaluate(c).getStringValue());
c.setCellFormula("VLOOKUP(\"v A1\", A1:B2, 1, )");
assertEquals("v A1", eval.evaluate(c).getStringValue());
c.setCellFormula("HLOOKUP(\"v A1\", A1:B2, 1)");
assertEquals("v A1", eval.evaluate(c).getStringValue());
c.setCellFormula("HLOOKUP(\"v A1\", A1:B2, 1, 1)");
assertEquals("v A1", eval.evaluate(c).getStringValue());
c.setCellFormula("HLOOKUP(\"v A1\", A1:B2, 1, )");
assertEquals("v A1", eval.evaluate(c).getStringValue());
Please make sure you're using a new enough version of POI
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org