added .xlsx .xls .ods export/reimport tests for new spreadsheet functions

Change-Id: I71baaf27a16e60eb11f80f61664fc09dcf047cf1

3 files changed, 159 insertions, 114 deletions

diff --git a/sc/qa/unit/helper/shared_test_impl.hxx b/sc/qa/unit/helper/shared_test_impl.hxx
index 210059614ce4..8be0a71dd548 100644
--- a/sc/qa/unit/helper/shared_test_impl.hxx
+++ b/sc/qa/unit/helper/shared_test_impl.hxx
@@ -13,6 +13,7 @@
 #include "colorscale.hxx"
 #include "conditio.hxx"
 #include "document.hxx"
+#include "formulacell.hxx"
 
 struct FindCondFormatByEnclosingRange
 {
@@ -151,6 +152,125 @@ void testColorScale3Entry_Impl(ScDocument* pDoc)
     }
 }
 
+void testFunctionsExcel2010_Impl( ScDocument* pDoc )
+{
+    // Original test case document is functions-excel-2010.xlsx
+    // Which test rows to evaluate, 1-based as in UI to ease maintenance.
+    struct
+    {
+        SCROW nRow;
+        bool  bEvaluate;
+    } aTests[] = {
+        {  2, false },
+        {  3, true  },
+        {  4, true  },
+        {  5, true  },
+        {  6, true  },
+        {  7, true  },
+        {  8, true  },
+        {  9, true  },
+        { 10, true  },
+        { 11, true  },
+        { 12, true  },
+        { 13, true  },
+        { 14, true  },
+        { 15, true  },
+        { 16, true  },
+        { 17, true  },
+        { 18, true  },
+        { 19, true  },
+        { 20, true  },
+        { 21, true  },
+        { 22, true  },
+        { 23, true  },
+        { 24, true  },
+        { 25, true  },
+        { 26, true  },
+        { 27, true  },
+        { 28, true  },
+        { 29, true  },
+        { 30, true  },
+        { 31, true  },
+        { 32, true  },
+        { 33, true  },
+        { 34, true  },
+        { 35, true  },
+        { 36, true  },
+        { 37, true  },
+        { 38, true  },
+        { 39, true  },
+        { 40, true  },
+        { 41, true  },
+        { 42, true  },
+        { 43, true  },
+        { 44, true  },
+        { 45, false },  // name=[ NETWORKDAYS.INTL ], result=18, expected=19
+        { 46, true  },
+        { 47, true  },
+        { 48, true  },
+        { 49, true  },
+        { 50, true  },
+        { 51, true  },
+        { 52, true  },
+        { 53, true  },
+        { 54, true  },
+        { 55, true  },
+        { 56, true  },
+        { 57, true  },
+        { 58, true  },
+        { 59, true  },
+        { 60, true  },
+        { 61, true  },
+        { 62, true  },
+        { 63, true  },
+        { 64, true  },
+        { 65, true  },
+        { 66, true  },
+        { 67, true  },
+        { 68, true  },
+        { 69, true  },
+        { 70, true  },
+        { 71, true  },
+        { 72, true  },
+        { 73, true  },
+        { 74, true  },
+        { 75, true  },
+        { 76, true  },
+        { 77, true  },
+        { 78, true  },
+        { 79, false },  // name=[ WORKDAY.INTL ], result=41755 , expected=41754
+        { 80, true  }
+    };
+
+    for (size_t i=0; i < SAL_N_ELEMENTS(aTests); ++i)
+    {
+        if (aTests[i].bEvaluate)
+        {
+            // Column A is description, B is formula, C is Excel result, D is
+            // comparison.
+            SCROW nRow = aTests[i].nRow - 1;    // 0-based
+
+            OString aStr = OString::number( aTests[i].nRow) +
+                ", function name=[ " +
+                OUStringToOString( pDoc->GetString( ScAddress( 0, nRow, 0)), RTL_TEXTENCODING_UTF8 ) +
+                " ], result=" +
+                OString::number( pDoc->GetValue( ScAddress( 1, nRow, 0)) ) +
+                ", expected=" +
+                OString::number( pDoc->GetValue( ScAddress( 2, nRow, 0)) );
+
+            ScFormulaCell* pFC = pDoc->GetFormulaCell( ScAddress( 1, nRow, 0) );
+            if ( pFC && pFC->GetErrCode() != 0 )
+                aStr += ", error code =" + OString::number( pFC->GetErrCode() );
+
+            CPPUNIT_ASSERT_MESSAGE( OString( "Expected a formula cell without error at row " +
+                    aStr ).getStr(), isFormulaWithoutError( *pDoc, ScAddress( 1, nRow, 0)));
+            CPPUNIT_ASSERT_MESSAGE( OString( "Expected a TRUE value at row " +
+                    aStr ).getStr(), 0 != pDoc->GetValue( ScAddress( 3, nRow, 0)));
+
+        }
+    }
+}
+
 #endif
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 228a16100484..43052d076fff 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -97,6 +97,11 @@ public:
     void testSharedFormulaExportXLSX();
     void testSharedFormulaStringResultExportXLSX();
 
+    void testFunctionsExcel2010( sal_uLong nFormatType );
+    void testFunctionsExcel2010XLSX();
+    void testFunctionsExcel2010XLS();
+    void testFunctionsExcel2010ODS();
+
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
 #if !defined(MACOSX) && !defined(DRAGONFLY)
@@ -126,6 +131,9 @@ public:
     CPPUNIT_TEST(testSharedFormulaExportXLS);
     CPPUNIT_TEST(testSharedFormulaExportXLSX);
     CPPUNIT_TEST(testSharedFormulaStringResultExportXLSX);
