tdf#126573 Add Excel2021 array function FILTER to Calc

https://issues.oasis-open.org/browse/OFFICE-4156

TODO: add dynamic arrays would be useful: Older array formulas,
known as legacy array formulas, always return a fixed-size result
- they always spill into the same number of cells. The spilling behavior
described in this topic does not apply to legacy array formulas.

More info about it:
https://support.microsoft.com/en-gb/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531

Related Bug: https://bugs.documentfoundation.org/show_bug.cgi?id=127808

Change-Id: I1c3769ef33fa0207f55e1c96083717c2d90402e5
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/163955
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

9 files changed, 218 insertions, 3 deletions

diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index 1c704c9933d4..1137d13ce14f 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -786,7 +786,8 @@ ScFunctionList::ScFunctionList( bool bEnglishFunctionNames )
         { SC_OPCODE_SEARCHB, ENTRY(SC_OPCODE_SEARCHB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_SEARCHB, 3, { 0, 0, 1 }, 0 },
         { SC_OPCODE_REGEX, ENTRY(SC_OPCODE_REGEX_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_REGEX, 4, { 0, 0, 1, 1 }, 0 },
         { SC_OPCODE_FOURIER, ENTRY(SC_OPCODE_FOURIER_ARY), 0, ID_FUNCTION_GRP_MATRIX, HID_FUNC_FOURIER, 5, { 0, 0, 1, 1, 1 }, 0 },
-        { 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_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 },
     };
 
     ScFuncDesc* pDesc = nullptr;
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 75f70c78d647..ebff66afafe0 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -685,6 +685,7 @@ private:
     void ScHLookup();
     void ScVLookup();
     void ScXLookup();
+    void ScFilter();
     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 a84187229d09..f6c5409bdbc3 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -8129,6 +8129,179 @@ void ScInterpreter::ScXLookup()
     }
 }
 
