Bug 44403

Summary: MID Excel evaluates incorrectly
Product: POI Reporter: David Webster <dave.webster>
Component: HSSFAssignee: POI Developers List <dev>
Severity: normal    
Priority: P3    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: svn diff of 2 modified files (Mid.java and a test suite)
tar bz2 of 3 added files (one test and two eval utility classes)

Description David Webster 2008-02-13 00:40:18 UTC
Overview Description
The Excel function MID(text, start_num, num_chars) which has been implemented in
POI does not evaulate correctly.  It appears the function actually implements
MID(text, start_num, *end_num*) instead.

Steps to Reproduce
1/ Create a worksheet with the formula =MID("galactica", 3, 4) in a cell
2/ Notice in Excel, it evaulates to "lact" as expected
3/ Parse the file evaulating cell formulas 
(i.e. using 
HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate(cell);
for example)
4/ Output will show "la" for the evaulated cell from 1/

Actual Results
As stated, actual results are "la"

Expected Results
As stated, expected results are "lact", given the Excel function signature for
MID is "MID(text, start_num, num_chars)" - Excel 2003

Build Date & Platform
2008-02-07 on Windows XP

Additional Information
In ./src/scratchpad/src/org/apache/poi/hssf/record/formula/functions/Mid.java, I
believe the following change needs to be made:

line 93:

   retval = new StringEval(str.substring(startNum - 1, numChars));

should be replaced with:

  retval = new StringEval(str.substring(startNum - 1, (numChars + startNum - 1)));

Much thanks as always
Comment 1 Nick Burch 2008-02-15 03:45:18 UTC
Thanks for the patch, fixed in svn, and a test added
Comment 2 Josh Micich 2008-02-15 17:53:42 UTC
Sorry to re-open this bug but while investigating I found many more problems 
with MID().  I am attaching a patch which contains junit tests to show the 
bugs and the fix to MID.
Comment 3 Josh Micich 2008-02-15 17:55:24 UTC
Created attachment 21541 [details]
svn diff of 2 modified files (Mid.java and a test suite)
Comment 4 Josh Micich 2008-02-15 17:56:22 UTC
Created attachment 21542 [details]
tar bz2 of 3 added files (one test and two eval utility classes)
Comment 5 Nick Burch 2008-02-20 10:15:02 UTC
Applied to svn trunk, thanks