poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bugzi...@apache.org
Subject [Bug 56106] End of Line character in formula throws exception in XSSFFormulaEvaluator.evaluate()
Date Wed, 05 Feb 2014 20:38:28 GMT

--- Comment #7 from David Crocker <david.crocker@nrel.gov> ---
Here is the code for the incomplete function in Index:

    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval
arg0, ValueEval arg1,
            ValueEval arg2, ValueEval arg3) {
        throw new RuntimeException("Incomplete code"
                + " - don't know how to support the 'area_num' parameter
        // Excel expression might look like this "INDEX( (A1:B4, C3:D6, D2:E5
), 1, 2, 3)
        // In this example, the 3rd area would be used i.e. D2:E5, and the
overall result would be E2
        // Token array might be encoded like this: MemAreaPtg, AreaPtg,
AreaPtg, UnionPtg, UnionPtg, ParenthesesPtg
        // The formula parser doesn't seem to support this yet. Not sure if the
evaluator does either

Here is some pseudocode that starts to address the problem:

if arg3 is an integer {
   ValueEval _arg0SubRange = arg0[arg3]
   if arg1 not null and arg1 != 0 {
      if arg2 is null or arg2 == 0 {
         if row[arg1] not out of bounds in _arg0SubRange {
            return cell range _arg0SubRange[row[arg1]]
         } else {
            return #REF
      } else {
         if cell[arg1,arg2] not out of bounds in _arg0SubRange {
            return cell reference _arg0SubRange[arg1,arg2]
         } else {
            return #REF
   } else {
      if arg2 is null or arg2 == 0 {
         return #REF
      } else {
         if col[arg2] not out of bounds in _arg0SubRange {
            return cell range _arg0SubRange[col[arg2]]
         } else {
            return #REF
} else {
   return #REF

Here's the help detail from Excel:

Reference form
Returns the reference of the cell at the intersection of a particular row and
column. If the reference is made up of nonadjacent selections, you can pick the
selection to look in.

INDEX(reference, row_num, [column_num], [area_num])The INDEX function syntax
has the following arguments (argument: A value that provides information to an
action, an event, a method, a property, a function, or a procedure.):

Reference  Required. A reference to one or more cell ranges.
If you are entering a nonadjacent range for the reference, enclose reference in

If each area in reference contains only one row or column, the row_num or
column_num argument, respectively, is optional. For example, for a single row
reference, use INDEX(reference,,column_num).
Row_num  Required. The number of the row in reference from which to return a
Column_num  Optional. The number of the column in reference from which to
return a reference.
Area_num  Optional. Selects a range in reference from which to return the
intersection of row_num and column_num. The first area selected or entered is
numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area
For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then
area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is
the range G1:H4.
After reference and area_num have selected a particular range, row_num and
column_num select a particular cell: row_num 1 is the first row in the range,
column_num 1 is the first column, and so on. The reference returned by INDEX is
the intersection of row_num and column_num. 
If you set row_num or column_num to 0 (zero), INDEX returns the reference for
the entire column or row, respectively.

Row_num, column_num, and area_num must point to a cell within reference;
otherwise, INDEX returns the #REF! error value. If row_num and column_num are
omitted, INDEX returns the area in reference specified by area_num. 
The result of the INDEX function is a reference and is interpreted as such by
other formulas. Depending on the formula, the return value of INDEX may be used
as a reference or as a value. For example, the formula
CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL
function uses the return value of INDEX as a cell reference. On the other hand,
a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into
the number in cell B1.

You are receiving this mail because:
You are the assignee for the bug.

To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

View raw message