tdf#136404 DOCX import: ignore NaN cells in table formula

Ignore empty cells or cells with text content in data range
of AVERAGE, COUNT and PRODUCT (the new interoperability
functions in Writer), like MSO does, instead of using
NaN data as zeroes here.

Add AVERAGE, as a new function instead of alias of MEAN
to return error message instead of zero for NaN-only
arguments, like Calc does (Note: also MSO gives empty
result instead of zero).

Change-Id: I5e18f3e2b16cb0621dad2bba141ae63fb5edd241
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/102012
Tested-by: Jenkins
Reviewed-by: László Németh <nemeth@numbertext.org>

2 files changed, 45 insertions, 7 deletions

diff --git a/sw/source/core/bastyp/calc.cxx b/sw/source/core/bastyp/calc.cxx
index 17179f472352..41bf202a9ea4 100644
--- a/sw/source/core/bastyp/calc.cxx
+++ b/sw/source/core/bastyp/calc.cxx
@@ -106,7 +106,7 @@ CalcOp const aOpTable[] = {
 /* AND */     {{sCalc_And},        CALC_AND},   // log. AND
 /* ASIN */    {{sCalc_Asin},       CALC_ASIN},  // Arc sine
 /* ATAN */    {{sCalc_Atan},       CALC_ATAN},  // Arc tangent
-/* AVERAGE */ {{sCalc_Average},    CALC_MEAN},  // Mean (since LibreOffice 7.1)
+/* AVERAGE */ {{sCalc_Average},    CALC_AVERAGE}, // Average (since LibreOffice 7.1)
 /* COS */     {{sCalc_Cos},        CALC_COS},   // Cosine
 /* COUNT */   {{sCalc_Count},      CALC_COUNT}, // Count (since LibreOffice 7.1)
 /* DATE */    {{sCalc_Date},       CALC_DATE},  // Date
@@ -221,6 +221,7 @@ SwCalc::SwCalc( SwDoc& rD )
     , m_pLocaleDataWrapper( m_aSysLocale.GetLocaleDataPtr() )
     , m_pCharClass( &GetAppCharClass() )
     , m_nListPor( 0 )
+    , m_bHasNumber( false )
     , m_eCurrOper( CALC_NAME )
     , m_eCurrListOper( CALC_NAME )
     , m_eError( SwCalcError::NONE )
@@ -460,6 +461,7 @@ SwCalcExp* SwCalc::VarLook( const OUString& rStr, bool bIns )
             {
                 // Save the current values...
                 sal_uInt16 nListPor = m_nListPor;
+                bool bHasNumber = m_bHasNumber;
                 SwSbxValue nLastLeft = m_nLastLeft;
                 SwSbxValue nNumberValue = m_nNumberValue;
                 sal_Int32 nCommandPos = m_nCommandPos;
@@ -471,6 +473,7 @@ SwCalcExp* SwCalc::VarLook( const OUString& rStr, bool bIns )
 
                 // ...and write them back.
                 m_nListPor = nListPor;
+                m_bHasNumber = bHasNumber;
                 m_nLastLeft = nLastLeft;
                 m_nNumberValue = nNumberValue;
                 m_nCommandPos = nCommandPos;
@@ -674,6 +677,7 @@ SwCalcOper SwCalc::GetToken()
                 {
                 case CALC_SUM:
                 case CALC_MEAN:
+                case CALC_AVERAGE:
                 case CALC_COUNT:
                     m_eCurrListOper = CALC_PLUS;
                     break;
@@ -1120,6 +1124,7 @@ SwSbxValue SwCalc::PrimFunc(bool &rChkPow)
         {
             SAL_INFO("sw.calc", "number: " << m_nNumberValue.GetDouble());
             SwSbxValue nErg;
+            m_bHasNumber = true;
             if( GetToken() == CALC_PHD )
             {
                 double aTmp = m_nNumberValue.GetDouble();
@@ -1196,14 +1201,19 @@ SwSbxValue SwCalc::PrimFunc(bool &rChkPow)
             SAL_INFO("sw.calc", ")");
             break;
         case CALC_MEAN:
+        case CALC_AVERAGE:
         {
             SAL_INFO("sw.calc", "mean");
             m_nListPor = 1;
+            m_bHasNumber = CALC_MEAN == m_eCurrOper;
             GetToken();
             SwSbxValue nErg = Expr();
             double aTmp = nErg.GetDouble();
             aTmp /= m_nListPor;
-            nErg.PutDouble( aTmp );
+            if ( !m_bHasNumber )
+                m_eError = SwCalcError::DivByZero;
+            else
+                nErg.PutDouble( aTmp );
             return nErg;
             break;
         }
@@ -1211,9 +1221,10 @@ SwSbxValue SwCalc::PrimFunc(bool &rChkPow)
         {
             SAL_INFO("sw.calc", "count");
             m_nListPor = 1;
+            m_bHasNumber = false;
             GetToken();
             SwSbxValue nErg = Expr();
-            nErg.PutDouble( m_nListPor );
+            nErg.PutDouble( m_bHasNumber ? m_nListPor : 0 );
             return nErg;
             break;
         }
diff --git a/sw/source/core/fields/cellfml.cxx b/sw/source/core/fields/cellfml.cxx
index 755d81f23fcc..47cbabe871a5 100644
--- a/sw/source/core/fields/cellfml.cxx
+++ b/sw/source/core/fields/cellfml.cxx
@@ -238,6 +238,8 @@ double SwTableBox::GetValue( SwTableCalcPara& rCalcPara ) const
 
             if( pDoc->IsNumberFormat( sText, nFormatIndex, aNum ))
                 nRet = aNum;
+            else
+                rCalcPara.m_rCalc.SetCalcError( SwCalcError::NaN ); // set for interoperability functions
         }
         // ?? otherwise it is an error
     } while( false );
@@ -354,6 +356,9 @@ void SwTableFormula::MakeFormula_( const SwTable& rTable, OUStringBuffer& rNewSt
         SwSelBoxes aBoxes;
         GetBoxes( *pSttBox, *pEndBox, aBoxes );
 
+        // don't use empty cells or cells with text content as zeroes in interoperability functions
+        sal_Int16 nUseOnlyNumber = -1;
+
         rNewStr.append("(");
         bool bDelim = false;
         for (size_t n = 0; n < aBoxes.size() &&
@@ -362,11 +367,26 @@ void SwTableFormula::MakeFormula_( const SwTable& rTable, OUStringBuffer& rNewSt
             const SwTableBox* pTableBox = aBoxes[n];
             if ( pTableBox->getRowSpan() >= 1 )
             {
+                double fVal = pTableBox->GetValue( *pCalcPara );
+
+                if ( pCalcPara->m_rCalc.IsCalcNotANumber() )
+                {
+                    if ( nUseOnlyNumber == -1 )
+                    {
+                        OUString sFormula = rNewStr.toString().toAsciiUpperCase();
+                        nUseOnlyNumber = sal_Int16(
+                                sFormula.lastIndexOf("AVERAGE") > -1 ||
+                                sFormula.lastIndexOf("COUNT") > -1 ||
+                                sFormula.lastIndexOf("PRODUCT") > -1 );
+                    }
+                    if ( nUseOnlyNumber > 0 )
+                        continue;
+                }
+
                 if( bDelim )
                     rNewStr.append(cListDelim);
                 bDelim = true;
-                rNewStr.append(pCalcPara->m_rCalc.GetStrResult(
-                            pTableBox->GetValue( *pCalcPara ) ));
+                rNewStr.append(pCalcPara->m_rCalc.GetStrResult( fVal ));
             }
         }
         rNewStr.append(")");
@@ -378,8 +398,15 @@ void SwTableFormula::MakeFormula_( const SwTable& rTable, OUStringBuffer& rNewSt
         if ( pSttBox->getRowSpan() >= 1 )
         {
             rNewStr.append("(");
-            rNewStr.append(pCalcPara->m_rCalc.GetStrResult(
-                            pSttBox->GetValue( *pCalcPara ) ));
+            double fVal = pSttBox->GetValue( *pCalcPara );
+            // don't use empty cell or a cell with text content as zero in interoperability functions
+            // (except PRODUCT, where the result is correct anyway)
+            if ( !pCalcPara->m_rCalc.IsCalcNotANumber() ||
+                 ( rNewStr.toString().toAsciiUpperCase().lastIndexOf("AVERAGE") == -1 &&
+                   rNewStr.toString().toAsciiUpperCase().lastIndexOf("COUNT") == -1 ) )
+            {
+                rNewStr.append(pCalcPara->m_rCalc.GetStrResult( fVal ));
+            }
             rNewStr.append(")");
         }
     }