[Libreoffice-commits] .: 3 commits - sc/qa

Kohei Yoshida kohei at kemper.freedesktop.org
Tue Nov 8 14:16:51 PST 2011


 sc/qa/unit/ucalc.cxx |  249 +++++++++++++++++++++++++++++++++++++++++----------
 1 file changed, 205 insertions(+), 44 deletions(-)

New commits:
commit 5d1a99169253b084d676234fa153f2acecc5d638
Author: Kohei Yoshida <kohei.yoshida at suse.com>
Date:   Tue Nov 8 17:16:21 2011 -0500

    Test case for VLOOKUP (in-exact lookup on sorted range).

diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index cfa367c..8f4478c 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -74,7 +74,7 @@
 #include <iostream>
 #include <vector>
 
-#define CALC_DEBUG_OUTPUT 1
+#define CALC_DEBUG_OUTPUT 0
 
 #include "helper/debughelper.hxx"
 
@@ -213,7 +213,7 @@ void printRange(ScDocument* pDoc, const ScRange& rRange, const char* pCaption)
         {
             rtl::OUString aVal;
             pDoc->GetString(nCol, nRow, rRange.aStart.Tab(), aVal);
-            printer.set(nRow, nCol, aVal);
+            printer.set(nRow-nRow1, nCol-nCol1, aVal);
         }
     }
     printer.print(pCaption);
@@ -397,7 +397,7 @@ void Test::testCellFunctions()
         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");
+        printRange(m_pDoc, ScRange(0, 0, 0, 0, 8, 0), "data range for COUNTIF");
 
         // formulas and results
         struct {
@@ -431,12 +431,98 @@ void Test::testCellFunctions()
             bool bGood = result == aChecks[i].fResult;
             if (!bGood)
             {
-                cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula << "  expected=" << aChecks[i].fResult << "  actual=" << result << endl;
+                cerr << "row " << (nRow+1) << ": formula" << aChecks[i].pFormula
+                    << "  expected=" << aChecks[i].fResult << "  actual=" << result << endl;
                 CPPUNIT_ASSERT_MESSAGE("Unexpected result for COUNTIF", false);
             }
         }
     }
 
+    {
+        // 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
+        };
+
+        // 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(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 }
+        };
+
+        // 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");
+
+        // Verify results.
+        for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
+        {
+            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);
+            }
+        }
+    }
+
     m_pDoc->DeleteTab(0);
 }
 
commit b45ef269809409d30932c4c25aa19afd6983f86f
Author: Kohei Yoshida <kohei.yoshida at suse.com>
Date:   Tue Nov 8 15:05:11 2011 -0500

    Added test for COUNTIF.

diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index e1cafbc..cfa367c 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -74,7 +74,7 @@
 #include <iostream>
 #include <vector>
 
-#define CALC_DEBUG_OUTPUT 0
+#define CALC_DEBUG_OUTPUT 1
 
 #include "helper/debughelper.hxx"
 
@@ -193,6 +193,32 @@ private:
     ScDocShellRef m_xDocShRef;
 };
 
+void clearRange(ScDocument* pDoc, const ScRange& rRange)
+{
+    ScMarkData aMarkData;
+    aMarkData.SetMarkArea(rRange);
+    pDoc->DeleteArea(
+        rRange.aStart.Col(), rRange.aStart.Row(),
+        rRange.aEnd.Col(), rRange.aEnd.Row(), aMarkData, IDF_CONTENTS);
+}
+
+void printRange(ScDocument* pDoc, const ScRange& rRange, const char* pCaption)
+{
+    SCROW nRow1 = rRange.aStart.Row(), nRow2 = rRange.aEnd.Row();
+    SCCOL nCol1 = rRange.aStart.Col(), nCol2 = rRange.aEnd.Col();
+    SheetPrinter printer(nRow2 - nRow1 + 1, nCol2 - nCol1 + 1);
+    for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+    {
+        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+        {
+            rtl::OUString aVal;
+            pDoc->GetString(nCol, nRow, rRange.aStart.Tab(), aVal);
+            printer.set(nRow, nCol, aVal);
+        }
+    }
+    printer.print(pCaption);
+}
+
 Test::Test()
     : m_pDoc(0)
 {
@@ -291,9 +317,7 @@ void Test::testCellFunctions()
         // N
 
         // Clear the area first.
-        ScMarkData aMarkData;
-        aMarkData.SetMarkArea(ScRange(0, 0, 0, 1, 20, 0));
-        m_pDoc->DeleteArea(0, 0, 1, 20, aMarkData, IDF_CONTENTS);
+        clearRange(m_pDoc, ScRange(0, 0, 0, 1, 20, 0));
 
         // Put values to reference.
         val = 0;
@@ -350,6 +374,69 @@ void Test::testCellFunctions()
         }
     }
 
+    {
+        // 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"
+        };
+
+        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");
+
+        // 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;
+            m_pDoc->SetString(0, nRow, 0, rtl::OUString::createFromAscii(aChecks[i].pFormula));
+        }
+        m_pDoc->CalcAll();
+
+        for (SCROW i = 0; i < nRows; ++i)
+        {
+            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);
+            }
+        }
+    }
+
     m_pDoc->DeleteTab(0);
 }
 
@@ -697,23 +784,6 @@ struct DPFieldDef
     sheet::DataPilotFieldOrientation eOrient;
 };
 
-void printRange(ScDocument* pDoc, const ScRange& rRange, const char* pCaption)
-{
-    SCROW nRow1 = rRange.aStart.Row(), nRow2 = rRange.aEnd.Row();
-    SCCOL nCol1 = rRange.aStart.Col(), nCol2 = rRange.aEnd.Col();
-    SheetPrinter printer(nRow2 - nRow1 + 1, nCol2 - nCol1 + 1);
-    for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
-    {
-        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
-        {
-            rtl::OUString aVal;
-            pDoc->GetString(nCol, nRow, rRange.aStart.Tab(), aVal);
-            printer.set(nRow, nCol, aVal);
-        }
-    }
-    printer.print(pCaption);
-}
-
 template<size_t _Size>
 ScRange insertDPSourceData(ScDocument* pDoc, DPFieldDef aFields[], size_t nFieldCount, const char* aData[][_Size], size_t nDataCount)
 {
commit 6303b977ed96bfce0253041c4e87a04dea82063c
Author: Kohei Yoshida <kohei.yoshida at suse.com>
Date:   Tue Nov 8 14:31:00 2011 -0500

    Put each function test in a separate scope, just to be consistent...

diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 24e183a..e1cafbc 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -255,32 +255,37 @@ void Test::testCellFunctions()
     CPPUNIT_ASSERT_MESSAGE ("failed to insert sheet",
                             m_pDoc->InsertTab (0, aTabName));
 
-    // SUM
-    double 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();
-    double result;
-    m_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);
+    }
 
-    // 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);
+    {
+        // PRODUCT
 
-    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);
+        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);
+
+        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);
+    }
 
     {
         // N


More information about the Libreoffice-commits mailing list