You need to return ArrayEval and it is not yet supported. Unfortunately you can't implement it without going into the source code of POI because this functionality touches the core of the formula evaluator .
There is a pending patch with support for array formulas: https://issues.apache.org/bugzilla/show_bug.cgi?id=48292 that in theory should help. We do plan to apply it but unfortunately it is no that easy. The patch is old and the divergence between it and trunk is big and no one volunteered to finish this task. Yegor On Wed, Apr 25, 2012 at 1:45 PM, Pantelis Sopasakis <[email protected]> wrote: > Hi Yegor, > Thanks a lot for your reply and for clarifying these things. How can I > create an AreaEval object from a double[][]? You mentioned that arrays are > not supported. Indeed I tried to run a test providing an array and I get the > following error: > > Exception in thread "main" java.lang.RuntimeException: Unexpected ptg class > (org.apache.poi.ss.formula.ptg.ArrayPtg) > at > org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:634) > at > org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:493) > at > org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:287) > at > org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:229) > at > org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:354) > at > org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:216) > at > org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:327) > at > org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAll(HSSFFormulaEvaluator.java:346) > at simpleboxapi.SimpleBoxAPI.main(SimpleBoxAPI.java:54) > > Is there a way to implement this functionality on my own without going into > the source code of POI? Again, thanks a lot! > > Best, > Pantelis Sopasakis > > > On Apr 25, 2012, at 11:33 AM, Yegor Kozlov wrote: > >> The array of ValueEval args contains all arguments passed to the >> function. If in Excel you pass N arguments, e.g. FUNC(arg1, arg2, >> arg3, ...., argN) >> then on the POI side the function takes it as a array of ValueEvals : >> {ve1, ve2, ve3, ...veN} where ve1 - ven are instances of the ValueEval >> interface. >> >> MINVERSE takes one argument so your code will receive a 1-length array >> of ValueEvals. >> >> The concrete type of ValueEval depends on what you pass in Excel: >> range, array or reference. >> In you code you should check the actual type with instanceof: >> >> case 1: passing a 2D area, e.g. MINVERSE(A1:C5) >> >> if( args[0]instanceof TwoDEval){ >> TwoDEval area = (TwoDEval )arg; >> } >> >> case 2: passing a reference to a 2D area, e.g. MINVERSE(A2) where A2 >> is a reference to B2: C5 >> >> else if( args[0]instanceof RefEval){ >> ; >> } >> >> case 3: passing a array, e.g. MINVERSE({1,2,3;4,5,6;7,8,9}) >> Unfortunately arrays are not yet supported. This means that the syntax >> like will not work and POI will throw a exception. >> >> >>> And then, how do I output an area back to the Excel spreadsheet? >> >> What is the calculation result? If it is a number then return >> NumberEval. If it is a range then return AreaEval. >> If the result is a array then POI cannot handle it. Array evals are >> not yet supported. >> >> Yegor >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: [email protected] >> For additional commands, e-mail: [email protected] >> >> > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
