core.git: formula/inc include/formula sc/inc sc/qa sc/README.md sc/source

2024-03-01 Thread Balazs Varga (via logerrit) 
 formula/inc/core_resource.hrc  |6 
 include/formula/compiler.hxx   |3 
 include/formula/opcode.hxx |2 
 sc/README.md   |1 
 sc/inc/helpids.h   |1 
 sc/inc/scfuncs.hrc |   12 
 sc/inc/scmatrix.hxx|2 
 sc/qa/extras/scfunctionlistobj.cxx |2 
 sc/qa/unit/data/functions/spreadsheet/fods/filter.fods | 5061 +
 sc/qa/unit/ucalc.cxx   |1 
 sc/source/core/data/funcdesc.cxx   |3 
 sc/source/core/inc/interpre.hxx|1 
 sc/source/core/tool/interpr1.cxx   |  173 
 sc/source/core/tool/interpr4.cxx   |1 
 sc/source/core/tool/parclass.cxx   |1 
 sc/source/core/tool/scmatrix.cxx   |   35 
 sc/source/core/tool/token.cxx  |1 
 sc/source/filter/excel/xlformula.cxx   |3 
 sc/source/filter/oox/formulabase.cxx   |3 
 19 files changed, 5307 insertions(+), 5 deletions(-)

New commits:
commit b0791dd9216224bdb266fd0d8c87df253b6d0583
Author: Balazs Varga 
AuthorDate: Mon Feb 26 16:13:33 2024 +0100
Commit: Balazs Varga 
CommitDate: Fri Mar 1 18:31:35 2024 +0100

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 

