Hi, The attached patch implements the much requested IFERROR and IFNA spreadsheet functions on the 4-0 branch. I would like to have them as late feature in 4-0-0 if possible though string freeze was announced already, so 3 reviews would be needed. At least 4-0 should have it for 4-1-0 Thanks Eike -- LibreOffice Calc developer. Number formatter stricken i18n transpositionizer. New GnuPG key 0x65632D3A : 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A Old GnuPG key 0x293C05FD : 997A 4C60 CE41 0149 0DB3 9E96 2F1A D073 293C 05FD Support the FSFE, care about Free Software! https://fsfe.org/support/?erack
From cc101e678799c0f437dc02b6930fe858c560972d Mon Sep 17 00:00:00 2001
Message-Id: <cc101e678799c0f437dc02b6930fe858c560972d.1358013933.git.erack@redhat.com>
From: Eike Rathke <erack@redhat.com>
Date: Sat, 12 Jan 2013 17:48:59 +0100
Subject: [PATCH] fdo#56124 add functions IFERROR and IFNA to calc as in
ODFF1.2
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="------------erAck-patch-parts"
This is a multi-part message in MIME format.
--------------erAck-patch-parts
Content-Type: text/plain; charset=UTF-8; format=fixed
Content-Transfer-Encoding: 8bit
Change-Id: Ic282e1510e121be8fe52320f1f0fe0acc4b9a652
---
formula/inc/formula/compiler.hrc | 42 +++---
formula/inc/formula/errorcodes.hxx | 3 +
formula/inc/formula/opcode.hxx | 2 +
formula/source/core/api/FormulaCompiler.cxx | 79 ++++++++--
formula/source/core/api/token.cxx | 22 ++-
formula/source/core/resource/core_resource.src | 12 ++
sc/inc/helpids.h | 2 +
sc/qa/unit/ucalc.cxx | 76 +++++++++
sc/source/core/inc/interpre.hxx | 1 +
sc/source/core/tool/interpr1.cxx | 198 ++++++++++++++++++++++--
sc/source/core/tool/interpr4.cxx | 13 +-
sc/source/core/tool/parclass.cxx | 4 +
sc/source/core/tool/token.cxx | 5 +
sc/source/filter/excel/xlformula.cxx | 13 ++-
sc/source/filter/oox/formulabase.cxx | 11 +-
sc/source/ui/src/scfuncs.src | 64 ++++++++
sc/util/hidother.src | 2 +
17 files changed, 485 insertions(+), 64 deletions(-)
--------------erAck-patch-parts
Content-Type: text/x-patch; name="0001-fdo-56124-add-functions-IFERROR-and-IFNA-to-calc-as-.patch"
Content-Transfer-Encoding: 8bit
Content-Disposition: attachment;
filename="0001-fdo-56124-add-functions-IFERROR-and-IFNA-to-calc-as-.patch"
diff --git a/formula/inc/formula/compiler.hrc b/formula/inc/formula/compiler.hrc
index 582e3a5..7f976ae 100644
--- a/formula/inc/formula/compiler.hrc
+++ b/formula/inc/formula/compiler.hrc
@@ -29,26 +29,28 @@
#define SC_OPCODE_NAME 4
#define SC_OPCODE_EXTERNAL_REF 5
#define SC_OPCODE_IF 6 /* jump commands */
-#define SC_OPCODE_CHOSE 7
-#define SC_OPCODE_OPEN 8 /* parentheses and separators */
-#define SC_OPCODE_CLOSE 9
-#define SC_OPCODE_SEP 10
-#define SC_OPCODE_MISSING 11 /* special OpCodes */
-#define SC_OPCODE_BAD 12
-#define SC_OPCODE_STRINGXML 13
-#define SC_OPCODE_SPACES 14
-#define SC_OPCODE_MAT_REF 15
-#define SC_OPCODE_DB_AREA 16 /* additional access operators */
-#define SC_OPCODE_MACRO 17
-#define SC_OPCODE_COL_ROW_NAME 18
-#define SC_OPCODE_COL_ROW_NAME_AUTO 19
-#define SC_OPCODE_PERCENT_SIGN 20 /* operator _follows_ value */
-#define SC_OPCODE_ARRAY_OPEN 21
-#define SC_OPCODE_ARRAY_CLOSE 22
-#define SC_OPCODE_ARRAY_ROW_SEP 23
-#define SC_OPCODE_ARRAY_COL_SEP 24 /* some convs use sep != col_sep */
-#define SC_OPCODE_STOP_DIV 25
-#define SC_OPCODE_SKIP 26 /* used to skip raw tokens during string compilation */
+#define SC_OPCODE_IF_ERROR 7
+#define SC_OPCODE_IF_NA 8
+#define SC_OPCODE_CHOSE 9
+#define SC_OPCODE_OPEN 10 /* parentheses and separators */
+#define SC_OPCODE_CLOSE 11
+#define SC_OPCODE_SEP 12
+#define SC_OPCODE_MISSING 13 /* special OpCodes */
+#define SC_OPCODE_BAD 14
+#define SC_OPCODE_STRINGXML 15
+#define SC_OPCODE_SPACES 16
+#define SC_OPCODE_MAT_REF 17
+#define SC_OPCODE_DB_AREA 18 /* additional access operators */
+#define SC_OPCODE_MACRO 19
+#define SC_OPCODE_COL_ROW_NAME 20
+#define SC_OPCODE_COL_ROW_NAME_AUTO 21
+#define SC_OPCODE_PERCENT_SIGN 22 /* operator _follows_ value */
+#define SC_OPCODE_ARRAY_OPEN 23
+#define SC_OPCODE_ARRAY_CLOSE 24
+#define SC_OPCODE_ARRAY_ROW_SEP 25
+#define SC_OPCODE_ARRAY_COL_SEP 26 /* some convs use sep != col_sep */
+#define SC_OPCODE_STOP_DIV 27
+#define SC_OPCODE_SKIP 28 /* used to skip raw tokens during string compilation */
/*** error constants #... ***/
#define SC_OPCODE_START_ERRORS 30
diff --git a/formula/inc/formula/errorcodes.hxx b/formula/inc/formula/errorcodes.hxx
index a99dc7b..1e145e0 100644
--- a/formula/inc/formula/errorcodes.hxx
+++ b/formula/inc/formula/errorcodes.hxx
@@ -72,6 +72,9 @@ const sal_uInt16 errNestedArray = 533;
// be used to distinguish that condition from all other (inherited) errors. Do
// not use for anything else! Never push or inherit the error otherwise!
const sal_uInt16 errNotNumericString = 534;
+// ScInterpreter internal: jump matrix already has a result at this position,
+// do not overwrite in case of empty code path.
+const sal_uInt16 errJumpMatHasResult = 535;
// Interpreter: NA() not available condition, not a real error
const sal_uInt16 NOTAVAILABLE = 0x7fff;
diff --git a/formula/inc/formula/opcode.hxx b/formula/inc/formula/opcode.hxx
index cd1831b..e37395e 100644
--- a/formula/inc/formula/opcode.hxx
+++ b/formula/inc/formula/opcode.hxx
@@ -34,6 +34,8 @@ enum OpCodeEnum
ocExternalRef = SC_OPCODE_EXTERNAL_REF,
// Jump commands
ocIf = SC_OPCODE_IF,
+ ocIfError = SC_OPCODE_IF_ERROR,
+ ocIfNA = SC_OPCODE_IF_NA,
ocChose = SC_OPCODE_CHOSE,
// Parentheses and separators
ocOpen = SC_OPCODE_OPEN,
diff --git a/formula/source/core/api/FormulaCompiler.cxx
b/formula/source/core/api/FormulaCompiler.cxx
index f79201b..c402113 100644
--- a/formula/source/core/api/FormulaCompiler.cxx
+++ b/formula/source/core/api/FormulaCompiler.cxx
@@ -433,6 +433,8 @@ uno::Sequence< sheet::FormulaOpCodeMapEntry > FormulaCompiler::OpCodeMap::create
// Additional functions not within range of functions.
static const sal_uInt16 aOpCodes[] = {
SC_OPCODE_IF,
+ SC_OPCODE_IF_ERROR,
+ SC_OPCODE_IF_NA,
SC_OPCODE_CHOSE,
SC_OPCODE_AND,
SC_OPCODE_OR,
@@ -824,7 +826,7 @@ sal_uInt16 FormulaCompiler::GetErrorConstant( const String& rName ) const
{
switch ((*iLook).second)
{
- // Not all may make sense in a formula, but these we know as
+ // Not all may make sense in a formula, but these we know as
// opcodes.
case ocErrNull:
nError = errNoCode;
@@ -1125,7 +1127,8 @@ void FormulaCompiler::Factor()
|| eOp == ocOr
|| eOp == ocBad
|| ( eOp >= ocInternalBegin && eOp <= ocInternalEnd )
- || (bCompileForFAP && ((eOp == ocIf) || (eOp == ocChose)))
+ || ( bCompileForFAP
+ && ( eOp == ocIf || eOp == ocIfError || eOp == ocIfNA || eOp == ocChose ) )
)
{
pFacToken = mpToken;
@@ -1174,14 +1177,25 @@ void FormulaCompiler::Factor()
pFacToken->SetByte( nSepCount );
PutCode( pFacToken );
}
- else if (eOp == ocIf || eOp == ocChose)
+ else if (eOp == ocIf || eOp == ocIfError || eOp == ocIfNA || eOp == ocChose)
{
// the PC counters are -1
pFacToken = mpToken;
- if ( eOp == ocIf )
- pFacToken->GetJump()[ 0 ] = 3; // if, else, behind
- else
- pFacToken->GetJump()[ 0 ] = MAXJUMPCOUNT+1;
+ switch (eOp)
+ {
+ case ocIf:
+ pFacToken->GetJump()[ 0 ] = 3; // if, else, behind
+ break;
+ case ocChose:
+ pFacToken->GetJump()[ 0 ] = MAXJUMPCOUNT+1;
+ break;
+ case ocIfError:
+ case ocIfNA:
+ pFacToken->GetJump()[ 0 ] = 2; // if, behind
+ break;
+ default:
+ SAL_WARN( "formula.core", "FormulaCompiler::Factor: forgot to add a jump count
case?");
+ }
eOp = NextToken();
if (eOp == ocOpen)
{
@@ -1190,14 +1204,30 @@ void FormulaCompiler::Factor()
}
else
SetError(errPairExpected);
- short nJumpCount = 0;
PutCode( pFacToken );
- // during AutoCorrect (since pArr->GetCodeError() is
+ // During AutoCorrect (since pArr->GetCodeError() is
// ignored) an unlimited ocIf would crash because
// ScRawToken::Clone() allocates the JumpBuffer according to
- // nJump[0]*2+2, which is 3*2+2 on ocIf.
- const short nJumpMax =
- (pFacToken->GetOpCode() == ocIf ? 3 : MAXJUMPCOUNT);
+ // nJump[0]*2+2, which is 3*2+2 on ocIf and 2*2+2 ocIfError and ocIfNA.
+ short nJumpMax;
+ OpCode eFacOpCode = pFacToken->GetOpCode();
+ switch (eFacOpCode)
+ {
+ case ocIf:
+ nJumpMax = 3;
+ break;
+ case ocChose:
+ nJumpMax = MAXJUMPCOUNT;
+ break;
+ case ocIfError:
+ case ocIfNA:
+ nJumpMax = 2;
+ break;
+ default:
+ nJumpMax = 0;
+ SAL_WARN( "formula.core", "FormulaCompiler::Factor: forgot to add a jump max
case?");
+ }
+ short nJumpCount = 0;
while ( (nJumpCount < (MAXJUMPCOUNT - 1)) && (eOp == ocSep)
&& (!pArr->GetCodeError() || bIgnoreErrors) )
{
@@ -1216,11 +1246,28 @@ void FormulaCompiler::Factor()
// always limit to nJumpMax, no arbitrary overwrites
if ( ++nJumpCount <= nJumpMax )
pFacToken->GetJump()[ nJumpCount ] = pc-1;
- if ((pFacToken->GetOpCode() == ocIf && (nJumpCount > 3)) ||
- (nJumpCount >= MAXJUMPCOUNT))
- SetError(errIllegalParameter);
- else
+ eFacOpCode = pFacToken->GetOpCode();
+ bool bLimitOk;
+ switch (eFacOpCode)
+ {
+ case ocIf:
+ bLimitOk = (nJumpCount <= 3);
+ break;
+ case ocChose:
+ bLimitOk = (nJumpCount < MAXJUMPCOUNT); /* TODO: check, really <, not <=?
*/
+ break;
+ case ocIfError:
+ case ocIfNA:
+ bLimitOk = (nJumpCount <= 2);
+ break;
+ default:
+ bLimitOk = false;
+ SAL_WARN( "formula.core", "FormulaCompiler::Factor: forgot to add a jump
limit case?");
+ }
+ if (bLimitOk)
pFacToken->GetJump()[ 0 ] = nJumpCount;
+ else
+ SetError(errIllegalParameter);
}
}
else if ( eOp == ocMissing )
diff --git a/formula/source/core/api/token.cxx b/formula/source/core/api/token.cxx
index d187073..f6110e7c 100644
--- a/formula/source/core/api/token.cxx
+++ b/formula/source/core/api/token.cxx
@@ -76,7 +76,7 @@ bool FormulaToken::IsFunction() const
eOp != ocColRowNameAuto && eOp != ocName && eOp != ocDBArea &&
(GetByte() != 0 // x parameters
|| (SC_OPCODE_START_NO_PAR <= eOp && eOp < SC_OPCODE_STOP_NO_PAR) // no parameter
- || (ocIf == eOp || ocChose == eOp ) // @ jump commands
+ || (ocIf == eOp || ocIfError == eOp || ocIfNA == eOp || ocChose == eOp ) // @ jump commands
|| (SC_OPCODE_START_1_PAR <= eOp && eOp < SC_OPCODE_STOP_1_PAR) // one parameter
|| (SC_OPCODE_START_2_PAR <= eOp && eOp < SC_OPCODE_STOP_2_PAR) // x parameters
(cByte==0 in
// FuncAutoPilot)
@@ -91,9 +91,10 @@ bool FormulaToken::IsFunction() const
sal_uInt8 FormulaToken::GetParamCount() const
{
if ( eOp < SC_OPCODE_STOP_DIV && eOp != ocExternal && eOp != ocMacro &&
- eOp != ocIf && eOp != ocChose && eOp != ocPercentSign )
+ eOp != ocIf && eOp != ocIfError && eOp != ocIfNA && eOp != ocChose &&
+ eOp != ocPercentSign )
return 0; // parameters and specials
- // ocIf and ocChose not for FAP, have cByte then
+ // ocIf, ocIfError, ocIfNA and ocChose not for FAP, have cByte then
//2do: bool parameter whether FAP or not?
else if ( GetByte() )
return GetByte(); // all functions, also ocExternal and ocMacro
@@ -106,7 +107,7 @@ sal_uInt8 FormulaToken::GetParamCount() const
return 0; // no parameter
else if (SC_OPCODE_START_1_PAR <= eOp && eOp < SC_OPCODE_STOP_1_PAR)
return 1; // one parameter
- else if ( eOp == ocIf || eOp == ocChose )
+ else if ( eOp == ocIf || eOp == ocIfError || eOp == ocIfNA || eOp == ocChose )
return 1; // only the condition counts as parameter
else
return 0; // all the rest, no Parameter, or
@@ -842,8 +843,8 @@ bool FormulaTokenArray::HasMatrixDoubleRefOps()
}
if ( eOp == ocPush || lcl_IsReference( eOp, t->GetType() ) )
pStack[sp++] = t;
- else if ( eOp == ocIf || eOp == ocChose )
- { // Jumps ignorieren, vorheriges Result (Condition) poppen
+ else if ( eOp == ocIf || eOp == ocIfError || eOp == ocIfNA || eOp == ocChose )
+ { // ignore Jumps, pop previous Result (Condition)
if ( sp )
--sp;
}
@@ -1178,10 +1179,17 @@ FormulaToken* FormulaTokenArray::AddOpCode( OpCode eOp )
pRet = new FormulaToken( svSep,eOp );
break;
case ocIf:
+ case ocIfError:
+ case ocIfNA:
case ocChose:
{
short nJump[MAXJUMPCOUNT + 1];
- nJump[ 0 ] = ocIf == eOp ? 3 : MAXJUMPCOUNT+1;
+ if ( eOp == ocIf )
+ nJump[ 0 ] = 3;
+ else if ( eOp == ocChose )
+ nJump[ 0 ] = MAXJUMPCOUNT + 1;
+ else
+ nJump[ 0 ] = 2;
pRet = new FormulaJumpToken( eOp, (short*)nJump );
}
break;
diff --git a/formula/source/core/resource/core_resource.src
b/formula/source/core/resource/core_resource.src
index 8b0ee14..d21a737 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -24,6 +24,8 @@
Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
{
String SC_OPCODE_IF { Text = "IF" ; };
+ String SC_OPCODE_IF_ERROR { Text = "IFERROR" ; };
+ String SC_OPCODE_IF_NA { Text = "IFNA" ; };
String SC_OPCODE_CHOSE { Text = "CHOOSE" ; };
String SC_OPCODE_OPEN { Text = "(" ; };
String SC_OPCODE_CLOSE { Text = ")" ; };
@@ -362,6 +364,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
{
String SC_OPCODE_IF { Text = "IF" ; };
+ String SC_OPCODE_IF_ERROR { Text = "IFERROR" ; };
+ String SC_OPCODE_IF_NA { Text = "IFNA" ; };
String SC_OPCODE_CHOSE { Text = "CHOOSE" ; };
String SC_OPCODE_OPEN { Text = "(" ; };
String SC_OPCODE_CLOSE { Text = ")" ; };
@@ -700,6 +704,14 @@ Resource RID_STRLIST_FUNCTION_NAMES
{
Text [ en-US ] = "IF" ;
};
+ String SC_OPCODE_IF_ERROR
+ {
+ Text [ en-US ] = "IFERROR" ;
+ };
+ String SC_OPCODE_IF_NA
+ {
+ Text [ en-US ] = "IFNA" ;
+ };
String SC_OPCODE_CHOSE
{
Text [ en-US ] = "CHOOSE" ;
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 2e2aa4c..e273ce4 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -433,6 +433,8 @@
#define HID_FUNC_NICHT "SC_HID_FUNC_NICHT"
#define HID_FUNC_WAHR "SC_HID_FUNC_WAHR"
#define HID_FUNC_WENN "SC_HID_FUNC_WENN"
+#define HID_FUNC_IFERROR "SC_HID_FUNC_IFERROR"
+#define HID_FUNC_IFNA "SC_HID_FUNC_IFNA"
#define HID_FUNC_ODER "SC_HID_FUNC_ODER"
#define HID_FUNC_UND "SC_HID_FUNC_UND"
#define HID_FUNC_XOR "SC_HID_FUNC_XOR"
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index d9d643f..14d0da1 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -671,6 +671,79 @@ void testFuncCOUNTIF(ScDocument* pDoc)
CPPUNIT_ASSERT_MESSAGE("We shouldn't count empty string as valid number.", result == 0.0);
}
+void testFuncIFERROR(ScDocument* pDoc)
+{
+ // IFERROR/IFNA (fdo#56124)
+
+ // Empty A1:A39 first.
+ clearRange(pDoc, ScRange(0, 0, 0, 0, 40, 0));
+
+ // Raw data (rows 1 through 12)
+ const char* aData[] = {
+ "1",
+ "e",
+ "=SQRT(4)",
+ "=SQRT(-2)",
+ "=A4",
+ "=1/0",
+ "=NA()",
+ "bar",
+ "4",
+ "gee",
+ "=1/0",
+ "23"
+ };
+
+ SCROW nRows = SAL_N_ELEMENTS(aData);
+ for (SCROW i = 0; i < nRows; ++i)
+ pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
+
+ printRange(pDoc, ScRange(0, 0, 0, 0, nRows-1, 0), "data range for IFERROR/IFNA");
+
+ // formulas and results
+ struct {
+ const char* pFormula; const char* pResult;
+ } aChecks[] = {
+ { "=IFERROR(A1;9)", "1" },
+ { "=IFERROR(A2;9)", "e" },
+ { "=IFERROR(A3;9)", "2" },
+ { "=IFERROR(A4;-7)", "-7" },
+ { "=IFERROR(A5;-7)", "-7" },
+ { "=IFERROR(A6;-7)", "-7" },
+ { "=IFERROR(A7;-7)", "-7" },
+ { "=IFNA(A6;9)", "#DIV/0!" },
+ { "=IFNA(A7;-7)", "-7" },
+ { "=IFNA(VLOOKUP(\"4\";A8:A10;1;0);-2)", "4" },
+ { "=IFNA(VLOOKUP(\"fop\";A8:A10;1;0);-2)", "-2" },
+ { "{=IFERROR(3*A11:A12;1998)}[0]", "1998" }, // um.. this is not the correct way to
insert a
+ { "{=IFERROR(3*A11:A12;1998)}[1]", "69" } // matrix formula, just a place
holder, see below
+ };
+
+ nRows = SAL_N_ELEMENTS(aChecks);
+ for (SCROW i = 0; i < nRows-2; ++i)
+ {
+ SCROW nRow = 20 + i;
+ pDoc->SetString(0, nRow, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
+ }
+
+ // Create a matrix range in last two rows of the range above, actual data
+ // of the placeholders.
+ ScMarkData aMark;
+ aMark.SelectOneTable(0);
+ pDoc->InsertMatrixFormula(0, 20 + nRows-2, 0, 20 + nRows-1, aMark, "=IFERROR(3*A11:A12;1998)",
NULL);
+
+ pDoc->CalcAll();
+
+ for (SCROW i = 0; i < nRows; ++i)
+ {
+ rtl::OUString aResult;
+ SCROW nRow = 20 + i;
+ pDoc->GetString(0, nRow, 0, aResult);
+ CPPUNIT_ASSERT_EQUAL_MESSAGE(
+ aChecks[i].pFormula, OUString::createFromAscii( aChecks[i].pResult), aResult);
+ }
+}
+
void testFuncVLOOKUP(ScDocument* pDoc)
{
// VLOOKUP
@@ -1063,6 +1136,7 @@ void Test::testCellFunctions()
testFuncPRODUCT(m_pDoc);
testFuncN(m_pDoc);
testFuncCOUNTIF(m_pDoc);
+ testFuncIFERROR(m_pDoc);
testFuncVLOOKUP(m_pDoc);
testFuncMATCH(m_pDoc);
testFuncCELL(m_pDoc);
@@ -4175,6 +4249,8 @@ void Test::testFunctionLists()
"AND",
"FALSE",
"IF",
+ "IFERROR",
+ "IFNA",
"NOT",
"OR",
"TRUE",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 762b818..267e659 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -368,6 +368,7 @@ bool LookupQueryWithCache( ScAddress & o_rResultPos,
const ScQueryParam & rParam ) const;
void ScIfJump();
+void ScIfError( bool bNAonly );
void ScChoseJump();
// Be sure to only call this if pStack[sp-nStackLevel] really contains a
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index ec85a01..0ef12c7 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -54,6 +54,7 @@
#include <math.h>
#include <vector>
#include <memory>
+#include <limits>
#include "cellkeytranslator.hxx"
#include "lookupcache.hxx"
#include "rangenam.hxx"
@@ -243,6 +244,188 @@ void ScInterpreter::ScIfJump()
}
+/** 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. */
+static void lcl_storeJumpMatResult( const ScMatrix* pMat, ScMatrix* pResMat, SCSIZE nC, SCSIZE nR )
+{
+ if ( pMat->IsValue( nC, nR ) )
+ {
+ double fVal = pMat->GetDouble( nC, nR );
+ pResMat->PutDouble( fVal, nC, nR );
+ }
+ else if ( pMat->IsEmpty( nC, nR ) )
+ {
+ pResMat->PutEmpty( nC, nR );
+ }
+ else
+ {
+ const String& rStr = pMat->GetString( nC, nR );
+ pResMat->PutString( rStr, nC, nR );
+ }
+}
+
+
+void ScInterpreter::ScIfError( bool bNAonly )
+{
+ RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "Donkers/erAck", "ScInterpreter::ScIfError" );
+ const short* pJump = pCur->GetJump();
+ short nJumpCount = pJump[ 0 ];
+ if (!sp)
+ {
+ PushError( errUnknownStackVariable);
+ aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
+ return;
+ }
+
+ FormulaTokenRef xToken( pStack[ sp - 1 ] );
+ bool bError = false;
+ sal_uInt16 nOldGlobalError = nGlobalError;
+ nGlobalError = 0;
+
+ MatrixDoubleRefToMatrix();
+ switch (GetStackType())
+ {
+ default:
+ Pop();
+ break;
+ case svError:
+ PopError();
+ bError = true;
+ break;
+ case svDoubleRef:
+ case svSingleRef:
+ {
+ ScAddress aAdr;
+ if (!PopDoubleRefOrSingleRef( aAdr))
+ bError = true;
+ else
+ {
+ ScBaseCell* pCell = GetCell( aAdr);
+ nGlobalError = GetCellErrCode( pCell);
+ if (nGlobalError)
+ bError = true;
+ }
+ }
+ break;
+ case svExternalSingleRef:
+ case svExternalDoubleRef:
+ {
+ double fVal;
+ String aStr;
+ // Handles also existing jump matrix case and sets error on
+ // elements.
+ GetDoubleOrStringFromMatrix( fVal, aStr);
+ if (nGlobalError)
+ bError = true;
+ }
+ break;
+ case svMatrix:
+ {
+ const ScMatrixRef pMat = PopMatrix();
+ if (!pMat || (nGlobalError && (!bNAonly || nGlobalError == NOTAVAILABLE)))
+ {
+ bError = true;
+ break; // switch
+ }
+ // If the matrix has no queried error at all we can simply use
+ // it as result and don't need to bother with jump matrix.
+ SCSIZE nErrorCol = ::std::numeric_limits<SCSIZE>::max(),
+ nErrorRow = ::std::numeric_limits<SCSIZE>::max();
+ SCSIZE nCols, nRows;
+ pMat->GetDimensions( nCols, nRows );
+ if (nCols == 0 || nRows == 0)
+ {
+ bError = true;
+ break; // switch
+ }
+ for (SCSIZE nC=0; nC < nCols && !bError; ++nC)
+ {
+ for (SCSIZE nR=0; nR < nRows && !bError; ++nR)
+ {
+ sal_uInt16 nErr = pMat->GetError( nC, nR );
+ if (nErr && (!bNAonly || nErr == NOTAVAILABLE))
+ {
+ bError = true;
+ nErrorCol = nC;
+ nErrorRow = nR;
+ }
+ }
+ }
+ if (!bError)
+ break; // switch, we're done and have the result
+
+ FormulaTokenRef xNew;
+ ScTokenMatrixMap::const_iterator aMapIter;
+ if (pTokenMatrixMap && ((aMapIter = pTokenMatrixMap->find( pCur)) !=
pTokenMatrixMap->end()))
+ {
+ xNew = (*aMapIter).second;
+ }
+ else
+ {
+ const ScMatrix* pMatPtr = pMat.get();
+ ScJumpMatrix* pJumpMat = new ScJumpMatrix( nCols, nRows );
+ ScMatrix* pResMatPtr = pJumpMat->GetResultMatrix();
+ // Init all jumps to no error to save single calls. Error
+ // is the exceptional condition.
+ const double fFlagResult = CreateDoubleError( errJumpMatHasResult);
+ pJumpMat->SetAllJumps( fFlagResult, pJump[ nJumpCount ], pJump[ nJumpCount ] );
+ // Up to first error position simply store results, no need
+ // to evaluate error conditions again.
+ SCSIZE nC = 0, nR = 0;
+ for ( ; nC < nCols && (nC != nErrorCol || nR != nErrorRow); /*nop*/ )
+ {
+ for ( ; nR < nRows && (nC != nErrorCol || nR != nErrorRow); ++nR)
+ {
+ lcl_storeJumpMatResult( pMatPtr, pResMatPtr, nC, nR);
+ }
+ if (nC != nErrorCol || nR != nErrorRow)
+ ++nC;
+ }
+ // Now the mixed cases.
+ for ( ; nC < nCols; ++nC)
+ {
+ for ( ; nR < nRows; ++nR)
+ {
+ sal_uInt16 nErr = pMat->GetError( nC, nR );
+ if (nErr && (!bNAonly || nErr == NOTAVAILABLE))
+ { // TRUE, THEN path
+ pJumpMat->SetJump( nC, nR, 1.0, pJump[ 1 ], pJump[ nJumpCount ] );
+ }
+ else
+ { // FALSE, EMPTY path, store result instead
+ lcl_storeJumpMatResult( pMatPtr, pResMatPtr, nC, nR);
+ }
+ }
+ }
+ xNew = new ScJumpMatrixToken( pJumpMat );
+ GetTokenMatrixMap().insert( ScTokenMatrixMap::value_type( pCur, xNew ));
+ }
+ nGlobalError = nOldGlobalError;
+ PushTempToken( xNew.get() );
+ // set endpoint of path for main code line
+ aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
+ return;
+ }
+ break;
+ }
+
+ if (bError && (!bNAonly || nGlobalError == NOTAVAILABLE))
+ {
+ // error, calculate 2nd argument
+ nGlobalError = 0;
+ aCode.Jump( pJump[ 1 ], pJump[ nJumpCount ] );
+ }
+ else
+ {
+ // no error, push 1st argument and continue
+ nGlobalError = nOldGlobalError;
+ PushTempToken( xToken.get());
+ aCode.Jump( pJump[ nJumpCount ], pJump[ nJumpCount ] );
+ }
+}
+
+
void ScInterpreter::ScChoseJump()
{
RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScChoseJump" );
@@ -560,18 +743,7 @@ bool ScInterpreter::JumpMatrix( short nStackLevel )
}
else
{
- if ( pMat->IsValue( nC, nR ) )
- {
- fVal = pMat->GetDouble( nC, nR );
- pResMat->PutDouble( fVal, nC, nR );
- }
- else if ( pMat->IsEmpty( nC, nR ) )
- pResMat->PutEmpty( nC, nR );
- else
- {
- const String& rStr = pMat->GetString( nC, nR );
- pResMat->PutString( rStr, nC, nR );
- }
+ lcl_storeJumpMatResult( pMat.get(), pResMat.get(), nC, nR);
}
lcl_AdjustJumpMatrix( pJumpMatrix, pResMat, nCols, nRows );
}
@@ -602,7 +774,7 @@ bool ScInterpreter::JumpMatrix( short nStackLevel )
pJumpMatrix->GetJump( nC, nR, fBool, nStart, nNext, nStop );
while ( bCont && nStart == nNext )
{ // push all results that have no jump path
- if ( pResMat )
+ if ( pResMat && (GetDoubleErrorValue( fBool) != errJumpMatHasResult) )
{
// a false without path results in an empty path value
if ( fBool == 0.0 )
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 7155445..2f3b5ac 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -2712,7 +2712,7 @@ void ScInterpreter::ScExternal()
}
else if ( ( aUnoName = ScGlobal::GetAddInCollection()->FindFunction(aFuncName, false) ).Len()
)
{
- // bLocalFirst=false in FindFunction, cFunc should be the stored
+ // bLocalFirst=false in FindFunction, cFunc should be the stored
// internal name
ScUnoAddInCall aCall( *ScGlobal::GetAddInCollection(), aUnoName, nParamCount );
@@ -3815,7 +3815,8 @@ StackVar ScInterpreter::Interpret()
// RPN code push without error
PushWithoutError( (FormulaToken&) *pCur );
}
- else if (pTokenMatrixMap && !(eOp == ocIf || eOp == ocChose) &&
+ else if (pTokenMatrixMap &&
+ !(eOp == ocIf || eOp == ocIfError || eOp == ocIfNA || eOp == ocChose) &&
((aTokenMatrixMapIter = pTokenMatrixMap->find( pCur)) !=
pTokenMatrixMap->end()) &&
(*aTokenMatrixMapIter).second->GetType() != svJumpMatrix)
@@ -3836,7 +3837,7 @@ StackVar ScInterpreter::Interpret()
nFuncFmtType = NUMBERFORMAT_NUMBER;
nFuncFmtIndex = 0;
- if ( eOp == ocIf || eOp == ocChose )
+ if ( eOp == ocIf || eOp == ocChose || eOp == ocIfError || eOp == ocIfNA )
nStackBase = sp; // don't mess around with the jumps
else
{
@@ -3863,6 +3864,8 @@ StackVar ScInterpreter::Interpret()
case ocDBArea : ScDBArea(); break;
case ocColRowNameAuto : ScColRowNameAuto(); break;
case ocIf : ScIfJump(); break;
+ case ocIfError : ScIfError( false ); break;
+ case ocIfNA : ScIfError( true ); break;
case ocChose : ScChoseJump(); break;
case ocAdd : ScAdd(); break;
case ocSub : ScSub(); break;
@@ -4258,7 +4261,9 @@ StackVar ScInterpreter::Interpret()
case ocIsString : \
case ocIsValue : \
case ocN : \
- case ocType :
+ case ocType : \
+ case ocIfError : \
+ case ocIfNA :
switch ( eOp )
{
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index ebdbb4a..6f3cae07 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -55,6 +55,8 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
// created inside those functions and ConvertMatrixParameters() is not
// called for them.
{ ocIf, {{ Array, Reference, Reference }, 0 }},
+ { ocIfError, {{ Array, Reference }, 0 }},
+ { ocIfNA, {{ Array, Reference }, 0 }},
{ ocChose, {{ Array, Reference }, 1 }},
// Other specials.
{ ocOpen, {{ Bounds }, 0 }},
@@ -495,6 +497,8 @@ void ScParameterClassification::GenerateDocumentation()
case ocIf:
aToken.SetByte(3);
break;
+ case ocIfError:
+ case ocIfNA:
case ocChose:
aToken.SetByte(2);
break;
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index 539cad0..4ea3d4e 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -129,6 +129,11 @@ void ScRawToken::SetOpCode( OpCode e )
eType = svJump;
nJump[ 0 ] = 3; // If, Else, Behind
break;
+ case ocIfError:
+ case ocIfNA:
+ eType = svJump;
+ nJump[ 0 ] = 2; // If, Behind
+ break;
case ocChose:
eType = svJump;
nJump[ 0 ] = MAXJUMPCOUNT+1;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 58b3aa9..0cee777 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -365,7 +365,16 @@ static const XclFunctionInfo saFuncTable_Oox[] =
{ ocAverageIf, NOID, 2, 3, V, { RO, VR, RO }, EXC_FUNCFLAG_IMPORTONLY,
EXC_FUNCNAME( "AVERAGEIF" ) },
{ ocAverageIf, 255, 3, 4, V, { RO_E, RO, VR, RO }, EXC_FUNCFLAG_EXPORTONLY,
EXC_FUNCNAME( "AVERAGEIF" ) },
{ ocAverageIfs, NOID, 3, MX, V, { RO, RO, VR },
EXC_FUNCFLAG_IMPORTONLY|EXC_FUNCFLAG_PARAMPAIRS, EXC_FUNCNAME( "AVERAGEIFS" ) },
- { ocAverageIfs, 255, 4, MX, V, { RO_E, RO, RO, VR },
EXC_FUNCFLAG_EXPORTONLY|EXC_FUNCFLAG_PARAMPAIRS, EXC_FUNCNAME( "AVERAGEIFS" ) }
+ { ocAverageIfs, 255, 4, MX, V, { RO_E, RO, RO, VR },
EXC_FUNCFLAG_EXPORTONLY|EXC_FUNCFLAG_PARAMPAIRS, EXC_FUNCNAME( "AVERAGEIFS" ) },
+ { ocIfError, NOID, 2, 2, V, { VO, RO }, EXC_FUNCFLAG_IMPORTONLY, EXC_FUNCNAME(
"IFERROR" ) },
+ { ocIfError, 255, 3, 3, V, { RO_E, VO, RO }, EXC_FUNCFLAG_EXPORTONLY,
EXC_FUNCNAME( "IFERROR" ) }
+};
+
+/** Functions new in Excel 2013. */
+static const XclFunctionInfo saFuncTable_2013[] =
+{
+ { ocIfNA, NOID, 2, 2, V, { VO, RO }, EXC_FUNCFLAG_IMPORTONLY, EXC_FUNCNAME(
"IFNA" ) },
+ { ocIfNA, 255, 3, 3, V, { RO_E, VO, RO }, EXC_FUNCFLAG_EXPORTONLY,
EXC_FUNCNAME( "IFNA" ) }
};
#define EXC_FUNCENTRY_ODF( opcode, minparam, maxparam, flags, asciiname ) \
@@ -393,7 +402,6 @@ static const XclFunctionInfo saFuncTable_Odf[] =
EXC_FUNCENTRY_ODF( ocFormula, 1, 1, 0, "FORMULA" ),
EXC_FUNCENTRY_ODF( ocGamma, 1, 1, 0, "GAMMA" ),
EXC_FUNCENTRY_ODF( ocGauss, 1, 1, 0, "GAUSS" ),
- EXC_FUNCENTRY_ODF( ocNoName, 2, 2, 0, "IFNA" ),
EXC_FUNCENTRY_ODF( ocIsFormula, 1, 1, 0, "ISFORMULA" ),
EXC_FUNCENTRY_ODF( ocWeek, 1, 2, 0, "ISOWEEKNUM" ),
EXC_FUNCENTRY_ODF( ocMatrixUnit, 1, 1, 0, "MUNIT" ),
@@ -434,6 +442,7 @@ XclFunctionProvider::XclFunctionProvider( const XclRoot& rRoot )
if( eBiff >= EXC_BIFF8 )
(this->*pFillFunc)( saFuncTable_8, STATIC_ARRAY_END( saFuncTable_8 ) );
(this->*pFillFunc)( saFuncTable_Oox, STATIC_ARRAY_END( saFuncTable_Oox ) );
+ (this->*pFillFunc)( saFuncTable_2013, STATIC_ARRAY_END( saFuncTable_2013 ) );
(this->*pFillFunc)( saFuncTable_Odf, STATIC_ARRAY_END( saFuncTable_Odf ) );
}
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index a6f7e453..90470ab 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -711,13 +711,20 @@ static const FunctionData saFuncTableOox[] =
{ 0, "CUBEKPIMEMBER", 477, NOID, 3, 4, V, { VR }, 0 },
{ 0, "CUBESET", 478, NOID, 2, 5, V, { VR, RX, VR },
0 },
{ 0, "CUBESETCOUNT", 479, NOID, 1, 1, V, { VR }, 0 },
- { 0, "IFERROR", 480, NOID, 2, 2, V, { VO, RO }, 0 },
+ { "IFERROR", "IFERROR", 480, NOID, 2, 2, V, { VO, RO },
FUNCFLAG_MACROCALL },
{ "COUNTIFS", "COUNTIFS", 481, NOID, 2, MX, V, { RO, VR },
FUNCFLAG_MACROCALL | FUNCFLAG_PARAMPAIRS },
{ "SUMIFS", "SUMIFS", 482, NOID, 3, MX, V, { RO, RO, VR },
FUNCFLAG_MACROCALL | FUNCFLAG_PARAMPAIRS },
{ "AVERAGEIF", "AVERAGEIF", 483, NOID, 2, 3, V, { RO, VR, RO },
FUNCFLAG_MACROCALL },
{ "AVERAGEIFS", "AVERAGEIFS", 484, NOID, 3, MX, V, { RO, RO, VR },
FUNCFLAG_MACROCALL | FUNCFLAG_PARAMPAIRS }
};
+/** Functions new in Excel 2013. */
+/* FIXME: BIFF12 function identifer available? Where to obtain? */
+static const FunctionData saFuncTable2013[] =
+{
+ { "IFNA", "IFNA", NOID, NOID, 2, 2, V, { VO, RO },
FUNCFLAG_MACROCALL },
+};
+
/** Functions defined by OpenFormula, but not supported by Calc or by Excel. */
static const FunctionData saFuncTableOdf[] =
{
@@ -739,7 +746,6 @@ static const FunctionData saFuncTableOdf[] =
{ "FORMULA", 0, NOID, NOID, 1, 1, V, { RO },
FUNCFLAG_MACROCALLODF },
{ "GAMMA", 0, NOID, NOID, 1, 1, V, { VR },
FUNCFLAG_MACROCALLODF },
{ "GAUSS", 0, NOID, NOID, 1, 1, V, { VR },
FUNCFLAG_MACROCALLODF },
- { "IFNA", 0, NOID, NOID, 2, 2, V, { VR, RO },
FUNCFLAG_MACROCALLODF },
{ "ISFORMULA", 0, NOID, NOID, 1, 1, V, { RO },
FUNCFLAG_MACROCALLODF },
{ "ISOWEEKNUM", 0, NOID, NOID, 1, 2, V, { VR },
FUNCFLAG_MACROCALLODF },
{ "MUNIT", 0, NOID, NOID, 1, 1, A, { VR },
FUNCFLAG_MACROCALLODF },
@@ -866,6 +872,7 @@ FunctionProviderImpl::FunctionProviderImpl( FilterType eFilter, BiffType eBiff,
if( eBiff >= BIFF8 )
initFuncs( saFuncTableBiff8, STATIC_ARRAY_END( saFuncTableBiff8 ), nMaxParam,
bImportFilter );
initFuncs( saFuncTableOox, STATIC_ARRAY_END( saFuncTableOox ), nMaxParam, bImportFilter );
+ initFuncs( saFuncTable2013, STATIC_ARRAY_END( saFuncTable2013 ), nMaxParam, bImportFilter );
initFuncs( saFuncTableOdf, STATIC_ARRAY_END( saFuncTableOdf ), nMaxParam, bImportFilter );
}
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index 1b68b2e..ce354e6 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -2651,6 +2651,70 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
Text [ en-US ] = "The result of the function if the logical test returns FALSE." ;
};
};
+ // -=*# Resource for function IFERROR #*=-
+ Resource SC_OPCODE_IF_ERROR
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns value if not an error value, else alternative." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_LOGIC;
+ U2S( HID_FUNC_IFERROR );
+ 2; 0; 0;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "value" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The value to be calculated." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "alternative value" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The alternative to be returned, should value be an error value." ;
+ };
+ };
+ // -=*# Resource for function IFNA #*=-
+ Resource SC_OPCODE_IF_NA
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns value if not a #N/A error, else alternative." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_LOGIC;
+ U2S( HID_FUNC_IFNA );
+ 2; 0; 0;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "value" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The value to be calculated." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "alternative value" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The alternative to be returned, should value be a #N/A error." ;
+ };
+ };
// -=*# Resource for function ODER #*=-
Resource SC_OPCODE_OR
{
diff --git a/sc/util/hidother.src b/sc/util/hidother.src
index d339229..718dce2 100644
--- a/sc/util/hidother.src
+++ b/sc/util/hidother.src
@@ -149,6 +149,8 @@ hidspecial HID_FUNC_FALSCH { HelpID = HID_FUNC_FALSCH; };
hidspecial HID_FUNC_NICHT { HelpID = HID_FUNC_NICHT; };
hidspecial HID_FUNC_WAHR { HelpID = HID_FUNC_WAHR; };
hidspecial HID_FUNC_WENN { HelpID = HID_FUNC_WENN; };
+hidspecial HID_FUNC_IFERROR { HelpID = HID_FUNC_IFERROR; };
+hidspecial HID_FUNC_IFNA { HelpID = HID_FUNC_IFNA; };
hidspecial HID_FUNC_ODER { HelpID = HID_FUNC_ODER; };
hidspecial HID_FUNC_UND { HelpID = HID_FUNC_UND; };
hidspecial HID_FUNC_XOR { HelpID = HID_FUNC_XOR; };
--------------erAck-patch-parts--
Attachment:
pgpcWe6oIatun.pgp
Description: PGP signature