Resolves: tdf#129681 Handle array/matrix in AGGREGATE() with ignore errors
This also changes AGGREGATE() 3rd and subsequent parameters'
classification from ReferenceOrRefArray to ReferenceOrForceArray
to force the expected array mode on inline calculations.
Change-Id: I53a5591e46bfbabbfa6a273f5b9590a69fad87a0
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/86388
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
diff --git a/sc/inc/scmatrix.hxx b/sc/inc/scmatrix.hxx
index ca9d349..2efa73c 100644
--- a/sc/inc/scmatrix.hxx
+++ b/sc/inc/scmatrix.hxx
@@ -362,15 +362,15 @@ public:
double Or() const ; // logical OR of all matrix values, or NAN
double Xor() const ; // logical XOR of all matrix values, or NAN
IterateResult Sum(bool bTextAsZero) const ;
IterateResult SumSquare(bool bTextAsZero) const ;
IterateResult Product(bool bTextAsZero) const ;
IterateResult Sum( bool bTextAsZero, bool bIgnoreErrorValues = false ) const ;
IterateResult SumSquare( bool bTextAsZero, bool bIgnoreErrorValues = false ) const ;
IterateResult Product( bool bTextAsZero, bool bIgnoreErrorValues = false ) const ;
size_t Count(bool bCountStrings, bool bCountErrors) const ;
size_t MatchDoubleInColumns(double fValue, size_t nCol1, size_t nCol2) const ;
size_t MatchStringInColumns(const svl::SharedString& rStr, size_t nCol1, size_t nCol2) const ;
double GetMaxValue( bool bTextAsZero ) const ;
double GetMinValue( bool bTextAsZero ) const ;
double GetMaxValue( bool bTextAsZero, bool bIgnoreErrorValues = false ) const ;
double GetMinValue( bool bTextAsZero, bool bIgnoreErrorValues = false ) const ;
double GetGcd() const ;
double GetLcm() const ;
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 175228c..b6fa53b 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -3721,7 +3721,7 @@ void ScInterpreter::ScMin( bool bTextAsZero )
if (pMat)
{
nFuncFmtType = SvNumFormatType::NUMBER;
nVal = pMat->GetMinValue(bTextAsZero);
nVal = pMat->GetMinValue(bTextAsZero, bool(mnSubTotalFlags & SubtotalFlags::IgnoreErrVal));
if (nMin > nVal)
nMin = nVal;
}
@@ -3879,7 +3879,7 @@ void ScInterpreter::ScMax( bool bTextAsZero )
if (pMat)
{
nFuncFmtType = SvNumFormatType::NUMBER;
nVal = pMat->GetMaxValue(bTextAsZero);
nVal = pMat->GetMaxValue(bTextAsZero, bool(mnSubTotalFlags & SubtotalFlags::IgnoreErrVal));
if (nMax < nVal)
nMax = nVal;
}
@@ -4069,6 +4069,7 @@ void ScInterpreter::GetStVarParams( bool bTextAsZero, double(*VarResult)( double
ScMatrixRef pMat = GetMatrix();
if (pMat)
{
const bool bIgnoreErrVal = bool(mnSubTotalFlags & SubtotalFlags::IgnoreErrVal);
SCSIZE nC, nR;
pMat->GetDimensions(nC, nR);
for (SCSIZE nMatCol = 0; nMatCol < nC; nMatCol++)
@@ -4083,6 +4084,8 @@ void ScInterpreter::GetStVarParams( bool bTextAsZero, double(*VarResult)( double
values.push_back(fVal);
fSum += fVal;
}
else if (bIgnoreErrVal)
nGlobalError = FormulaError::NONE;
}
else if ( bTextAsZero )
{
@@ -7565,6 +7568,9 @@ void ScInterpreter::ScAggregate()
sal_uInt8 nParamCount = GetByte();
if ( MustHaveParamCountMin( nParamCount, 3 ) )
{
const FormulaError nErr = nGlobalError;
nGlobalError = FormulaError::NONE;
// fish the 1st parameter from the stack and push it on top.
const FormulaToken* p = pStack[ sp - nParamCount ];
PushWithoutError( *p );
@@ -7575,7 +7581,10 @@ void ScInterpreter::ScAggregate()
sal_Int32 nOption = GetInt32();
if ( nGlobalError != FormulaError::NONE || nFunc < 1 || nFunc > 19 )
{
nGlobalError = nErr;
PushIllegalArgument();
}
else
{
switch ( nOption)
@@ -7605,10 +7614,14 @@ void ScInterpreter::ScAggregate()
mnSubTotalFlags = SubtotalFlags::IgnoreHidden | SubtotalFlags::IgnoreErrVal ;
break;
default :
nGlobalError = nErr;
PushIllegalArgument();
return;
}
if ((mnSubTotalFlags & SubtotalFlags::IgnoreErrVal) == SubtotalFlags::NONE)
nGlobalError = nErr;
cPar = nParamCount - 2;
switch ( nFunc )
{
@@ -7631,7 +7644,10 @@ void ScInterpreter::ScAggregate()
case AGGREGATE_FUNC_QRTINC : ScQuartile( true ); break;
case AGGREGATE_FUNC_PERCEXC : ScPercentile( false ); break;
case AGGREGATE_FUNC_QRTEXC : ScQuartile( false ); break;
default : PushIllegalArgument(); break;
default:
nGlobalError = nErr;
PushIllegalArgument();
break;
}
mnSubTotalFlags = SubtotalFlags::NONE;
}
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index 31dee4c..ad9db97 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -3943,6 +3943,7 @@ void ScInterpreter::GetNumberSequenceArray( sal_uInt8 nParamCount, vector<double
{
ScAddress aAdr;
ScRange aRange;
const bool bIgnoreErrVal = bool(mnSubTotalFlags & SubtotalFlags::IgnoreErrVal);
short nParam = nParamCount;
size_t nRefInList = 0;
ReverseStack( nParamCount );
@@ -3958,7 +3959,9 @@ void ScInterpreter::GetNumberSequenceArray( sal_uInt8 nParamCount, vector<double
{
PopSingleRef( aAdr );
ScRefCellValue aCell(*pDok, aAdr);
if (aCell.hasNumeric())
if (bIgnoreErrVal && aCell.hasError())
; // nothing
else if (aCell.hasNumeric())
rArray.push_back(GetCellValue(aAdr, aCell));
}
break;
@@ -3979,11 +3982,24 @@ void ScInterpreter::GetNumberSequenceArray( sal_uInt8 nParamCount, vector<double
ScValueIterator aValIter( pDok, aRange, mnSubTotalFlags );
if (aValIter.GetFirst( fCellVal, nErr))
{
rArray.push_back( fCellVal);
SetError(nErr);
while ((nErr == FormulaError::NONE) && aValIter.GetNext( fCellVal, nErr))
if (bIgnoreErrVal)
{
if (nErr == FormulaError::NONE)
rArray.push_back( fCellVal);
while (aValIter.GetNext( fCellVal, nErr))
{
if (nErr == FormulaError::NONE)
rArray.push_back( fCellVal);
}
}
else
{
rArray.push_back( fCellVal);
SetError(nErr);
SetError(nErr);
while ((nErr == FormulaError::NONE) && aValIter.GetNext( fCellVal, nErr))
rArray.push_back( fCellVal);
SetError(nErr);
}
}
}
break;
@@ -3999,15 +4015,40 @@ void ScInterpreter::GetNumberSequenceArray( sal_uInt8 nParamCount, vector<double
rArray.reserve( rArray.size() + nCount);
if (pMat->IsNumeric())
{
for (SCSIZE i = 0; i < nCount; ++i)
rArray.push_back( pMat->GetDouble(i));
if (bIgnoreErrVal)
{
for (SCSIZE i = 0; i < nCount; ++i)
{
const double fVal = pMat->GetDouble(i);
if (nGlobalError == FormulaError::NONE)
rArray.push_back( fVal);
else
nGlobalError = FormulaError::NONE;
}
}
else
{
for (SCSIZE i = 0; i < nCount; ++i)
rArray.push_back( pMat->GetDouble(i));
}
}
else if (bConvertTextInArray && eStackType == svMatrix)
{
for (SCSIZE i = 0; i < nCount; ++i)
{
if ( pMat->IsValue( i ) )
rArray.push_back( pMat->GetDouble(i));
{
if (bIgnoreErrVal)
{
const double fVal = pMat->GetDouble(i);
if (nGlobalError == FormulaError::NONE)
rArray.push_back( fVal);
else
nGlobalError = FormulaError::NONE;
}
else
rArray.push_back( pMat->GetDouble(i));
}
else
{
// tdf#88547 try to convert string to (date)value
@@ -4024,13 +4065,17 @@ void ScInterpreter::GetNumberSequenceArray( sal_uInt8 nParamCount, vector<double
}
else
{
rArray.push_back( CreateDoubleError( FormulaError::NoValue));
if (!bIgnoreErrVal)
rArray.push_back( CreateDoubleError( FormulaError::NoValue));
// Propagate previous error if any, else
// the current #VALUE! error.
// the current #VALUE! error, unless
// ignoring error values.
if (nErr != FormulaError::NONE)
nGlobalError = nErr;
else
else if (!bIgnoreErrVal)
nGlobalError = FormulaError::NoValue;
else
nGlobalError = FormulaError::NONE;
}
}
}
@@ -4038,10 +4083,27 @@ void ScInterpreter::GetNumberSequenceArray( sal_uInt8 nParamCount, vector<double
}
else
{
for (SCSIZE i = 0; i < nCount; ++i)
if (bIgnoreErrVal)
{
if ( pMat->IsValue( i ) )
rArray.push_back( pMat->GetDouble(i));
for (SCSIZE i = 0; i < nCount; ++i)
{
if (pMat->IsValue(i))
{
const double fVal = pMat->GetDouble(i);
if (nGlobalError == FormulaError::NONE)
rArray.push_back( fVal);
else
nGlobalError = FormulaError::NONE;
}
}
}
else
{
for (SCSIZE i = 0; i < nCount; ++i)
{
if (pMat->IsValue(i))
rArray.push_back( pMat->GetDouble(i));
}
}
}
}
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 78a2bf5..b1a3658 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -3926,6 +3926,7 @@ bool IsErrFunc(OpCode oc)
case ocIfError :
case ocIfNA :
case ocErrorType_ODF :
case ocAggregate: // may ignore errors depending on option
case ocIfs_MS:
case ocSwitch_MS:
return true;
diff --git a/sc/source/core/tool/interpr6.cxx b/sc/source/core/tool/interpr6.cxx
index 7038e4d..327fff5 100644
--- a/sc/source/core/tool/interpr6.cxx
+++ b/sc/source/core/tool/interpr6.cxx
@@ -404,20 +404,20 @@ public:
}
static void IterateMatrix(
const ScMatrixRef& pMat, ScIterFunc eFunc, bool bTextAsZero,
const ScMatrixRef& pMat, ScIterFunc eFunc, bool bTextAsZero, SubtotalFlags nSubTotalFlags,
sal_uLong& rCount, SvNumFormatType& rFuncFmtType, double& fRes, double& fMem )
{
if (!pMat)
return;
// TODO fdo73148 take mnSubTotalFlags into account
const bool bIgnoreErrVal = bool(nSubTotalFlags & SubtotalFlags::IgnoreErrVal);
rFuncFmtType = SvNumFormatType::NUMBER;
switch (eFunc)
{
case ifAVERAGE:
case ifSUM:
{
ScMatrix::IterateResult aRes = pMat->Sum(bTextAsZero);
ScMatrix::IterateResult aRes = pMat->Sum(bTextAsZero, bIgnoreErrVal);
// If the first value is a NaN, it probably means it was an empty cell,
// and should be treated as zero.
if ( !rtl::math::isFinite(aRes.mfFirst) )
@@ -442,11 +442,12 @@ static void IterateMatrix(
rCount += pMat->Count(bTextAsZero, false); // do not count error values
break;
case ifCOUNT2:
/* TODO: what is this supposed to be with bIgnoreErrVal? */
rCount += pMat->Count(true, true); // do count error values
break;
case ifPRODUCT:
{
ScMatrix::IterateResult aRes = pMat->Product(bTextAsZero);
ScMatrix::IterateResult aRes = pMat->Product(bTextAsZero, bIgnoreErrVal);
fRes *= aRes.mfFirst;
fRes *= aRes.mfRest;
rCount += aRes.mnCount;
@@ -454,7 +455,7 @@ static void IterateMatrix(
break;
case ifSUMSQ:
{
ScMatrix::IterateResult aRes = pMat->SumSquare(bTextAsZero);
ScMatrix::IterateResult aRes = pMat->SumSquare(bTextAsZero, bIgnoreErrVal);
fRes += aRes.mfFirst;
fRes += aRes.mfRest;
rCount += aRes.mnCount;
@@ -979,14 +980,14 @@ void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
if ( nGlobalError != FormulaError::NONE && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
break;
IterateMatrix( pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem );
IterateMatrix( pMat, eFunc, bTextAsZero, mnSubTotalFlags, nCount, nFuncFmtType, fRes, fMem );
}
break;
case svMatrix :
{
ScMatrixRef pMat = PopMatrix();
IterateMatrix( pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem );
IterateMatrix( pMat, eFunc, bTextAsZero, mnSubTotalFlags, nCount, nFuncFmtType, fRes, fMem );
}
break;
case svError:
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index e05b48e..f285df4 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -99,7 +99,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
{ ocTableOp, {{ Value, Value, Value, Value, Value }, 0, Value }},
// Operators and functions.
{ ocAdd, {{ Array, Array }, 0, Value }},
{ ocAggregate, {{ Value, Value, ReferenceOrRefArray }, 1, Value }},
{ ocAggregate, {{ Value, Value, ReferenceOrForceArray }, 1, Value }},
{ ocAmpersand, {{ Array, Array }, 0, Value }},
{ ocAnd, {{ Reference }, 1, Value }},
{ ocAreas, {{ Reference }, 0, Value }},
diff --git a/sc/source/core/tool/scmatrix.cxx b/sc/source/core/tool/scmatrix.cxx
index 934c14d..869a914 100644
--- a/sc/source/core/tool/scmatrix.cxx
+++ b/sc/source/core/tool/scmatrix.cxx
@@ -308,15 +308,15 @@ public:
double Or() const;
double Xor() const;
ScMatrix::IterateResult Sum(bool bTextAsZero) const;
ScMatrix::IterateResult SumSquare(bool bTextAsZero) const;
ScMatrix::IterateResult Product(bool bTextAsZero) const;
ScMatrix::IterateResult Sum( bool bTextAsZero, bool bIgnoreErrorValues ) const;
ScMatrix::IterateResult SumSquare( bool bTextAsZero, bool bIgnoreErrorValues ) const;
ScMatrix::IterateResult Product( bool bTextAsZero, bool bIgnoreErrorValues ) const;
size_t Count(bool bCountStrings, bool bCountErrors) const;
size_t MatchDoubleInColumns(double fValue, size_t nCol1, size_t nCol2) const;
size_t MatchStringInColumns(const svl::SharedString& rStr, size_t nCol1, size_t nCol2) const;
double GetMaxValue( bool bTextAsZero ) const;
double GetMinValue( bool bTextAsZero ) const;
double GetMaxValue( bool bTextAsZero, bool bIgnoreErrorValues ) const;
double GetMinValue( bool bTextAsZero, bool bIgnoreErrorValues ) const;
double GetGcd() const;
double GetLcm() const;
@@ -1120,8 +1120,12 @@ class WalkElementBlocks
ScMatrix::IterateResult maRes;
bool mbFirst:1;
bool mbTextAsZero:1;
bool mbIgnoreErrorValues:1;
public:
WalkElementBlocks(bool bTextAsZero) : maRes(Op::InitVal, Op::InitVal, 0), mbFirst(true), mbTextAsZero(bTextAsZero) {}
WalkElementBlocks(bool bTextAsZero, bool bIgnoreErrorValues) :
maRes(Op::InitVal, Op::InitVal, 0), mbFirst(true),
mbTextAsZero(bTextAsZero), mbIgnoreErrorValues(bIgnoreErrorValues)
{}
const ScMatrix::IterateResult& getResult() const { return maRes; }
@@ -1133,10 +1137,17 @@ public:
{
typedef MatrixImplType::numeric_block_type block_type;
size_t nIgnored = 0;
block_type::const_iterator it = block_type::begin(*node.data);
block_type::const_iterator itEnd = block_type::end(*node.data);
for (; it != itEnd; ++it)
{
if (mbIgnoreErrorValues && !rtl::math::isFinite(*it))
{
++nIgnored;
continue;
}
if (mbFirst)
{
maOp(maRes.mfFirst, *it);
@@ -1147,7 +1158,7 @@ public:
maOp(maRes.mfRest, *it);
}
}
maRes.mnCount += node.size;
maRes.mnCount += node.size - nIgnored;
}
break;
case mdds::mtm::element_boolean:
@@ -1535,11 +1546,13 @@ class CalcMaxMinValue
{
double mfVal;
bool mbTextAsZero;
bool mbIgnoreErrorValues;
bool mbHasValue;
public:
CalcMaxMinValue( bool bTextAsZero ) :
CalcMaxMinValue( bool bTextAsZero, bool bIgnoreErrorValues ) :
mfVal(Op::init()),
mbTextAsZero(bTextAsZero),
mbIgnoreErrorValues(bIgnoreErrorValues),
mbHasValue(false) {}
double getValue() const { return mbHasValue ? mfVal : 0.0; }
@@ -1555,8 +1568,19 @@ public:
block_type::const_iterator it = block_type::begin(*node.data);
block_type::const_iterator itEnd = block_type::end(*node.data);
for (; it != itEnd; ++it)
mfVal = Op::compare(mfVal, *it);
if (mbIgnoreErrorValues)
{
for (; it != itEnd; ++it)
{
if (rtl::math::isFinite(*it))
mfVal = Op::compare(mfVal, *it);
}
}
else
{
for (; it != itEnd; ++it)
mfVal = Op::compare(mfVal, *it);
}
mbHasValue = true;
}
@@ -2069,28 +2093,28 @@ public:
namespace {
template<typename TOp>
ScMatrix::IterateResult GetValueWithCount(bool bTextAsZero, const MatrixImplType& maMat)
ScMatrix::IterateResult GetValueWithCount(bool bTextAsZero, bool bIgnoreErrorValues, const MatrixImplType& maMat)
{
WalkElementBlocks<TOp> aFunc(bTextAsZero);
WalkElementBlocks<TOp> aFunc(bTextAsZero, bIgnoreErrorValues);
aFunc = maMat.walk(aFunc);
return aFunc.getResult();
}
}
ScMatrix::IterateResult ScMatrixImpl::Sum(bool bTextAsZero) const
ScMatrix::IterateResult ScMatrixImpl::Sum(bool bTextAsZero, bool bIgnoreErrorValues) const
{
return GetValueWithCount<sc::op::Sum>(bTextAsZero, maMat);
return GetValueWithCount<sc::op::Sum>(bTextAsZero, bIgnoreErrorValues, maMat);
}
ScMatrix::IterateResult ScMatrixImpl::SumSquare(bool bTextAsZero) const
ScMatrix::IterateResult ScMatrixImpl::SumSquare(bool bTextAsZero, bool bIgnoreErrorValues) const
{
return GetValueWithCount<sc::op::SumSquare>(bTextAsZero, maMat);
return GetValueWithCount<sc::op::SumSquare>(bTextAsZero, bIgnoreErrorValues, maMat);
}
ScMatrix::IterateResult ScMatrixImpl::Product(bool bTextAsZero) const
ScMatrix::IterateResult ScMatrixImpl::Product(bool bTextAsZero, bool bIgnoreErrorValues) const
{
return GetValueWithCount<sc::op::Product>(bTextAsZero, maMat);
return GetValueWithCount<sc::op::Product>(bTextAsZero, bIgnoreErrorValues, maMat);
}
size_t ScMatrixImpl::Count(bool bCountStrings, bool bCountErrors) const
@@ -2114,16 +2138,16 @@ size_t ScMatrixImpl::MatchStringInColumns(const svl::SharedString& rStr, size_t
return aFunc.getMatching();
}
double ScMatrixImpl::GetMaxValue( bool bTextAsZero ) const
double ScMatrixImpl::GetMaxValue( bool bTextAsZero, bool bIgnoreErrorValues ) const
{
CalcMaxMinValue<MaxOp> aFunc(bTextAsZero);
CalcMaxMinValue<MaxOp> aFunc(bTextAsZero, bIgnoreErrorValues);
aFunc = maMat.walk(aFunc);
return aFunc.getValue();
}
double ScMatrixImpl::GetMinValue( bool bTextAsZero ) const
double ScMatrixImpl::GetMinValue( bool bTextAsZero, bool bIgnoreErrorValues ) const
{
CalcMaxMinValue<MinOp> aFunc(bTextAsZero);
CalcMaxMinValue<MinOp> aFunc(bTextAsZero, bIgnoreErrorValues);
aFunc = maMat.walk(aFunc);
return aFunc.getValue();
}
@@ -3194,19 +3218,19 @@ double ScMatrix::Xor() const
return pImpl->Xor();
}
ScMatrix::IterateResult ScMatrix::Sum(bool bTextAsZero) const
ScMatrix::IterateResult ScMatrix::Sum(bool bTextAsZero, bool bIgnoreErrorValues) const
{
return pImpl->Sum(bTextAsZero);
return pImpl->Sum(bTextAsZero, bIgnoreErrorValues);
}
ScMatrix::IterateResult ScMatrix::SumSquare(bool bTextAsZero) const
ScMatrix::IterateResult ScMatrix::SumSquare(bool bTextAsZero, bool bIgnoreErrorValues) const
{
return pImpl->SumSquare(bTextAsZero);
return pImpl->SumSquare(bTextAsZero, bIgnoreErrorValues);
}
ScMatrix::IterateResult ScMatrix::Product(bool bTextAsZero) const
ScMatrix::IterateResult ScMatrix::Product(bool bTextAsZero, bool bIgnoreErrorValues) const
{
return pImpl->Product(bTextAsZero);
return pImpl->Product(bTextAsZero, bIgnoreErrorValues);
}
size_t ScMatrix::Count(bool bCountStrings, bool bCountErrors) const
@@ -3224,14 +3248,14 @@ size_t ScMatrix::MatchStringInColumns(const svl::SharedString& rStr, size_t nCol
return pImpl->MatchStringInColumns(rStr, nCol1, nCol2);
}
double ScMatrix::GetMaxValue( bool bTextAsZero ) const
double ScMatrix::GetMaxValue( bool bTextAsZero, bool bIgnoreErrorValues ) const
{
return pImpl->GetMaxValue(bTextAsZero);
return pImpl->GetMaxValue(bTextAsZero, bIgnoreErrorValues);
}
double ScMatrix::GetMinValue( bool bTextAsZero ) const
double ScMatrix::GetMinValue( bool bTextAsZero, bool bIgnoreErrorValues ) const
{
return pImpl->GetMinValue(bTextAsZero);
return pImpl->GetMinValue(bTextAsZero, bIgnoreErrorValues);
}
double ScMatrix::GetGcd() const
