| Summary: | MID Excel evaluates incorrectly | ||
|---|---|---|---|
| Product: | POI | Reporter: | David Webster <dave.webster> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| 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) |
||
Thanks for the patch, fixed in svn, and a test added 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. Created attachment 21541 [details]
svn diff of 2 modified files (Mid.java and a test suite)
Created attachment 21542 [details]
tar bz2 of 3 added files (one test and two eval utility classes)
Applied to svn trunk, thanks |
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