[prev in list] [next in list] [prev in thread] [next in thread] 

List:       poi-dev
Subject:    [Bug 63302] New: [PATCH] Formula evaluation of names with offset or row function is incorrect
From:       bugzilla () apache ! org
Date:       2019-03-29 16:47:56
Message-ID: bug-63302-47293 () https ! bz ! apache ! org/bugzilla/
[Download RAW message or body]

https://bz.apache.org/bugzilla/show_bug.cgi?id=63302

            Bug ID: 63302
           Summary: [PATCH] Formula evaluation of names with offset or row
                    function is incorrect
           Product: POI
           Version: 4.0.x-dev
          Hardware: All
                OS: All
            Status: NEW
          Severity: normal
          Priority: P2
         Component: SS Common
          Assignee: dev@poi.apache.org
          Reporter: jwhite@venacorp.com
  Target Milestone: ---

Created attachment 36504
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=36504&action=edit
Proposed patch

Overview

Formula evaluation of names with OFFSET or ROW function is incorrect

  Steps to Reproduce

1) In a new workbook, put values 2, 5, 3, 7 in cells Sheet1!A1:A4:

   | A 
---+----
 1 | 2
 2 | 5
 3 | 3
 4 | 7

2) Create a named range offsetFormula with the refers to formula
=OFFSET(Sheet1!$A$1:$A$4,2,0,2,1). Create a named range rowFormula with the
refers to formula =ROW().

3) Set cell C5 to =SUM(offsetFormula). Set cell C6 to =rowFormula.

Evaluate cells C5 and C6 with the workbook's FormulaEvaluator.

  Actual Results

Cell C5 evaluates to 0.
Evaluation of cell C6 throws a RuntimeException.

  Expected Results

Cell C5 should be evaluated to 10, the sum of Sheet1!A3:A4.
Cell C6 should be evaluated to 6, the row number of cell C6.

  Additional Information

In the process of evaluating the formula =SUM(offsetFormula), when
WorkbookEvaluator.evaluateFormula sees the named range, it calls
getEvalForNameRecord to evaluate the named range, calling evaluateNameFormula
on the refers to formula of the name. evaluateNameFormula calls getEvalForPtg
when the formula has a single Ptg and calls WorkbookEvaluator.evaluateFormula
when there is more than one Ptg.

getEvalForPtg cannot evaluate a Ptg of type FuncVarPtg, causing the
RuntimeException. Evaluating the Ptg in this case through
WorkbookEvaluator.evaluateFormula correctly returns the row number of the cell
whose formula is being evaluated.

When the formula is evaluated by WorkbookEvaluator.evaluateFormula and
OperationEvaluationContext.isSingleValue() is TRUE. evaluateFormula returns a
single value "unwrapped" based on the context's row and column. In the case of
an OFFSET formula, this returns some value in the range of the OFFSET formula
result, or an error value if beyond the range. The correct evaluation of the
formula should be a type such as LazyAreaEval, which IS the value before
WorkbookEvaluator.dereferenceResult is applied. Calling
WorkbookEvaluator.evaluateFormula with an
OperationEvaluationContext.isSingleValue() FALSE fixes the issue of incorrect
evaluation of offsetFormula.

I've attached a patch that adds the reproduction case to the test
TestWorkbookEvaluator.testNamesInFormulas and makes the changes to
evaluateNameFormula as described.

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

[prev in list] [next in list] [prev in thread] [next in thread]