tdf#126573 Add Excel2021 array function SORTBY to Calc

TODO/WIP: oasis proposal
More information about how this new function works:
https://support.microsoft.com/en-au/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f
https://exceljet.net/functions/sortby-function

Change-Id: I4538a32f7f75056d3055369fc5f4483d24fd1089
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/165069
Tested-by: Jenkins
Tested-by: Gabor Kelemen <gabor.kelemen.extern@allotropia.de>
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

9 files changed, 275 insertions, 42 deletions

diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index 677f76453ff5..5d89eb333350 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -789,6 +789,7 @@ ScFunctionList::ScFunctionList( bool bEnglishFunctionNames )
         { SC_OPCODE_RANDBETWEEN_NV, ENTRY(SC_OPCODE_RANDBETWEEN_NV_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_RANDBETWEEN_NV, 2, { 0, 0 }, 0 },
         { SC_OPCODE_FILTER, ENTRY(SC_OPCODE_FILTER_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_FILTER_MS, 3, { 0, 0, 1 }, 0 },
         { SC_OPCODE_SORT, ENTRY(SC_OPCODE_SORT_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_SORT_MS, 4, { 0, 1, 1, 1 }, 0 },
+        { SC_OPCODE_SORTBY, ENTRY(SC_OPCODE_SORTBY_ARY), 0, ID_FUNCTION_GRP_TABLE, HID_FUNC_SORTBY_MS, PAIRED_VAR_ARGS + 1, { 0, 0, 1 }, 0 },
     };
 
     ScFuncDesc* pDesc = nullptr;
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index b984f8a4e37c..31ff2ecb7a9c 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -567,6 +567,8 @@ private:
     std::unique_ptr<ScSortInfoArray> CreateFastSortInfoArray(
         const ScSortParam& rSortParam, bool bMatrix, SCCOLROW nInd1, SCCOLROW nInd2);
     std::vector<SCCOLROW> GetSortOrder(const ScSortParam& rSortParam, const ScMatrixRef& pMatSrc);
+    ScMatrixRef CreateSortedMatrix(const ScSortParam& rSortParam, const ScMatrixRef& pMatSrc,
+        const ScRange& rSourceRange, const std::vector<SCCOLROW>& rSortArray, SCSIZE nsC, SCSIZE nsR);
 
     void QuickSort(ScSortInfoArray* pArray, const ScMatrixRef& pMatSrc, SCCOLROW nLo, SCCOLROW nHi);
 
@@ -712,6 +714,7 @@ private:
     void ScXLookup();
     void ScFilter();
     void ScSort();
+    void ScSortBy();
     void ScSubTotal();
 
     // If upon call rMissingField==true then the database field parameter may be
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index c1a4af803ef1..8ea81b336641 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -8433,75 +8433,230 @@ void ScInterpreter::ScSort()
 
     // sorting...
     std::vector<SCCOLROW> aOrderIndices = GetSortOrder(aSortData, pMatSrc);
+    // create sorted matrix
+    ScMatrixRef pResMat = CreateSortedMatrix(aSortData, pMatSrc,
+        ScRange(aSortData.nCol1, aSortData.nRow1, aSortData.nSourceTab,
+                aSortData.nCol2, aSortData.nRow2, aSortData.nSourceTab),
+                aOrderIndices, nsC, nsR);
 
-    SCCOLROW nStartPos = (!aSortData.bByRow ? aSortData.nCol1 : aSortData.nRow1);
-    size_t nCount = aOrderIndices.size();
-    std::vector<SCCOLROW> aPosTable(nCount);
+    if (pResMat)
+        PushMatrix(pResMat);
+    else
+        PushIllegalParameter();
+}
 
-    for (size_t i = 0; i < nCount; ++i)
-        aPosTable[aOrderIndices[i] - nStartPos] = i;
+void ScInterpreter::ScSortBy()
+{
+    sal_uInt8 nParamCount = GetByte();
 
-    ScMatrixRef pResMat = nullptr;
-    if (!aOrderIndices.empty())
+    if (nParamCount < 2/*|| (nParamCount % 2 != 1)*/)
     {
-        pResMat = GetNewMat(nsC, nsR, /*bEmpty*/true);
-        if (!pMatSrc)
+        PushError(FormulaError::ParameterExpected);
+        return;
+    }
+
+    sal_uInt8 nSortCount = nParamCount / 2;
+
+    ScSortParam aSortData;
+    aSortData.maKeyState.resize(nSortCount);
+
+    // 127th, ..., 3rd and 2nd argument: sort by range/array and sort orders pair
+    sal_uInt8 nSortBy = nSortCount;
+    ScMatrixRef pFullMatSortBy = nullptr;
+    while (nSortBy-- > 0 && nGlobalError == FormulaError::NONE)
+    {
+        // 3rd argument sort_order optional: default ascending
+        if (nParamCount >= 3 && (nParamCount % 2 == 1))
         {
-            ScCellIterator aCellIter(mrDoc, ScRange(aSortData.nCol1, aSortData.nRow1, aSortData.nSourceTab,
-                aSortData.nCol2, aSortData.nRow2, aSortData.nSourceTab));
-            for (bool bHas = aCellIter.first(); bHas; bHas = aCellIter.next())
+            sal_Int8 nSortOrder = static_cast<sal_Int8>(GetInt32WithDefault(1));
+            if (nSortOrder != 1 && nSortOrder != -1)
             {
-                SCSIZE nThisCol = static_cast<SCSIZE>(aCellIter.GetPos().Col() - aSortData.nCol1);
-                SCSIZE nThisRow = static_cast<SCSIZE>(aCellIter.GetPos().Row() - aSortData.nRow1);
+                PushIllegalParameter();
+                return;
+            }
+            aSortData.maKeyState[nSortBy].bAscending = (nSortOrder == 1);
+            nParamCount--;
+        }
 
-                ScRefCellValue aCell = aCellIter.getRefCellValue();
-                if (aCell.hasNumeric())
+        // 2nd argument: take sort by ranges
+        ScMatrixRef pMatSortBy = nullptr;
+        SCSIZE nbyC = 0, nbyR = 0;
+        switch (GetStackType())
+        {
+            case svSingleRef:
+            case svDoubleRef:
+            case svMatrix:
+            case svExternalSingleRef:
+            case svExternalDoubleRef:
+            {
+                if (nSortCount == 1)
                 {
-                    if (aSortData.bByRow)
-                        pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), nThisCol, aPosTable[nThisRow]);
-                    else
-                        pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), aPosTable[nThisCol], nThisRow);
+                    pFullMatSortBy = GetMatrix();
+                    if (!pFullMatSortBy)
+                    {
+                        PushIllegalParameter();
+                        return;
+                    }
+                    pFullMatSortBy->GetDimensions(nbyC, nbyR);
                 }
                 else
                 {
-                    svl::SharedString aStr;
-                    GetCellString(aStr, aCell);
-                    if (aSortData.bByRow)
-                        pResMat->PutString(aStr, nThisCol, aPosTable[nThisRow]);
+                    pMatSortBy = GetMatrix();
+                    if (!pMatSortBy)
+                    {
+                        PushIllegalParameter();
+                        return;
+                    }
+                    pMatSortBy->GetDimensions(nbyC, nbyR);
+                }
+
+                // last->first (backward) sortby array
+                if (nSortBy == nSortCount - 1)
+                {
+                    if (nbyC == 1 && nbyR > 1)
+                        aSortData.bByRow = true;
+                    else if (nbyR == 1 && nbyC > 1)
+                        aSortData.bByRow = false;
                     else
-                        pResMat->PutString(aStr, aPosTable[nThisCol], nThisRow);
+                    {
+                        PushIllegalParameter();
+                        return;
+                    }
+
+                    if (nSortCount > 1)
+                    {
+                        pFullMatSortBy = GetNewMat(aSortData.bByRow ? (nbyC * nSortCount) : nbyC,
+                            aSortData.bByRow ? nbyR : (nbyR * nSortCount), /*bEmpty*/true);
+                    }
                 }
             }