+void ScInterpreter::ScFilter()
+{
+    sal_uInt8 nParamCount = GetByte();
+    if (!MustHaveParamCount(nParamCount, 2, 3))
+        return;
+
+    // Optional 3th argument to set the value to return if all values
+    // in the included array are empty (filter returns nothing)
+    formula::FormulaConstTokenRef xNotFound;
+    if (nParamCount == 3 && GetStackType() != svEmptyCell)
+        xNotFound = PopToken();
+
+    SCCOL nCondResultColEnd = 0;
+    SCROW nCondResultRowEnd = 0;
+    ScMatrixRef pCondResultMatrix = nullptr;
+    std::vector<double> aResValues;
+    size_t nMatch = 0;
+    // take 2nd argument criteria bool array
+    switch ( GetStackType() )
+    {
+        case svMatrix :
+        case svExternalDoubleRef:
+        case svExternalSingleRef:
+        case svDoubleRef:
+        case svSingleRef:
+            {
+                pCondResultMatrix = GetMatrix();
+                if (!pCondResultMatrix)
+                {
+                    PushError(FormulaError::IllegalParameter);
+                    return;
+                }
+                SCSIZE nC, nR;
+                pCondResultMatrix->GetDimensions(nC, nR);
+                nCondResultColEnd = static_cast<SCCOL>(nC - 1);
+                nCondResultRowEnd = static_cast<SCROW>(nR - 1);
+
+                // only 1 dimension of filtering allowed (also in excel)
+                if (nCondResultColEnd > 0 && nCondResultRowEnd > 0)
+                {
+                    PushError(FormulaError::NoValue);
+                    return;
+                }
+
+                // result matrix is filled with boolean values.
+                pCondResultMatrix->GetDoubleArray(aResValues);
+
+                FormulaError nError = FormulaError::NONE;
+                auto matchNum = [&nMatch, &nError](double i) {
+                    nError = GetDoubleErrorValue(i);
+                    if (nError != FormulaError::NONE)
+                    {
+                        return true;
+                    }
+                    else
+                    {
+                        if (i > 0)
+                            nMatch++;
+                        return false;
+                    }
+                };
+
+                if (auto it = std::find_if(aResValues.begin(), aResValues.end(), matchNum); it != aResValues.end())
+                {
+                    PushError(nError);
+                    return;
+                }
+            }
+            break;
+
+        default:
+            {
+                PushIllegalParameter();
+                return;
+            }
+    }
+
+    // bail out, no need to evaluate other arguments
+    if (nGlobalError != FormulaError::NONE)
+    {
+        PushError(nGlobalError);
+        return;
+    }
+
+    SCCOL nQueryCol1 = 0;
+    SCROW nQueryRow1 = 0;
+    SCCOL nQueryCol2 = 0;
+    SCROW nQueryRow2 = 0;
+    ScMatrixRef pQueryMatrix = nullptr;
+    // take 1st argument range
+    switch ( GetStackType() )
+    {
+        case svSingleRef:
+        case svDoubleRef:
+        case svMatrix:
+        case svExternalSingleRef:
+        case svExternalDoubleRef:
+            {
+                pQueryMatrix = GetMatrix();
+                if (!pQueryMatrix)
+                {
+                    PushError( FormulaError::IllegalParameter);
+                    return;
+                }
+                SCSIZE nC, nR;
+                pQueryMatrix->GetDimensions( nC, nR);
+                nQueryCol2 = static_cast<SCCOL>(nC - 1);
+                nQueryRow2 = static_cast<SCROW>(nR - 1);
+            }
+        break;
+        default:
+            PushError( FormulaError::IllegalParameter);
+            return;
+    }
+
+    // bail out, no need to set a matrix if we have no result
+    if (!nMatch)
+    {
+        if (xNotFound && (xNotFound->GetType() != svMissing))
+            PushTokenRef(xNotFound);
+        else
+            PushError(FormulaError::NestedArray);
+        return;
+    }
+
+    SCSIZE nResPos = 0;
+    ScMatrixRef pResMat = nullptr;
+    if (nQueryCol2 == nCondResultColEnd && nCondResultColEnd > 0)
+    {
+        pResMat = GetNewMat(nMatch, nQueryRow2 + 1 , /*bEmpty*/true);
+        for (SCROW iR = nQueryRow1; iR <= nQueryRow2; iR++)
+        {
+            for (size_t iC = 0; iC < aResValues.size(); iC++)
+            {
+                if (aResValues[iC] > 0)
+                {
+                    if (pQueryMatrix->IsStringOrEmpty(iC, iR))
+                        pResMat->PutStringTrans(pQueryMatrix->GetString(iC, iR), nResPos++);
+                    else
+                        pResMat->PutDoubleTrans(pQueryMatrix->GetDouble(iC, iR), nResPos++);
+                }
+            }
+        }
+    }
+    else if (nQueryRow2 == nCondResultRowEnd && nCondResultRowEnd > 0)
+    {
+        pResMat = GetNewMat(nQueryCol2 + 1, nMatch, /*bEmpty*/true);
+        for (SCCOL iC = nQueryCol1; iC <= nQueryCol2; iC++)
+        {
+            for (size_t iR = 0; iR < aResValues.size(); iR++)
+            {
+                if (aResValues[iR] > 0)
+                {
+                    if (pQueryMatrix->IsStringOrEmpty(iC, iR))
+                        pResMat->PutString(pQueryMatrix->GetString(iC, iR), nResPos++);
+                    else
+                        pResMat->PutDouble(pQueryMatrix->GetDouble(iC, iR), nResPos++);
+                }
+            }
+        }
+    }
+    else
+    {
+        PushError(FormulaError::IllegalParameter);
+        return;
+    }
+
+    if (pResMat)
+        PushMatrix(pResMat);
+    else
+        PushError(FormulaError::NestedArray);
+}
+
 void ScInterpreter::ScSubTotal()
 {
     sal_uInt8 nParamCount = GetByte();
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index d85ba1c144fe..f31e35269953 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4128,6 +4128,7 @@ StackVar ScInterpreter::Interpret()
                 case ocRandom           : ScRandom();                   break;
                 case ocRandomNV         : ScRandom();                   break;
                 case ocRandbetweenNV    : ScRandbetween();              break;
+                case ocFilter           : ScFilter();                   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 448731491154..a123762cc6cd 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -137,6 +137,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocEqual,           {{ Array, Array                                         }, 0, Value }},
     { ocFTest,           {{ ForceArray, ForceArray                               }, 0, Value }},
     { ocFalse,           {{ Bounds                                               }, 0, Value }},
+    { ocFilter,          {{ ReferenceOrRefArray, ForceArray, Value               }, 0, ForceArrayReturn }},
     { ocForecast,        {{ Value, ForceArray, ForceArray                        }, 0, Value }},
     { ocForecast_ETS_ADD, {{ ForceArray, ForceArray, ForceArray, Value, Value, Value        }, 0, Value }},
     { ocForecast_ETS_MUL, {{ ForceArray, ForceArray, ForceArray, Value, Value, Value        }, 0, Value }},
diff --git a/sc/source/core/tool/scmatrix.cxx b/sc/source/core/tool/scmatrix.cxx
index 4f351b06b510..698c06bf21a5 100644
--- a/sc/source/core/tool/scmatrix.cxx
+++ b/sc/source/core/tool/scmatrix.cxx
@@ -260,10 +260,12 @@ public:
 
     void PutDouble(double fVal, SCSIZE nC, SCSIZE nR);
     void PutDouble( double fVal, SCSIZE nIndex);
+    void PutDoubleTrans( double fVal, SCSIZE nIndex);
     void PutDouble(const double* pArray, size_t nLen, SCSIZE nC, SCSIZE nR);
 
     void PutString(const svl::SharedString& rStr, SCSIZE nC, SCSIZE nR);
     void PutString(const svl::SharedString& rStr, SCSIZE nIndex);
