fdo#33705: Fixed cell function N.

Also added unit test for this built-in function.

1 files changed, 70 insertions, 0 deletions

diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 446e734b49cf..fe75b0f1daa0 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -60,6 +60,7 @@
 #include "drwlayer.hxx"
 #include "scitems.hxx"
 #include "reffind.hxx"
+#include "markdata.hxx"
 
 #include "docsh.hxx"
 #include "funcdesc.hxx"
@@ -432,6 +433,75 @@ void Test::testCellFunctions()
     m_pDoc->GetValue(0, 4, 0, result);
     CPPUNIT_ASSERT_MESSAGE("Calculation of PRODUCT with inline array failed", result == 6.0);
 
+    {
+        // 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);
+
+        // 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("TRUE")));
+        m_pDoc->SetString(0, 7, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("FALSE")));
+        m_pDoc->SetString(0, 8, 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(A7)")));
+        m_pDoc->SetString(1, 7, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A8)")));
+        m_pDoc->SetString(1, 8, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A9)")));
+
+        // In-line values
+        m_pDoc->SetString(1, 9, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(0)")));
+        m_pDoc->SetString(1, 10, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(1)")));
+        m_pDoc->SetString(1, 11, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(-1)")));
+        m_pDoc->SetString(1, 12, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(12.3)")));
+        m_pDoc->SetString(1, 13, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(TRUE)")));
+        m_pDoc->SetString(1, 14, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(FALSE)")));
+        m_pDoc->SetString(1, 15, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"\")")));
+        m_pDoc->SetString(1, 16, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"1.2\")")));
+        m_pDoc->SetString(1, 17, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(\"foo\")")));
+
+        // Range references
+        m_pDoc->SetString(1, 18, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A1:A8)")));
+        m_pDoc->SetString(1, 19, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A4:B8)")));
+        m_pDoc->SetString(1, 20, 0, OUString(RTL_CONSTASCII_USTRINGPARAM("=N(A6:B8)")));
+        m_pDoc->SetString(1, 21, 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, 1, 0, 0, // cell reference
+            0, 1, -1, 12.3,  1,    0, 0, 0, 0, // in-line values
+            0, 1, 12.3, 0
+        };
+        for (size_t i = 0; i < SAL_N_ELEMENTS(checks); ++i)
+        {
+            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);
+            }
+        }
+    }
+
     m_pDoc->DeleteTab(0);
 }