+            break;
+
+            default:
+                PushIllegalParameter();
+                return;
         }
-        else
+
+        // ..., penultimate sortby arrays
+        if (nSortCount > 1 && nSortBy <= nSortCount - 1)
         {
-            for (SCCOL ci = aSortData.nCol1; ci <= aSortData.nCol2; ci++)
+            SCSIZE nCheckCol = 0, nCheckRow = 0;
+            pFullMatSortBy->GetDimensions(nCheckCol, nCheckRow);
+            if ((aSortData.bByRow && nbyR == nCheckRow && nbyC == 1) ||
+                (!aSortData.bByRow && nbyC == nCheckCol && nbyR == 1))
             {
-                for (SCROW rj = aSortData.nRow1; rj <= aSortData.nRow2; rj++)
+                for (SCSIZE ci = 0; ci < nbyC; ci++)//col
                 {
-                    if (pMatSrc->IsStringOrEmpty(ci, rj))
+                    for (SCSIZE rj = 0; rj < nbyR; rj++)//row
                     {
-                        if (aSortData.bByRow)
-                            pResMat->PutString(pMatSrc->GetString(ci, rj), ci, aPosTable[rj]);
-                        else
-                            pResMat->PutString(pMatSrc->GetString(ci, rj), aPosTable[ci], rj);
-                    }
-                    else
-                    {
-                        if (aSortData.bByRow)
-                            pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), ci, aPosTable[rj]);
+                        if (pMatSortBy->IsStringOrEmpty(ci, rj))
+                        {
+                            if (aSortData.bByRow)
+                                pFullMatSortBy->PutString(pMatSortBy->GetString(ci, rj), ci + nSortBy, rj);
+                            else
+                                pFullMatSortBy->PutString(pMatSortBy->GetString(ci, rj), ci, rj + nSortBy);
+                        }
                         else
-                            pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), aPosTable[ci], rj);
+                        {
+                            if (aSortData.bByRow)
+                                pFullMatSortBy->PutDouble(pMatSortBy->GetDouble(ci, rj), ci + nSortBy, rj);
+                            else
+                                pFullMatSortBy->PutDouble(pMatSortBy->GetDouble(ci, rj), ci, rj + nSortBy);
+                        }
                     }
                 }
             }
