Bug 44450

Summary: [patch] Added implementations for VLOOKUP and HLOOKUP
Product: POI Reporter: Josh Micich <josh>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal    
Priority: P2    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: Other   
OS: other   
Attachments: svn diff of 8 modified files
tar bz2 of 5 new files (2 identical to those added in patch 44403)
Svn patch showing what was applied vs trunk when the tests failed
svn diff of 9 changed files
tar bz2 of 3 added files

Description Josh Micich 2008-02-18 23:20:50 UTC
Also added a very basic implementation of LOOKUP and refactored much code in 
MATCH.

Two files added in 44403 were also added in this patch 
EvaluationException.java  OperandResolver.java.  The content is identical.

One file modified in 44449 (ValueRecordsAggregate.java) has a 'hack' of that 
same bug-fix made in this patch.  The version from patch 44449 is much better.

New junit TestLookupFunctionsFromSpreadsheet and a dedicated test data 
spreadsheet were added to test specific behaviour of these test cases more 
thoroughly.
Comment 1 Josh Micich 2008-02-18 23:25:17 UTC
Created attachment 21556 [details]
svn diff of 8 modified files
Comment 2 Josh Micich 2008-02-18 23:26:37 UTC
Created attachment 21557 [details]
tar bz2 of 5 new files (2 identical to those added in patch 44403)
Comment 3 Nick Burch 2008-02-21 08:02:17 UTC
This is blowing up for me, on test
org.apache.poi.hssf.record.formula.functions.TestLookupFunctionsFromSpreadsheet

    [junit] Testcase: testFunctionsFromTestSpreadsheet took 1.284 sec
    [junit]     FAILED
    [junit] 5 sheets(s) failed with 68 evaluation(s).  There were 0 successful
sheets(s) and 1 function(s) without error
    [junit] junit.framework.AssertionFailedError: 5 sheets(s) failed with 68
evaluation(s).  There were 0 successful sheets(s) and 1 function(s) without error
    [junit]     at
org.apache.poi.hssf.record.formula.functions.TestLookupFunctionsFromSpreadsheet.testFunctionsFromTestSpreadsheet(TestLookupFunctionsFromSpreadsheet.java:216)

I'll attach a svn diff so you can see what I have and haven't applied. Any
chance you could spot what's gone wrong?
Comment 4 Nick Burch 2008-02-21 08:03:21 UTC
Created attachment 21574 [details]
Svn patch showing what was applied vs trunk when the tests failed
Comment 5 Josh Micich 2008-02-21 12:58:09 UTC
On first inspection it looks like the edits for Vlookup.java and Hlookup.java 
didn't make it through.  It is probably easier for me just to re-do this 
patch, since there were a lot of POI changes applied recently.

While working on a future patch (regarding formula parsing) I found a better 
home for the raw excel error codes stuff - HSSFErrorConstants.  For the re-do 
of this patch (44450) I re-worked the error code related stuff accordingly.
Comment 6 Josh Micich 2008-02-21 13:01:09 UTC
Created attachment 21576 [details]
svn diff of 9 changed files

 - Added implementation for HLOOKUP, VLOOKUP, and LOOKUP. Refactored Match to
use LookupUtils.
 - Found a better home for constants class ErrorEval.ErrorCode.  Merged
functionality with HSSFErrorConstants.	ErrPtg updated.
 - Added javadoc on StringValueEval, and null check in StringEval constructor.
Comment 7 Josh Micich 2008-02-21 13:02:27 UTC
Created attachment 21577 [details]
tar bz2 of 3 added files

LookupUtils.java
TestLookupFunctionsFromSpreadsheet.java
LookupFunctionsTestCaseData.xls
Comment 8 Nick Burch 2008-02-22 03:24:06 UTC
Thanks, this new version applied nicely and tests all passed.

I've committed it to svn trunk