Resolves: tdf#96426 significant whitespace as intersection in Excel syntax

Also when reading/writing OOXML, so change SC_OPCODE_INTERSECT of
RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML accordingly to " ", where
previously "!" was expected and written, which was plain wrong.

Change-Id: Ic0cfd7afc657f07bfd8e37de61b3621cc68685ff

3 files changed, 139 insertions, 4 deletions

diff --git a/formula/source/core/api/FormulaCompiler.cxx b/formula/source/core/api/FormulaCompiler.cxx
index fdf21e041942..303e00e7a19c 100644
--- a/formula/source/core/api/FormulaCompiler.cxx
+++ b/formula/source/core/api/FormulaCompiler.cxx
@@ -1061,6 +1061,7 @@ bool FormulaCompiler::GetToken()
         bStop = true;
     else
     {
+        FormulaTokenRef pSpacesToken;
         short nWasColRowName;
         if ( pArr->nIndex > 0 && pArr->pCode[ pArr->nIndex-1 ]->GetOpCode() == ocColRowName )
              nWasColRowName = 1;
@@ -1069,6 +1070,9 @@ bool FormulaCompiler::GetToken()
         mpToken = pArr->Next();
         while( mpToken && mpToken->GetOpCode() == ocSpaces )
         {
+            // For significant whitespace remember last ocSpaces token. Usually
+            // there's only one even for multiple spaces.
+            pSpacesToken = mpToken;
             if ( nWasColRowName )
                 nWasColRowName++;
             if ( bAutoCorrect && !pStack )
@@ -1094,6 +1098,14 @@ bool FormulaCompiler::GetToken()
                 mpToken = new FormulaByteToken( ocIntersect );
                 pArr->nIndex--;     // we advanced to the second ocColRowName, step back
             }
+            else if (pSpacesToken && FormulaGrammar::isExcelSyntax( meGrammar))
+            {
+                // Let IntersectionLine() <- Factor() decide how to treat this,
+                // once the actual arguments are determined in RPN.
+                mpToken = pSpacesToken;
+                pArr->nIndex--;     // step back from next non-spaces token
+                return true;
+            }
         }
     }
     if( bStop )
@@ -1562,15 +1574,98 @@ void FormulaCompiler::RangeLine()
     }
 }
 
+namespace {
+
+bool isRangeResultFunction( OpCode eOp )
+{
+    switch (eOp)
+    {
+        case ocIndirect:
+        case ocOffset:
+            return true;
+        default:
+            return false;
+    }
+}
+
+bool isRangeResultOpCode( OpCode eOp )
+{
+    switch (eOp)
+    {
+        case ocRange:
+        case ocUnion:
+        case ocIntersect:
+        case ocIndirect:
+        case ocOffset:
+            return true;
+        default:
+            return false;
+    }
+}
+
+bool isPotentialRangeType( FormulaToken* pToken, bool bRPN )
+{
+    switch (pToken->GetType())
+    {
+        case svByte:                // could be range result, but only a few
+            if (bRPN)
+                return isRangeResultOpCode( pToken->GetOpCode());
+            else
+                return isRangeResultFunction( pToken->GetOpCode());
+        case svSingleRef:
+        case svDoubleRef:
+        case svIndex:               // could be range
+        //case svRefList:           // um..what?
+        case svExternalSingleRef:
+        case svExternalDoubleRef:
+        case svExternalName:        // could be range
+            return true;
+        default:
+            return false;
+    }
+}
+
+bool isIntersectable( FormulaToken** pCode1, FormulaToken** pCode2 )
+{
+    FormulaToken* pToken1 = *pCode1;
+    FormulaToken* pToken2 = *pCode2;
+    if (pToken1 && pToken2)
+        return isPotentialRangeType( pToken1, true) && isPotentialRangeType( pToken2, true);
+    return false;
+}
+
+}
+
 void FormulaCompiler::IntersectionLine()
 {
     RangeLine();
-    while (mpToken->GetOpCode() == ocIntersect)
+    while (mpToken->GetOpCode() == ocIntersect || mpToken->GetOpCode() == ocSpaces)
     {
+        sal_uInt16 nCodeIndex = pArr->nIndex - 1;
+        FormulaToken** pCode1 = pCode - 1;
         FormulaTokenRef p = mpToken;
         NextToken();
         RangeLine();
-        PutCode(p);
+        FormulaToken** pCode2 = pCode - 1;
+        if (p->GetOpCode() == ocSpaces)
+        {
+            // Convert to intersection if both left and right are references or
+            // functions (potentially returning references, if not then a space
+            // or no space would be a syntax error anyway), not other operators
+            // or operands. Else discard.
+            if (isIntersectable( pCode1, pCode2))
+            {
+                FormulaTokenRef pIntersect( new FormulaByteToken( ocIntersect));
+                // Replace ocSpaces with ocIntersect so that when switching
+                // formula syntax the correct operator string is created.
+                pArr->ReplaceToken( nCodeIndex, pIntersect.get(), FormulaTokenArray::ReplaceMode::CODE_ONLY);
+                PutCode( pIntersect);
+            }
+        }
+        else
+        {
+            PutCode(p);
+        }
     }
 }
 