+            else
+            {
+                PushIllegalParameter();
+                return;
+            }
         }
+
+        aSortData.maKeyState[nSortBy].bDoSort = true;
+        aSortData.maKeyState[nSortBy].nField = nSortBy;
+
+        nParamCount--;
     }
 
+    // 1st argument is the range/array to be sorted
+    SCSIZE nsC = 0, nsR = 0;
+    SCCOL nSortCol1 = 0, nSortCol2 = 0;
+    SCROW nSortRow1 = 0, nSortRow2 = 0;
+    SCTAB nSortTab1 = 0, nSortTab2 = 0;
+    ScMatrixRef pMatSrc = nullptr;
+    switch ( GetStackType() )
+    {
+        case svSingleRef:
+            PopSingleRef(nSortCol1, nSortRow1, nSortTab1);
+            nSortCol2 = nSortCol1;
+            nSortRow2 = nSortRow1;
+            nsC = nSortCol2 - nSortCol1 + 1;
+            nsR = nSortRow2 - nSortRow1 + 1;
+        break;
+        case svDoubleRef:
+        {
+            PopDoubleRef(nSortCol1, nSortRow1, nSortTab1, nSortCol2, nSortRow2, nSortTab2);
+            if (nSortTab1 != nSortTab2)
+            {
+                PushIllegalParameter();
+                return;
+            }
+            nsC = nSortCol2 - nSortCol1 + 1;
+            nsR = nSortRow2 - nSortRow1 + 1;
+        }
+        break;
+        case svMatrix:
+        case svExternalSingleRef:
+        case svExternalDoubleRef:
+        {
+            pMatSrc = GetMatrix();
+            if (!pMatSrc)
+            {
+                PushIllegalParameter();
+                return;
+            }
+            pMatSrc->GetDimensions(nsC, nsR);
+            nSortCol2 = nsC - 1; // nSortCol1 = 0
+            nSortRow2 = nsR - 1; // nSortRow1 = 0
+        }
+        break;
+
+        default:
+            PushIllegalParameter();
+            return;
+    }
+
+    SCSIZE nCheckMatrixCol = 0, nCheckMatrixRow = 0;
+    pFullMatSortBy->GetDimensions(nCheckMatrixCol, nCheckMatrixRow);
+    if (nGlobalError != FormulaError::NONE)
+    {
+        PushError(nGlobalError);
+        return;
+    }
+    else if ((aSortData.bByRow && nsR != nCheckMatrixRow) ||
+        (!aSortData.bByRow && nsC != nCheckMatrixCol))
+    {
+        PushIllegalParameter();
+        return;
+    }
+    else
+    {
+        aSortData.nCol2 = nCheckMatrixCol - 1;
+        aSortData.nRow2 = nCheckMatrixRow - 1;
+    }
+
+    // sorting...
+    std::vector<SCCOLROW> aOrderIndices = GetSortOrder(aSortData, pFullMatSortBy);
+    // create sorted matrix
+    ScMatrixRef pResMat = CreateSortedMatrix(aSortData, pMatSrc,
+        ScRange(nSortCol1, nSortRow1, nSortTab1, nSortCol2, nSortRow2, nSortTab2),
+        aOrderIndices, nsC, nsR);
+
     if (pResMat)
         PushMatrix(pResMat);
     else
