sc/qa/unit/ucalc.cxx | 576 ++++++++++++++++++++++++++++++++-------------------
1 file changed, 362 insertions(+), 214 deletions(-)
New commits:
commit 0f6bb1763f73481cd18681983af676ba02f36276
Author: Kohei Yoshida <kohei.yosh...@suse.com>
Date: Wed Nov 9 17:35:09 2011 -0500
Added text values to the mix.
Textural evaluations throw an UNO exception, however. I need to look
into this.
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 8186be5..497dd35 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -527,33 +527,46 @@ struct NumStrCheck {
const char* pRes;
};
+struct StrStrCheck {
+ const char* pVal;
+ const char* pRes;
+};
+
template<size_t _DataSize, size_t _FormulaSize, int _Type>
-void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], NumStrCheck
aChecks[_FormulaSize])
+void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], StrStrCheck
aChecks[_FormulaSize])
{
- for (size_t i = 0; i < _DataSize; ++i)
+ size_t nDataSize = _DataSize;
+ for (size_t i = 0; i < nDataSize; ++i)
pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
for (size_t i = 0; i < _FormulaSize; ++i)
{
+ pDoc->SetString(1, i, 0,
rtl::OUString::createFromAscii(aChecks[i].pVal));
+
rtl::OUStringBuffer aBuf;
- aBuf.appendAscii("=MATCH(");
- aBuf.append(aChecks[i].fVal);
- aBuf.appendAscii(";A1:A9;");
+ aBuf.appendAscii("=MATCH(B");
+ aBuf.append(static_cast<sal_Int32>(i+1));
+ aBuf.appendAscii(";A1:A");
+ aBuf.append(static_cast<sal_Int32>(nDataSize));
+ aBuf.appendAscii(";");
aBuf.append(static_cast<sal_Int32>(_Type));
aBuf.appendAscii(")");
- pDoc->SetString(1, i, 0, aBuf.makeStringAndClear());
+ rtl::OUString aFormula = aBuf.makeStringAndClear();
+ pDoc->SetString(2, i, 0, aFormula);
}
pDoc->CalcAll();
+ printRange(pDoc, ScRange(0, 0, 0, 2, _FormulaSize-1, 0), "MATCH");
// verify the results.
for (size_t i = 0; i < _FormulaSize; ++i)
{
rtl::OUString aStr;
- pDoc->GetString(1, i, 0, aStr);
+ pDoc->GetString(2, i, 0, aStr);
if (!aStr.equalsAscii(aChecks[i].pRes))
{
- cerr << "row " << (i+1) << ": expected='" << aChecks[i].pRes << "'
actual='" << aStr << "'" << endl;
+ cerr << "row " << (i+1) << ": expected='" << aChecks[i].pRes << "'
actual='" << aStr << "'"
+ << " criterion='" << aChecks[i].pVal << "'" << endl;
CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
}
}
@@ -561,6 +574,7 @@ void runTestMATCH(ScDocument* pDoc, const char*
aData[_DataSize], NumStrCheck aC
void testFuncMATCH(ScDocument* pDoc)
{
+ clearRange(pDoc, ScRange(0, 0, 0, 4, 40, 0));
{
// Ascending in-exact match
@@ -574,23 +588,29 @@ void testFuncMATCH(ScDocument* pDoc)
"6",
"7",
"8",
- "9"
+ "9",
+ "A",
+ "B",
+ "C",
};
// formula (B1:C12)
- NumStrCheck aChecks[] = {
- { 0.8, "#N/A" },
- { 1.2, "1" },
- { 2.3, "2" },
- { 3.9, "3" },
- { 4.1, "4" },
- { 5.99, "5" },
- { 6.1, "6" },
- { 7.2, "7" },
- { 8.569, "8" },
- { 9.59, "9" },
- { 10, "9" },
- { 100, "9" }
+ StrStrCheck aChecks[] = {
+ { "0.8", "#N/A" },
+ { "1.2", "1" },
+ { "2.3", "2" },
+ { "3.9", "3" },
+ { "4.1", "4" },
+ { "5.99", "5" },
+ { "6.1", "6" },
+ { "7.2", "7" },
+ { "8.569", "8" },
+ { "9.59", "9" },
+ { "10", "9" },
+ { "100", "9" },
+// { "Andy", "10" },
+// { "Bruce", "11" },
+// { "Charlie", "12" }
};
runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(pDoc,
aData, aChecks);
@@ -601,6 +621,9 @@ void testFuncMATCH(ScDocument* pDoc)
// data range (A1:A9)
const char* aData[] = {
+ "C",
+ "B",
+ "A",
"9",
"8",
"7",
@@ -613,19 +636,22 @@ void testFuncMATCH(ScDocument* pDoc)
};
// formula (B1:C12)
- NumStrCheck aChecks[] = {
- { 10, "#N/A" },
- { 8.9, "1" },
- { 7.8, "2" },
- { 6.7, "3" },
- { 5.5, "4" },
- { 4.6, "5" },
- { 3.3, "6" },
- { 2.2, "7" },
- { 1.1, "8" },
- { 0.8, "9" },
- { 0, "9" },
- { -2, "9" }
+ StrStrCheck aChecks[] = {
+ { "10", "#N/A" },
+ { "8.9", "4" },
+ { "7.8", "5" },
+ { "6.7", "6" },
+ { "5.5", "7" },
+ { "4.6", "8" },
+ { "3.3", "9" },
+ { "2.2", "10" },
+ { "1.1", "11" },
+ { "0.8", "12" },
+ { "0", "12" },
+ { "-2", "12" },
+// { "Andy", "2" },
+// { "Bruce", "1" },
+// { "Charlie", "#N/A" },
};
runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(pDoc,
aData, aChecks);
commit ecea4e54e1654c24d9d02f14b6b4915d50f02b48
Author: Kohei Yoshida <kohei.yosh...@suse.com>
Date: Wed Nov 9 16:51:10 2011 -0500
Added new unit test for MATCH function.
Only ascending and descending in-exact matches for now.
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index c815bf3..8186be5 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -522,6 +522,116 @@ void testFuncVLOOKUP(ScDocument* pDoc)
}
}
+struct NumStrCheck {
+ double fVal;
+ const char* pRes;
+};
+
+template<size_t _DataSize, size_t _FormulaSize, int _Type>
+void runTestMATCH(ScDocument* pDoc, const char* aData[_DataSize], NumStrCheck
aChecks[_FormulaSize])
+{
+ for (size_t i = 0; i < _DataSize; ++i)
+ pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
+
+ for (size_t i = 0; i < _FormulaSize; ++i)
+ {
+ rtl::OUStringBuffer aBuf;
+ aBuf.appendAscii("=MATCH(");
+ aBuf.append(aChecks[i].fVal);
+ aBuf.appendAscii(";A1:A9;");
+ aBuf.append(static_cast<sal_Int32>(_Type));
+ aBuf.appendAscii(")");
+ pDoc->SetString(1, i, 0, aBuf.makeStringAndClear());
+ }
+
+ pDoc->CalcAll();
+
+ // verify the results.
+ for (size_t i = 0; i < _FormulaSize; ++i)
+ {
+ rtl::OUString aStr;
+ pDoc->GetString(1, i, 0, aStr);
+ if (!aStr.equalsAscii(aChecks[i].pRes))
+ {
+ cerr << "row " << (i+1) << ": expected='" << aChecks[i].pRes << "'
actual='" << aStr << "'" << endl;
+ CPPUNIT_ASSERT_MESSAGE("Unexpected result for MATCH", false);
+ }
+ }
+}
+
+void testFuncMATCH(ScDocument* pDoc)
+{
+ {
+ // Ascending in-exact match
+
+ // data range (A1:A9)
+ const char* aData[] = {
+ "1",
+ "2",
+ "3",
+ "4",
+ "5",
+ "6",
+ "7",
+ "8",
+ "9"
+ };
+
+ // formula (B1:C12)
+ NumStrCheck aChecks[] = {
+ { 0.8, "#N/A" },
+ { 1.2, "1" },
+ { 2.3, "2" },
+ { 3.9, "3" },
+ { 4.1, "4" },
+ { 5.99, "5" },
+ { 6.1, "6" },
+ { 7.2, "7" },
+ { 8.569, "8" },
+ { 9.59, "9" },
+ { 10, "9" },
+ { 100, "9" }
+ };
+
+ runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),1>(pDoc,
aData, aChecks);
+ }
+
+ {
+ // Descending in-exact match
+
+ // data range (A1:A9)
+ const char* aData[] = {
+ "9",
+ "8",
+ "7",
+ "6",
+ "5",
+ "4",
+ "3",
+ "2",
+ "1"
+ };
+
+ // formula (B1:C12)
+ NumStrCheck aChecks[] = {
+ { 10, "#N/A" },
+ { 8.9, "1" },
+ { 7.8, "2" },
+ { 6.7, "3" },
+ { 5.5, "4" },
+ { 4.6, "5" },
+ { 3.3, "6" },
+ { 2.2, "7" },
+ { 1.1, "8" },
+ { 0.8, "9" },
+ { 0, "9" },
+ { -2, "9" }
+ };
+
+ runTestMATCH<SAL_N_ELEMENTS(aData),SAL_N_ELEMENTS(aChecks),-1>(pDoc,
aData, aChecks);
+ }
+}
+
void Test::testCellFunctions()
{
rtl::OUString aTabName(RTL_CONSTASCII_USTRINGPARAM("foo"));
@@ -533,6 +643,7 @@ void Test::testCellFunctions()
testFuncN(m_pDoc);
testFuncCOUNTIF(m_pDoc);
testFuncVLOOKUP(m_pDoc);
+ testFuncMATCH(m_pDoc);
m_pDoc->DeleteTab(0);
}
commit 4c53798989e4ef71ce5a9d11646bee3a0ff3ec4f
Author: Kohei Yoshida <kohei.yosh...@suse.com>
Date: Wed Nov 9 15:40:05 2011 -0500
Devided built-in cell function tests into separate functions.
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 8f4478c..c815bf3 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -275,253 +275,264 @@ void Test::testInput()
m_pDoc->DeleteTab(0);
}
-void Test::testCellFunctions()
+void testFuncSUM(ScDocument* pDoc)
{
- rtl::OUString aTabName(RTL_CONSTASCII_USTRINGPARAM("foo"));
- CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
- m_pDoc->InsertTab (0, aTabName));
+ double val = 1;
+ double result;
+ pDoc->SetValue (0, 0, 0, val);
+ pDoc->SetValue (0, 1, 0, val);
+ pDoc->SetString (0, 2, 0,
rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("=SUM(A1:A2)")));
+ pDoc->CalcAll();
+ pDoc->GetValue (0, 2, 0, result);
+ CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 2.0);
+}
- double val, result;
- {
- // SUM
- val = 1;
- m_pDoc->SetValue (0, 0, 0, val);
- m_pDoc->SetValue (0, 1, 0, val);
- m_pDoc->SetString (0, 2, 0,
rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("=SUM(A1:A2)")));
- m_pDoc->CalcAll();
- m_pDoc->GetValue (0, 2, 0, result);
- CPPUNIT_ASSERT_MESSAGE ("calculation failed", result == 2.0);
- }
+void testFuncPRODUCT(ScDocument* pDoc)
+{
+ double val = 1;
+ double result;
+ pDoc->SetValue(0, 0, 0, val);
+ val = 2;
+ pDoc->SetValue(0, 1, 0, val);
+ val = 3;
+ pDoc->SetValue(0, 2, 0, val);
+ pDoc->SetString(0, 3, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=PRODUCT(A1:A3)")));
+ pDoc->CalcAll();
+ pDoc->GetValue(0, 3, 0, result);
+ CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT failed", result == 6.0);
+
+ pDoc->SetString(0, 4, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=PRODUCT({1;2;3})")));
+ pDoc->CalcAll();
+ pDoc->GetValue(0, 4, 0, result);
+ CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array failed",
result == 6.0);
+}
- {
- // PRODUCT
-
- val = 1;
- m_pDoc->SetValue(0, 0, 0, val);
- val = 2;
- m_pDoc->SetValue(0, 1, 0, val);
- val = 3;
- m_pDoc->SetValue(0, 2, 0, val);
- m_pDoc->SetString(0, 3, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=PRODUCT(A1:A3)")));
- m_pDoc->CalcAll();
- m_pDoc->GetValue(0, 3, 0, result);
- CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT failed", result == 6.0);
+void testFuncN(ScDocument* pDoc)
+{
+ double result;
- m_pDoc->SetString(0, 4, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=PRODUCT({1;2;3})")));
- m_pDoc->CalcAll();
- m_pDoc->GetValue(0, 4, 0, result);
- CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array
failed", result == 6.0);
- }
+ // Clear the area first.
+ clearRange(pDoc, ScRange(0, 0, 0, 1, 20, 0));
+ // Put values to reference.
+ double val = 0;
+ pDoc->SetValue(0, 0, 0, val);
+ pDoc->SetString(0, 2, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("Text")));
+ val = 1;
+ pDoc->SetValue(0, 3, 0, val);
+ val = -1;
+ pDoc->SetValue(0, 4, 0, val);
+ val = 12.3;
+ pDoc->SetValue(0, 5, 0, val);
+ pDoc->SetString(0, 6, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("'12.3")));
+
+ // Cell references
+ pDoc->SetString(1, 0, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A1)")));
+ pDoc->SetString(1, 1, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A2)")));
+ pDoc->SetString(1, 2, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A3)")));
+ pDoc->SetString(1, 3, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A4)")));
+ pDoc->SetString(1, 4, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A5)")));
+ pDoc->SetString(1, 5, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A6)")));
+ pDoc->SetString(1, 6, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A9)")));
+
+ // In-line values
+ pDoc->SetString(1, 7, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(0)")));
+ pDoc->SetString(1, 8, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(1)")));
+ pDoc->SetString(1, 9, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(-1)")));
+ pDoc->SetString(1, 10, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(123)")));
+ pDoc->SetString(1, 11, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"\")")));
+ pDoc->SetString(1, 12, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"12\")")));
+ pDoc->SetString(1, 13, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"foo\")")));
+
+ // Range references
+ pDoc->SetString(1, 14, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A1:A8)")));
+ pDoc->SetString(1, 15, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A4:B8)")));
+ pDoc->SetString(1, 16, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A6:B8)")));
+ pDoc->SetString(1, 17, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A2:B8)")));
+
+ // Calculate and check the results.
+ pDoc->CalcAll();
+ double checks[] = {
+ 0, 0, 0, 1, -1, 12.3, 0, // cell reference
+ 0, 1, -1, 123, 0, 0, 0, // in-line values
+ 0, 1, 12.3, 0 // range references
+ };
+ for (size_t i = 0; i < SAL_N_ELEMENTS(checks); ++i)
{
- // N
-
- // Clear the area first.
- clearRange(m_pDoc, ScRange(0, 0, 0, 1, 20, 0));
-
- // Put values to reference.
- val = 0;
- m_pDoc->SetValue(0, 0, 0, val);
- m_pDoc->SetString(0, 2, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("Text")));
- val = 1;
- m_pDoc->SetValue(0, 3, 0, val);
- val = -1;
- m_pDoc->SetValue(0, 4, 0, val);
- val = 12.3;
- m_pDoc->SetValue(0, 5, 0, val);
- m_pDoc->SetString(0, 6, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("'12.3")));
-
- // Cell references
- m_pDoc->SetString(1, 0, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A1)")));
- m_pDoc->SetString(1, 1, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A2)")));
- m_pDoc->SetString(1, 2, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A3)")));
- m_pDoc->SetString(1, 3, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A4)")));
- m_pDoc->SetString(1, 4, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A5)")));
- m_pDoc->SetString(1, 5, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A6)")));
- m_pDoc->SetString(1, 6, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A9)")));
-
- // In-line values
- m_pDoc->SetString(1, 7, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(0)")));
- m_pDoc->SetString(1, 8, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(1)")));
- m_pDoc->SetString(1, 9, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(-1)")));
- m_pDoc->SetString(1, 10, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(123)")));
- m_pDoc->SetString(1, 11, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"\")")));
- m_pDoc->SetString(1, 12, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"12\")")));
- m_pDoc->SetString(1, 13, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"foo\")")));
-
- // Range references
- m_pDoc->SetString(1, 14, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A1:A8)")));
- m_pDoc->SetString(1, 15, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A4:B8)")));
- m_pDoc->SetString(1, 16, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A6:B8)")));
- m_pDoc->SetString(1, 17, 0,
OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A2:B8)")));
-
- // Calculate and check the results.
- m_pDoc->CalcAll();
- double checks[] = {
- 0, 0, 0, 1, -1, 12.3, 0, // cell reference
- 0, 1, -1, 123, 0, 0, 0, // in-line values
- 0, 1, 12.3, 0 // range references
- };
- for (size_t i = 0; i < SAL_N_ELEMENTS(checks); ++i)
+ pDoc->GetValue(1, i, 0, result);
+ bool bGood = result == checks[i];
+ if (!bGood)
{
- m_pDoc->GetValue(1, i, 0, result);
- bool bGood = result == checks[i];
- if (!bGood)
- {
- cerr << "row " << (i+1) << ": expected=" << checks[i] << "
actual=" << result << endl;
- CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
- }
+ cerr << "row " << (i+1) << ": expected=" << checks[i] << "
actual=" << result << endl;
+ CPPUNIT_ASSERT_MESSAGE("Unexpected result for N", false);
}
}
+}
- {
- // COUNTIF (test case adopted from OOo i#36381)
-
- // Empty A1:A39 first.
- clearRange(m_pDoc, ScRange(0, 0, 0, 0, 40, 0));
-
- // Raw data (rows 1 through 9)
- const char* aData[] = {
- "1999",
- "2000",
- "0",
- "0",
- "0",
- "2002",
- "2001",
- "X",
- "2002"
- };
+void testFuncCOUNTIF(ScDocument* pDoc)
+{
+ // COUNTIF (test case adopted from OOo i#36381)
+
+ // Empty A1:A39 first.
+ clearRange(pDoc, ScRange(0, 0, 0, 0, 40, 0));
+
+ // Raw data (rows 1 through 9)
+ const char* aData[] = {
+ "1999",
+ "2000",
+ "0",
+ "0",
+ "0",
+ "2002",
+ "2001",
+ "X",
+ "2002"
+ };
- SCROW nRows = SAL_N_ELEMENTS(aData);
- for (SCROW i = 0; i < nRows; ++i)
- m_pDoc->SetString(0, i, 0,
rtl::OUString::createFromAscii(aData[i]));
-
- printRange(m_pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for
COUNTIF");
-
- // formulas and results
- struct {
- const char* pFormula; double fResult;
- } aChecks[] = {
- { "=COUNTIF(A1:A12;1999)", 1 },
- { "=COUNTIF(A1:A12;2002)", 2 },
- { "=COUNTIF(A1:A12;1998)", 0 },
- { "=COUNTIF(A1:A12;\">=1999\")", 5 },
- { "=COUNTIF(A1:A12;\">1999\")", 4 },
- { "=COUNTIF(A1:A12;\"<2001\")", 5 },
- { "=COUNTIF(A1:A12;\">0\")", 5 },
- { "=COUNTIF(A1:A12;\">=0\")", 8 },
- { "=COUNTIF(A1:A12;0)", 3 },
- { "=COUNTIF(A1:A12;\"X\")", 1 },
- { "=COUNTIF(A1:A12;)", 3 }
- };
+ SCROW nRows = SAL_N_ELEMENTS(aData);
+ for (SCROW i = 0; i < nRows; ++i)
+ pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i]));
- nRows = SAL_N_ELEMENTS(aChecks);
- for (SCROW i = 0; i < nRows; ++i)
- {
- SCROW nRow = 20 + i;
- m_pDoc->SetString(0, nRow, 0,
rtl::OUString::createFromAscii(aChecks[i].pFormula));
- }
- m_pDoc->CalcAll();
+ printRange(pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for COUNTIF");
+
+ // formulas and results
+ struct {
+ const char* pFormula; double fResult;
+ } aChecks[] = {
+ { "=COUNTIF(A1:A12;1999)", 1 },
+ { "=COUNTIF(A1:A12;2002)", 2 },
+ { "=COUNTIF(A1:A12;1998)", 0 },
+ { "=COUNTIF(A1:A12;\">=1999\")", 5 },
+ { "=COUNTIF(A1:A12;\">1999\")", 4 },
+ { "=COUNTIF(A1:A12;\"<2001\")", 5 },
+ { "=COUNTIF(A1:A12;\">0\")", 5 },
+ { "=COUNTIF(A1:A12;\">=0\")", 8 },
+ { "=COUNTIF(A1:A12;0)", 3 },
+ { "=COUNTIF(A1:A12;\"X\")", 1 },
+ { "=COUNTIF(A1:A12;)", 3 }
+ };
+
+ nRows = SAL_N_ELEMENTS(aChecks);
+ for (SCROW i = 0; i < nRows; ++i)
+ {
+ SCROW nRow = 20 + i;
+ pDoc->SetString(0, nRow, 0,
rtl::OUString::createFromAscii(aChecks[i].pFormula));
+ }
+ pDoc->CalcAll();
- for (SCROW i = 0; i < nRows; ++i)
+ for (SCROW i = 0; i < nRows; ++i)
+ {
+ double result;
+ SCROW nRow = 20 + i;
+ pDoc->GetValue(0, nRow, 0, result);
+ bool bGood = result == aChecks[i].fResult;
+ if (!bGood)
{
- SCROW nRow = 20 + i;
- m_pDoc->GetValue(0, nRow, 0, result);
- bool bGood = result == aChecks[i].fResult;
- if (!bGood)
- {
- cerr << "row " << (nRow+1) << ": formula" <<
aChecks[i].pFormula
- << " expected=" << aChecks[i].fResult << " actual=" <<
result << endl;
- CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
- }
+ cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula
+ << " expected=" << aChecks[i].fResult << " actual=" <<
result << endl;
+ CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
}
}
+}
+
+void testFuncVLOOKUP(ScDocument* pDoc)
+{
+ // VLOOKUP
+
+ // Clear A1:F40.
+ clearRange(pDoc, ScRange(0, 0, 0, 5, 39, 0));
+
+ // Raw data
+ const char* aData[][2] = {
+ { "Key", "Val" },
+ { "10", "3" },
+ { "20", "4" },
+ { "30", "5" },
+ { "40", "6" },
+ { "50", "7" },
+ { "60", "8" },
+ { "70", "9" },
+ { "A", "10" },
+ { "B", "11" },
+ { "C", "12" },
+ { "D", "13" },
+ { "E", "14" },
+ { "F", "15" },
+ { 0, 0 } // terminator
+ };
+ // Insert raw data into A1:B14.
+ for (SCROW i = 0; aData[i][0]; ++i)
{
- // VLOOKUP
-
- // Clear A1:F40.
- clearRange(m_pDoc, ScRange(0, 0, 0, 5, 39, 0));
-
- // Raw data
- const char* aData[][2] = {
- { "Key", "Val" },
- { "10", "3" },
- { "20", "4" },
- { "30", "5" },
- { "40", "6" },
- { "50", "7" },
- { "60", "8" },
- { "70", "9" },
- { "A", "10" },
- { "B", "11" },
- { "C", "12" },
- { "D", "13" },
- { "E", "14" },
- { "F", "15" },
- { 0, 0 } // terminator
- };
+ pDoc->SetString(0, i, 0, rtl::OUString::createFromAscii(aData[i][0]));
+ pDoc->SetString(1, i, 0, rtl::OUString::createFromAscii(aData[i][1]));
+ }
- // Insert raw data into A1:B14.
- for (SCROW i = 0; aData[i][0]; ++i)
- {
- m_pDoc->SetString(0, i, 0,
rtl::OUString::createFromAscii(aData[i][0]));
- m_pDoc->SetString(1, i, 0,
rtl::OUString::createFromAscii(aData[i][1]));
- }
+ printRange(pDoc, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
- printRange(m_pDoc, ScRange(0, 0, 0, 1, 13, 0), "raw data for VLOOKUP");
-
- // Formula data
- struct {
- const char* pLookup; const char* pFormula; double fResult;
- } aChecks[] = {
- { "Lookup", "Formula", 0 },
- { "12", "=VLOOKUP(D2;A2:B14;2;1)", 3 },
- { "29", "=VLOOKUP(D3;A2:B14;2;1)", 4 },
- { "31", "=VLOOKUP(D4;A2:B14;2;1)", 5 },
- { "45", "=VLOOKUP(D5;A2:B14;2;1)", 6 },
- { "56", "=VLOOKUP(D6;A2:B14;2;1)", 7 },
- { "65", "=VLOOKUP(D7;A2:B14;2;1)", 8 },
- { "78", "=VLOOKUP(D8;A2:B14;2;1)", 9 },
- { "Andy", "=VLOOKUP(D9;A2:B14;2;1)", 10 },
- { "Bruce", "=VLOOKUP(D10;A2:B14;2;1)", 11 },
- { "Charlie", "=VLOOKUP(D11;A2:B14;2;1)", 12 },
- { "David", "=VLOOKUP(D12;A2:B14;2;1)", 13 },
- { "Edward", "=VLOOKUP(D13;A2:B14;2;1)", 14 },
- { "Frank", "=VLOOKUP(D14;A2:B14;2;1)", 15 },
- { "Henry", "=VLOOKUP(D15;A2:B14;2;1)", 15 },
- { "100", "=VLOOKUP(D16;A2:B14;2;1)", 9 },
- { "1000", "=VLOOKUP(D17;A2:B14;2;1)", 9 },
- { "Zena", "=VLOOKUP(D18;A2:B14;2;1)", 15 }
- };
+ // Formula data
+ struct {
+ const char* pLookup; const char* pFormula; double fResult;
+ } aChecks[] = {
+ { "Lookup", "Formula", 0 },
+ { "12", "=VLOOKUP(D2;A2:B14;2;1)", 3 },
+ { "29", "=VLOOKUP(D3;A2:B14;2;1)", 4 },
+ { "31", "=VLOOKUP(D4;A2:B14;2;1)", 5 },
+ { "45", "=VLOOKUP(D5;A2:B14;2;1)", 6 },
+ { "56", "=VLOOKUP(D6;A2:B14;2;1)", 7 },
+ { "65", "=VLOOKUP(D7;A2:B14;2;1)", 8 },
+ { "78", "=VLOOKUP(D8;A2:B14;2;1)", 9 },
+ { "Andy", "=VLOOKUP(D9;A2:B14;2;1)", 10 },
+ { "Bruce", "=VLOOKUP(D10;A2:B14;2;1)", 11 },
+ { "Charlie", "=VLOOKUP(D11;A2:B14;2;1)", 12 },
+ { "David", "=VLOOKUP(D12;A2:B14;2;1)", 13 },
+ { "Edward", "=VLOOKUP(D13;A2:B14;2;1)", 14 },
+ { "Frank", "=VLOOKUP(D14;A2:B14;2;1)", 15 },
+ { "Henry", "=VLOOKUP(D15;A2:B14;2;1)", 15 },
+ { "100", "=VLOOKUP(D16;A2:B14;2;1)", 9 },
+ { "1000", "=VLOOKUP(D17;A2:B14;2;1)", 9 },
+ { "Zena", "=VLOOKUP(D18;A2:B14;2;1)", 15 }
+ };
- // Insert formula data into D1:E18.
- for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
- {
- m_pDoc->SetString(3, i, 0,
rtl::OUString::createFromAscii(aChecks[i].pLookup));
- m_pDoc->SetString(4, i, 0,
rtl::OUString::createFromAscii(aChecks[i].pFormula));
- }
- m_pDoc->CalcAll();
- printRange(m_pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for
VLOOKUP");
+ // Insert formula data into D1:E18.
+ for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
+ {
+ pDoc->SetString(3, i, 0,
rtl::OUString::createFromAscii(aChecks[i].pLookup));
+ pDoc->SetString(4, i, 0,
rtl::OUString::createFromAscii(aChecks[i].pFormula));
+ }
+ pDoc->CalcAll();
+ printRange(pDoc, ScRange(3, 0, 0, 4, 17, 0), "formula data for VLOOKUP");
- // Verify results.
- for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
+ // Verify results.
+ for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
+ {
+ if (i == 0)
+ // Skip the header row.
+ continue;
+
+ double result;
+ pDoc->GetValue(4, i, 0, result);
+ bool bGood = result == aChecks[i].fResult;
+ if (!bGood)
{
- if (i == 0)
- // Skip the header row.
- continue;
-
- m_pDoc->GetValue(4, i, 0, result);
- bool bGood = result == aChecks[i].fResult;
- if (!bGood)
- {
- cerr << "row " << (i+1) << ": lookup value='" <<
aChecks[i].pLookup
- << "' expected=" << aChecks[i].fResult << " actual=" <<
result << endl;
- CPPUNIT_ASSERT_MESSAGE("Unexpected result for VLOOKUP", false);
- }
+ cerr << "row " << (i+1) << ": lookup value='" << aChecks[i].pLookup
+ << "' expected=" << aChecks[i].fResult << " actual=" <<
result << endl;
+ CPPUNIT_ASSERT_MESSAGE("Unexpected result for VLOOKUP", false);
}
}
+}
+
+void Test::testCellFunctions()
+{
+ rtl::OUString aTabName(RTL_CONSTASCII_USTRINGPARAM("foo"));
+ CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
+ m_pDoc->InsertTab (0, aTabName));
+
+ testFuncSUM(m_pDoc);
+ testFuncPRODUCT(m_pDoc);
+ testFuncN(m_pDoc);
+ testFuncCOUNTIF(m_pDoc);
+ testFuncVLOOKUP(m_pDoc);
m_pDoc->DeleteTab(0);
}
_______________________________________________
Libreoffice-commits mailing list
Libreoffice-commits@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