diff --git a/formula/inc/core_resource.hrc b/formula/inc/core_resource.hrc
index 60460e93d38b..f0efc468ab6f 100644
--- a/formula/inc/core_resource.hrc
+++ b/formula/inc/core_resource.hrc
@@ -278,6 +278,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF[] =
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
 { "COM.MICROSOFT.XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
+{ "COM.MICROSOFT.FILTER" , SC_OPCODE_FILTER },
 { "ORG.OPENOFFICE.MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy for 
range list (union)
 { "OFFSET" , SC_OPCODE_OFFSET },
 { "INDEX" , SC_OPCODE_INDEX },
@@ -727,6 +728,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML[] =
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
 { "_xlfn.XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
+{ "_xlfn._xlws.FILTER" , SC_OPCODE_FILTER },
 { "_xlfn.ORG.OPENOFFICE.MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy 
for range list (union)
 { "OFFSET" , SC_OPCODE_OFFSET },
 { "INDEX" , SC_OPCODE_INDEX },
@@ -1179,6 +1181,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_PODF[] =
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
 { "XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
+{ "FILTER" , SC_OPCODE_FILTER },
 { "MULTIRANGE" , SC_OPCODE_MULTI_AREA },// legacy for range list 
(union)
 { "OFFSET" , SC_OPCODE_OFFSET },
 { "INDEX" , SC_OPCODE_INDEX },
@@ -1632,6 +1635,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_API[] =
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
 { "XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
+{ "FILTER" , SC_OPCODE_FILTER },
 { "MULTIRANGE" , SC_OPCODE_MULTI_AREA },// legacy for range list 
(union)
 { "OFFSET" , SC_OPCODE_OFFSET },
 { "INDEX" , SC_OPCODE_INDEX },  // ?? first character = I ??
@@ -2083,6 +2087,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH[] =
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
 { "XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
+{ "FILTER" , SC_OPCODE_FILTER },
 { "MULTIRANGE" , SC_OPCODE_MULTI_AREA },
 { "OFFSET" , SC_OPCODE_OFFSET },
 { "INDEX" , SC_OPCODE_INDEX },
@@ -2515,6 +2520,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES[] =
 { NC_("RID_STRLIST_FUNCTION_NAMES", "VLOOKUP") , SC_OPCODE_V_LOOKUP },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "XLOOKUP") , SC_OPCODE_X_LOOKUP },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "HLOOKUP") , SC_OPCODE_H_LOOKUP },
+{ NC_("RID_STRLIST_FUNCTION_NAMES", "FILTER") , SC_OPCODE_FILTER },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "MULTIRANGE") ,

core.git: formula/inc include/formula sc/inc sc/qa sc/README.md sc/source

2024-02-18 Thread Balazs Varga (via logerrit) 
 formula/inc/core_resource.hrc  |6 
 include/formula/compiler.hxx   |2 
 include/formula/opcode.hxx |2 
 sc/README.md   |1 
 sc/inc/helpids.h   |1 
 sc/inc/scfuncs.hrc |   14 
 sc/qa/extras/scfunctionlistobj.cxx |2 
 sc/qa/unit/data/functions/spreadsheet/fods/xmatch.fods | 3753 +
 sc/qa/unit/ucalc.cxx   |1 
 sc/source/core/data/funcdesc.cxx   |1 
 sc/source/core/data/queryiter.cxx  |   39 
 sc/source/core/inc/interpre.hxx|3 
 sc/source/core/tool/interpr1.cxx   |  180 
 sc/source/core/tool/interpr4.cxx   |1 
 sc/source/core/tool/parclass.cxx   |3 
 sc/source/core/tool/token.cxx  |1 
 sc/source/filter/excel/xlformula.cxx   |3 
 sc/source/filter/oox/formulabase.cxx   |3 
 18 files changed, 3988 insertions(+), 28 deletions(-)

New commits:
commit b36ecef07a4933c0943b27ea937f31e1df2e77cd
Author: Balazs Varga 
AuthorDate: Mon Feb 12 21:36:30 2024 +0100
Commit: Balazs Varga 
CommitDate: Sun Feb 18 23:13:01 2024 +0100

tdf#128425 Add Excel2021 function XMATCH to Calc

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

Change-Id: I7791ab873cf62ce882feba75dc0d722bc5990f9a
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/163260
Tested-by: Jenkins
Reviewed-by: Balazs Varga 

diff --git a/formula/inc/core_resource.hrc b/formula/inc/core_resource.hrc
index 835d928ea4a8..60460e93d38b 100644
--- a/formula/inc/core_resource.hrc
+++ b/formula/inc/core_resource.hrc
@@ -266,6 +266,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF[] =
 { "INDIRECT" , SC_OPCODE_INDIRECT },
 { "ADDRESS" , SC_OPCODE_ADDRESS },
 { "MATCH" , SC_OPCODE_MATCH },
+{ "COM.MICROSOFT.XMATCH" , SC_OPCODE_X_MATCH },
 { "COUNTBLANK" , SC_OPCODE_COUNT_EMPTY_CELLS },
 { "COUNTIF" , SC_OPCODE_COUNT_IF },
 { "SUMIF" , SC_OPCODE_SUM_IF },
@@ -714,6 +715,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML[] =
 { "INDIRECT" , SC_OPCODE_INDIRECT },
 { "ADDRESS" , SC_OPCODE_ADDRESS },
 { "MATCH" , SC_OPCODE_MATCH },
+{ "_xlfn.XMATCH" , SC_OPCODE_X_MATCH },
 { "COUNTBLANK" , SC_OPCODE_COUNT_EMPTY_CELLS },
 { "COUNTIF" , SC_OPCODE_COUNT_IF },
 { "SUMIF" , SC_OPCODE_SUM_IF },
@@ -1165,6 +1167,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_PODF[] =
 { "INDIRECT" , SC_OPCODE_INDIRECT },
 { "ADDRESS" , SC_OPCODE_ADDRESS },
 { "MATCH" , SC_OPCODE_MATCH },
+{ "XMATCH" , SC_OPCODE_X_MATCH },
 { "COUNTBLANK" , SC_OPCODE_COUNT_EMPTY_CELLS },
 { "COUNTIF" , SC_OPCODE_COUNT_IF },
 { "SUMIF" , SC_OPCODE_SUM_IF },
@@ -1617,6 +1620,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_API[] =
 { "INDIRECT" , SC_OPCODE_INDIRECT },
 { "ADDRESS" , SC_OPCODE_ADDRESS },
 { "MATCH" , SC_OPCODE_MATCH },
+{ "XMATCH" , SC_OPCODE_X_MATCH },
 { "COUNTBLANK" , SC_OPCODE_COUNT_EMPTY_CELLS },
 { "COUNTIF" , SC_OPCODE_COUNT_IF },
 { "SUMIF" , SC_OPCODE_SUM_IF },
@@ -2067,6 +2071,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH[] =
 { "INDIRECT" , SC_OPCODE_INDIRECT },
 { "ADDRESS" , SC_OPCODE_ADDRESS },
 { "MATCH" , SC_OPCODE_MATCH },
+{ "XMATCH" , SC_OPCODE_X_MATCH },
 { "COUNTBLANK" , SC_OPCODE_COUNT_EMPTY_CELLS },
 { "COUNTIF" , SC_OPCODE_COUNT_IF },
 { "SUMIF" , SC_OPCODE_SUM_IF },
@@ -2498,6 +2503,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES[] =
 { NC_("RID_STRLIST_FUNCTION_NAMES", "INDIRECT") , SC_OPCODE_INDIRECT },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "ADDRESS") , SC_OPCODE_ADDRESS },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "MATCH") , SC_OPCODE_MATCH },