-        PushError(FormulaError::NestedArray);
+        PushIllegalParameter();
 }
 
 void ScInterpreter::ScSubTotal()
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index 17e52858637b..860f98988a41 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -4253,6 +4253,75 @@ std::vector<SCCOLROW> ScInterpreter::GetSortOrder( const ScSortParam& rSortParam
     return aOrderIndices;
 }
 
+ScMatrixRef ScInterpreter::CreateSortedMatrix( const ScSortParam& rSortParam, const ScMatrixRef& pMatSrc,
+    const ScRange& rSourceRange, const std::vector<SCCOLROW>& rSortArray, SCSIZE nsC, SCSIZE nsR )
+{
+    SCCOLROW nStartPos = (!rSortParam.bByRow ? rSortParam.nCol1 : rSortParam.nRow1);
+    size_t nCount = rSortArray.size();
+    std::vector<SCCOLROW> aPosTable(nCount);
+
+    for (size_t i = 0; i < nCount; ++i)
+        aPosTable[rSortArray[i] - nStartPos] = i;
+
+    ScMatrixRef pResMat = nullptr;
+    if (!rSortArray.empty())
+    {
+        pResMat = GetNewMat(nsC, nsR, /*bEmpty*/true);
+        if (!pMatSrc)
+        {
+            ScCellIterator aCellIter(mrDoc, rSourceRange);
+            for (bool bHas = aCellIter.first(); bHas; bHas = aCellIter.next())
+            {
+                SCSIZE nThisCol = static_cast<SCSIZE>(aCellIter.GetPos().Col() - rSourceRange.aStart.Col());
+                SCSIZE nThisRow = static_cast<SCSIZE>(aCellIter.GetPos().Row() - rSourceRange.aStart.Row());
+
+                ScRefCellValue aCell = aCellIter.getRefCellValue();
+                if (aCell.hasNumeric())
+                {
+                    if (rSortParam.bByRow)
+                        pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), nThisCol, aPosTable[nThisRow]);
+                    else
+                        pResMat->PutDouble(GetCellValue(aCellIter.GetPos(), aCell), aPosTable[nThisCol], nThisRow);
+                }
+                else
+                {
+                    svl::SharedString aStr;
+                    GetCellString(aStr, aCell);
+                    if (rSortParam.bByRow)
+                        pResMat->PutString(aStr, nThisCol, aPosTable[nThisRow]);
+                    else
+                        pResMat->PutString(aStr, aPosTable[nThisCol], nThisRow);
+                }
+            }
+        }
+        else
+        {
+            for (SCCOL ci = rSourceRange.aStart.Col(); ci <= rSourceRange.aEnd.Col(); ci++)
+            {
+                for (SCROW rj = rSourceRange.aStart.Row(); rj <= rSourceRange.aEnd.Row(); rj++)
+                {
+                    if (pMatSrc->IsStringOrEmpty(ci, rj))
+                    {
+                        if (rSortParam.bByRow)
+                            pResMat->PutString(pMatSrc->GetString(ci, rj), ci, aPosTable[rj]);
+                        else
+                            pResMat->PutString(pMatSrc->GetString(ci, rj), aPosTable[ci], rj);
+                    }
+                    else
+                    {
+                        if (rSortParam.bByRow)
+                            pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), ci, aPosTable[rj]);
+                        else
+                            pResMat->PutDouble(pMatSrc->GetDouble(ci, rj), aPosTable[ci], rj);
+                    }
+                }
+            }
+        }
+    }
+
+    return pResMat;
+}
+
 void ScInterpreter::QuickSort( ScSortInfoArray* pArray, const ScMatrixRef& pMatSrc, SCCOLROW nLo, SCCOLROW nHi )
 {
     if ((nHi - nLo) == 1)
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index c9e795176297..5e5d513289d0 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4129,6 +4129,7 @@ StackVar ScInterpreter::Interpret()
                 case ocRandbetweenNV    : ScRandbetween();              break;
                 case ocFilter           : ScFilter();                   break;
                 case ocSort             : ScSort();                     break;
+                case ocSortBy           : ScSortBy();                   break;
                 case ocTrue             : ScTrue();                     break;
                 case ocFalse            : ScFalse();                    break;
                 case ocGetActDate       : ScGetActDate();               break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index 0bc87183ad89..e07645ec908b 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -235,6 +235,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocSlope,           {{ ForceArray, ForceArray                               }, 0, Value }},
     { ocSmall,           {{ Reference, Value                                     }, 0, Value }},
     { ocSort,            {{ ReferenceOrRefArray, ForceArray, ForceArray, Value   }, 0, ForceArrayReturn }},