+    CPPUNIT_TEST(testFunctionsExcel2010XLSX);
+    CPPUNIT_TEST(testFunctionsExcel2010XLS);
+    CPPUNIT_TEST(testFunctionsExcel2010ODS);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -1406,6 +1414,36 @@ void ScExportTest::testSharedFormulaStringResultExportXLSX()
     xDocSh2->DoClose();
 }
 
+void ScExportTest::testFunctionsExcel2010( sal_uLong nFormatType )
+{
+    ScDocShellRef xShell = loadDoc("functions-excel-2010.", XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load the document.", xShell.Is());
+
+    ScDocShellRef xDocSh = saveAndReload(xShell, nFormatType);
+    ScDocument* pDoc = xDocSh->GetDocument();
+    CPPUNIT_ASSERT(pDoc);
+    pDoc->CalcAll(); // perform hard re-calculation.
+
+    testFunctionsExcel2010_Impl(pDoc);
+
+    xDocSh->DoClose();
+}
+
+void ScExportTest::testFunctionsExcel2010XLSX()
+{
+    testFunctionsExcel2010(XLSX);
+}
+
+void ScExportTest::testFunctionsExcel2010XLS()
+{
+    testFunctionsExcel2010(XLS);
+}
+
+void ScExportTest::testFunctionsExcel2010ODS()
+{
+    testFunctionsExcel2010(ODS);
+}
+
 ScExportTest::ScExportTest()
       : ScBootstrapFixture("/sc/qa/unit/data")
 {
diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx
index 318cd68d5e6b..7482bed20ab8 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -434,120 +434,7 @@ void ScFiltersTest::testFunctionsExcel2010()
     ScDocument* pDoc = xDocSh->GetDocument();
     pDoc->CalcAll(); // perform hard re-calculation.
 
-    // Which test rows to evaluate, 1-based as in UI to ease maintenance.
-    struct
-    {
-        SCROW nRow;
-        bool  bEvaluate;
-    } aTests[] = {
-        {  2, false },
-        {  3, true  },
-        {  4, true  },
-        {  5, true  },
-        {  6, true  },
-        {  7, true  },
-        {  8, true  },
-        {  9, true  },
-        { 10, true  },
-        { 11, true  },
-        { 12, true  },
-        { 13, true  },
-        { 14, true  },
-        { 15, true  },
-        { 16, true  },
-        { 17, true  },
-        { 18, true  },
-        { 19, true  },
-        { 20, true  },
-        { 21, true  },
-        { 22, true  },
-        { 23, true  },
-        { 24, true  },
-        { 25, true  },
-        { 26, true  },
-        { 27, true  },
-        { 28, true  },
-        { 29, true  },
-        { 30, true  },
-        { 31, true  },
-        { 32, true  },
-        { 33, true  },
-        { 34, true  },
-        { 35, true  },
-        { 36, true  },
-        { 37, true  },
-        { 38, true  },
-        { 39, true  },
-        { 40, true  },
-        { 41, true  },
-        { 42, true  },
-        { 43, true  },
-        { 44, true  },
-        { 45, false },  // name=[ NETWORKDAYS.INTL ], result=18, expected=19
-        { 46, true  },
-        { 47, true  },
-        { 48, true  },
-        { 49, true  },
-        { 50, true  },
-        { 51, true  },
-        { 52, true  },
-        { 53, true  },
-        { 54, true  },
-        { 55, true  },
-        { 56, true  },
-        { 57, true  },
-        { 58, true  },
-        { 59, true  },
-        { 60, true  },
-        { 61, true  },
-        { 62, true  },
-        { 63, true  },
-        { 64, true  },
-        { 65, true  },
-        { 66, true  },
-        { 67, true  },
-        { 68, true  },
-        { 69, true  },
-        { 70, true  },
-        { 71, true  },
-        { 72, true  },
-        { 73, true  },
-        { 74, true  },
-        { 75, true  },
-        { 76, true  },
-        { 77, true  },
-        { 78, true  },
-        { 79, false },  // name=[ WORKDAY.INTL ], result=41755 , expected=41754
-        { 80, true  }
-    };
-
-    for (size_t i=0; i < SAL_N_ELEMENTS(aTests); ++i)
-    {
-        if (aTests[i].bEvaluate)
-        {
-            // Column A is description, B is formula, C is Excel result, D is
-            // comparison.
-            SCROW nRow = aTests[i].nRow - 1;    // 0-based
-
-            OString aStr = OString::number( aTests[i].nRow) +
-                ", function name=[ " +
-                OUStringToOString( pDoc->GetString( ScAddress( 0, nRow, 0)), RTL_TEXTENCODING_UTF8 ) +
-                " ], result=" +
-                OString::number( pDoc->GetValue( ScAddress( 1, nRow, 0)) ) +
-                ", expected=" +
-                OString::number( pDoc->GetValue( ScAddress( 2, nRow, 0)) );
-
-            ScFormulaCell* pFC = pDoc->GetFormulaCell( ScAddress( 1, nRow, 0) );
-            if ( pFC && pFC->GetErrCode() != 0 )
-                aStr += ", error code =" + OString::number( pFC->GetErrCode() );
-
-            CPPUNIT_ASSERT_MESSAGE( OString( "Expected a formula cell without error at row " +
-                    aStr ).getStr(), isFormulaWithoutError( *pDoc, ScAddress( 1, nRow, 0)));
-            CPPUNIT_ASSERT_MESSAGE( OString( "Expected a TRUE value at row " +
-                    aStr ).getStr(), 0 != pDoc->GetValue( ScAddress( 3, nRow, 0)));
-
-        }
-    }
+    testFunctionsExcel2010_Impl(pDoc);
 
     xDocSh->DoClose();
 }