+{ NC_("RID_STRLIST_FUNCTION_NAMES", "XMATCH") , SC_OPCODE_X_MATCH },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "COUNTBLANK") , 
SC_OPCODE_COUNT_EMPTY_CELLS },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "COUNTIF") , SC_OPCODE_COUNT_IF },
 { NC_("RID_STRLIST_FUNCTION_NAMES", "SUMIF") , SC_OPCODE_SUM_IF },
diff --git a/include/formula/compiler.hxx b/include/formula/compiler.hxx
index 479008dfb1a2..f3187ae95baa 100644
--- a/include/formula/compiler.hxx
+++ b/include/formula/compiler.hxx
@@ -399,7 +399,7 @@
 #define SC_OPCODE_ISPMT 386
 #define SC_OPCODE_HYPERLINK 387
 #define SC_OPCODE_X_LOOKUP  388
-// free: 389
+#define SC_OPCODE_X_MATCH   389
 #define SC_OPCODE_GET_PIVOT_DATA390
 #define SC_OPCODE_EUROCONVERT   391
 #define SC_OPCODE_NUMBERVALUE   392
diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx
index ad056a9b0b50..3c8053ffb1e3 100644
---

core.git: formula/inc include/formula sc/inc sc/qa sc/README.md sc/source

2024-01-31 Thread Winfried Donkers (via logerrit) 
 formula/inc/core_resource.hrc   |6 
 include/formula/compiler.hxx|2 
 include/formula/opcode.hxx  |2 
 sc/README.md|1 
 sc/inc/helpids.h|1 
 sc/inc/queryiter.hxx|   60 
 sc/inc/scfuncs.hrc  |   18 
 sc/qa/extras/scfunctionlistobj.cxx  |2 
 sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods | 4553 
 sc/qa/unit/ucalc.cxx|1 
 sc/qa/unit/ucalc_sort.cxx   |4 
 sc/source/core/data/funcdesc.cxx|1 
 sc/source/core/data/queryiter.cxx   |  184 
 sc/source/core/inc/interpre.hxx |   61 
 sc/source/core/tool/interpr1.cxx| 1232 +++-
 sc/source/core/tool/interpr4.cxx|1 
 sc/source/core/tool/parclass.cxx|1 
 sc/source/core/tool/token.cxx   |1 
 sc/source/filter/excel/xlformula.cxx|   13 
 sc/source/filter/oox/formulabase.cxx|   15 
 20 files changed, 5816 insertions(+), 343 deletions(-)

