core.git: formula/inc include/formula sc/inc sc/qa sc/README.md sc/source
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
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
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",