+    void PutStringTrans(const svl::SharedString& rStr, SCSIZE nIndex);
     void PutString(const svl::SharedString* pArray, size_t nLen, SCSIZE nC, SCSIZE nR);
 
     void PutEmpty(SCSIZE nC, SCSIZE nR);
@@ -355,6 +357,7 @@ public:
 
 private:
     void CalcPosition(SCSIZE nIndex, SCSIZE& rC, SCSIZE& rR) const;
+    void CalcTransPosition(SCSIZE nIndex, SCSIZE& rC, SCSIZE& rR) const;
 };
 
 static std::once_flag bElementsMaxFetched;
@@ -537,6 +540,13 @@ void ScMatrixImpl::PutDouble( double fVal, SCSIZE nIndex)
     PutDouble(fVal, nC, nR);
 }
 
+void ScMatrixImpl::PutDoubleTrans(double fVal, SCSIZE nIndex)
+{
+    SCSIZE nC, nR;
+    CalcTransPosition(nIndex, nC, nR);
+    PutDouble(fVal, nC, nR);
+}
+
 void ScMatrixImpl::PutString(const svl::SharedString& rStr, SCSIZE nC, SCSIZE nR)
 {
     if (ValidColRow( nC, nR))
@@ -564,6 +574,13 @@ void ScMatrixImpl::PutString(const svl::SharedString& rStr, SCSIZE nIndex)
     PutString(rStr, nC, nR);
 }
 
+void ScMatrixImpl::PutStringTrans(const svl::SharedString& rStr, SCSIZE nIndex)
+{
+    SCSIZE nC, nR;
+    CalcTransPosition(nIndex, nC, nR);
+    PutString(rStr, nC, nR);
+}
+
 void ScMatrixImpl::PutEmpty(SCSIZE nC, SCSIZE nR)
 {
     if (ValidColRow( nC, nR))
@@ -2641,6 +2658,14 @@ void ScMatrixImpl::CalcPosition(SCSIZE nIndex, SCSIZE& rC, SCSIZE& rR) const
     rR = nIndex - rC*nRowSize;
 }
 
+void ScMatrixImpl::CalcTransPosition(SCSIZE nIndex, SCSIZE& rC, SCSIZE& rR) const
+{
+    SCSIZE nColSize = maMat.size().column;
+    SAL_WARN_IF(!nColSize, "sc.core", "ScMatrixImpl::CalcPosition: 0 cols!");
+    rR = nColSize > 1 ? nIndex / nColSize : nIndex;
+    rC = nIndex - rR * nColSize;
+}
+
 namespace {
 
 size_t get_index(SCSIZE nMaxRow, size_t nRow, size_t nCol, size_t nRowOffset, size_t nColOffset)
@@ -3182,6 +3207,11 @@ void ScMatrix::PutDouble( double fVal, SCSIZE nIndex)
     pImpl->PutDouble(fVal, nIndex);
 }
 
+void ScMatrix::PutDoubleTrans(double fVal, SCSIZE nIndex)
+{
+    pImpl->PutDoubleTrans(fVal, nIndex);
+}
+
 void ScMatrix::PutDouble(const double* pArray, size_t nLen, SCSIZE nC, SCSIZE nR)
 {
     pImpl->PutDouble(pArray, nLen, nC, nR);
@@ -3197,6 +3227,11 @@ void ScMatrix::PutString(const svl::SharedString& rStr, SCSIZE nIndex)
     pImpl->PutString(rStr, nIndex);
 }
 
+void ScMatrix::PutStringTrans(const svl::SharedString& rStr, SCSIZE nIndex)
+{
+    pImpl->PutStringTrans(rStr, nIndex);
+}
+
 void ScMatrix::PutString(const svl::SharedString* pArray, size_t nLen, SCSIZE nC, SCSIZE nR)
 {
     pImpl->PutString(pArray, nLen, nC, nR);
diff --git a/sc/source/core/tool/token.cxx b/sc/source/core/tool/token.cxx
index f57efce3414c..aefd7a350478 100644
--- a/sc/source/core/tool/token.cxx
+++ b/sc/source/core/tool/token.cxx
@@ -1389,6 +1389,7 @@ void ScTokenArray::CheckToken( const FormulaToken& r )
             case ocVLookup:
             case ocXLookup:
             case ocXMatch:
+            case ocFilter:
             case ocSLN:
             case ocIRR:
             case ocMIRR:
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 3968e33e794d..bf32fde22f2d 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -602,7 +602,8 @@ const XclFunctionInfo saFuncTable_2016[] =
 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(  ocXMatch,     2,  4,  0,  "XMATCH" ),
+    EXC_FUNCENTRY_V_VR(  ocFilter,     2,  3,  0,  "FILTER" )
 };
 
 
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 6f4037712d1d..59bfc8cf3281 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -876,7 +876,8 @@ const FunctionData saFuncTable2016[] =
 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.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 }
 };