New commits:
commit f7039822c7ad3987326e1c20ea4a745c158f9682
Author: Winfried Donkers 
AuthorDate: Wed Jun 28 16:13:45 2023 +0200
Commit: Balazs Varga 
CommitDate: Wed Jan 31 09:14:19 2024 +0100

tdf#127293 Add Excel2021 function XLOOKUP to Calc

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

What is working already: xlookup with normal forward,
backward search in columns/rows. Binary search in rows with
real binary search algorithm, in columns only works with linear search yet.
Linear forward backward wildcard/regex search in columns/rows.
Looking for the first smaller or greater value with linear and binary search
ALso all the combination of all these options. Except XLOOKUP
not supperted wildcard/regex search with binary search.

TODO in next patches:
- add the binary search option for searching in columns.
- Evaluate Formula calculation not working in general.

Co-authored-by: Balazs Varga 

Change-Id: I15fd4479b63ec13b093d269760d1bbb5957553e8
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/131905
Tested-by: Jenkins
Tested-by: Gabor Kelemen 
Reviewed-by: Balazs Varga 

diff --git a/formula/inc/core_resource.hrc b/formula/inc/core_resource.hrc
index baed318f586e..835d928ea4a8 100644
--- a/formula/inc/core_resource.hrc
+++ b/formula/inc/core_resource.hrc
@@ -275,6 +275,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF[] =
 { "COUNTIFS" , SC_OPCODE_COUNT_IFS },
 { "LOOKUP" , SC_OPCODE_LOOKUP },
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
+{ "COM.MICROSOFT.XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
 { "ORG.OPENOFFICE.MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy for 
range list (union)
 { "OFFSET" , SC_OPCODE_OFFSET },
@@ -722,6 +723,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML[] =
 { "COUNTIFS" , SC_OPCODE_COUNT_IFS },
 { "LOOKUP" , SC_OPCODE_LOOKUP },
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
+{ "_xlfn.XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
 { "_xlfn.ORG.OPENOFFICE.MULTIRANGE" , SC_OPCODE_MULTI_AREA }, // legacy 
for range list (union)
 { "OFFSET" , SC_OPCODE_OFFSET },
@@ -1172,6 +1174,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_PODF[] =
 { "COUNTIFS" , SC_OPCODE_COUNT_IFS },
 { "LOOKUP" , SC_OPCODE_LOOKUP },
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
+{ "XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
 { "MULTIRANGE" , SC_OPCODE_MULTI_AREA },// legacy for range list 
(union)
 { "OFFSET" , SC_OPCODE_OFFSET },
@@ -1623,6 +1626,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH_API[] =
 { "COUNTIFS" , SC_OPCODE_COUNT_IFS },
 { "LOOKUP" , SC_OPCODE_LOOKUP },
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
+{ "XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
 { "MULTIRANGE" , SC_OPCODE_MULTI_AREA },// legacy for range list 
(union)
 { "OFFSET" , SC_OPCODE_OFFSET },
@@ -2072,6 +2076,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES_ENGLISH[] =
 { "COUNTIFS" , SC_OPCODE_COUNT_IFS },
 { "LOOKUP" , SC_OPCODE_LOOKUP },
 { "VLOOKUP" , SC_OPCODE_V_LOOKUP },
+{ "XLOOKUP" , SC_OPCODE_X_LOOKUP },
 { "HLOOKUP" , SC_OPCODE_H_LOOKUP },
 { "MULTIRANGE" , SC_OPCODE_MULTI_AREA },
 { "OFFSET" , SC_OPCODE_OFFSET },
@@ -2502,6 +2507,7 @@ const std::pair 
RID_STRLIST_FUNCTION_NAMES[] =
 { NC_("RID_STRLIST_FUNCTION_NAMES", "COUNTIFS") , SC_OPCODE_COUNT_IFS },
 { NC_("RID_STRLIST_FUNCTION_NAMES",