tdf#150878: Add support for TOROW function

Change-Id: Ic1acb5c2fd08ecab7c3ee059888cf44aa45ee287
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/180308
Tested-by: Jenkins
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>

8 files changed, 118 insertions, 0 deletions

diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index d7b25f53b88b..ea2bba6ecea2 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -794,6 +794,7 @@ ScFunctionList::ScFunctionList( bool bEnglishFunctionNames )
         { SC_OPCODE_MAT_SEQUENCE, ENTRY(SC_OPCODE_MAT_SEQUENCE_ARY), 0, ID_FUNCTION_GRP_MATRIX, HID_FUNC_MSEQUENCE_MS, 4, { 0, 1, 1, 1 }, 0 },
         { SC_OPCODE_RANDARRAY, ENTRY(SC_OPCODE_RANDARRAY_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_RANDARRAY_MS, 5, { 1, 1, 1, 1, 1 }, 0 },
         { SC_OPCODE_TOCOL, ENTRY(SC_OPCODE_TOCOL_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_TOCOL_MS, 3, { 0, 1, 1 }, 0 },
+        { SC_OPCODE_TOROW, ENTRY(SC_OPCODE_TOROW_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_TOROW_MS, 3, { 0, 1, 1 }, 0 },
         { SC_OPCODE_UNIQUE, ENTRY(SC_OPCODE_UNIQUE_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_UNIQUE_MS, 3, { 0, 1, 1 }, 0 },
     };
 
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index ae65507f79f8..d9450ea07149 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -725,6 +725,7 @@ private:
     void ScSort();
     void ScSortBy();
     void ScToCol();
+    void ScToRow();
     void ScUnique();
     void ScLet();
     void ScSubTotal();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 79f639dbc2bb..fe0cc47d9e87 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -8918,6 +8918,117 @@ void ScInterpreter::ScToCol()
     }
 }
 
+void ScInterpreter::ScToRow()
+{
+    sal_uInt8 nParamCount = GetByte();
+    if (!MustHaveParamCount(nParamCount, 1, 3))
+        return;
+
+    // 3rd argument optional - Scan_by_column: default FALSE
+    bool bByColumn = false;
+    if (nParamCount == 3)
+        bByColumn = GetBoolWithDefault(false);
+
+    // 2nd argument optional - Ignore: default keep all values
+    IgnoreValues eIgnoreValues = IgnoreValues::DEFAULT;
+    if (nParamCount >= 2)
+    {
+        sal_Int32 k = GetInt32WithDefault(0);
+        if (k >= 0 && k <= 3)
+            eIgnoreValues = static_cast<IgnoreValues>(k);
+        else
+        {
+            PushIllegalParameter();
+            return;
+        }
+    }
+
+    // 1st argument: take torow range
+    ScMatrixRef pMatSource = nullptr;
+    SCSIZE nsC = 0, nsR = 0;
+    switch (GetStackType())
+    {
+        case svSingleRef:
+        case svDoubleRef:
+        case svMatrix:
+        case svExternalSingleRef:
+        case svExternalDoubleRef:
+        {
+            pMatSource = GetMatrix();
+            if (!pMatSource)
+            {
+                PushIllegalParameter();
+                return;
+            }
+
+            pMatSource->GetDimensions(nsC, nsR);
+        }
+        break;
+
+        default:
+            PushIllegalParameter();
+            return;
+    }
+
+    if (nGlobalError != FormulaError::NONE || nsC < 1 || nsR < 1)
+    {
+        PushIllegalArgument();
+        return;
+    }
+
+    std::vector<std::pair<SCSIZE, SCSIZE>> aResPos;
+    SCSIZE nOut = bByColumn ? nsC : nsR;
+    SCSIZE nIn = bByColumn ? nsR : nsC;
+
+    for (SCSIZE i = 0; i < nOut; i++)
+    {
+        for (SCSIZE j = 0; j < nIn; j++)
+        {
+            SCSIZE nCol = bByColumn ? i : j;
+            SCSIZE nRow = bByColumn ? j : i;
+            if ((eIgnoreValues == IgnoreValues::ALL || eIgnoreValues == IgnoreValues::BLANKS) && pMatSource->IsEmptyCell(nCol, nRow))
+                continue; // Nothing to do
+            else if ((eIgnoreValues == IgnoreValues::ALL || eIgnoreValues == IgnoreValues::ERRORS) && pMatSource->GetError(nCol, nRow) != FormulaError::NONE)
+                continue; // Nothing to do
+            else
+                aResPos.emplace_back(nCol, nRow);
+        }
+
+    }
+    // No result
+    if (aResPos.size() == 0)
+    {
+        PushNA();
+        return;
+    }
+
+    ScMatrixRef pResMat = GetNewMat(aResPos.size(), 1, /*bEmpty*/true);
+    if (!pResMat)
+    {
+        PushIllegalArgument();
+        return;
+    }
+
+    // fill result matrix to the same row
+    for (SCSIZE iPos = 0; iPos < aResPos.size(); ++iPos)
+    {
+        if (pMatSource->IsEmptyCell(aResPos[iPos].first, aResPos[iPos].second))
+        {
+            pResMat->PutEmpty(iPos, 0);
+        }
+        else if (!pMatSource->IsStringOrEmpty(aResPos[iPos].first, aResPos[iPos].second))
+        {
+            pResMat->PutDouble(pMatSource->GetDouble(aResPos[iPos].first, aResPos[iPos].second), iPos, 0);
+        }
+        else
+        {
+            pResMat->PutString(pMatSource->GetString(aResPos[iPos].first, aResPos[iPos].second), iPos, 0);
+        }
+    }
+
+    PushMatrix(pResMat);
+}
+
 void ScInterpreter::ScUnique()
 {
     sal_uInt8 nParamCount = GetByte();
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index e1094f994362..693d71856db1 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4160,6 +4160,7 @@ StackVar ScInterpreter::Interpret()
                     case ocSort             : ScSort();                     break;
                     case ocSortBy           : ScSortBy();                   break;
                     case ocToCol            : ScToCol();                    break;
+                    case ocToRow            : ScToRow();                    break;
                     case ocUnique           : ScUnique();                   break;
                     case ocLet              : ScLet();                      break;
                     case ocTrue             : ScTrue();                     break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index ac7790eae31e..bcaca39b0570 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -259,6 +259,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocTTest,           {{ ForceArray, ForceArray, Value, Value                 }, 0, Value }},
     { ocTextJoin_MS,     {{ Reference, Value, Reference                          }, 1, Value }},
     { ocToCol,           {{ ReferenceOrRefArray, Value, Value,                   }, 0, ForceArrayReturn } },
+    { ocToRow,           {{ ReferenceOrRefArray, Value, Value,                   }, 0, ForceArrayReturn } },
     { ocTrend,           {{ Reference, Reference, Reference, Value               }, 0, Value }},
     { ocTrimMean,        {{ Reference, Value                                     }, 0, Value }},
     { ocTrue,            {{ Bounds                                               }, 0, Value }},
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index be982f8f4c28..cda61367b7a2 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -1592,6 +1592,7 @@ void ScTokenArray::CheckToken( const FormulaToken& r )
             case ocMatSequence:
             case ocRandArray:
             case ocToCol:
+            case ocToRow:
             case ocUnique:
             // Don't change the state.
             break;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 97bc56b108b0..b4db2be5068e 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -609,6 +609,7 @@ const XclFunctionInfo saFuncTable_2021[] =
     EXC_FUNCENTRY_V_VR(  ocMatSequence,1,  4,  0,  "SEQUENCE" ),
     EXC_FUNCENTRY_V_VR(  ocRandArray,  0,  5,  0,  "RANDARRAY" ),
     EXC_FUNCENTRY_V_VR(  ocToCol,      1,  3,  0,  "TOCOL" ),
+    EXC_FUNCENTRY_V_VR(  ocToRow,      1,  3,  0,  "TOROW" ),
     EXC_FUNCENTRY_V_VR(  ocUnique,     1,  3,  0,  "UNIQUE" ),
     EXC_FUNCENTRY_V_VR(  ocLet,        3,  3,  0,  "LET")
 };
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 2b165dd956b0..913deb8e4a2a 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -883,6 +883,7 @@ const FunctionData saFuncTable2021[] =
     { "COM.MICROSOFT.SEQUENCE",            "SEQUENCE",            NOID,   NOID,   1,  4,  A, { VO }, FuncFlags::MACROCALL_NEW },
     { "COM.MICROSOFT.RANDARRAY",           "RANDARRAY",           NOID,   NOID,   0,  5,  A, { VO }, FuncFlags::MACROCALL_NEW },
     { "COM.MICROSOFT.TOCOL",               "TOCOL",               NOID,   NOID,   1,  3,  A, { VO }, FuncFlags::MACROCALL_NEW },
+    { "COM.MICROSOFT.TOROW",               "TOROW",               NOID,   NOID,   1,  3,  A, { VO }, FuncFlags::MACROCALL_NEW },
     { "COM.MICROSOFT.UNIQUE",              "UNIQUE",              NOID,   NOID,   1,  3,  A, { VO }, FuncFlags::MACROCALL_NEW },
     { "COM.MICROSOFT.LET",                 "LET",                 NOID,   NOID,   3,  MX, R, { VR, VR, VA }, FuncFlags::MACROCALL_NEW | FuncFlags::PARAMPAIRS },
 };