@@ -1920,6 +2015,14 @@ const FormulaToken* FormulaCompiler::CreateStringFromToken( OUStringBuffer& rBuf
     }
     else if( eOp >= ocInternalBegin && eOp <= ocInternalEnd )
         rBuffer.appendAscii( pInternal[ eOp - ocInternalBegin ] );
+    else if (eOp == ocIntersect)
+    {
+        // Nasty, ugly, horrific, terrifying..
+        if (FormulaGrammar::isExcelSyntax( meGrammar))
+            rBuffer.append(' ');
+        else
+            rBuffer.append( mxSymbols->getSymbol( eOp));
+    }
     else if( (sal_uInt16) eOp < mxSymbols->getSymbolCount())        // Keyword:
         rBuffer.append( mxSymbols->getSymbol( eOp));
     else
@@ -2208,7 +2311,24 @@ OpCode FormulaCompiler::NextToken()
                 }
             }
         }
-        eLastOp = eOp;
+        // Nasty, ugly, horrific, terrifying.. significant whitespace..
+        if (eOp == ocSpaces && FormulaGrammar::isExcelSyntax( meGrammar))
+        {
+            // Fake an intersection op as last op for the next round, but at
+            // least roughly check if it could make sense at all.
+            if (eLastOp == ocPush || eLastOp == ocClose)
+            {
+                FormulaToken* pNext = pArr->PeekNextNoSpaces();
+                if (pNext && isPotentialRangeType( pNext, false))
+                    eLastOp = ocIntersect;
+                else
+                    eLastOp = eOp;
+            }
+            else
+                eLastOp = eOp;
+        }
+        else
+            eLastOp = eOp;
     }
     return eOp;
 }
diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index 0fb5a635d7d1..bf496242aa30 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -486,7 +486,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML
     String SC_OPCODE_AND { Text = "AND" ; };
     String SC_OPCODE_OR { Text = "OR" ; };
     String SC_OPCODE_XOR { Text = "_xlfn.XOR" ; };
-    String SC_OPCODE_INTERSECT { Text = "!" ; };
+    String SC_OPCODE_INTERSECT { Text = " " ; };
     String SC_OPCODE_UNION { Text = "~" ; };
     String SC_OPCODE_RANGE { Text = ":" ; };
     String SC_OPCODE_NOT { Text = "NOT" ; };
diff --git a/include/formula/grammar.hxx b/include/formula/grammar.hxx
index 91ca7ae1171a..6500d68df898 100644
--- a/include/formula/grammar.hxx
+++ b/include/formula/grammar.hxx
@@ -203,6 +203,21 @@ public:
             css::sheet::FormulaLanguage::OOXML;
     }
 
+    /// If grammar has an Excel syntax, determined by address convention.
+    static inline bool isExcelSyntax( const Grammar eGrammar )
+    {
+        AddressConvention eConv = extractRefConvention( eGrammar );
+        switch (eConv)
+        {
+            case FormulaGrammar::AddressConvention::CONV_XL_A1:
+            case FormulaGrammar::AddressConvention::CONV_XL_R1C1:
+            case FormulaGrammar::AddressConvention::CONV_XL_OOX:
+                return true;
+            default:
+                return false;
+        }
+    }
+
 };
 
 } // formula