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
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