+    { ocSortBy,          {{ ReferenceOrRefArray, ReferenceOrRefArray, Value,     }, 2, ForceArrayReturn }},
     { ocStDev,           {{ Reference                                            }, 1, Value }},
     { ocStDevA,          {{ Reference                                            }, 1, Value }},
     { ocStDevP,          {{ Reference                                            }, 1, Value }},
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index b81cb8bb3d26..f2d21ca74963 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -1391,6 +1391,7 @@ void ScTokenArray::CheckToken( const FormulaToken& r )
             case ocXMatch:
             case ocFilter:
             case ocSort:
+            case ocSortBy:
             case ocSLN:
             case ocIRR:
             case ocMIRR:
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 00b5843ad421..787d4c53f5d1 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -604,7 +604,8 @@ const XclFunctionInfo saFuncTable_2021[] =
     EXC_FUNCENTRY_V_VR(  ocXLookup,    3,  6,  0,  "XLOOKUP" ),
     EXC_FUNCENTRY_V_VR(  ocXMatch,     2,  4,  0,  "XMATCH" ),
     EXC_FUNCENTRY_V_VR(  ocFilter,     2,  3,  0,  "FILTER" ),
-    EXC_FUNCENTRY_V_VR(  ocSort,       1,  4,  0,  "SORT" )
+    EXC_FUNCENTRY_V_VR(  ocSort,       1,  4,  0,  "SORT" ),
+    EXC_FUNCENTRY_V_VR(  ocSortBy,     2,  3,  0,  "SORTBY" )
 };
 
 
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index a07edc25efbc..003c1378df32 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -878,7 +878,8 @@ const FunctionData saFuncTable2021[] =
     { "COM.MICROSOFT.XLOOKUP",             "XLOOKUP",             NOID,   NOID,   3,  6,  R, { VR, VA, VR }, FuncFlags::MACROCALL_NEW },
     { "COM.MICROSOFT.XMATCH",              "XMATCH",              NOID,   NOID,   2,  4,  V, { VR, VA }, FuncFlags::MACROCALL_NEW },
     { "COM.MICROSOFT.FILTER",              "FILTER",              NOID,   NOID,   2,  3,  A, { VR, VA }, FuncFlags::MACROCALL_NEW },
-    { "COM.MICROSOFT.SORT",                "SORT",                NOID,   NOID,   1,  4,  A, { VO }, FuncFlags::MACROCALL_NEW }
+    { "COM.MICROSOFT.SORT",                "SORT",                NOID,   NOID,   1,  4,  A, { VO }, FuncFlags::MACROCALL_NEW },
+    { "COM.MICROSOFT.SORTBY",              "SORTBY",              NOID,   NOID,   2,  MX, V, { RO, RO, VR }, FuncFlags::MACROCALL_NEW | FuncFlags::PARAMPAIRS }
 };