sc/qa/unit/ucalc.cxx | 249 +++++++++++++++++++++++++++++++++++++++++----------
1 file changed, 205 insertions(+), 44 deletions(-)
New commits:
commit 5d1a99169253b084d676234fa153f2acecc5d638
Author: Kohei Yoshida <kohei.yosh...@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.yosh...@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.yosh...@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
_______________________________________________
Libreoffice-commits mailing list
Libreoffice-commits@lists.freedesktop.org
http://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
