/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ /* * This file is part of the LibreOffice project. * * This Source Code Form is subject to the terms of the Mozilla Public * License, v. 2.0. If a copy of the MPL was not distributed with this * file, You can obtain one at http://mozilla.org/MPL/2.0/. * * This file incorporates work covered by the following license notice: * * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed * with this work for additional information regarding copyright * ownership. The ASF licenses this file to you under the Apache * License, Version 2.0 (the "License"); you may not use this file * except in compliance with the License. You may obtain a copy of * the License at http://www.apache.org/licenses/LICENSE-2.0 .
*/
/** Store a matrix value in another matrix in the context of that other matrix is the result matrix of a jump matrix. All arguments must be valid and are
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 ) return 0;
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 * Takashi Nakamoto <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() ) );
}
/* ODFF: * Summary: Converts half-width to full-width ASCII and katakana characters. * Semantics: Conversion is done for half-width ASCII and katakana characters, * other characters are simply copied from T to the result. This is the * complementary function to ASC. * For references regarding halfwidth and fullwidth characters see * http://www.unicode.org/reports/tr11/ * http://www.unicode.org/charts/charindex2.html#H * http://www.unicode.org/charts/charindex2.html#F
*/ void ScInterpreter::ScJis()
{ if (MustHaveParamCount( GetByte(), 1))
PushString( lcl_convertIntoFullWidth( GetString().getString()));
}
/* ODFF: * Summary: Converts full-width to half-width ASCII and katakana characters. * Semantics: Conversion is done for full-width ASCII and katakana characters, * other characters are simply copied from T to the result. This is the * complementary function to JIS.
*/ void ScInterpreter::ScAsc()
{ if (MustHaveParamCount( GetByte(), 1))
PushString( lcl_convertIntoHalfWidth( GetString().getString()));
}
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 * if a value is numeric_limits::max. Still, that could be a valid * minimum value as well, but nVal and nMin had been reset 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 < 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 * if a value is numeric_limits::lowest. Still, that could be a * valid maximum value as well, but nVal and nMax had been reset
* 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);
}
}
/* this was: PushDouble( sqrt( div( nVal, nValCount))); * * Besides that the special NAN gets lost in the call through sqrt(), * unxlngi6.pro then looped back and forth somewhere between div() and * ::rtl::math::setNan(). Tests showed that * * sqrt( div( 1, 0)); * * produced a loop, but * * double f1 = div( 1, 0); * sqrt( f1 ); * * was fine. There seems to be some compiler optimization problem. It does * not occur when compiled with debug=t.
*/
}
/** returns -1 when the matrix value is smaller than the query value, 0 when they are equal, and 1 when the matrix value is larger than the query
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 return 1; // 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. return 1; // 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) return 0;
return nVal1 < nVal2 ? -1 : 1;
}
if (!bByString) return 1; // 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) return 0; // 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)) return 1;
bool bByString = rMat.IsStringOrEmpty(j); // string, empty has already been handled if (rMat.IsValue(i))
{ constdouble nVal1 = rMat.GetDouble(i); if (!std::isfinite(nVal1)) return 1; // error always greater than numeric or string
if (bByString) return -1; // numeric always less than string
constdouble nVal2 = rMat.GetDouble(j); if (nVal1 == nVal2) return 0;
return ( nVal1 < nVal2 ? -1 : 1 );
}
if (!bByString) return 1; // 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 * all constellations or if there are cases that include unsorted error * values and thus yield arbitrary binary search results or something * different or whether there are cases where error values are also * omitted from mixed numeric/string arrays or if it's not an interim
* 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.
void ScInterpreter::ScXLookup()
{ /* TODO -use VectorSearchArguments and SearchVectorForValue() with ScLookup, ScHLookup and ScVLookup as well to reduce redundant code, can de done later with lots of other MATCH/LOOKUP related code that can be unified -BinarySearch not supported for columns (horizontal search), now just use linear mode in this case -improve efficiency of code
*/
sal_uInt8 nParamCount = GetByte(); if ( !MustHaveParamCount( nParamCount, 3, 6 ) ) return;
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);
}
switch ( nErr )
{ case FormulaError::NoCode : // #NULL!
nErrType = 1; break; case FormulaError::DivisionByZero : // #DIV/0!
nErrType = 2; break; case FormulaError::NoValue : // #VALUE!
nErrType = 3; break; case FormulaError::NoRef : // #REF!
nErrType = 4; break; case FormulaError::NoName : // #NAME?
nErrType = 5; break; case FormulaError::IllegalFPOperation : // #NUM!
nErrType = 6; break; case FormulaError::NotAvailable : // #N/A
nErrType = 7; break; /* #GETTING_DATA is a message that can appear in Excel when a large or complex worksheet is being calculated. In Excel 2007 and newer, operations are grouped so more complicated cells may finish after earlier ones do. While the calculations are still processing, the unfinished cells may display #GETTING_DATA. Because the message is temporary and disappears when the calculations complete, this isn’t a true error. No calc error code known (yet).
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;
/** When search value is found, the index is stored in struct VectorSearchArguments.nIndex and SearchVectorForValue() returns true. When search value is not found or an error occurs, SearchVectorForValue() pushes the relevant (error)message and returns false, expect when SearchVectorForValue() is called by ScXLookup and the search value is not found. This difference in behaviour is because MATCH returns the found index and XLOOKUP uses the found index to determine the result(s) to be pushed and may return a custom value when the search value is not found.
*/ bool ScInterpreter::SearchVectorForValue( VectorSearchArguments& vsa )
{ // preparations
ScQueryParam rParam;
rParam.nCol1 = vsa.nCol1;
rParam.nRow1 = vsa.nRow1;
rParam.nCol2 = vsa.nCol2;
rParam.nRow2 = vsa.nRow2;
rParam.nTab = vsa.nTab1;
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 * generating and not reference generating functions to have to force less * direct lookups here. We could even further attribute volatility per
* 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);
¤ 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.0.776Bemerkung:
(vorverarbeitet am 2026-05-06)
¤
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.
