summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorEike Rathke <erack@redhat.com>2017-05-20 20:09:01 +0200
committerEike Rathke <erack@redhat.com>2017-05-22 11:00:56 +0200
commit91bdd41bb0092e7ed9ef4e5b782650be3cfd9440 (patch)
tree0365db0fae045133e5fe5de993fe70123b4930a0
parentf6703ce85f9399b94958601ed6623865b205c5fb (diff)
Handle reference list as array in IterateParameters(), tdf#58874
This in array context now returns the expexted array for an expression like =SUBTOTAL(9,OFFSET(A1,ROW(1:3),0,2)) where OFFSET returns an array (svRefList) of references, here A2:A3 A3:A4 A4:A5 and SUBTOTAL(9,...) sums each of the references independently to return an array of three sums. Currently works with SUM, COUNT, COUNT2, AVERAGE, PRODUCT and SUMSQ. MAX and MIN are not handle in IterateParameters() and need an extra implementation, or rather move them into IterateParameters() as well? Change-Id: I03b7d1cccf78c1a831348106432126aec8d1f519
-rw-r--r--sc/source/core/tool/interpr6.cxx142
1 files changed, 131 insertions, 11 deletions
diff --git a/sc/source/core/tool/interpr6.cxx b/sc/source/core/tool/interpr6.cxx
index 72c82e9474a7..0b36b129b48f 100644
--- a/sc/source/core/tool/interpr6.cxx
+++ b/sc/source/core/tool/interpr6.cxx
@@ -459,9 +459,49 @@ void IterateMatrix(
}
}
+static double lcl_IterResult( ScIterFunc eFunc, double fRes, double fMem, sal_uLong nCount )
+{
+ switch( eFunc )
+ {
+ case ifSUM:
+ fRes = ::rtl::math::approxAdd( fRes, fMem );
+ break;
+ case ifAVERAGE:
+ fRes = sc::div( ::rtl::math::approxAdd( fRes, fMem ), nCount);
+ break;
+ case ifCOUNT2:
+ case ifCOUNT:
+ fRes = nCount;
+ break;
+ case ifPRODUCT:
+ if ( !nCount )
+ fRes = 0.0;
+ break;
+ default:
+ ; // nothing
+ }
+ return fRes;
+}
+
void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
{
short nParamCount = GetByte();
+ SCSIZE nMatRows = 0;
+ if (bMatrixFormula || pCur->IsInForceArray())
+ {
+ // Check for arrays of references to determine the maximum size of a
+ // return column vector.
+ for (short i=1; i <= nParamCount; ++i)
+ {
+ if (GetStackType(i) == svRefList)
+ {
+ const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp - i]);
+ if (p && p->IsArrayResult() && p->GetRefList()->size() > nMatRows)
+ nMatRows = p->GetRefList()->size();
+ }
+ }
+ }
+ ScMatrixRef xResMat, xResCount;
double fRes = ( eFunc == ifPRODUCT ) ? 1.0 : 0.0;
double fVal = 0.0;
double fMem = 0.0; // first numeric value != 0.0
@@ -469,6 +509,7 @@ void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
ScAddress aAdr;
ScRange aRange;
size_t nRefInList = 0;
+ size_t nRefArrayPos = std::numeric_limits<size_t>::max();
if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
nGlobalError = FormulaError::NONE;
@@ -678,9 +719,55 @@ void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
}
}
break;
- case svDoubleRef :
case svRefList :
{
+ const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp-1]);
+ if (p && p->IsArrayResult())
+ {
+ nRefArrayPos = nRefInList;
+ if ((eFunc == ifSUM || eFunc == ifAVERAGE) && fMem != 0.0)
+ {
+ fRes = rtl::math::approxAdd( fRes, fMem);
+ fMem = 0.0;
+ }
+ // The "one value to all references of an array" seems to
+ // be what Excel does if there are other types than just
+ // arrays of references.
+ if (!xResMat)
+ {
+ // Create and init all elements with current value.
+ assert(nMatRows > 0);
+ xResMat = GetNewMat( 1, nMatRows, true);
+ xResMat->FillDouble( fRes, 0,0, 0,nMatRows-1);
+ if (eFunc != ifSUM)
+ {
+ xResCount = GetNewMat( 1, nMatRows, true);
+ xResCount->FillDouble( nCount, 0,0, 0,nMatRows-1);
+ }
+ }
+ else
+ {
+ // Current value and values from vector are operands
+ // for each vector position.
+ for (SCSIZE i=0; i < nMatRows; ++i)
+ {
+ if (xResCount)
+ xResCount->PutDouble( xResCount->GetDouble(0,i) + nCount, 0,i);
+ double fVecRes = xResMat->GetDouble(0,i);
+ if (eFunc == ifPRODUCT)
+ fVecRes *= fRes;
+ else
+ fVecRes += fRes;
+ xResMat->PutDouble( fVecRes, 0,i);
+ }
+ }
+ fRes = ((eFunc == ifPRODUCT) ? 1.0 : 0.0);
+ nCount = 0;
+ }
+ }
+ SAL_FALLTHROUGH;
+ case svDoubleRef :
+ {
PopDoubleRef( aRange, nParamCount, nRefInList);
if (nGlobalError == FormulaError::NoRef)
{
@@ -835,6 +922,27 @@ void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
SetError( nErr );
}
}
+ if (nRefArrayPos != std::numeric_limits<size_t>::max())
+ {
+ // Update vector element with current value.
+ if ((eFunc == ifSUM || eFunc == ifAVERAGE) && fMem != 0.0)
+ {
+ fRes = rtl::math::approxAdd( fRes, fMem);
+ fMem = 0.0;
+ }
+ if (xResCount)
+ xResCount->PutDouble( xResCount->GetDouble(0,nRefArrayPos) + nCount, 0,nRefArrayPos);
+ double fVecRes = xResMat->GetDouble(0,nRefArrayPos);
+ if (eFunc == ifPRODUCT)
+ fVecRes *= fRes;
+ else
+ fVecRes += fRes;
+ xResMat->PutDouble( fVecRes, 0,nRefArrayPos);
+ // Reset.
+ fRes = ((eFunc == ifPRODUCT) ? 1.0 : 0.0);
+ nCount = 0;
+ nRefArrayPos = std::numeric_limits<size_t>::max();
+ }
}
break;
case svExternalDoubleRef:
@@ -874,21 +982,33 @@ void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
SetError(FormulaError::IllegalParameter);
}
}
- switch( eFunc )
- {
- case ifSUM: fRes = ::rtl::math::approxAdd( fRes, fMem ); break;
- case ifAVERAGE: fRes = div(::rtl::math::approxAdd( fRes, fMem ), nCount); break;
- case ifCOUNT2:
- case ifCOUNT: fRes = nCount; break;
- case ifPRODUCT: if ( !nCount ) fRes = 0.0; break;
- default: ; // nothing
- }
+
// A boolean return type makes no sense on sums et al.
// Counts are always numbers.
if( nFuncFmtType == css::util::NumberFormat::LOGICAL || eFunc == ifCOUNT || eFunc == ifCOUNT2 )
nFuncFmtType = css::util::NumberFormat::NUMBER;
- PushDouble( fRes);
+ if (xResMat)
+ {
+ // Include value of last non-references-array type and calculate final result.
+ for (SCSIZE i=0; i < nMatRows; ++i)
+ {
+ if (xResCount)
+ nCount += xResCount->GetDouble(0,i);
+ double fVecRes = xResMat->GetDouble(0,i);
+ if (eFunc == ifPRODUCT)
+ fVecRes *= fRes;
+ else
+ fVecRes += fRes;
+ fVecRes = lcl_IterResult( eFunc, fVecRes, fMem, nCount);
+ xResMat->PutDouble( fVecRes, 0,i);
+ }
+ PushMatrix( xResMat);
+ }
+ else
+ {
+ PushDouble( lcl_IterResult( eFunc, fRes, fMem, nCount));
+ }
}
void ScInterpreter::ScSumSQ()