Bug 44403 - MID Excel evaluates incorrectly
Summary: MID Excel evaluates incorrectly
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: PC Windows XP
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2008-02-13 00:40 UTC by David Webster
Modified: 2008-02-20 10:15 UTC (History)
0 users

svn diff of 2 modified files (Mid.java and a test suite) (8.25 KB, patch)
2008-02-15 17:55 UTC, Josh Micich
Details | Diff
tar bz2 of 3 added files (one test and two eval utility classes) (6.10 KB, patch)
2008-02-15 17:56 UTC, Josh Micich
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
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