diff options
| -rw-r--r-- | sc/inc/queryiter.hxx | 18 | ||||
| -rw-r--r-- | sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods | 406 | ||||
| -rw-r--r-- | sc/source/core/data/queryiter.cxx | 57 | ||||
| -rw-r--r-- | sc/source/core/tool/interpr1.cxx | 22 |
4 files changed, 423 insertions, 80 deletions
diff --git a/sc/inc/queryiter.hxx b/sc/inc/queryiter.hxx index e34a7be20e13..662fe8a9cf35 100644 --- a/sc/inc/queryiter.hxx +++ b/sc/inc/queryiter.hxx @@ -171,8 +171,16 @@ protected: nTestEqualConditionFulfilled = nTestEqualConditionEnabled | nTestEqualConditionMatched }; + enum SortedBinarySearchBits + { + nBinarySearchDisabled = 0x00, + nSearchbAscd = 0x01, + nSearchbDesc = 0x02, + }; + sal_uInt8 nStopOnMismatch; sal_uInt8 nTestEqualCondition; + sal_uInt8 nSortedBinarySearch; bool bAdvanceQuery; bool bIgnoreMismatchOnLeadingStrings; bool bSortedBinarySearch; @@ -263,8 +271,11 @@ public: { bAdvanceQuery = bVal; } void AdvanceQueryParamEntryField(); - void SetSortedBinarySearchMode( bool bVal ) - { bSortedBinarySearch = bVal; } + void SetSortedBinarySearchMode( sal_Int8 nSearchMode ) + { + nSortedBinarySearch = sal::static_int_cast<sal_uInt8>(nSearchMode == 2 ? + nSearchbAscd : (nSearchMode == -2 ? nSearchbDesc : nBinarySearchDisabled)); + } void SetXlookupMode( bool bVal ) { bXLookUp = bVal; } @@ -309,11 +320,12 @@ class ScQueryCellIterator using Base::nStopOnMismatchEnabled; using Base::nTestEqualCondition; using Base::nTestEqualConditionEnabled; + using Base::nSortedBinarySearch; + using Base::nBinarySearchDisabled; using Base::PerformQuery; using Base::getThisResult; using Base::nBestFitCol; using Base::nBestFitRow; - using Base::bSortedBinarySearch; using Base::bXLookUp; bool GetThis(); diff --git a/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods b/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods index 16abcbcacb4b..49ecf573b50c 100644 --- a/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods +++ b/sc/qa/unit/data/functions/spreadsheet/fods/xlookup.fods @@ -1,7 +1,7 @@ <?xml version="1.0" encoding="UTF-8"?> <office:document xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:config="urn:oasis:names:tc:opendocument:xmlns:config:1.0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:rpt="http://openoffice.org/2005/report" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:tableooo="http://openoffice.org/2009/table" xmlns:calcext="urn:org:documentfoundation:names:experimental:calc:xmlns:calcext:1.0" xmlns:drawooo="http://openoffice.org/2010/draw" xmlns:loext="urn:org:documentfoundation:names:experimental:office:xmlns:loext:1.0" xmlns:field="urn:openoffice:names:experimental:ooo-ms-interop:xmlns:field:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:formx="urn:openoffice:names:experimental:ooxml-odf-interop:xmlns:form:1.0" xmlns:dom="http://www.w3.org/2001/xml-events" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:grddl="http://www.w3.org/2003/g/data-view#" xmlns:css3t="http://www.w3.org/TR/css3-text/" xmlns:presentation="urn:oasis:names:tc:opendocument:xmlns:presentation:1.0" office:version="1.3" office:mimetype="application/vnd.oasis.opendocument.spreadsheet"> - <office:meta><meta:creation-date>2024-01-16T18:30:06.278000000</meta:creation-date><meta:editing-duration>PT52M13S</meta:editing-duration><meta:editing-cycles>14</meta:editing-cycles><meta:generator>LibreOfficeDev/24.8.0.0.alpha0$Windows_X86_64 LibreOffice_project/8f959fffda28199c3d97aea8d5468776a0e43d90</meta:generator><dc:date>2024-01-26T18:16:42.850000000</dc:date><meta:document-statistic meta:table-count="2" meta:cell-count="754" meta:object-count="0"/></office:meta> + <office:meta><meta:creation-date>2024-01-16T18:30:06.278000000</meta:creation-date><meta:editing-duration>PT1H12M</meta:editing-duration><meta:editing-cycles>22</meta:editing-cycles><meta:generator>LibreOfficeDev/24.8.0.0.alpha0$Windows_X86_64 LibreOffice_project/2b1f5fa987f02ec8014dbf6837c6b090faf11f2d</meta:generator><dc:date>2024-02-01T11:50:13.789000000</dc:date><meta:document-statistic meta:table-count="2" meta:cell-count="858" meta:object-count="0"/></office:meta> <office:settings> <config:config-item-set config:name="ooo:view-settings"> <config:config-item config:name="VisibleAreaTop" config:type="int">0</config:config-item> @@ -30,13 +30,13 @@ <config:config-item config:name="IgnoreBreakAfterMultilineField" config:type="boolean">false</config:config-item> </config:config-item-map-entry> <config:config-item-map-entry config:name="Sheet2"> - <config:config-item config:name="CursorPositionX" config:type="int">0</config:config-item> - <config:config-item config:name="CursorPositionY" config:type="int">104</config:config-item> + <config:config-item config:name="CursorPositionX" config:type="int">1</config:config-item> + <config:config-item config:name="CursorPositionY" config:type="int">115</config:config-item> <config:config-item config:name="ActiveSplitRange" config:type="short">2</config:config-item> <config:config-item config:name="PositionLeft" config:type="int">0</config:config-item> <config:config-item config:name="PositionRight" config:type="int">0</config:config-item> <config:config-item config:name="PositionTop" config:type="int">0</config:config-item> - <config:config-item config:name="PositionBottom" config:type="int">81</config:config-item> + <config:config-item config:name="PositionBottom" config:type="int">0</config:config-item> <config:config-item config:name="ZoomType" config:type="short">0</config:config-item> <config:config-item config:name="ZoomValue" config:type="int">65</config:config-item> <config:config-item config:name="PageViewZoomValue" config:type="int">60</config:config-item> @@ -48,7 +48,7 @@ </config:config-item-map-entry> </config:config-item-map-named> <config:config-item config:name="ActiveTable" config:type="string">Sheet1</config:config-item> - <config:config-item config:name="HorizontalScrollbarWidth" config:type="int">1835</config:config-item> + <config:config-item config:name="HorizontalScrollbarWidth" config:type="int">1837</config:config-item> <config:config-item config:name="ZoomType" config:type="short">0</config:config-item> <config:config-item config:name="ZoomValue" config:type="int">65</config:config-item> <config:config-item config:name="PageViewZoomValue" config:type="int">60</config:config-item> @@ -126,7 +126,7 @@ <config:config-item config:name="UpdateFromTemplate" config:type="boolean">true</config:config-item> <config:config-item-map-named config:name="ScriptConfiguration"> <config:config-item-map-entry config:name="Sheet1"> - <config:config-item config:name="CodeName" config:type="string">Sheet1</config:config-item> + <config:config-item config:name="CodeName" config:type="string">Munkalap1</config:config-item> </config:config-item-map-entry> <config:config-item-map-entry config:name="Sheet2"> <config:config-item config:name="CodeName" config:type="string">Sheet2</config:config-item> @@ -1597,6 +1597,9 @@ <style:style style:name="co8" style:family="table-column"> <style:table-column-properties fo:break-before="auto" style:column-width="5.657cm"/> </style:style> + <style:style style:name="co9" style:family="table-column"> + <style:table-column-properties fo:break-before="auto" style:column-width="1.875cm"/> + </style:style> <style:style style:name="ro1" style:family="table-row"> <style:table-row-properties style:row-height="1.614cm" fo:break-before="auto" style:use-optimal-row-height="true"/> </style:style> @@ -1610,7 +1613,7 @@ <style:table-row-properties style:row-height="0.529cm" fo:break-before="auto" style:use-optimal-row-height="true"/> </style:style> <style:style style:name="ro5" style:family="table-row"> - <style:table-row-properties style:row-height="1.656cm" fo:break-before="auto" style:use-optimal-row-height="true"/> + <style:table-row-properties style:row-height="1.632cm" fo:break-before="auto" style:use-optimal-row-height="true"/> </style:style> <style:style style:name="ta1" style:family="table" style:master-page-name="Default"> <style:table-properties table:display="true" style:writing-mode="lr-tb"/> @@ -1673,6 +1676,7 @@ <style:style style:name="ce12" style:family="table-cell" style:parent-style-name="Default"> <style:table-cell-properties fo:wrap-option="wrap"/> </style:style> + <style:style style:name="ce20" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N0"/> <style:page-layout style:name="pm1"> <style:page-layout-properties style:writing-mode="lr-tb"/> <style:header-style> @@ -1715,7 +1719,7 @@ <text:p><text:sheet-name>???</text:sheet-name><text:s/>(<text:title>???</text:title>)</text:p> </style:region-left> <style:region-right> - <text:p><text:date style:data-style-name="N2" text:date-value="2024-01-26">0000.00.00</text:date>, <text:time style:data-style-name="N2" text:time-value="18:11:05.851000000">00:00:00</text:time></text:p> + <text:p><text:date style:data-style-name="N2" text:date-value="2024-02-01">0000.00.00</text:date>, <text:time style:data-style-name="N2" text:time-value="14:12:01.197000000">00:00:00</text:time></text:p> </style:region-right> </style:header> <style:header-left style:display="false"/> @@ -1773,7 +1777,7 @@ <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float"> <text:p>2</text:p> </table:table-cell> - <table:table-cell table:style-name="ce14" table:formula="of:=AND([Sheet2.C2:.C201])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <table:table-cell table:style-name="ce14" table:formula="of:=AND([Sheet2.C2:.C202])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> <text:p>IGAZ</text:p> </table:table-cell> <table:table-cell table:style-name="ce22" office:value-type="string" calcext:value-type="string"> @@ -1804,7 +1808,8 @@ <table:table-column table:style-name="co6" table:default-cell-style-name="ce10"/> <table:table-column table:style-name="co7" table:default-cell-style-name="Default"/> <table:table-column table:style-name="co8" table:default-cell-style-name="Default"/> - <table:table-column table:style-name="co4" table:number-columns-repeated="15" table:default-cell-style-name="Default"/> + <table:table-column table:style-name="co4" table:number-columns-repeated="14" table:default-cell-style-name="Default"/> + <table:table-column table:style-name="co9" table:default-cell-style-name="Default"/> <table:table-row table:style-name="ro2"> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>Function</text:p> @@ -1846,7 +1851,24 @@ <table:table-cell office:value-type="float" office:value="4" calcext:value-type="float"> <text:p>4</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="6"/> + <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float"> + <text:p>2</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>Country Asc1</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>Country Asc2</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>Country Desc1</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>Country Desc2</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>Prefix</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.G2];[.H2:.H11];[.J2:.J11])" office:value-type="string" office:string-value="+55" calcext:value-type="string"> @@ -1880,7 +1902,16 @@ <table:table-cell office:value-type="float" office:value="10" calcext:value-type="float"> <text:p>10</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="0" calcext:value-type="float"> + <text:p>0</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="8" calcext:value-type="float"> + <text:p>8</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>a</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("India";[.H2:.H11];[.J2:.J11])" office:value-type="string" office:string-value="+91" calcext:value-type="string"> @@ -1911,7 +1942,16 @@ <table:table-cell office:value-type="float" office:value="9" calcext:value-type="float"> <text:p>9</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="1" calcext:value-type="float"> + <text:p>1</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="7" calcext:value-type="float"> + <text:p>7</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>b</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("INDIA";[.H2:.H11];[.J2:.J11])" office:value-type="string" office:string-value="+91" calcext:value-type="string"> @@ -1942,14 +1982,26 @@ <table:table-cell office:value-type="float" office:value="8" calcext:value-type="float"> <text:p>8</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float"> + <text:p>1</text:p> + </table:table-cell> + <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float"> + <text:p>2</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="7" calcext:value-type="float"> + <text:p>7</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce20" office:value-type="string" calcext:value-type="string"> + <text:p>c</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:formula="of:=#N/A" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:style-name="ce18" table:formula="of:=ISNA([.A5])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> <text:p>IGAZ</text:p> @@ -1973,7 +2025,19 @@ <table:table-cell office:value-type="float" office:value="7" calcext:value-type="float"> <text:p>7</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell office:value-type="float" office:value="3" calcext:value-type="float"> + <text:p>3</text:p> + </table:table-cell> + <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float"> + <text:p>2</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="6" calcext:value-type="float"> + <text:p>6</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>d</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11];"")"> @@ -2004,7 +2068,16 @@ <table:table-cell office:value-type="float" office:value="6" calcext:value-type="float"> <text:p>6</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="3" calcext:value-type="float"> + <text:p>3</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="4" calcext:value-type="float"> + <text:p>4</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>e</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11];-99)" office:value-type="float" office:value="-99" calcext:value-type="float"> @@ -2032,7 +2105,16 @@ <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="6" calcext:value-type="float"> <text:p>6</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="4" calcext:value-type="float"> + <text:p>4</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="3" calcext:value-type="float"> + <text:p>3</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>f</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("Ireland";[.H2:.H11];[.J2:.J11];"Not here")" office:value-type="string" office:string-value="Not here" calcext:value-type="string"> @@ -2063,14 +2145,26 @@ <table:table-cell office:value-type="float" office:value="4" calcext:value-type="float"> <text:p>4</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="6" calcext:value-type="float"> + <text:p>6</text:p> + </table:table-cell> + <table:table-cell office:value-type="float" office:value="3" calcext:value-type="float"> + <text:p>3</text:p> + </table:table-cell> + <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float"> + <text:p>2</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>g</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("";[.H2:.H11];[.J2:.J11])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:formula="of:=#N/A" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:style-name="ce18" table:formula="of:=ISNA([.A9])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> <text:p>IGAZ</text:p> @@ -2094,7 +2188,19 @@ <table:table-cell office:value-type="float" office:value="3" calcext:value-type="float"> <text:p>3</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="7" calcext:value-type="float"> + <text:p>7</text:p> + </table:table-cell> + <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float"> + <text:p>1</text:p> + </table:table-cell> + <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float"> + <text:p>2</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>h</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("Nigeria";[.H2:.H11];[.J2:.J11];;;1)" office:value-type="string" office:string-value="00234" calcext:value-type="string"> @@ -2125,7 +2231,16 @@ <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float"> <text:p>2</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="7" calcext:value-type="float"> + <text:p>7</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="1" calcext:value-type="float"> + <text:p>1</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>I</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("Nigeria";[.H2:.H11];[.J2:.J11];;;-1)" office:value-type="string" office:string-value="+234" calcext:value-type="string"> @@ -2156,7 +2271,16 @@ <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float"> <text:p>1</text:p> </table:table-cell> - <table:table-cell table:number-columns-repeated="8"/> + <table:table-cell table:number-columns-repeated="3"/> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="8" calcext:value-type="float"> + <text:p>8</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="2" office:value-type="float" office:value="0" calcext:value-type="float"> + <text:p>0</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>j</text:p> + </table:table-cell> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:number-columns-repeated="2"/> @@ -2385,10 +2509,10 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:number-matrix-columns-spanned="1" table:number-matrix-rows-spanned="2" table:formula="of:=COM.MICROSOFT.XLOOKUP("";[.I14:.R14];[.I15:.R16])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:formula="of:=#N/A" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:style-name="ce18" table:formula="of:=ISNA([.A20])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> <text:p>IGAZ</text:p> @@ -2400,10 +2524,10 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:formula="of:=#N/A" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell table:style-name="ce18" table:formula="of:=ISNA([.A21])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> <text:p>IGAZ</text:p> @@ -2546,8 +2670,8 @@ <table:table-cell office:value-type="float" office:value="0.24" calcext:value-type="float"> <text:p>0,24</text:p> </table:table-cell> - <table:table-cell table:style-name="ce18" office:value-type="string" calcext:value-type="string"> - <text:p/> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A30]=[.B30]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> </table:table-cell> <table:table-cell table:formula="of:=FORMULA([.A30])" office:value-type="string" office:string-value="=XLOOKUP(G29;I30:I35;H30:H35;;1;2)" calcext:value-type="string"> <text:p>=XLOOKUP(G29;I30:I35;H30:H35;;1;2)</text:p> @@ -2588,7 +2712,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.G29];[.I30:.I35];[.H30:.H35];;2;2)" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>#N/A</text:p> @@ -2937,14 +3061,14 @@ </table:table-row> <table:table-row table:style-name="ro5"> <table:table-cell table:formula="of:=SUM(COM.MICROSOFT.XLOOKUP([.G44];[.H45:.H49];[.K45:.K49]):COM.MICROSOFT.XLOOKUP([.G45];[.H45:.H49];[.K45:.K49]))" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>Err:502</text:p> + <text:p>Hiba:502</text:p> </table:table-cell> <table:table-cell office:value-type="float" office:value="11070" calcext:value-type="float"> <text:p>11070</text:p> </table:table-cell> <table:table-cell table:style-name="ce18"/> - <table:table-cell table:formula="of:=FORMULA([.A44])" office:value-type="string" office:string-value="=SUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))" calcext:value-type="string"> - <text:p>=SUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))</text:p> + <table:table-cell table:formula="of:=FORMULA([.A44])" office:value-type="string" office:string-value="=SZUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))" calcext:value-type="string"> + <text:p>=SZUM(XLOOKUP(G44;H45:H49;K45:K49):XLOOKUP(G45;H45:H49;K45:K49))</text:p> </table:table-cell> <table:table-cell table:style-name="ce12" office:value-type="string" calcext:value-type="string"> <text:p>XLOOKUP should (in this case) return a range (TODO: Missing feature from Calc: Evaluate Formula)</text:p> @@ -3067,7 +3191,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("B";[.H51:.H53];[.K51:.K52])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>Err:504</text:p> + <text:p>Hiba:504</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>err:504</text:p> @@ -3100,7 +3224,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("C";[.H51:.H53];[.K51:.K52])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>Err:504</text:p> + <text:p>Hiba:504</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>err:504</text:p> @@ -3133,7 +3257,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("E";[.H51:.I53];[.K51:.L53])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>Err:504</text:p> + <text:p>Hiba:504</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>err:504</text:p> @@ -3166,7 +3290,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("E";[.H51:.I53];[.K51:.K53])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>Err:504</text:p> + <text:p>Hiba:504</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>err:504</text:p> @@ -3185,7 +3309,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("C";[.H51:.I53];[.K51:.K53])" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>Err:504</text:p> + <text:p>Hiba:504</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>err:504</text:p> @@ -4197,7 +4321,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("C?";[.H$95:.L$95];[.H$96:.L$96];;2;1)" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>#N/A</text:p> @@ -4346,7 +4470,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP("f?";[.I$79:.I$83];[.L$79:.L$83];;2;1)" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>#N/A</text:p> @@ -4515,7 +4639,7 @@ </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.M1];[.K$1:.K$12];[.I$1:.I$12];;0;2)" office:value-type="string" office:string-value="" calcext:value-type="error"> - <text:p>#N/A</text:p> + <text:p>#HIÁNYZIK</text:p> </table:table-cell> <table:table-cell office:value-type="string" calcext:value-type="string"> <text:p>#N/A</text:p> @@ -4528,19 +4652,209 @@ </table:table-cell> <table:table-cell table:number-columns-repeated="16"/> </table:table-row> - <table:table-row table:style-name="ro2" table:number-rows-repeated="90"> + <table:table-row table:style-name="ro2"> + <table:table-cell table:number-columns-repeated="2"/> + <table:table-cell table:style-name="ce18"/> + <table:table-cell table:number-columns-repeated="17"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.P$1:.P$11];[.T$1:.T$11];;0;2)" office:value-type="string" office:string-value="" calcext:value-type="error"> + <text:p>#HIÁNYZIK</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>#N/A</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=ISERROR([.A113])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A113])" office:value-type="string" office:string-value="=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;0;2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;0;2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.P$1:.P$11];[.T$1:.T$11];;-1;2)" office:value-type="string" office:string-value="b" calcext:value-type="string"> + <text:p>b</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>b</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A114]=[.B114]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A114])" office:value-type="string" office:string-value="=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;-1;2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;-1;2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.P$1:.P$11];[.T$1:.T$11];;1;2)" office:value-type="string" office:string-value="d" calcext:value-type="string"> + <text:p>d</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>d</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A115]=[.B115]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A115])" office:value-type="string" office:string-value="=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;1;2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;P$1:P$11;T$1:T$11;;1;2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.Q$1:.Q$11];[.T$1:.T$11];;0;2)" office:value-type="string" office:string-value="c" calcext:value-type="string"> + <text:p>c</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>c</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A116]=[.B116]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A116])" office:value-type="string" office:string-value="=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;0;2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;0;2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.Q$1:.Q$11];[.T$1:.T$11];;-1;2)" office:value-type="string" office:string-value="c" calcext:value-type="string"> + <text:p>c</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>c</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A117]=[.B117]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A117])" office:value-type="string" office:string-value="=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;-1;2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;-1;2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.Q$1:.Q$11];[.T$1:.T$11];;1;2)" office:value-type="string" office:string-value="c" calcext:value-type="string"> + <text:p>c</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>c</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A118]=[.B118]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A118])" office:value-type="string" office:string-value="=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;1;2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;Q$1:Q$11;T$1:T$11;;1;2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:number-columns-repeated="2"/> + <table:table-cell table:style-name="ce18"/> + <table:table-cell table:number-columns-repeated="17"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.R$1:.R$11];[.T$1:.T$11];;0;-2)" office:value-type="string" office:string-value="" calcext:value-type="error"> + <text:p>#HIÁNYZIK</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>#N/A</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=ISERROR([.A120])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A120])" office:value-type="string" office:string-value="=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;0;-2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;0;-2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.R$1:.R$11];[.T$1:.T$11];;-1;-2)" office:value-type="string" office:string-value="I" calcext:value-type="string"> + <text:p>I</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>I</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A121]=[.B121]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A121])" office:value-type="string" office:string-value="=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;-1;-2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;-1;-2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.R$1:.R$11];[.T$1:.T$11];;1;-2)" office:value-type="string" office:string-value="g" calcext:value-type="string"> + <text:p>g</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>g</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A122]=[.B122]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A122])" office:value-type="string" office:string-value="=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;1;-2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;R$1:R$11;T$1:T$11;;1;-2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.S$1:.S$11];[.T$1:.T$11];;0;-2)" office:value-type="string" office:string-value="h" calcext:value-type="string"> + <text:p>h</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>h</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A123]=[.B123]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A123])" office:value-type="string" office:string-value="=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;0;-2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;0;-2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.S$1:.S$11];[.T$1:.T$11];;-1;-2)" office:value-type="string" office:string-value="h" calcext:value-type="string"> + <text:p>h</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>h</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A124]=[.B124]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A124])" office:value-type="string" office:string-value="=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;-1;-2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;-1;-2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2"> + <table:table-cell table:formula="of:=COM.MICROSOFT.XLOOKUP([.$O$1];[.S$1:.S$11];[.T$1:.T$11];;1;-2)" office:value-type="string" office:string-value="h" calcext:value-type="string"> + <text:p>h</text:p> + </table:table-cell> + <table:table-cell office:value-type="string" calcext:value-type="string"> + <text:p>h</text:p> + </table:table-cell> + <table:table-cell table:style-name="ce18" table:formula="of:=[.A125]=[.B125]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean"> + <text:p>IGAZ</text:p> + </table:table-cell> + <table:table-cell table:formula="of:=FORMULA([.A125])" office:value-type="string" office:string-value="=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;1;-2)" calcext:value-type="string"> + <text:p>=XLOOKUP($O$1;S$1:S$11;T$1:T$11;;1;-2)</text:p> + </table:table-cell> + <table:table-cell table:number-columns-repeated="16"/> + </table:table-row> + <table:table-row table:style-name="ro2" table:number-rows-repeated="77"> <table:table-cell table:number-columns-repeated="2"/> <table:table-cell table:style-name="ce18"/> <table:table-cell table:number-columns-repeated="17"/> </table:table-row> - <table:table-row table:style-name="ro2" table:number-rows-repeated="1048374"> + <table:table-row table:style-name="ro2" table:number-rows-repeated="1048373"> <table:table-cell table:number-columns-repeated="20"/> </table:table-row> <table:table-row table:style-name="ro2"> <table:table-cell table:number-columns-repeated="20"/> </table:table-row> <calcext:conditional-formats> - <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C201"> + <calcext:conditional-format calcext:target-range-address="Sheet2.C2:Sheet2.C202"> <calcext:condition calcext:apply-style-name="Default" calcext:value="=""" calcext:base-cell-address="Sheet2.C2"/> <calcext:condition calcext:apply-style-name="true" calcext:value="=1" calcext:base-cell-address="Sheet2.C2"/> <calcext:condition calcext:apply-style-name="false" calcext:value="=0" calcext:base-cell-address="Sheet2.C2"/> diff --git a/sc/source/core/data/queryiter.cxx b/sc/source/core/data/queryiter.cxx index 39b86fe173c0..43996b1693eb 100644 --- a/sc/source/core/data/queryiter.cxx +++ b/sc/source/core/data/queryiter.cxx @@ -64,9 +64,9 @@ ScQueryCellIteratorBase< accessType, queryType >::ScQueryCellIteratorBase(ScDocu : AccessBase( rDocument, rContext, rParam, bReverse ) , nStopOnMismatch( nStopOnMismatchDisabled ) , nTestEqualCondition( nTestEqualConditionDisabled ) + , nSortedBinarySearch( nBinarySearchDisabled ) , bAdvanceQuery( false ) , bIgnoreMismatchOnLeadingStrings( false ) - , bSortedBinarySearch( false ) , bXLookUp( false ) , nBestFitCol(SCCOL_MAX) , nBestFitRow(SCROW_MAX) @@ -206,8 +206,8 @@ void ScQueryCellIteratorBase< accessType, queryType >::PerformQuery() if ( aCell.isEmpty()) return; - // XLookUp: Forward/backward search for best fit value, except if we have an exact match - if (bXLookUp && !bSortedBinarySearch && (rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp == SC_GREATER_EQUAL) && + // XLookUp: Forward/asc/backward/desc search for best fit value, except if we have an exact match + if (bXLookUp && (rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp == SC_GREATER_EQUAL) && (nBestFitCol != nCol || nBestFitRow != nRow)) { bool bNumSearch = rItem.meType == ScQueryEntry::ByValue && aCell.hasNumeric(); @@ -314,14 +314,18 @@ void ScQueryCellIteratorBase< accessType, queryType >::InitPos() // non-matching position using SC_LESS and the start position // is the one after it. lastRow = nRow; - ScQueryOp saveOp = op; - op = SC_LESS; - if( BinarySearch( nCol, true )) - beforeRow = nRow; - // If BinarySearch() returns false, there was no match, which means - // there's no value smaller. In that case BinarySearch() has set - // the position to the first row in the range. - op = saveOp; // back to SC_EQUAL + // BinarySearch() looks for the first match for XLOOKUP + if (!bXLookUp) + { + ScQueryOp saveOp = op; + op = SC_LESS; + if( BinarySearch( nCol, true )) + beforeRow = nRow; + // If BinarySearch() returns false, there was no match, which means + // there's no value smaller. In that case BinarySearch() has set + // the position to the first row in the range. + op = saveOp; // back to SC_EQUAL + } } else if( maParam.GetEntry(0).GetQueryItem().mbMatchEmpty && rDoc.IsEmptyData(nCol, maParam.nRow1, nCol, maParam.nRow2, nTab)) @@ -337,8 +341,7 @@ void ScQueryCellIteratorBase< accessType, queryType >::InitPos() if( BinarySearch( nCol )) lastRow = nRow; } - bool bFirstMatch = (bXLookUp && op != SC_EQUAL); - AccessBase::InitPosFinish(beforeRow, lastRow, bFirstMatch); + AccessBase::InitPosFinish(beforeRow, lastRow, bXLookUp); } } @@ -542,8 +545,13 @@ bool ScQueryCellIteratorBase< accessType, queryType >::BinarySearch( SCCOL col, { if (fLastInRangeValue <= nCellVal) { - fLastInRangeValue = nCellVal; - nLastInRange = i; + if (bXLookUp && nSortedBinarySearch != nSearchbDesc && fLastInRangeValue == nCellVal && aIndexer.getLowIndex() != i) + bDone = true; + else + { + fLastInRangeValue = nCellVal; + nLastInRange = i; + } } else if (fLastInRangeValue >= nCellVal) { @@ -585,8 +593,13 @@ bool ScQueryCellIteratorBase< accessType, queryType >::BinarySearch( SCCOL col, aCellStr); if (nTmp <= 0) { - aLastInRangeString = aCellStr; - nLastInRange = i; + if (bXLookUp && nSortedBinarySearch != nSearchbDesc && nTmp == 0 && aIndexer.getLowIndex() != i) + bDone = true; + else + { + aLastInRangeString = aCellStr; + nLastInRange = i; + } } else if (nTmp > 0) { @@ -654,8 +667,10 @@ bool ScQueryCellIteratorBase< accessType, queryType >::BinarySearch( SCCOL col, { found = i; nLastInRange = i; - // But keep searching to find the last matching one. - nLo = nMid + 1; + if (bXLookUp && (nSortedBinarySearch == nSearchbAscd && (rEntry.eOp == SC_LESS_EQUAL || rEntry.eOp == SC_EQUAL))) + bDone = true; + else // But keep searching to find the last matching one. + nLo = nMid + 1; } else if (bAscending) { @@ -732,7 +747,7 @@ bool ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange( SCCOL& nFo nFoundCol = rDoc.MaxCol()+1; nFoundRow = rDoc.MaxRow()+1; - if (bXLookUp && !bSortedBinarySearch) + if (bXLookUp && nSortedBinarySearch == nBinarySearchDisabled) SetStopOnMismatch( false ); // assume not sorted keys for XLookup else SetStopOnMismatch( true ); // assume sorted keys @@ -747,7 +762,7 @@ bool ScQueryCellIterator< accessType >::FindEqualOrSortedLastInRange( SCCOL& nFo (maParam.GetEntry(0).eOp == SC_LESS_EQUAL || maParam.GetEntry(0).eOp == SC_GREATER_EQUAL); // assume not sorted properly if we are using XLookup with forward or backward search - if (bBinary && bXLookUp && !bSortedBinarySearch) + if (bBinary && bXLookUp && nSortedBinarySearch == nBinarySearchDisabled) bBinary = false; bool bFound = false; diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx index 6b82844f95ce..22fc30c23976 100644 --- a/sc/source/core/tool/interpr1.cxx +++ b/sc/source/core/tool/interpr1.cxx @@ -10642,7 +10642,7 @@ bool ScInterpreter::SearchRangeForValue( VectorSearchArguments& vsa, ScQueryPara aCellIter.SetAdvanceQueryParamEntryField(true); aCellIter.SetXlookupMode(vsa.isXLookup); // TODO: no binary search for column (horizontal) search (use linear) - aCellIter.SetSortedBinarySearchMode(false); + aCellIter.SetSortedBinarySearchMode(vsa.eSearchMode); if (rEntry.eOp == SC_EQUAL) { if (aCellIter.GetFirst()) @@ -10807,13 +10807,14 @@ static bool lcl_LookupQuery( ScAddress & o_rResultPos, ScDocument& rDoc, ScInter SCROW nRow; bool bLessOrEqual = rEntry.eOp == SC_LESS || rEntry.eOp == SC_LESS_EQUAL; // we can use binary search if the SearchMode is searchbasc or searchbdesc - if (ScQueryCellIteratorSortedCache::CanBeUsed(rDoc, rParam, rParam.nTab, cell, refData, rContext) || - (static_cast<SearchMode>(nSearchMode) == searchbasc && !bLessOrEqual) || - (static_cast<SearchMode>(nSearchMode) == searchbdesc && bLessOrEqual)) + if ((static_cast<SearchMode>(nSearchMode) == searchbasc && !bLessOrEqual) || + (static_cast<SearchMode>(nSearchMode) == searchbdesc && bLessOrEqual) || + ScQueryCellIteratorSortedCache::CanBeUsed(rDoc, rParam, rParam.nTab, cell, refData, rContext)) { // search for the first LessOrEqual value if SearchMode is desc or // search for the first GreaterOrEqual value if SearchMode is asc ScQueryCellIteratorSortedCache aCellIter(rDoc, rContext, rParam.nTab, rParam, false, false); + aCellIter.SetSortedBinarySearchMode(nSearchMode); aCellIter.SetXlookupMode(bXlookupMode); if (aCellIter.GetFirst()) { @@ -10828,8 +10829,7 @@ static bool lcl_LookupQuery( ScAddress & o_rResultPos, ScDocument& rDoc, ScInter bool bReverse = (static_cast<SearchMode>(nSearchMode) == searchrev); ScQueryCellIteratorDirect aCellIter(rDoc, rContext, rParam.nTab, rParam, false, bReverse); - aCellIter.SetSortedBinarySearchMode(static_cast<SearchMode>(nSearchMode) == searchbasc || - static_cast<SearchMode>(nSearchMode) == searchbdesc); + aCellIter.SetSortedBinarySearchMode(nSearchMode); aCellIter.SetXlookupMode(bXlookupMode); if (aCellIter.FindEqualOrSortedLastInRange(nCol, nRow)) { @@ -10847,10 +10847,11 @@ static bool lcl_LookupQuery( ScAddress & o_rResultPos, ScDocument& rDoc, ScInter bool bBinary = rParam.bByRow && (bLiteral || rEntry.GetQueryItem().meType == ScQueryEntry::ByValue); - if( bBinary && (ScQueryCellIteratorSortedCache::CanBeUsed( rDoc, rParam, rParam.nTab, cell, refData, rContext ) || - static_cast<SearchMode>(nSearchMode) == searchbasc || static_cast<SearchMode>(nSearchMode) == searchbdesc )) + if( bBinary && (static_cast<SearchMode>(nSearchMode) == searchbasc || static_cast<SearchMode>(nSearchMode) == searchbdesc || + ScQueryCellIteratorSortedCache::CanBeUsed(rDoc, rParam, rParam.nTab, cell, refData, rContext))) { ScQueryCellIteratorSortedCache aCellIter( rDoc, rContext, rParam.nTab, rParam, false, false ); + aCellIter.SetSortedBinarySearchMode(nSearchMode); aCellIter.SetXlookupMode(bXlookupMode); if (aCellIter.GetFirst()) { @@ -10861,8 +10862,9 @@ static bool lcl_LookupQuery( ScAddress & o_rResultPos, ScDocument& rDoc, ScInter } else { - bool bReverse = (static_cast<SearchMode>(nSearchMode) == searchrev); - ScQueryCellIteratorDirect aCellIter( rDoc, rContext, rParam.nTab, rParam, false, bReverse ); + ScQueryCellIteratorDirect aCellIter( rDoc, rContext, rParam.nTab, rParam, false, + static_cast<SearchMode>(nSearchMode) == searchrev); + aCellIter.SetSortedBinarySearchMode(nSearchMode); aCellIter.SetXlookupMode(bXlookupMode); if (aCellIter.GetFirst()) { |