/** Store a matrix value in another matrix in the context of that other matrix istheresultmatrixofajumpmatrix.Allargumentsmustbevalidandare
not checked. */ staticvoid lcl_storeJumpMatResult( const ScMatrix* pMat, ScJumpMatrix* pJumpMat, SCSIZE nC, SCSIZE nR )
{ if ( pMat->IsValue( nC, nR ) )
{ double fVal = pMat->GetDouble( nC, nR );
pJumpMat->PutResultDouble( fVal, nC, nR );
} elseif ( pMat->IsEmpty( nC, nR ) )
{
pJumpMat->PutResultEmpty( nC, nR );
} else
{
pJumpMat->PutResultString(pMat->GetString(nC, nR), nC, nR);
}
}
void ScInterpreter::ScIfError( bool bNAonly )
{ constshort* pJump = pCur->GetJump(); short nJumpCount = pJump[ 0 ]; if (!sp || nJumpCount != 2)
{ // Reset nGlobalError here to not propagate the old error, if any.
nGlobalError = (sp ? FormulaError::ParameterExpected : FormulaError::UnknownStackVariable);
PushError( nGlobalError);
aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] ); return;
}
formula::ParamClass eReturnType = ScParameterClassification::GetParameterType( pCur, SAL_MAX_UINT16); if (eReturnType == ParamClass::Reference)
{ /* TODO: What about error handling and do we actually
* need the result matrix above at all in this case? */
ScComplexRefData aRef;
aRef.Ref1 = aRef.Ref2 = *(xRef->GetSingleRef());
pJumpMatrix->GetRefList().push_back( aRef);
}
} break; case svDoubleRef:
{ // upper left plus offset within matrix
FormulaConstTokenRef xRef = pStack[sp-1]; double fVal;
ScRange aRange;
PopDoubleRef( aRange ); if ( nGlobalError != FormulaError::NONE )
{
fVal = CreateDoubleError( nGlobalError );
nGlobalError = FormulaError::NONE;
pJumpMatrix->PutResultDouble( fVal, nC, nR );
} else
{ // Do not modify the original range because we use it // to adjust the size of the result matrix if necessary.
ScAddress aAdr( aRange.aStart);
sal_uLong nCol = static_cast<sal_uLong>(aAdr.Col()) + nC;
sal_uLong nRow = static_cast<sal_uLong>(aAdr.Row()) + nR; if ((nCol > o3tl::make_unsigned(aRange.aEnd.Col()) &&
aRange.aEnd.Col() != aRange.aStart.Col())
|| (nRow > o3tl::make_unsigned(aRange.aEnd.Row()) &&
aRange.aEnd.Row() != aRange.aStart.Row()))
{
fVal = CreateDoubleError( FormulaError::NotAvailable );
pJumpMatrix->PutResultDouble( fVal, nC, nR );
} else
{ // Replicate column and/or row of a vector if it is // one. Note that this could be a range reference // that in fact consists of only one cell, e.g. A1:A1 if (aRange.aEnd.Col() == aRange.aStart.Col())
nCol = aRange.aStart.Col(); if (aRange.aEnd.Row() == aRange.aStart.Row())
nRow = aRange.aStart.Row();
aAdr.SetCol( static_cast<SCCOL>(nCol) );
aAdr.SetRow( static_cast<SCROW>(nRow) );
ScRefCellValue aCell(mrDoc, aAdr); if (aCell.hasEmptyValue())
pJumpMatrix->PutResultEmpty( nC, nR ); elseif (aCell.hasNumeric())
{ double fCellVal = GetCellValue(aAdr, aCell); if ( nGlobalError != FormulaError::NONE )
{
fCellVal = CreateDoubleError(
nGlobalError);
nGlobalError = FormulaError::NONE;
}
pJumpMatrix->PutResultDouble( fCellVal, nC, nR );
} else
{
svl::SharedString aStr;
GetCellString(aStr, aCell); if ( nGlobalError != FormulaError::NONE )
{
pJumpMatrix->PutResultDouble( CreateDoubleError(
nGlobalError), nC, nR);
nGlobalError = FormulaError::NONE;
} else
pJumpMatrix->PutResultString(aStr, nC, nR);
}
}
SCSIZE nParmCols = aRange.aEnd.Col() - aRange.aStart.Col() + 1;
SCSIZE nParmRows = aRange.aEnd.Row() - aRange.aStart.Row() + 1;
lcl_AdjustJumpMatrix( pJumpMatrix, nParmCols, nParmRows );
}
formula::ParamClass eReturnType = ScParameterClassification::GetParameterType( pCur, SAL_MAX_UINT16); if (eReturnType == ParamClass::Reference)
{ /* TODO: What about error handling and do we actually
* need the result matrix above at all in this case? */
pJumpMatrix->GetRefList().push_back( *(xRef->GetDoubleRef()));
}
} break; case svExternalSingleRef:
{
ScExternalRefCache::TokenRef pToken;
PopExternalSingleRef(pToken); if (nGlobalError != FormulaError::NONE)
{
pJumpMatrix->PutResultDouble( CreateDoubleError( nGlobalError), nC, nR );
nGlobalError = FormulaError::NONE;
} else
{ switch (pToken->GetType())
{ case svDouble:
pJumpMatrix->PutResultDouble( pToken->GetDouble(), nC, nR ); break; case svString:
pJumpMatrix->PutResultString( pToken->GetString(), nC, nR ); break; case svEmptyCell:
pJumpMatrix->PutResultEmpty( nC, nR ); break; default: // svError was already handled (set by // PopExternalSingleRef()) with nGlobalError // above.
assert(!"unhandled svExternalSingleRef case");
pJumpMatrix->PutResultDouble( CreateDoubleError(
FormulaError::UnknownStackVariable), nC, nR );
}
}
} break; case svExternalDoubleRef: case svMatrix:
{ // match matrix offsets double fVal;
ScMatrixRef pMat = GetMatrix(); if ( nGlobalError != FormulaError::NONE )
{
fVal = CreateDoubleError( nGlobalError );
nGlobalError = FormulaError::NONE;
pJumpMatrix->PutResultDouble( fVal, nC, nR );
} elseif ( !pMat )
{
fVal = CreateDoubleError( FormulaError::UnknownVariable );
pJumpMatrix->PutResultDouble( fVal, nC, nR );
} else
{
SCSIZE nCols, nRows;
pMat->GetDimensions( nCols, nRows ); if ((nCols <= nC && nCols != 1) ||
(nRows <= nR && nRows != 1))
{
fVal = CreateDoubleError( FormulaError::NotAvailable );
pJumpMatrix->PutResultDouble( fVal, nC, nR );
} else
{ // GetMatrix() does SetErrorInterpreter() at the // matrix, do not propagate an error from // matrix->GetValue() as global error.
pMat->SetErrorInterpreter(nullptr);
lcl_storeJumpMatResult(pMat.get(), pJumpMatrix, nC, nR);
}
lcl_AdjustJumpMatrix( pJumpMatrix, nCols, nRows );
}
} break; case svError:
{
PopError(); double fVal = CreateDoubleError( nGlobalError);
nGlobalError = FormulaError::NONE;
pJumpMatrix->PutResultDouble( fVal, nC, nR );
} break; default:
{
Pop(); double fVal = CreateDoubleError( FormulaError::IllegalArgument);
pJumpMatrix->PutResultDouble( fVal, nC, nR );
}
}
}
} bool bCont = pJumpMatrix->Next( nC, nR ); if ( bCont )
{ double fBool; short nStart, nNext, nStop;
pJumpMatrix->GetJump( nC, nR, fBool, nStart, nNext, nStop ); while ( bCont && nStart == nNext )
{ // push all results that have no jump path if ( bHasResMat && (GetDoubleErrorValue( fBool) != FormulaError::JumpMatHasResult) )
{ // a false without path results in an empty path value if ( fBool == 0.0 )
pJumpMatrix->PutResultEmptyPath( nC, nR ); else
pJumpMatrix->PutResultDouble( fBool, nC, nR );
}
bCont = pJumpMatrix->Next( nC, nR ); if ( bCont )
pJumpMatrix->GetJump( nC, nR, fBool, nStart, nNext, nStop );
} if ( bCont && nStart != nNext )
{ const ScTokenVec & rParams = pJumpMatrix->GetJumpParameters(); for ( autoconst & i : rParams )
{ // This is not the current state of the interpreter, so // push without error, and elements' errors are coded into // double.
PushWithoutError(*i);
}
aCode.Jump( nStart, nNext, nStop );
}
} if ( !bCont )
{ // We're done with it, throw away jump matrix, keep result. // For an intermediate result of Reference use the array of references // if there are more than one reference and the current ForceArray // context is ReferenceOrRefArray. // Else (also for a final result of Reference) use the matrix. // Treat the result of a jump command as final and use the matrix (see // tdf#115493 for why). if (pCur->GetInForceArray() == ParamClass::ReferenceOrRefArray &&
pJumpMatrix->GetRefList().size() > 1 &&
ScParameterClassification::GetParameterType( pCur, SAL_MAX_UINT16) == ParamClass::Reference &&
!FormulaCompiler::IsOpCodeJumpCommand( pJumpMatrix->GetOpCode()) &&
aCode.PeekNextOperator())
{
FormulaTokenRef xRef = new ScRefListToken(true);
*(xRef->GetRefList()) = pJumpMatrix->GetRefList();
pJumpMatrix = nullptr;
Pop();
PushTokenRef( xRef);
maTokenMatrixMap.erase( pCur); // There's no result matrix to remember in this case.
} else
{
ScMatrix* pResMat = pJumpMatrix->GetResultMatrix();
pJumpMatrix = nullptr;
Pop();
PushMatrix( pResMat ); // Remove jump matrix from map and remember result matrix in case it // could be reused in another path of the same condition.
maTokenMatrixMap.erase( pCur);
maTokenMatrixMap.emplace(pCur, pStack[sp-1]);
} returntrue;
} returnfalse;
}
double ScInterpreter::Compare( ScQueryOp eOp )
{
sc::Compare aComp;
aComp.meOp = eOp;
aComp.mbIgnoreCase = mrDoc.GetDocOptions().IsIgnoreCase(); for( short i = 1; i >= 0; i-- )
{
sc::Compare::Cell& rCell = aComp.maCells[i];
switch ( GetRawStackType() )
{ case svEmptyCell:
Pop();
rCell.mbEmpty = true; break; case svMissing: case svDouble:
rCell.mfValue = GetDouble();
rCell.mbValue = true; break; case svString:
rCell.maStr = GetString();
rCell.mbValue = false; break; case svDoubleRef : case svSingleRef :
{
ScAddress aAdr; if ( !PopDoubleRefOrSingleRef( aAdr ) ) break;
ScRefCellValue aCell(mrDoc, aAdr); if (aCell.hasEmptyValue())
rCell.mbEmpty = true; elseif (aCell.hasString())
{
svl::SharedString aStr;
GetCellString(aStr, aCell);
rCell.maStr = std::move(aStr);
rCell.mbValue = false;
} else
{
rCell.mfValue = GetCellValue(aAdr, aCell);
rCell.mbValue = true;
}
} break; case svExternalSingleRef:
{
ScMatrixRef pMat = GetMatrix(); if (!pMat)
{
SetError( FormulaError::IllegalParameter); break;
}
SCSIZE nC, nR;
pMat->GetDimensions(nC, nR); if (!nC || !nR)
{
SetError( FormulaError::IllegalParameter); break;
} if (pMat->IsEmpty(0, 0))
rCell.mbEmpty = true; elseif (pMat->IsStringOrEmpty(0, 0))
{
rCell.maStr = pMat->GetString(0, 0);
rCell.mbValue = false;
} else
{
rCell.mfValue = pMat->GetDouble(0, 0);
rCell.mbValue = true;
}
} break; case svExternalDoubleRef: // TODO: Find out how to handle this... // Xcl generates a position dependent intersection using // col/row, as it seems to do for all range references, not // only in compare context. We'd need a general implementation // for that behavior similar to svDoubleRef in scalar and array // mode. Which also means we'd have to change all places where // it currently is handled along with svMatrix. default:
PopError();
SetError( FormulaError::IllegalParameter); break;
}
} if( nGlobalError != FormulaError::NONE ) return0;
nCurFmtType = nFuncFmtType = SvNumFormatType::LOGICAL; return sc::CompareFunc(aComp);
}
void ScInterpreter::ScRandomImpl( const std::function<double( double fFirst, double fLast )>& RandomFunc, double fFirst, double fLast )
{ if (bMatrixFormula)
{
SCCOL nCols = 0;
SCROW nRows = 0; // In JumpMatrix context use its dimensions for the return matrix; the // formula cell range selected may differ, for example if the result is // to be transposed. if (GetStackType(1) == svJumpMatrix)
{
SCSIZE nC, nR;
pStack[sp-1]->GetJumpMatrix()->GetDimensions( nC, nR);
nCols = std::max<SCCOL>(0, static_cast<SCCOL>(nC));
nRows = std::max<SCROW>(0, static_cast<SCROW>(nR));
} elseif (pMyFormulaCell)
pMyFormulaCell->GetMatColsRows( nCols, nRows);
if (nCols == 1 && nRows == 1)
{ // For compatibility with existing // com.sun.star.sheet.FunctionAccess.callFunction() calls that per // default are executed in array context unless // FA.setPropertyValue("IsArrayFunction",False) was set, return a // scalar double instead of a 1x1 matrix object. tdf#128218
PushDouble( RandomFunc( fFirst, fLast)); return;
}
// ScViewFunc::EnterMatrix() might be asking for // ScFormulaCell::GetResultDimensions(), which here are none so create // a 1x1 matrix at least which exactly is the case when EnterMatrix() // asks for a not selected range. if (nCols == 0)
nCols = 1; if (nRows == 0)
nRows = 1;
ScMatrixRef pResMat = GetNewMat( static_cast<SCSIZE>(nCols), static_cast<SCSIZE>(nRows), /*bEmpty*/true ); if (!pResMat)
PushError( FormulaError::MatrixSize); else
{ for (SCCOL i=0; i < nCols; ++i)
{ for (SCROW j=0; j < nRows; ++j)
{
pResMat->PutDouble( RandomFunc( fFirst, fLast), static_cast<SCSIZE>(i), static_cast<SCSIZE>(j));
}
}
PushMatrix( pResMat);
}
} else
{
PushDouble( RandomFunc( fFirst, fLast));
}
}
void ScInterpreter::ScRandArray()
{
sal_uInt8 nParamCount = GetByte(); // optional 5th para: // TRUE for a whole number // FALSE for a decimal number - default. bool bWholeNumber = false; if (nParamCount == 5)
bWholeNumber = GetBoolWithDefault(false);
// optional 4th para: The maximum value of the random numbers double fMax = 1.0; if (nParamCount >= 4)
fMax = GetDoubleWithDefault(1.0);
// optional 3rd para: The minimum value of the random numbers double fMin = 0.0; if (nParamCount >= 3)
fMin = GetDoubleWithDefault(0.0);
// optional 2nd para: The number of columns of the return array
SCCOL nCols = 1; if (nParamCount >= 2)
nCols = static_cast<SCCOL>(GetInt32WithDefault(1));
// optional 1st para: The number of rows of the return array
SCROW nRows = 1; if (nParamCount >= 1)
nRows = static_cast<SCROW>(GetInt32WithDefault(1));
// Follow the configurable string reference address syntax as also // used by INDIRECT() (and ADDRESS() for the sheet separator).
FormulaGrammar::AddressConvention eConv = maCalcConfig.meStringRefAddressSyntax; switch (eConv)
{ default: // Use the current address syntax if unspecified or says // one or the other or one we don't explicitly handle.
eConv = mrDoc.GetAddressConvention(); break; case FormulaGrammar::CONV_OOO: case FormulaGrammar::CONV_XL_A1: case FormulaGrammar::CONV_XL_R1C1: // Use that. break;
}
if ( aInfoType == "COL" )
PushInt(nCol + 1); elseif ( aInfoType == "ROW" )
PushInt(nRow + 1); elseif ( aInfoType == "SHEET" )
{ // For SHEET, No idea what number we should set, but let's always set // 1 if the external sheet exists, no matter what sheet. Excel does // the same. if (pRefMgr->getCacheTable(nFileId, aTabName, false))
PushInt(1); else
SetError(FormulaError::NoName);
} elseif ( aInfoType == "ADDRESS" )
{ // ODF 1.2 says we need to always display address using the ODF A1 grammar.
ScTokenArray aArray(mrDoc);
aArray.AddExternalSingleReference(nFileId, svl::SharedString( aTabName), aRef); // string not interned
ScCompiler aComp(mrDoc, aPos, aArray, formula::FormulaGrammar::GRAM_ODFF_A1);
OUString aStr;
aComp.CreateStringFromTokenArray(aStr);
PushString(aStr);
} elseif ( aInfoType == "FILENAME" )
{ const OUString* p = pRefMgr->getExternalFileName(nFileId); if (!p)
{ // In theory this should never happen...
SetError(FormulaError::NoName); return;
}
void ScInterpreter::ScTrim()
{ // Doesn't only trim but also removes duplicated blanks within!
OUString aVal = comphelper::string::strip(GetString().getString(), ' ');
OUStringBuffer aStr; const sal_Unicode* p = aVal.getStr(); const sal_Unicode* const pEnd = p + aVal.getLength(); while ( p < pEnd )
{ if ( *p != ' ' || p[-1] != ' ' ) // first can't be ' ', so -1 is fine
aStr.append(*p);
p++;
}
PushString(aStr.makeStringAndClear());
}
/* #i70213# fullwidth/halfwidth conversion provided by *TakashiNakamoto<bluedwarf@ooo>
* erAck: added Excel compatibility conversions as seen in issue's test case. */
static OUString lcl_convertIntoHalfWidth( const OUString & rStr )
{ // Make the initialization thread-safe. Since another function needs to be called, move it all to another // function and thread-safely initialize a static reference in this function. auto init = []() -> utl::TransliterationWrapper&
{ static utl::TransliterationWrapper trans( ::comphelper::getProcessComponentContext(), TransliterationFlags::NONE );
trans.loadModuleByImplName( u"FULLWIDTH_HALFWIDTH_LIKE_ASC"_ustr, LANGUAGE_SYSTEM ); return trans;
}; static utl::TransliterationWrapper& aTrans( init()); return aTrans.transliterate( rStr, 0, sal_uInt16( rStr.getLength() ) );
}
if (!xResMat)
{ // Create and init all elements with current value.
assert(nMatRows > 0);
xResMat = GetNewMat( 1, nMatRows, true);
xResMat->FillDouble( fCurrent, 0,0, 0,nMatRows-1);
} elseif (bDoMatOp)
{ // Current value and values from vector are operands // for each vector position. for (SCSIZE i=0; i < nMatRows; ++i)
{
MatOpFunc( i, fCurrent);
}
} returntrue;
}
void ScInterpreter::ScMin( bool bTextAsZero )
{ short nParamCount = GetByte(); if (!MustHaveParamCountMin( nParamCount, 1)) return;
double nVal = 0.0;
ScAddress aAdr;
ScRange aRange;
size_t nRefInList = 0; while (nParamCount-- > 0)
{ switch (GetStackType())
{ case svDouble :
{
nVal = GetDouble(); if (nMin > nVal) nMin = nVal;
nFuncFmtType = SvNumFormatType::NUMBER;
} break; case svSingleRef :
{
PopSingleRef( aAdr );
ScRefCellValue aCell(mrDoc, aAdr); if (aCell.hasNumeric())
{
nVal = GetCellValue(aAdr, aCell);
CurFmtToFuncFmt(); if (nMin > nVal) nMin = nVal;
} elseif (bTextAsZero && aCell.hasString())
{ if ( nMin > 0.0 )
nMin = 0.0;
}
} break; case svRefList :
{ // bDoMatOp only for non-array value when switching to // ArrayRefList. if (SwitchToArrayRefList( xResMat, nMatRows, nMin, MatOpFunc,
nRefArrayPos == std::numeric_limits<size_t>::max()))
{
nRefArrayPos = nRefInList;
}
}
[[fallthrough]]; case svDoubleRef :
{
FormulaError nErr = FormulaError::NONE;
PopDoubleRef( aRange, nParamCount, nRefInList);
ScValueIterator aValIter( mrContext, aRange, mnSubTotalFlags, bTextAsZero ); if (aValIter.GetFirst(nVal, nErr))
{ if (nMin > nVal)
nMin = nVal;
aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex ); while ((nErr == FormulaError::NONE) && aValIter.GetNext(nVal, nErr))
{ if (nMin > nVal)
nMin = nVal;
}
SetError(nErr);
} if (nRefArrayPos != std::numeric_limits<size_t>::max())
{ // Update vector element with current value.
MatOpFunc( nRefArrayPos, nMin);
// Reset.
nMin = std::numeric_limits<double>::max();
nVal = 0.0;
nRefArrayPos = std::numeric_limits<size_t>::max();
}
} break; case svMatrix : case svExternalSingleRef: case svExternalDoubleRef:
{
ScMatrixRef pMat = GetMatrix(); if (pMat)
{
nFuncFmtType = SvNumFormatType::NUMBER;
nVal = pMat->GetMinValue(bTextAsZero, bool(mnSubTotalFlags & SubtotalFlags::IgnoreErrVal)); if (nMin > nVal)
nMin = nVal;
}
} break; case svString :
{
Pop(); if ( bTextAsZero )
{ if ( nMin > 0.0 )
nMin = 0.0;
} else
SetError(FormulaError::IllegalParameter);
} break; default :
PopError();
SetError(FormulaError::IllegalParameter);
}
}
if (xResMat)
{ // Include value of last non-references-array type and calculate final result. if (nMin < std::numeric_limits<double>::max())
{ for (SCSIZE i=0; i < nMatRows; ++i)
{
MatOpFunc( i, nMin);
}
} else
{ /* TODO: the awkward "no value is minimum 0.0" is likely the case *ifavalueisnumeric_limits::max.Still,thatcouldbeavalid *minimumvalueaswell,butnValandnMinhadbeenresetafter
* the last svRefList... so we may lie here. */ for (SCSIZE i=0; i < nMatRows; ++i)
{ double fVecRes = xResMat->GetDouble(0,i); if (fVecRes == std::numeric_limits<double>::max())
xResMat->PutDouble( 0.0, 0,i);
}
}
PushMatrix( xResMat);
} else
{ if (!std::isfinite(nVal))
PushError( GetDoubleErrorValue( nVal)); elseif ( nVal < nMin )
PushDouble(0.0); // zero or only empty arguments else
PushDouble(nMin);
}
}
void ScInterpreter::ScMax( bool bTextAsZero )
{ short nParamCount = GetByte(); if (!MustHaveParamCountMin( nParamCount, 1)) return;
double nVal = 0.0;
ScAddress aAdr;
ScRange aRange;
size_t nRefInList = 0; while (nParamCount-- > 0)
{ switch (GetStackType())
{ case svDouble :
{
nVal = GetDouble(); if (nMax < nVal) nMax = nVal;
nFuncFmtType = SvNumFormatType::NUMBER;
} break; case svSingleRef :
{
PopSingleRef( aAdr );
ScRefCellValue aCell(mrDoc, aAdr); if (aCell.hasNumeric())
{
nVal = GetCellValue(aAdr, aCell);
CurFmtToFuncFmt(); if (nMax < nVal) nMax = nVal;
} elseif (bTextAsZero && aCell.hasString())
{ if ( nMax < 0.0 )
nMax = 0.0;
}
} break; case svRefList :
{ // bDoMatOp only for non-array value when switching to // ArrayRefList. if (SwitchToArrayRefList( xResMat, nMatRows, nMax, MatOpFunc,
nRefArrayPos == std::numeric_limits<size_t>::max()))
{
nRefArrayPos = nRefInList;
}
}
[[fallthrough]]; case svDoubleRef :
{
FormulaError nErr = FormulaError::NONE;
PopDoubleRef( aRange, nParamCount, nRefInList);
ScValueIterator aValIter( mrContext, aRange, mnSubTotalFlags, bTextAsZero ); if (aValIter.GetFirst(nVal, nErr))
{ if (nMax < nVal)
nMax = nVal;
aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex ); while ((nErr == FormulaError::NONE) && aValIter.GetNext(nVal, nErr))
{ if (nMax < nVal)
nMax = nVal;
}
SetError(nErr);
} if (nRefArrayPos != std::numeric_limits<size_t>::max())
{ // Update vector element with current value.
MatOpFunc( nRefArrayPos, nMax);
// Reset.
nMax = std::numeric_limits<double>::lowest();
nVal = 0.0;
nRefArrayPos = std::numeric_limits<size_t>::max();
}
} break; case svMatrix : case svExternalSingleRef: case svExternalDoubleRef:
{
ScMatrixRef pMat = GetMatrix(); if (pMat)
{
nFuncFmtType = SvNumFormatType::NUMBER;
nVal = pMat->GetMaxValue(bTextAsZero, bool(mnSubTotalFlags & SubtotalFlags::IgnoreErrVal)); if (nMax < nVal)
nMax = nVal;
}
} break; case svString :
{
Pop(); if ( bTextAsZero )
{ if ( nMax < 0.0 )
nMax = 0.0;
} else
SetError(FormulaError::IllegalParameter);
} break; default :
PopError();
SetError(FormulaError::IllegalParameter);
}
}
if (xResMat)
{ // Include value of last non-references-array type and calculate final result. if (nMax > std::numeric_limits<double>::lowest())
{ for (SCSIZE i=0; i < nMatRows; ++i)
{
MatOpFunc( i, nMax);
}
} else
{ /* TODO: the awkward "no value is maximum 0.0" is likely the case *ifavalueisnumeric_limits::lowest.Still,thatcouldbea *validmaximumvalueaswell,butnValandnMaxhadbeenreset
* after the last svRefList... so we may lie here. */ for (SCSIZE i=0; i < nMatRows; ++i)
{ double fVecRes = xResMat->GetDouble(0,i); if (fVecRes == -std::numeric_limits<double>::max())
xResMat->PutDouble( 0.0, 0,i);
}
}
PushMatrix( xResMat);
} else
{ if (!std::isfinite(nVal))
PushError( GetDoubleErrorValue( nVal)); elseif ( nVal > nMax )
PushDouble(0.0); // zero or only empty arguments else
PushDouble(nMax);
}
}
/** returns -1 when the matrix value is smaller than the query value, 0 when theyareequal,and1whenthematrixvalueislargerthanthequery
value. */
sal_Int32 lcl_CompareMatrix2Query( SCSIZE i, const VectorMatrixAccessor& rMat, const ScQueryParam& rParam, const ScQueryEntry& rEntry, bool bMatchWholeCell, bool bEmptyIsLess = true )
{ if (rMat.IsEmpty(i))
{ /* TODO: in case we introduced query for real empty this would have to
* be changed! */ if (bEmptyIsLess) return -1; // empty always less than anything else else return1; // empty always greater than anything else
}
/* FIXME: what is an empty path (result of IF(false;true_path) in
* comparisons? */
bool bByString = rEntry.GetQueryItem().meType == ScQueryEntry::ByString; if (rMat.IsValue(i))
{ constdouble nVal1 = rMat.GetDouble(i); if (!std::isfinite(nVal1))
{ // XXX Querying for error values is not required, otherwise we'd // need to check here. return1; // error always greater than numeric or string
}
if (bByString) return -1; // numeric always less than string
constdouble nVal2 = rEntry.GetQueryItem().mfVal; // XXX Querying for error values is not required, otherwise we'd need // to check here and move that check before the bByString check. if (nVal1 == nVal2) return0;
return nVal1 < nVal2 ? -1 : 1;
}
if (!bByString) return1; // string always greater than numeric
bool bMatch = rEntry.GetSearchTextPtr(rParam.eSearchType, rParam.bCaseSens, bMatchWholeCell)
->SearchForward(aStr1, &nStart, &nEnd); // from 614 on, nEnd is behind the found text if (bMatch && bMatchWholeCell
&& (nStart != 0 || nEnd != aStr1.getLength()))
bMatch = false; // RegExp must match entire cell string
bool bOk = ((rEntry.eOp == SC_NOT_EQUAL) ? !bMatch : bMatch);
if (bOk) return0; // we have a WildOrRegExp match
}
/** returns -1 when matrix(i) value is smaller than matrix(j) value, 0 when
they are equal, and 1 when larger */
sal_Int32 lcl_Compare2MatrixCells( SCSIZE i, const VectorMatrixAccessor& rMat, SCSIZE j )
{ // empty always less than anything else if (rMat.IsEmpty(i)) return ( rMat.IsEmpty(j) ? 0 : -1 ); elseif (rMat.IsEmpty(j)) return1;
bool bByString = rMat.IsStringOrEmpty(j); // string, empty has already been handled if (rMat.IsValue(i))
{ constdouble nVal1 = rMat.GetDouble(i); if (!std::isfinite(nVal1)) return1; // error always greater than numeric or string
if (bByString) return -1; // numeric always less than string
constdouble nVal2 = rMat.GetDouble(j); if (nVal1 == nVal2) return0;
return ( nVal1 < nVal2 ? -1 : 1 );
}
if (!bByString) return1; // string always greater than numeric
// get search value if (nGlobalError == FormulaError::NONE)
{ switch (GetRawStackType())
{ case svMissing: case svEmptyCell:
{
vsa.isEmptySearch = true;
vsa.isStringSearch = false;
vsa.sSearchStr = GetString();
} break; case svDouble:
{
vsa.isStringSearch = false;
vsa.fSearchVal = GetDouble();
} break; case svString:
{
vsa.isStringSearch = true;
vsa.sSearchStr = GetString();
} break; case svDoubleRef: case svSingleRef:
{
ScAddress aAdr; if (!PopDoubleRefOrSingleRef(aAdr))
{
PushInt(0); return;
}
ScRefCellValue aCell(mrDoc, aAdr); if (aCell.hasNumeric())
{
vsa.isStringSearch = false;
vsa.fSearchVal = GetCellValue(aAdr, aCell);
} else
{
vsa.isStringSearch = true;
GetCellString(vsa.sSearchStr, aCell);
}
} break; case svExternalSingleRef:
{
ScExternalRefCache::TokenRef pToken;
PopExternalSingleRef(pToken); if (nGlobalError != FormulaError::NONE)
{
PushError(nGlobalError); return;
} if (pToken->GetType() == svDouble)
{
vsa.isStringSearch = false;
vsa.fSearchVal = pToken->GetDouble();
} else
{
vsa.isStringSearch = true;
vsa.sSearchStr = pToken->GetString();
}
} break; case svExternalDoubleRef: case svMatrix:
{
ScMatValType nType = GetDoubleOrStringFromMatrix(
vsa.fSearchVal, vsa.sSearchStr);
vsa.isStringSearch = ScMatrix::IsNonValueType(nType);
} break; default:
{
PushIllegalParameter(); return;
}
}
// execute search if (SearchVectorForValue(vsa))
PushDouble(vsa.nIndex); else
{ if (vsa.isResultNA)
PushNA(); else return; // error occurred and has already been pushed
}
} else
PushIllegalParameter();
}
namespace {
bool isCellContentEmpty( const ScRefCellValue& rCell )
{ switch (rCell.getType())
{ case CELLTYPE_VALUE: case CELLTYPE_STRING: case CELLTYPE_EDIT: returnfalse; case CELLTYPE_FORMULA:
{ // NOTE: Excel treats ="" in a referenced cell as blank in // COUNTBLANK() but not in ISBLANK(), which is inconsistent. // COUNTBLANK() tests the (display) result whereas ISBLANK() tests // the cell content. // ODFF allows both for COUNTBLANK(). // OOo and LibreOffice prior to 4.4 did not treat ="" as blank in // COUNTBLANK(), we now do for Excel interoperability. /* TODO: introduce yet another compatibility option? */
sc::FormulaResultValue aRes = rCell.getFormula()->GetResult(); if (aRes.meType != sc::FormulaResultValue::String) returnfalse; if (!aRes.maString.isEmpty()) returnfalse;
} break; default:
;
}
ScCellIterator aIter( mrDoc, aRange, mnSubTotalFlags); for (bool bHas = aIter.first(); bHas; bHas = aIter.next())
{ const ScRefCellValue& rCell = aIter.getRefCellValue(); if (!isCellContentEmpty(rCell))
++nCount;
} if (xResMat)
{
xResMat->PutDouble( nMaxCount - nCount, 0, nRefListArrayPos);
nMaxCount = nCount = 0;
}
}
} break; case svMatrix: case svExternalSingleRef: case svExternalDoubleRef:
{
ScMatrixRef xMat = GetMatrix(); if (!xMat)
SetError( FormulaError::IllegalParameter); else
{
SCSIZE nC, nR;
xMat->GetDimensions( nC, nR);
nMaxCount = nC * nR; // Numbers (implicit), strings and error values, ignore empty // strings as those if not entered in an inline array are the // result of a formula, to be par with a reference to formula // cell as *visual* blank, see isCellContentEmpty() above.
nCount = xMat->Count( true, true, true);
}
} break; default : SetError(FormulaError::IllegalParameter); break;
} if (xResMat)
PushMatrix( xResMat); else
PushDouble(nMaxCount - nCount);
}
ScMatrixRef pSumExtraMatrix; bool bSumExtraRange = (nParamCount == 3); if (bSumExtraRange)
{ // Save only the upperleft cell in case of cell range. The geometry // of the 3rd parameter is taken from the 1st parameter.
KahanSum fSum = 0.0; double fRes = 0.0; double fCount = 0.0; short nParam = 1; const SCSIZE nMatRows = GetRefListArrayMaxSize( nParam); // There's either one RefList and nothing else, or none.
ScMatrixRef xResMat = (nMatRows ? GetNewMat( 1, nMatRows, /*bEmpty*/true ) : nullptr);
SCSIZE nRefListArrayPos = 0;
size_t nRefInList = 0; while (nParam-- > 0)
{
SCCOL nCol1 = 0;
SCROW nRow1 = 0;
SCTAB nTab1 = 0;
SCCOL nCol2 = 0;
SCROW nRow2 = 0;
SCTAB nTab2 = 0;
ScMatrixRef pQueryMatrix; switch ( GetStackType() )
{ case svRefList : if (bSumExtraRange)
{ /* TODO: this could resolve if all refs are of the same size */
SetError( FormulaError::IllegalParameter);
} else
{
nRefListArrayPos = nRefInList;
ScRange aRange;
PopDoubleRef( aRange, nParam, nRefInList);
aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
} break; case svDoubleRef :
PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 ); break; case svSingleRef :
PopSingleRef( nCol1, nRow1, nTab1 );
nCol2 = nCol1;
nRow2 = nRow1;
nTab2 = nTab1; break; case svMatrix: case svExternalSingleRef: case svExternalDoubleRef:
{
pQueryMatrix = GetMatrix(); if (!pQueryMatrix)
{
PushError( FormulaError::IllegalParameter); return;
}
nCol1 = 0;
nRow1 = 0;
nTab1 = 0;
SCSIZE nC, nR;
pQueryMatrix->GetDimensions( nC, nR);
nCol2 = static_cast<SCCOL>(nC - 1);
nRow2 = static_cast<SCROW>(nR - 1);
nTab2 = 0;
} break; default:
SetError( FormulaError::IllegalParameter);
} if ( nTab1 != nTab2 )
{
SetError( FormulaError::IllegalParameter);
}
if (bSumExtraRange)
{ // Take the range geometry of the 1st parameter and apply it to // the 3rd. If parts of the resulting range would point outside // the sheet, don't complain but silently ignore and simply cut // them away, this is what Xcl does :-/
// For the cut-away part we also don't need to determine the // criteria match, so shrink the source range accordingly, // instead of the result range.
SCCOL nColDelta = nCol2 - nCol1;
SCROW nRowDelta = nRow2 - nRow1;
SCCOL nMaxCol;
SCROW nMaxRow; if (pSumExtraMatrix)
{
SCSIZE nC, nR;
pSumExtraMatrix->GetDimensions( nC, nR);
nMaxCol = static_cast<SCCOL>(nC - 1);
nMaxRow = static_cast<SCROW>(nR - 1);
} else
{
nMaxCol = mrDoc.MaxCol();
nMaxRow = mrDoc.MaxRow();
} if (nCol3 + nColDelta > nMaxCol)
{
SCCOL nNewDelta = nMaxCol - nCol3;
nCol2 = nCol1 + nNewDelta;
}
std::vector<sal_uInt8>& vConditions = mrContext.maConditions; // vConditions is cached, although it is clear'ed after every cell is interpreted, // if the SUMIFS/COUNTIFS are part of a matrix formula, then that is not enough because // with a single InterpretTail() call it results in evaluation of all the cells in the // matrix formula.
vConditions.clear();
bool bHasDoubleRefCriteriaRanges = true; // Do not attempt main-range reduce if any of the criteria-ranges are not double-refs. // For COUNTIFS queries it's possible to range-reduce too, if the query is not supposed // to match empty cells (will be checked and undone later if needed), so simply treat // the first criteria range as the main range for purposes of detecting if this can be done. for (sal_uInt16 nParamIdx = 2; nParamIdx < nParamCount; nParamIdx += 2 )
{ const formula::FormulaToken* pCriteriaRangeToken = pStack[ sp-nParamIdx ]; if (pCriteriaRangeToken->GetType() != svDoubleRef )
{
bHasDoubleRefCriteriaRanges = false; break;
}
}
// Probe the main range token, and try if we can shrink the range without altering results. const formula::FormulaToken* pMainRangeToken = pStack[ sp-nParamCount ]; if (pMainRangeToken->GetType() == svDoubleRef && bHasDoubleRefCriteriaRanges)
{ const ScComplexRefData* pRefData = pMainRangeToken->GetDoubleRef(); if (!pRefData->IsDeleted())
{
DoubleRefToRange( *pRefData, aMainRange); if (aMainRange.aStart.Tab() == aMainRange.aEnd.Tab())
{ // Shrink the range to actual data content.
ScRange aSubRange = aMainRange;
mrDoc.GetDataAreaSubrange(aSubRange);
nStartColDiff = aSubRange.aStart.Col() - aMainRange.aStart.Col();
nStartRowDiff = aSubRange.aStart.Row() - aMainRange.aStart.Row();
nEndColDiff = aSubRange.aEnd.Col() - aMainRange.aEnd.Col();
nEndRowDiff = aSubRange.aEnd.Row() - aMainRange.aEnd.Row();
bRangeReduce = nStartColDiff || nStartRowDiff || nEndColDiff || nEndRowDiff;
}
}
}
// Undo bRangeReduce if asked to match empty cells for COUNTIFS (which should be rare).
assert(rEntry.GetQueryItems().size() == 1); constbool isCountIfs = (nParamCount % 2) == 0; if(isCountIfs && (rEntry.IsQueryByEmpty() || rItem.mbMatchEmpty) && bRangeReduce)
{
bRangeReduce = false; // All criteria ranges are svDoubleRef's, so only vConditions needs adjusting.
assert(vRefArrayConditions.empty()); if(!vConditions.empty())
{
std::vector<sal_uInt8> newConditions;
SCCOL newDimensionCols = nCol2 - nCol1 + 1;
SCROW newDimensionRows = nRow2 - nRow1 + 1;
newConditions.reserve( newDimensionCols * newDimensionRows );
SCCOL col = nCol1; for(; col < nCol1 + nStartColDiff; ++col)
newConditions.insert( newConditions.end(), newDimensionRows, 0 ); for(; col <= nCol2 - nStartColDiff; ++col)
{
newConditions.insert( newConditions.end(), nStartRowDiff, 0 );
SCCOL oldCol = col - ( nCol1 + nStartColDiff );
size_t nIndex = oldCol * nDimensionRows; if (nIndex < vConditions.size())
{ auto it = vConditions.begin() + nIndex;
newConditions.insert( newConditions.end(), it, it + nDimensionRows );
} else
newConditions.insert( newConditions.end(), nDimensionRows, 0 );
newConditions.insert( newConditions.end(), -nEndRowDiff, 0 );
} for(; col <= nCol2; ++col)
newConditions.insert( newConditions.end(), newDimensionRows, 0 );
assert( newConditions.size() == o3tl::make_unsigned( newDimensionCols * newDimensionRows ));
vConditions = std::move( newConditions );
nDimensionCols = newDimensionCols;
nDimensionRows = newDimensionRows;
}
}
if (bRangeReduce)
{ // All reference ranges must be of the same size as the main range. if( aMainRange.aEnd.Col() - aMainRange.aStart.Col() != nCol2 - nCol1
|| aMainRange.aEnd.Row() - aMainRange.aStart.Row() != nRow2 - nRow1)
{
PushError ( FormulaError::IllegalArgument); return;
}
nCol1 += nStartColDiff;
nRow1 += nStartRowDiff;
nCol2 += nEndColDiff;
nRow2 += nEndRowDiff;
}
// All reference ranges must be of same dimension and size. if (!nDimensionCols)
nDimensionCols = nCol2 - nCol1 + 1; if (!nDimensionRows)
nDimensionRows = nRow2 - nRow1 + 1; if ((nDimensionCols != (nCol2 - nCol1 + 1)) || (nDimensionRows != (nRow2 - nRow1 + 1)))
{
PushError ( FormulaError::IllegalArgument); return;
}
// result matrix is filled with boolean values.
std::vector<double> aResValues;
pResultMatrix->GetDoubleArray(aResValues); if (vConditions.size() != aResValues.size())
{
PushError( FormulaError::IllegalParameter); return;
}
std::vector<double>::const_iterator itThisRes = aResValues.begin(); for (auto& rCondition : vConditions)
{
rCondition += *itThisRes;
++itThisRes;
}
} else
{ if( ScQueryCellIteratorSortedCache::CanBeUsed( mrDoc, rParam, nTab1, pMyFormulaCell,
refData, mrContext ))
{
ScQueryCellIteratorSortedCache aCellIter(mrDoc, mrContext, nTab1, rParam, false, false); // Increment Entry.nField in iterator when switching to next column.
aCellIter.SetAdvanceQueryParamEntryField( true ); if ( aCellIter.GetFirst() )
{ do
{
size_t nC = aCellIter.GetCol() + nColDiff;
size_t nR = aCellIter.GetRow() + nRowDiff;
++vConditions[nC * nDimensionRows + nR];
} while ( aCellIter.GetNext() );
}
} else
{
ScQueryCellIteratorDirect aCellIter(mrDoc, mrContext, nTab1, rParam, false, false); // Increment Entry.nField in iterator when switching to next column.
aCellIter.SetAdvanceQueryParamEntryField( true ); if ( aCellIter.GetFirst() )
{ do
{
size_t nC = aCellIter.GetCol() + nColDiff;
size_t nR = aCellIter.GetRow() + nRowDiff;
++vConditions[nC * nDimensionRows + nR];
} while ( aCellIter.GetNext() );
}
}
} if (nRefArrayPos != std::numeric_limits<size_t>::max())
{ // Apply condition result to reference list array result position.
std::vector<sal_uInt8>& rVec = vRefArrayConditions[nRefArrayPos]; if (rVec.empty())
rVec = vConditions; else
{
assert(rVec.size() == vConditions.size()); // see dimensions above for (size_t i=0, n = rVec.size(); i < n; ++i)
{
rVec[i] += vConditions[i];
}
} // Reset conditions vector. // When leaving an svRefList this has to be emptied not set to // 0.0 because it's checked when entering an svRefList. if (nRefInList == 0)
std::vector<sal_uInt8>().swap( vConditions); else
std::for_each( vConditions.begin(), vConditions.end(), [](sal_uInt8 & r){ r = 0; } );
}
}
nParamCount -= 2;
}
if (!vRefArrayConditions.empty() && !vConditions.empty())
{ // Add/op the last current value to all array positions. for (auto & rVec : vRefArrayConditions)
{ if (rVec.empty())
rVec = vConditions; else
{
assert(rVec.size() == vConditions.size()); // see dimensions above for (size_t i=0, n = rVec.size(); i < n; ++i)
{
rVec[i] += vConditions[i];
}
}
}
}
if (nGlobalError != FormulaError::NONE)
{
PushError( nGlobalError); return; // bail out
}
sc::ParamIfsResult aRes;
ScMatrixRef xResMat;
// main range - only for AVERAGEIFS, SUMIFS, MINIFS and MAXIFS if (nParamCount == 1)
{ short nParam = nParamCount;
size_t nRefInList = 0;
size_t nRefArrayPos = std::numeric_limits<size_t>::max(); bool bRefArrayMain = false; while (nParam-- == nParamCount)
{
SCCOL nMainCol1 = 0;
SCROW nMainRow1 = 0;
SCTAB nMainTab1 = 0;
SCCOL nMainCol2 = 0;
SCROW nMainRow2 = 0;
SCTAB nMainTab2 = 0;
ScMatrixRef pMainMatrix; switch ( GetStackType() )
{ case svRefList :
{ const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp-1]); if (p && p->IsArrayResult())
{ if (vRefArrayConditions.empty())
{ // Replicate conditions if there wasn't a // reference list array for criteria // evaluation.
vRefArrayConditions.resize( nRefArrayRows); for (auto & rVec : vRefArrayConditions)
{
rVec = vConditions;
}
}
// All reference ranges must be of same dimension and size. if ((nDimensionCols != (nMainCol2 - nMainCol1 + 1)) || (nDimensionRows != (nMainRow2 - nMainRow1 + 1)))
{
PushError ( FormulaError::IllegalArgument); return;
}
if (nGlobalError != FormulaError::NONE)
{
PushError( nGlobalError); return; // bail out
}
// end-result calculation
// This gets weird... if conditions were calculated using a // reference list array but the main calculation range is not a // reference list array, then the conditions of the array are // applied to the main range each in turn to form the array result.
// The third parameter, result array, double, string and reference. double fResVal = 0.0;
svl::SharedString aResStr;
StackVar eResArrayType = svUnknown;
if (nParamCount == 3)
{
eResArrayType = GetStackType(); switch (eResArrayType)
{ case svDoubleRef:
{
SCTAB nTabJunk;
PopDoubleRef(nResCol1, nResRow1, nResTab,
nResCol2, nResRow2, nTabJunk); if (nResTab != nTabJunk ||
((nResRow2 - nResRow1) > 0 && (nResCol2 - nResCol1) > 0))
{ // The result array must be a vector.
PushIllegalParameter(); return;
}
} break; case svSingleRef:
PopSingleRef( nResCol1, nResRow1, nResTab);
nResCol2 = nResCol1;
nResRow2 = nResRow1; break; case svMatrix: case svExternalSingleRef: case svExternalDoubleRef:
{
pResMat = GetMatrix(); if (!pResMat)
{
PushIllegalParameter(); return;
}
SCSIZE nC, nR;
pResMat->GetDimensions(nC, nR); if (nC != 1 && nR != 1)
{ // Result matrix must be a vector.
PushIllegalParameter(); return;
}
nResCol2 = nC - 1;
nResRow2 = nR - 1;
} break; case svDouble:
fResVal = GetDouble(); break; case svString:
aResStr = GetString(); break; default:
PushIllegalParameter(); return;
}
}
// For double, string and single reference. double fDataVal = 0.0;
svl::SharedString aDataStr;
ScAddress aDataAdr; bool bValueData = false;
// Get the data-result range and also determine whether this is vertical // lookup or horizontal lookup.
if ( eDataArrayType == svDouble || eDataArrayType == svString ||
eDataArrayType == svSingleRef )
{ // Delta position for a single value is always 0.
// Found if data <= query, but not if query is string and found data is // numeric or vice versa. This is how Excel does it but doesn't // document it.
if (pResMat)
{ if (pResMat->IsValue( 0, 0 ))
PushDouble(pResMat->GetDouble( 0, 0 )); else
PushString(pResMat->GetString(0, 0));
} elseif (nParamCount == 3)
{ switch (eResArrayType)
{ case svDouble:
PushDouble( fResVal ); break; case svString:
PushString( aResStr ); break; case svDoubleRef: case svSingleRef:
PushCellResultToken( true, ScAddress( nResCol1, nResRow1, nResTab), nullptr, nullptr); break; default:
assert(!"ScInterpreter::ScLookup: unhandled eResArrayType, single value data");
PushIllegalParameter();
}
} else
{ switch (eDataArrayType)
{ case svDouble:
PushDouble( fDataVal ); break; case svString:
PushString( aDataStr ); break; case svSingleRef:
PushCellResultToken( true, aDataAdr, nullptr, nullptr); break; default:
assert(!"ScInterpreter::ScLookup: unhandled eDataArrayType, single value data");
PushIllegalParameter();
}
} return;
}
// Now, perform the search to compute the delta position (nDelta).
if (pDataMat)
{ // Data array is given as a matrix.
rEntry.bDoQuery = true;
rEntry.eOp = SC_LESS_EQUAL; bool bFound = false;
SCSIZE nC, nR;
pDataMat->GetDimensions(nC, nR);
// Do not propagate errors from matrix while copying to vector.
pDataMat->SetErrorInterpreter( nullptr);
// Excel has an undocumented behaviour in that it seems to internally // sort an interim array (i.e. error values specifically #DIV/0! are // sorted to the end) or ignore error values that makes these "get last // non-empty" searches work, e.g. =LOOKUP(2,1/NOT(ISBLANK(A:A)),A:A) // see tdf#117016 // Instead of sorting a million entries of which mostly only a bunch of // rows are filled and moving error values to the end which most are // already anyway, assume the matrix to be sorted except error values // and omit the coded DoubleError values. // Do this only for a numeric matrix (that includes errors coded as // doubles), which covers the case in question. /* TODO: it's unclear whether this really matches Excel behaviour in *allconstellationsoriftherearecasesthatincludeunsortederror *valuesandthusyieldarbitrarybinarysearchresultsorsomething *differentorwhethertherearecaseswhereerrorvaluesarealso *omittedfrommixednumeric/stringarraysorifit'snotaninterim
* matrix but a cell range reference instead. */ constbool bOmitErrorValues = (eDataArrayType == svMatrix && pDataMat->IsNumeric());
// In case of non-vector matrix, only search the first row or column.
ScMatrixRef pDataMat2;
std::vector<SCCOLROW> vIndex; if (bOmitErrorValues)
{
std::vector<double> vArray;
VectorMatrixAccessor aMatAcc(*pDataMat, bVertical); const SCSIZE nElements = aMatAcc.GetElementCount(); for (SCSIZE i=0; i < nElements; ++i)
{ constdouble fVal = aMatAcc.GetDouble(i); if (std::isfinite(fVal))
{
vArray.push_back(fVal);
vIndex.push_back(i);
}
} if (vArray.empty())
{
PushNA(); return;
} const size_t nElems = vArray.size(); if (nElems == nElements)
{ // No error value omitted, use as is.
pDataMat2 = pDataMat;
std::vector<SCCOLROW>().swap( vIndex);
} else
{
nLenMajor = nElems; if (bVertical)
{
ScMatrixRef pTempMat = GetNewMat( 1, nElems, /*bEmpty*/true );
pTempMat->PutDoubleVector( vArray, 0, 0);
pDataMat2 = std::move(pTempMat);
} else
{
ScMatrixRef pTempMat = GetNewMat( nElems, 1, /*bEmpty*/true ); for (size_t i=0; i < nElems; ++i)
pTempMat->PutDouble( vArray[i], i, 0);
pDataMat2 = std::move(pTempMat);
}
}
} else
{ // Just use as is with the VectorMatrixAccessor.
pDataMat2 = pDataMat;
}
// Do not propagate errors from matrix while searching.
pDataMat2->SetErrorInterpreter( nullptr);
SCSIZE nFirst = 0, nLast = nLenMajor-1; //, nHitIndex = 0; for (SCSIZE nLen = nLast-nFirst; nLen > 0; nLen = nLast-nFirst)
{
SCSIZE nMid = nFirst + nLen/2;
sal_Int32 nCmp = lcl_CompareMatrix2Query( nMid, aMatAcc2, aParam, rEntry, bMatchWholeCell, false/* bEmptyIsLess, instead empty are sorted to end */); if (nCmp == 0)
{ // exact match. find the last item with the same value.
lcl_GetLastMatch( nMid, aMatAcc2, nLenMajor);
nDelta = nMid;
bFound = true; break;
}
if (nLen == 1) // first and last items are next to each other.
{
nDelta = nCmp < 0 ? nLast - 1 : nFirst - 1; // If already the 1st item is greater there's nothing found.
bFound = (nDelta >= 0); break;
}
if (nCmp < 0)
nFirst = nMid; else
nLast = nMid;
}
if (nDelta == static_cast<SCCOLROW>(nLenMajor-2)) // last item
{
sal_Int32 nCmp = lcl_CompareMatrix2Query(nDelta+1, aMatAcc2, aParam, rEntry, bMatchWholeCell, false/* bEmptyIsLess, instead empty are sorted to end */); if (nCmp <= 0)
{ // either the last item is an exact match or the real // hit is beyond the last item.
nDelta += 1;
bFound = true;
}
} elseif (nDelta > 0) // valid hit must be 2nd item or higher
{ // non-exact match
bFound = true;
}
// With 0-9 < A-Z, if query is numeric and data found is string, or // vice versa, the (yet another undocumented) Excel behavior is to // return #N/A instead.
if (bFound)
{ if (!vIndex.empty())
nDelta = vIndex[nDelta];
VectorMatrixAccessor aMatAcc(*pDataMat, bVertical);
SCCOLROW i = nDelta;
SCSIZE n = aMatAcc.GetElementCount(); if (o3tl::make_unsigned(i) >= n)
i = static_cast<SCCOLROW>(n); bool bByString = rEntry.GetQueryItem().meType == ScQueryEntry::ByString; if (bByString == aMatAcc.IsValue(i))
bFound = false;
}
if (!bFound)
{
PushNA(); return;
}
// Now that we've found the delta, push the result back to the cell.
if (pResMat)
{
VectorMatrixAccessor aResMatAcc(*pResMat, (nResRow2 - nResRow1) > 0); // Result array is matrix. // Note this does not replicate the other dimension. if (o3tl::make_unsigned(nDelta) >= aResMatAcc.GetElementCount())
{
PushNA(); return;
} if (aResMatAcc.IsValue(nDelta))
PushDouble(aResMatAcc.GetDouble(nDelta)); else
PushString(aResMatAcc.GetString(nDelta));
} elseif (nParamCount == 3)
{ /* TODO: the entire switch is a copy of the cell range search
* result, factor out. */ switch (eResArrayType)
{ case svDoubleRef: case svSingleRef:
{ // Use the result array vector. Note that the result array is assumed // to be a vector (i.e. 1-dimensional array).
ScAddress aAdr;
aAdr.SetTab(nResTab); bool bResVertical = (nResRow2 - nResRow1) > 0; if (bResVertical)
{
SCROW nTempRow = static_cast<SCROW>(nResRow1 + nDelta); if (nTempRow > mrDoc.MaxRow())
{
PushDouble(0); return;
}
aAdr.SetCol(nResCol1);
aAdr.SetRow(nTempRow);
} else
{
SCCOL nTempCol = static_cast<SCCOL>(nResCol1 + nDelta); if (nTempCol > mrDoc.MaxCol())
{
PushDouble(0); return;
}
aAdr.SetCol(nTempCol);
aAdr.SetRow(nResRow1);
}
PushCellResultToken( true, aAdr, nullptr, nullptr);
} break; case svDouble: case svString:
{ if (nDelta != 0)
PushNA(); else
{ switch (eResArrayType)
{ case svDouble:
PushDouble( fResVal ); break; case svString:
PushString( aResStr ); break; default:
; // nothing
}
}
} break; default:
assert(!"ScInterpreter::ScLookup: unhandled eResArrayType, array search");
PushIllegalParameter();
}
} else
{ // No result array. Use the data array to get the final value from. // Propagate errors from matrix again.
pDataMat->SetErrorInterpreter( this); if (bVertical)
{ if (pDataMat->IsValue(nC-1, nDelta))
PushDouble(pDataMat->GetDouble(nC-1, nDelta)); else
PushString(pDataMat->GetString(nC-1, nDelta));
} else
{ if (pDataMat->IsValue(nDelta, nR-1))
PushDouble(pDataMat->GetDouble(nDelta, nR-1)); else
PushString(pDataMat->GetString(nDelta, nR-1));
}
}
if (pResMat)
{
VectorMatrixAccessor aResMatAcc(*pResMat, (nResRow2 - nResRow1) > 0); // Use the matrix result array. // Note this does not replicate the other dimension. if (o3tl::make_unsigned(nDelta) >= aResMatAcc.GetElementCount())
{
PushNA(); return;
} if (aResMatAcc.IsValue(nDelta))
PushDouble(aResMatAcc.GetDouble(nDelta)); else
PushString(aResMatAcc.GetString(nDelta));
} elseif (nParamCount == 3)
{ /* TODO: the entire switch is a copy of the array search result, factor
* out. */ switch (eResArrayType)
{ case svDoubleRef: case svSingleRef:
{ // Use the result array vector. Note that the result array is assumed // to be a vector (i.e. 1-dimensional array).
ScAddress aAdr;
aAdr.SetTab(nResTab); bool bResVertical = (nResRow2 - nResRow1) > 0; if (bResVertical)
{
SCROW nTempRow = static_cast<SCROW>(nResRow1 + nDelta); if (nTempRow > mrDoc.MaxRow())
{
PushDouble(0); return;
}
aAdr.SetCol(nResCol1);
aAdr.SetRow(nTempRow);
} else
{
SCCOL nTempCol = static_cast<SCCOL>(nResCol1 + nDelta); if (nTempCol > mrDoc.MaxCol())
{
PushDouble(0); return;
}
aAdr.SetCol(nTempCol);
aAdr.SetRow(nResRow1);
}
PushCellResultToken( true, aAdr, nullptr, nullptr);
} break; case svDouble: case svString:
{ if (nDelta != 0)
PushNA(); else
{ switch (eResArrayType)
{ case svDouble:
PushDouble( fResVal ); break; case svString:
PushString( aResStr ); break; default:
; // nothing
}
}
} break; default:
assert(!"ScInterpreter::ScLookup: unhandled eResArrayType, range search");
PushIllegalParameter();
}
} else
{ // Regardless of whether or not the result array exists, the last // array is always used as the "result" array.
if ( nParamCount == 6 )
{
sal_Int16 k = GetInt16(); if ( k >= -2 && k <= 2 && k != 0 )
vsa.eSearchMode = static_cast<LookupSearchMode>(k); else
{
PushIllegalParameter(); return;
}
} else
vsa.eSearchMode = LookupSearchMode::Forward;
if ( nParamCount >= 5 )
{
sal_Int16 k = GetInt16(); if ( k >= -1 && k <= 3 )
vsa.eMatchMode = static_cast<MatchMode>(k); else
{
PushIllegalParameter(); return;
}
} else
vsa.eMatchMode = exactorNA;
// Optional 4th argument to set return values if not found (default is #N/A)
formula::FormulaConstTokenRef xNotFound;
FormulaError nFirstMatchError = FormulaError::NONE; if ( nParamCount >= 4 && GetStackType() != svEmptyCell )
{
xNotFound = PopToken();
nFirstMatchError = xNotFound->GetError();
nGlobalError = FormulaError::NONE; // propagate only for match or active result path
}
default:
PushIllegalParameter(); return;
} if ( ( nsR >= nsC && nsR != nrR ) || ( nsR < nsC && nsC != nrC ) )
{ // search matrix must have same number of elements as result matrix in search direction
PushIllegalParameter(); return;
}
// 1st argument is search value if (nGlobalError == FormulaError::NONE)
{ switch ( GetRawStackType() )
{ case svMissing: case svEmptyCell:
{
vsa.isEmptySearch = true;
vsa.isStringSearch = false;
vsa.sSearchStr = GetString();
} break;
// Optional 3th argument to set the value to return if all values // in the included array are empty (filter returns nothing)
formula::FormulaConstTokenRef xNotFound; if (nParamCount == 3 && GetStackType() != svEmptyCell)
xNotFound = PopToken();
SCCOL nCondResultColEnd = 0;
SCROW nCondResultRowEnd = 0;
ScMatrixRef pCondResultMatrix = nullptr;
std::vector<double> aResValues;
size_t nMatch = 0; // take 2nd argument criteria bool array switch ( GetStackType() )
{ case svMatrix : case svExternalDoubleRef: case svExternalSingleRef: case svDoubleRef: case svSingleRef:
{
pCondResultMatrix = GetMatrix(); if (!pCondResultMatrix)
{
PushError(FormulaError::IllegalParameter); return;
}
SCSIZE nC, nR;
pCondResultMatrix->GetDimensions(nC, nR);
nCondResultColEnd = static_cast<SCCOL>(nC - 1);
nCondResultRowEnd = static_cast<SCROW>(nR - 1);
// only 1 dimension of filtering allowed (also in excel) if (nCondResultColEnd > 0 && nCondResultRowEnd > 0)
{
PushError(FormulaError::NoValue); return;
}
// result matrix is filled with boolean values.
pCondResultMatrix->GetDoubleArray(aResValues);
FormulaError nError = FormulaError::NONE; auto matchNum = [&nMatch, &nError](double i) {
nError = GetDoubleErrorValue(i); if (nError != FormulaError::NONE)
{ returntrue;
} else
{ if (i > 0)
nMatch++; returnfalse;
}
};
if (auto it = std::find_if(aResValues.begin(), aResValues.end(), matchNum); it != aResValues.end())
{
PushError(nError); return;
}
} break;
default:
{
PushIllegalParameter(); return;
}
}
// bail out, no need to evaluate other arguments if (nGlobalError != FormulaError::NONE)
{
PushError(nGlobalError); return;
}
SCCOL nQueryCol1 = 0;
SCROW nQueryRow1 = 0;
SCCOL nQueryCol2 = 0;
SCROW nQueryRow2 = 0;
ScMatrixRef pQueryMatrix = nullptr; // take 1st argument range switch ( GetStackType() )
{ case svSingleRef: case svDoubleRef: case svMatrix: case svExternalSingleRef: case svExternalDoubleRef:
{
pQueryMatrix = GetMatrix(); if (!pQueryMatrix)
{
PushError( FormulaError::IllegalParameter); return;
}
SCSIZE nC, nR;
pQueryMatrix->GetDimensions( nC, nR);
nQueryCol2 = static_cast<SCCOL>(nC - 1);
nQueryRow2 = static_cast<SCROW>(nR - 1);
} break; default:
PushError( FormulaError::IllegalParameter); return;
}
// bail out, no need to set a matrix if we have no result if (!nMatch)
{ if (xNotFound && (xNotFound->GetType() != svMissing))
PushTokenRef(xNotFound); else
PushError(FormulaError::NestedArray); return;
}
SCSIZE nResPos = 0;
ScMatrixRef pResMat = nullptr; if (nQueryCol2 == nCondResultColEnd && nCondResultColEnd > 0)
{
pResMat = GetNewMat(nMatch, nQueryRow2 + 1 , /*bEmpty*/true); for (SCROW iR = nQueryRow1; iR <= nQueryRow2; iR++)
{ for (size_t iC = 0; iC < aResValues.size(); iC++)
{ if (aResValues[iC] > 0)
{ if (pQueryMatrix->IsEmptyCell(iC, iR))
pResMat->PutEmptyTrans(nResPos++); elseif (pQueryMatrix->IsStringOrEmpty(iC, iR))
pResMat->PutStringTrans(pQueryMatrix->GetString(iC, iR), nResPos++); else
pResMat->PutDoubleTrans(pQueryMatrix->GetDouble(iC, iR), nResPos++);
}
}
}
} elseif (nQueryRow2 == nCondResultRowEnd && nCondResultRowEnd > 0)
{
pResMat = GetNewMat(nQueryCol2 + 1, nMatch, /*bEmpty*/true); for (SCCOL iC = nQueryCol1; iC <= nQueryCol2; iC++)
{ for (size_t iR = 0; iR < aResValues.size(); iR++)
{ if (aResValues[iR] > 0)
{ if (pQueryMatrix->IsEmptyCell(iC, iR))
pResMat->PutEmpty(nResPos++); elseif (pQueryMatrix->IsStringOrEmpty(iC, iR))
pResMat->PutString(pQueryMatrix->GetString(iC, iR), nResPos++); else
pResMat->PutDouble(pQueryMatrix->GetDouble(iC, iR), nResPos++);
}
}
}
} else
{
PushError(FormulaError::IllegalParameter); return;
}
if (pResMat)
PushMatrix(pResMat); else
PushError(FormulaError::NestedArray);
}
// fill result matrix to the same column for (SCSIZE iPos = 0; iPos < aResPos.size(); ++iPos)
{ if (bCol)
lcl_FillCell(pMatSource, pResMat, aResPos[iPos].first, aResPos[iPos].second, 0, iPos); else
lcl_FillCell(pMatSource, pResMat, aResPos[iPos].first, aResPos[iPos].second, iPos, 0);
}
// We must fish the 1st parameter deep from the stack! And push it on top. const FormulaToken* p = pStack[ sp - nParamCount ];
PushWithoutError( *p );
sal_Int32 nFunc = GetInt32();
mnSubTotalFlags |= SubtotalFlags::IgnoreNestedStAg | SubtotalFlags::IgnoreFiltered; if (nFunc > 100)
{ // For opcodes 101 through 111, we need to skip hidden cells. // Other than that these opcodes are identical to 1 through 11.
mnSubTotalFlags |= SubtotalFlags::IgnoreHidden;
nFunc -= 100;
}
if ( nGlobalError != FormulaError::NONE || nFunc < 1 || nFunc > 11 )
PushIllegalArgument(); // simulate return on stack, not SetError(...) else
{
cPar = nParamCount - 1; switch( nFunc )
{ case SUBTOTAL_FUNC_AVE : ScAverage(); break; case SUBTOTAL_FUNC_CNT : ScCount(); break; case SUBTOTAL_FUNC_CNT2 : ScCount2(); break; case SUBTOTAL_FUNC_MAX : ScMax(); break; case SUBTOTAL_FUNC_MIN : ScMin(); break; case SUBTOTAL_FUNC_PROD : ScProduct(); break; case SUBTOTAL_FUNC_STD : ScStDev(); break; case SUBTOTAL_FUNC_STDP : ScStDevP(); break; case SUBTOTAL_FUNC_SUM : ScSum(); break; case SUBTOTAL_FUNC_VAR : ScVar(); break; case SUBTOTAL_FUNC_VARP : ScVarP(); break; default : PushIllegalArgument(); break;
}
}
mnSubTotalFlags = SubtotalFlags::NONE; // Get rid of the 1st (fished) parameter.
FormulaConstTokenRef xRef( PopToken());
Pop();
PushTokenRef( xRef);
}
// 1st argument: take range
ScMatrixRef pMatSource = nullptr;
SCSIZE nsC = 0, nsR = 0; switch (GetStackType())
{ case svSingleRef: case svDoubleRef: case svMatrix: case svExternalSingleRef: case svExternalDoubleRef:
{
pMatSource = GetMatrix(); if (!pMatSource)
{
PushIllegalParameter(); return;
}
// fish the 1st parameter from the stack and push it on top. const FormulaToken* p = pStack[ sp - nParamCount ];
PushWithoutError( *p );
sal_Int32 nFunc = GetInt32(); // fish the 2nd parameter from the stack and push it on top. const FormulaToken* p2 = pStack[ sp - ( nParamCount - 1 ) ];
PushWithoutError( *p2 );
sal_Int32 nOption = GetInt32();
if (nGlobalError != FormulaError::NONE || !pDBRef) return nullptr;
if ( bRangeFake )
{ // range parameter must match entire database range if (pDBRef->isRangeEqual(aMissingRange))
rMissingField = true; else
SetError( FormulaError::IllegalParameter );
}
if (nGlobalError != FormulaError::NONE) return nullptr;
SCCOL nField = pDBRef->getFirstFieldColumn(); if (rMissingField)
; // special case elseif (bByVal)
nField = pDBRef->findFieldColumn(static_cast<SCCOL>(nVal)); else
{
FormulaError nErr = FormulaError::NONE;
nField = pDBRef->findFieldColumn(aStr.getString(), &nErr);
SetError(nErr);
}
if (pParam)
{ // An allowed missing field parameter sets the result field // to any of the query fields, just to be able to return // some cell from the iterator. if ( rMissingField )
nField = static_cast<SCCOL>(pParam->GetEntry(0).nField);
pParam->mnField = nField;
SCSIZE nCount = pParam->GetEntryCount(); for ( SCSIZE i=0; i < nCount; i++ )
{
ScQueryEntry& rEntry = pParam->GetEntry(i); if (!rEntry.bDoQuery) break;
void ScInterpreter::ScDBCount()
{ bool bMissingField = true;
unique_ptr<ScDBQueryParamBase> pQueryParam( GetDBParams(bMissingField) ); if (pQueryParam)
{
sal_uLong nCount = 0; if ( bMissingField && pQueryParam->GetType() == ScDBQueryParamBase::INTERNAL )
{ // count all matching records // TODO: currently the QueryIterators only return cell pointers of // existing cells, so if a query matches an empty cell there's // nothing returned, and therefore not counted! // Since this has ever been the case and this code here only came // into existence to fix #i6899 and it never worked before we'll // have to live with it until we reimplement the iterators to also // return empty cells, which would mean to adapt all callers of // iterators.
ScDBQueryParamInternal* p = static_cast<ScDBQueryParamInternal*>(pQueryParam.get());
p->nCol2 = p->nCol1; // Don't forget to select only one column.
SCTAB nTab = p->nTab; // ScQueryCellIteratorDirect doesn't make use of ScDBQueryParamBase::mnField, // so the source range has to be restricted, like before the introduction // of ScDBQueryParamBase.
p->nCol1 = p->nCol2 = p->mnField;
ScQueryCellIteratorDirect aCellIter(mrDoc, mrContext, nTab, *p, true, false); if ( aCellIter.GetFirst() )
{ do
{
nCount++;
} while ( aCellIter.GetNext() );
}
} else
{ // count only matching records with a value in the "result" field if (!pQueryParam->IsValidFieldIndex())
{
SetError(FormulaError::NoValue); return;
}
ScDBQueryDataIterator aValIter(mrDoc, mrContext, std::move(pQueryParam));
ScDBQueryDataIterator::Value aValue; if ( aValIter.GetFirst(aValue) && aValue.mnError == FormulaError::NONE )
{ do
{
nCount++;
} while ( aValIter.GetNext(aValue) && aValue.mnError == FormulaError::NONE );
}
SetError(aValue.mnError);
}
PushDouble( nCount );
} else
PushIllegalParameter();
}
// Reference address syntax for INDIRECT is configurable.
FormulaGrammar::AddressConvention eConv = maCalcConfig.meStringRefAddressSyntax; if (eConv == FormulaGrammar::CONV_UNSPECIFIED) // Use the current address syntax if unspecified.
eConv = mrDoc.GetAddressConvention();
// either CONV_A1_XL_A1 was explicitly configured, or it wasn't possible // to determine which syntax to use during doc import bool bTryXlA1 = (eConv == FormulaGrammar::CONV_A1_XL_A1);
if (nParamCount == 2 && 0.0 == GetDouble() )
{ // Overwrite the config and try Excel R1C1.
eConv = FormulaGrammar::CONV_XL_R1C1;
bTryXlA1 = false;
}
svl::SharedString sSharedRefStr = GetString(); const OUString & sRefStr = sSharedRefStr.getString(); if (sRefStr.isEmpty())
{ // Bail out early for empty cells, rely on "we do have a string" below.
PushError( FormulaError::NoRef); return;
}
// Named expressions and DB range names need to be tried first, as older 1K // columns allowed names that would now match a 16k columns cell address. do
{
ScRangeData* pData = ScRangeStringConverter::GetRangeDataFromString( sRefStr, nTab, mrDoc, eConv); if (!pData) break;
// We need this in order to obtain a good range.
pData->ValidateTabRefs();
ScRange aRange;
// This is the usual way to treat named ranges containing // relative references. if (!pData->IsReference(aRange, aPos))
{
sTabRefStr = pData->GetSymbol();
bTableRefNamed = lcl_IsTableStructuredRef(sTabRefStr, nTableRefNamedIndex); // if bTableRefNamed is true, we have a name that maps to a table structured reference. // Such a case is handled below. break;
}
// In Excel, specifying a table name without [] resolves to the // same as with [], a range that excludes header and totals // rows and contains only data rows. Do the same. if (pData->HasHeader())
aRange.aStart.IncRow(); if (pData->HasTotals())
aRange.aEnd.IncRow(-1);
if (aRange.aStart.Row() > aRange.aEnd.Row()) break;
ScRefAddress aRefAd, aRefAd2;
ScAddress::ExternalInfo aExtInfo; if ( !bTableRefNamed &&
(ConvertDoubleRef(mrDoc, sRefStr, nTab, aRefAd, aRefAd2, aDetails, &aExtInfo) ||
( bTryXlA1 && ConvertDoubleRef(mrDoc, sRefStr, nTab, aRefAd,
aRefAd2, aDetailsXlA1, &aExtInfo) ) ) )
{ if (aExtInfo.mbExternal)
{
PushExternalDoubleRef(
aExtInfo.mnFileId, aExtInfo.maTabName,
aRefAd.Col(), aRefAd.Row(), aRefAd.Tab(),
aRefAd2.Col(), aRefAd2.Row(), aRefAd2.Tab());
} else
PushDoubleRef( aRefAd, aRefAd2);
} elseif ( !bTableRefNamed &&
(ConvertSingleRef(mrDoc, sRefStr, nTab, aRefAd, aDetails, &aExtInfo) ||
( bTryXlA1 && ConvertSingleRef (mrDoc, sRefStr, nTab, aRefAd,
aDetailsXlA1, &aExtInfo) ) ) )
{ if (aExtInfo.mbExternal)
{
PushExternalSingleRef(
aExtInfo.mnFileId, aExtInfo.maTabName, aRefAd.Col(), aRefAd.Row(), aRefAd.Tab());
} else
PushSingleRef( aRefAd);
} else
{ // It may be even a TableRef or an external name. // Anything else that resolves to one reference could be added // here, but we don't want to compile every arbitrary string. This // is already nasty enough...
sal_Int32 nIndex = bTableRefNamed ? nTableRefNamedIndex : -1; bool bTableRef = bTableRefNamed; if (!bTableRefNamed)
bTableRef = lcl_IsTableStructuredRef(sRefStr, nIndex); bool bExternalName = false; // External references would had been consumed above already. if (!bTableRef)
{ // This is our own file name reference representation centric.. but // would work also for XL '[doc]'!name and also for // '[doc]Sheet1'!name ... sickos. if (sRefStr[0] == '\'')
{ // Minimum 'a'#name or 'a'!name // bTryXlA1 means try both, first our own. if (bTryXlA1 || eConv == FormulaGrammar::CONV_OOO)
{
nIndex = ScGlobal::FindUnquoted( sRefStr, '#'); if (nIndex >= 3 && sRefStr[nIndex-1] == '\'')
{
bExternalName = true;
eConv = FormulaGrammar::CONV_OOO;
}
} if (!bExternalName && (bTryXlA1 || eConv != FormulaGrammar::CONV_OOO))
{
nIndex = ScGlobal::FindUnquoted( sRefStr, '!'); if (nIndex >= 3 && sRefStr[nIndex-1] == '\'')
{
bExternalName = true;
}
}
}
} if (bExternalName || bTableRef)
{ do
{
ScCompiler aComp( mrDoc, aPos, mrDoc.GetGrammar());
aComp.SetRefConvention( eConv); // must be after grammar
std::unique_ptr<ScTokenArray> pTokArr( aComp.CompileString(bTableRefNamed ? sTabRefStr : sRefStr));
if (pTokArr->GetCodeError() != FormulaError::NONE || !pTokArr->GetLen()) break;
// Whatever... use only the specific case. if (bExternalName)
{ const formula::FormulaToken* pTok = pTokArr->FirstToken(); if (!pTok || pTok->GetType() != svExternalName) break;
} elseif (!pTokArr->HasOpCode( ocTableRef)) break;
aComp.CompileTokenArray();
// A syntactically valid reference will generate exactly // one RPN token, a reference or error. Discard everything // else as error. if (pTokArr->GetCodeLen() != 1) break;
ScTokenRef xTok( pTokArr->FirstRPNToken()); if (!xTok) break;
switch (xTok->GetType())
{ case svSingleRef: case svDoubleRef: case svExternalSingleRef: case svExternalDoubleRef: case svError:
PushTokenRef( xTok); // success! return; default:
; // nothing
}
} while (false);
}
FormulaGrammar::AddressConvention eConv = FormulaGrammar::CONV_OOO; // default if (nParamCount >= 4 && 0.0 == GetDoubleWithDefault( 1.0))
eConv = FormulaGrammar::CONV_XL_R1C1; else
{ // If A1 syntax is requested then the actual sheet separator and format // convention depends on the syntax configured for INDIRECT to match // that, and if it is unspecified then the document's address syntax.
FormulaGrammar::AddressConvention eForceConv = maCalcConfig.meStringRefAddressSyntax; if (eForceConv == FormulaGrammar::CONV_UNSPECIFIED)
eForceConv = mrDoc.GetAddressConvention(); if (eForceConv == FormulaGrammar::CONV_XL_A1 || eForceConv == FormulaGrammar::CONV_XL_R1C1)
eConv = FormulaGrammar::CONV_XL_A1; // for anything Excel use Excel A1
}
// Access one element of a vector independent of col/row // orientation. Excel documentation does not mention, but // i62850 had a .xls example of a row vector accessed by // row number returning one element. This // INDEX(row_vector;element) behaves the same as // INDEX(row_vector;0;element) and thus contradicts Excel // documentation where the second parameter is always // row_num. // // ODFF v1.3 in 6.14.6 INDEX states "If DataSource is a // one-dimensional row vector, Row is optional, which // effectively makes Row act as the column offset into the // vector". Guess the first Row is a typo and should read // Column instead.
// Flags are supported only for replacement, search match flags can be // individually and much more flexible set in the regular expression // pattern using (?ismwx-ismwx) bool bGlobalReplacement = false;
sal_Int32 nOccurrence = 1; // default first occurrence, if any if (nParamCount == 4)
{ // Argument can be either string or double. double fOccurrence;
svl::SharedString aFlagsString; bool bDouble; if (!IsMissing())
bDouble = GetDoubleOrString( fOccurrence, aFlagsString); else
{ // For an omitted argument keep the default.
PopError();
bDouble = true;
fOccurrence = nOccurrence;
} if (nGlobalError != FormulaError::NONE)
{
PushError( nGlobalError); return;
} if (bDouble)
{ if (!CheckStringPositionArgument( fOccurrence))
{
PushError( FormulaError::IllegalArgument); return;
}
nOccurrence = static_cast<sal_Int32>(fOccurrence);
} else
{ const OUString& aFlags( aFlagsString.getString()); // Empty flags string is valid => no flag set. if (aFlags.getLength() > 1)
{ // Only one flag supported.
PushIllegalArgument(); return;
} if (aFlags.getLength() == 1)
{ if (aFlags.indexOf('g') >= 0)
bGlobalReplacement = true; else
{ // Unsupported flag.
PushIllegalArgument(); return;
}
}
}
}
bool bReplacement = false;
OUString aReplacement; if (nParamCount >= 3)
{ // A missing argument is not an empty string to replace the match. // nOccurrence==0 forces no replacement, so simply discard the // argument. if (IsMissing() || nOccurrence == 0)
PopError(); else
{
aReplacement = GetString().getString();
bReplacement = true;
}
} // If bGlobalReplacement==true and bReplacement==false then // bGlobalReplacement is silently ignored.
if (!bReplacement)
{ // Find n-th occurrence.
sal_Int32 nCount = 0; while (aRegexMatcher.find(status) && U_SUCCESS(status) && ++nCount < nOccurrence)
; if (U_FAILURE(status))
{ // Some error.
PushIllegalArgument(); return;
} // n-th match found? if (nCount != nOccurrence)
{
PushError( FormulaError::NotAvailable); return;
} // Extract matched text.
icu::UnicodeString aMatch( aRegexMatcher.group( status)); if (U_FAILURE(status))
{ // Some error.
PushIllegalArgument(); return;
}
OUString aResult( reinterpret_cast<const sal_Unicode*>(aMatch.getBuffer()), aMatch.length());
PushString( aResult); return;
}
const icu::UnicodeString aIcuReplacement( false, reinterpret_cast<const UChar*>(aReplacement.getStr()), aReplacement.getLength());
icu::UnicodeString aReplaced; if (bGlobalReplacement) // Replace all occurrences of match with replacement.
aReplaced = aRegexMatcher.replaceAll( aIcuReplacement, status); elseif (nOccurrence == 1) // Replace first occurrence of match with replacement.
aReplaced = aRegexMatcher.replaceFirst( aIcuReplacement, status); else
{ // Replace n-th occurrence of match with replacement.
sal_Int32 nCount = 0; while (aRegexMatcher.find(status) && U_SUCCESS(status))
{ // XXX NOTE: After several RegexMatcher::find() the // RegexMatcher::appendReplacement() still starts at the // beginning (or after the last appendReplacement() position // which is none here) and copies the original text up to the // current found match and then replaces the found match. if (++nCount == nOccurrence)
{
aRegexMatcher.appendReplacement( aReplaced, aIcuReplacement, status); break;
}
}
aRegexMatcher.appendTail( aReplaced);
} if (U_FAILURE(status))
{ // Some error, e.g. extraneous $1 without group.
PushIllegalArgument(); return;
}
OUString aResult( reinterpret_cast<const sal_Unicode*>(aReplaced.getBuffer()), aReplaced.length());
PushString( aResult);
}
sal_Int32 nCnt; if (nParamCount == 4)
{
nCnt = GetStringPositionArgument(); if (nCnt < 1)
{
PushIllegalArgument(); return;
}
} else
nCnt = 0;
OUString sNewStr = GetString().getString();
OUString sOldStr = GetString().getString();
OUString sStr = GetString().getString();
sal_Int32 nPos = 0;
sal_Int32 nCount = 0;
std::optional<OUStringBuffer> oResult; for (sal_Int32 nEnd = sStr.indexOf(sOldStr); nEnd >= 0; nEnd = sStr.indexOf(sOldStr, nEnd))
{ if (nCnt == 0 || ++nCount == nCnt) // Found a replacement cite
{ if (!oResult) // Only allocate buffer when needed
oResult.emplace(sStr.getLength() + sNewStr.getLength() - sOldStr.getLength());
oResult->append(sStr.subView(nPos, nEnd - nPos)); // Copy leading unchanged text if (!CheckStringResultLen(*oResult, sNewStr.getLength())) return PushError(GetError());
oResult->append(sNewStr); // Copy the replacement
nPos = nEnd + sOldStr.getLength(); if (nCnt > 0) // Found the single replacement site - end the loop break;
}
nEnd += sOldStr.getLength();
} if (oResult) // If there were prior replacements, copy the rest, otherwise use original
oResult->append(sStr.subView(nPos, sStr.getLength() - nPos));
PushString(oResult ? oResult->makeStringAndClear() : sStr);
}
staticbool MayBeRegExp( std::u16string_view rStr )
{ if ( rStr.empty() || (rStr.size() == 1 && rStr[0] != '.') ) returnfalse; // single meta characters can not be a regexp // First two characters are wildcard '?' and '*' characters.
std::u16string_view cre(u"?*+.[]^$\\<>()|"); return rStr.find_first_of(cre) != std::u16string_view::npos;
}
staticbool MayBeWildcard( std::u16string_view rStr )
{ // Wildcards with '~' escape, if there are no wildcards then an escaped // character does not make sense, but it modifies the search pattern in an // Excel compatible wildcard search...
std::u16string_view cw(u"*?~"); return rStr.find_first_of(cw) != std::u16string_view::npos;
}
switch ( vsa.eSearchMode )
{ case LookupSearchMode::Forward :
{ switch ( vsa.eMatchMode )
{ case exactorNA : case wildcard : // simple serial search for equality mode (source data doesn't // need to be sorted). for (SCSIZE i = 0; i < nMatCount; ++i)
{ if (lcl_CompareMatrix2Query( i, aMatAcc, rParam, rEntry, bMatchWholeCell ) == 0)
{
vsa.nHitIndex = i+1; // found ! break;
}
} break;
case exactorS : case exactorG : for (SCSIZE i = 0; i < nMatCount; ++i)
{
sal_Int32 result = lcl_CompareMatrix2Query( i, aMatAcc, rParam, rEntry, bMatchWholeCell ); if (result == 0)
{
vsa.nHitIndex = i+1; // found ! break;
} elseif (vsa.eMatchMode == exactorS && result == -1)
{ if ( vsa.nBestFit == SCSIZE_MAX )
vsa.nBestFit = i; else
{ // replace value of vsa.nBestFit if value(i) > value(vsa.nBestFit) if ( lcl_Compare2MatrixCells( i, aMatAcc, vsa.nBestFit) == 1 )
vsa.nBestFit = i;
}
} elseif (vsa.eMatchMode == exactorG && result == 1)
{ if ( vsa.nBestFit == SCSIZE_MAX )
vsa.nBestFit = i; else
{ // replace value of vsa.nBestFit if value(i) < value(vsa.nBestFit) if ( lcl_Compare2MatrixCells( i, aMatAcc, vsa.nBestFit) == -1 )
vsa.nBestFit = i;
}
} // else do nothing
} break;
case LookupSearchMode::Reverse:
{ switch ( vsa.eMatchMode )
{ case exactorNA : case wildcard : // simple serial search for equality mode (source data doesn't // need to be sorted). for ( SCSIZE i = nMatCount; i > 0; i-- )
{ if (lcl_CompareMatrix2Query(i - 1, aMatAcc, rParam, rEntry, bMatchWholeCell) == 0)
{
vsa.nHitIndex = i; // found ! break;
}
} break;
case exactorS : case exactorG : for (SCSIZE i = nMatCount - 1; i-- > 0; )
{
sal_Int32 result = lcl_CompareMatrix2Query( i, aMatAcc, rParam, rEntry, bMatchWholeCell ); if (result == 0)
{
vsa.nHitIndex = i + 1; // found ! break;
} elseif (vsa.eMatchMode == exactorS && result == -1)
{ if ( vsa.nBestFit == SCSIZE_MAX )
vsa.nBestFit = i; else
{ // replace value of vsa.nBestFit if value(i) > value(vsa.nBestFit) if ( lcl_Compare2MatrixCells( i, aMatAcc, vsa.nBestFit) == 1 )
vsa.nBestFit = i;
}
} elseif (vsa.eMatchMode == exactorG && result == 1)
{ if ( vsa.nBestFit == SCSIZE_MAX )
vsa.nBestFit = i; else
{ // replace value of vsa.nBestFit if value(i) < value(vsa.nBestFit) if ( lcl_Compare2MatrixCells( i, aMatAcc, vsa.nBestFit) == -1 )
vsa.nBestFit = i;
}
} // else do nothing
} break;
case exactorS :
rEntry.eOp = SC_LESS_EQUAL; break;
case exactorG :
rEntry.eOp = SC_GREATER_EQUAL; break;
case wildcard : case regex : // this mode can only used with XLOOKUP/XMATCH if ( vsa.nSearchOpCode == SC_OPCODE_X_LOOKUP || vsa.nSearchOpCode == SC_OPCODE_X_MATCH )
{ // Wildcard/Regex search mode with binary search is not allowed if (vsa.eSearchMode == LookupSearchMode::BinaryAscending || vsa.eSearchMode == LookupSearchMode::BinaryDescending)
{
PushNoValue(); returnfalse;
}
ScQueryEntry::Item& rItem = rEntry.GetQueryItem(); // allow to match empty cells as result if we are looking for the next smaller // or larger values in case of the new lookup functions if (rEntry.eOp != SC_EQUAL && (vsa.nSearchOpCode == SC_OPCODE_X_LOOKUP ||
vsa.nSearchOpCode == SC_OPCODE_X_MATCH))
rItem.mbMatchEmpty = true;
// tdf#121052: // =VLOOKUP(SearchCriterion; RangeArray; Index; Sorted) // [SearchCriterion] is the value searched for in the first column of the array. // [RangeArray] is the reference, which is to comprise at least two columns. // [Index] is the number of the column in the array that contains the value to be returned. The first column has the number 1. // // Prerequisite of lcl_getPrevRowWithEmptyValueLookup(): // Value referenced by [SearchCriterion] is empty. // lcl_getPrevRowWithEmptyValueLookup() performs following checks: // - if we run query with "exact match" mode (i.e. VLOOKUP) // - and if we already have the same lookup done before but for another row // which is also had empty [SearchCriterion] // // then // we could say, that for current row we could reuse results of the cached call which was done for the row2 // In this case we return row index, which is >= 0. // // Elsewhere // -1 is returned, which will lead to default behavior => // complete lookup will be done in RangeArray inside lcl_LookupQuery() method. // // This method was added only for speed up to avoid several useless complete // lookups inside [RangeArray] for searching empty strings. // static SCROW lcl_getPrevRowWithEmptyValueLookup( const ScLookupCache& rCache, const ScLookupCache::QueryCriteria& rCriteria, const ScQueryParam & rParam)
{ // is lookup value empty? const ScQueryEntry& rEntry = rParam.GetEntry(0); const ScQueryEntry::Item& rItem = rEntry.GetQueryItem(); if (! rItem.maString.getString().isEmpty()) return -1; // not found
// try to find the row index for which we have already performed lookup // and have some result of it inside cache return rCache.lookup( rCriteria );
}
bool ScInterpreter::LookupQueryWithCache( ScAddress & o_rResultPos, const ScQueryParam & rParam, const ScComplexRefData* refData,
LookupSearchMode nSearchMode, sal_uInt16 nOpCode ) const
{ bool bFound = false; const ScQueryEntry& rEntry = rParam.GetEntry(0); bool bColumnsMatch = (rParam.nCol1 == rEntry.nField); // At least all volatile functions that generate indirect references have // to force non-cached lookup. /* TODO: We could further classify volatile functions into reference *generatingandnotreferencegeneratingfunctionstohavetoforceless *directlookupshere.Wecouldevenfurtherattributevolatilityper
* parameter so it would affect only the lookup range parameter. */ if (!bColumnsMatch || GetVolatileType() != NOT_VOLATILE)
bFound = lcl_LookupQuery( o_rResultPos, mrDoc, mrContext, rParam, rEntry, pMyFormulaCell,
refData, nSearchMode, nOpCode ); else
{
ScRange aLookupRange( rParam.nCol1, rParam.nRow1, rParam.nTab,
rParam.nCol2, rParam.nRow2, rParam.nTab);
ScLookupCache& rCache = mrDoc.GetLookupCache( aLookupRange, &mrContext );
ScLookupCache::QueryCriteria aCriteria( rEntry, nSearchMode);
ScLookupCache::Result eCacheResult = rCache.lookup( o_rResultPos,
aCriteria, aPos);
// tdf#121052: Slow load of cells with VLOOKUP with references to empty cells // This check was added only for speed up to avoid several useless complete // lookups inside [RangeArray] for searching empty strings. if (eCacheResult == ScLookupCache::NOT_CACHED && aCriteria.isEmptyStringQuery())
{ const SCROW nPrevRowWithEmptyValueLookup = lcl_getPrevRowWithEmptyValueLookup(rCache, aCriteria, rParam); if (nPrevRowWithEmptyValueLookup >= 0)
{ // make the same lookup using cache with different row index // (this lookup was already cached)
ScAddress aPosPrev(aPos);
aPosPrev.SetRow(nPrevRowWithEmptyValueLookup);
¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen1.247Angebot
(Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können 2026-10-01)
¤
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung und die Messung sind noch experimentell.
