tdf#123401 DOCX: fix table formula AVERAGE

by adding AVERAGE as an alternative name of MEAN,
and by converting MEAN to AVERAGE during DOCX export.

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

5 files changed, 67 insertions, 3 deletions

diff --git a/sw/inc/calc.hxx b/sw/inc/calc.hxx
index b0f45af20c3f..a8eb9c18933a 100644
--- a/sw/inc/calc.hxx
+++ b/sw/inc/calc.hxx
@@ -54,7 +54,7 @@ enum SwCalcOper
     CALC_COS=274,           CALC_TAN=275,           CALC_ASIN=276,
     CALC_ACOS=278,          CALC_ATAN=279,          CALC_TDIF=280,
     CALC_ROUND=281,         CALC_DATE=282,          CALC_MONTH=283,
-    CALC_DAY=284,           CALC_PRODUCT=285
+    CALC_DAY=284,           CALC_PRODUCT=285,       CALC_AVERAGE=286
 };
 
 // Calculate Operations Strings
@@ -77,6 +77,7 @@ extern const char sCalc_L[];
 extern const char sCalc_G[];
 extern const char sCalc_Sum[];
 extern const char sCalc_Mean[];
+extern const char sCalc_Average[];
 extern const char sCalc_Product[];
 extern const char sCalc_Min[];
 extern const char sCalc_Max[];
diff --git a/sw/qa/extras/ooxmlexport/data/tdf123401.fodt b/sw/qa/extras/ooxmlexport/data/tdf123401.fodt
new file mode 100644
index 000000000000..d0cdfe2c9020
--- /dev/null
+++ b/sw/qa/extras/ooxmlexport/data/tdf123401.fodt
@@ -0,0 +1,36 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<office:document xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:officeooo="http://openoffice.org/2009/office" xmlns:loext="urn:org:documentfoundation:names:experimental:office:xmlns:loext:1.0" xmlns:ooow="http://openoffice.org/2004/writer" office:version="1.2" office:mimetype="application/vnd.oasis.opendocument.text">
+ <office:styles>
+  <style:style style:name="Standard" style:family="paragraph" style:class="text"/>
+  <style:default-style style:family="paragraph">
+   <style:text-properties fo:language="en" fo:country="US"/>
+  </style:default-style>
+ </office:styles>
+ <office:body>
+  <office:text>
+   <table:table>
+    <table:table-column/>
+    <table:table-row>
+     <table:table-cell office:value-type="string">
+      <text:p>2</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row>
+     <table:table-cell office:value-type="string">
+      <text:p>4</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row>
+     <table:table-cell table:formula="ooow:mean(&lt;A1:A2&gt;)" office:value-type="float" office:value="3">
+      <text:p>3</text:p>
+     </table:table-cell>
+    </table:table-row>
+    <table:table-row>
+     <table:table-cell table:formula="ooow:AVERAGE(&lt;A1:A3&gt;)" office:value-type="float" office:value="3">
+      <text:p>3</text:p>
+     </table:table-cell>
+    </table:table-row>
+   </table:table>
+  </office:text>
+ </office:body>
+</office:document>
diff --git a/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx b/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx
index 05ad6a9ce69d..62d1339b3127 100644
--- a/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx
+++ b/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx
@@ -145,6 +145,28 @@ DECLARE_OOXMLEXPORT_TEST(testTdf123388, "tdf123388.docx")
     CPPUNIT_ASSERT_EQUAL(OUString("640"), xEnumerationAccess1->getPresentation(false).trim());
 }
 
+DECLARE_OOXMLEXPORT_TEST(testTdf123401, "tdf123401.fodt")
+{
+    uno::Reference<text::XTextFieldsSupplier> xTextFieldsSupplier(mxComponent, uno::UNO_QUERY);
+    uno::Reference<container::XEnumerationAccess> xFieldsAccess(xTextFieldsSupplier->getTextFields());
+    uno::Reference<container::XEnumeration> xFields(xFieldsAccess->createEnumeration());
+
+    // Tests new cell formula AVERAGE
+    uno::Reference<text::XTextField> xEnumerationAccess1(xFields->nextElement(), uno::UNO_QUERY);
+    CPPUNIT_ASSERT_EQUAL(OUString("AVERAGE(<A1:A2>)"), xEnumerationAccess1->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("3"), xEnumerationAccess1->getPresentation(false).trim());
+
+    uno::Reference<text::XTextField> xEnumerationAccess2(xFields->nextElement(), uno::UNO_QUERY);
+    CPPUNIT_ASSERT_EQUAL(OUString("AVERAGE(<A1:A3>)"), xEnumerationAccess2->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("3"), xEnumerationAccess2->getPresentation(false).trim());
+
+    xmlDocUniquePtr pXmlDoc = parseExport();
+
+    // MEAN converted to AVERAGE
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[3]/w:tc/w:p/w:r[2]/w:instrText", " = AVERAGE(A1:A2)");
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[4]/w:tc/w:p/w:r[2]/w:instrText", " = AVERAGE(A1:A3)");
+}
+
 DECLARE_OOXMLEXPORT_TEST(testTdf98000_changePageStyle, "tdf98000_changePageStyle.odt")
 {
     uno::Reference<frame::XModel> xModel(mxComponent, uno::UNO_QUERY);
diff --git a/sw/source/core/bastyp/calc.cxx b/sw/source/core/bastyp/calc.cxx
index b604ff49d066..6e06fbd52e53 100644
--- a/sw/source/core/bastyp/calc.cxx
+++ b/sw/source/core/bastyp/calc.cxx
@@ -84,6 +84,7 @@ const char sCalc_Atan[] =   "atan";
 const char sCalc_Round[]=   "round";
 const char sCalc_Date[] =   "date";
 const char sCalc_Product[] = "product";
+const char sCalc_Average[] = "average";
 
 // ATTENTION: sorted list of all operators
 struct CalcOp
@@ -101,6 +102,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)
 /* COS */     {{sCalc_Cos},        CALC_COS},   // Cosine
 /* DATE */    {{sCalc_Date},       CALC_DATE},  // Date
 /* DIV */     {{sCalc_Div},        CALC_DIV},   // Division
@@ -118,7 +120,7 @@ CalcOp const aOpTable[] = {
 /* OR */      {{sCalc_Or},         CALC_OR},    // log. OR
 /* PHD */     {{sCalc_Phd},        CALC_PHD},   // Percentage
 /* POW */     {{sCalc_Pow},        CALC_POW},   // Exponentiation
-/* PRODUCT */ {{sCalc_Product},    CALC_PRODUCT}, // Product
+/* PRODUCT */ {{sCalc_Product},    CALC_PRODUCT}, // Product (since LibreOffice 7.1)
 /* ROUND */   {{sCalc_Round},      CALC_ROUND}, // Rounding
 /* SIN */     {{sCalc_Sin},        CALC_SIN},   // Sine
 /* SQRT */    {{sCalc_Sqrt},       CALC_SQRT},  // Square root
diff --git a/sw/source/filter/ww8/docxattributeoutput.cxx b/sw/source/filter/ww8/docxattributeoutput.cxx
index 81760cc044ab..83527d26c470 100644
--- a/sw/source/filter/ww8/docxattributeoutput.cxx
+++ b/sw/source/filter/ww8/docxattributeoutput.cxx
@@ -2178,7 +2178,10 @@ void DocxAttributeOutput::CmdField_Impl( const SwTextNode* pNode, sal_Int32 nPos
                     icu::UnicodeString sInput(sToken.getStr());
                     // remove < and > around cell references, e.g. <A1> to A1, <A1:B2> to A1:B2
                     icu::RegexMatcher aMatcher("<([A-Z]{1,3}[0-9]+(:[A-Z]{1,3}[0-9]+)?)>", sInput, 0, nErr);
-                    sToken = aMatcher.replaceAll(icu::UnicodeString("$1"), nErr).getTerminatedBuffer();
+                    sInput = aMatcher.replaceAll(icu::UnicodeString("$1"), nErr);
+                    // convert MEAN to AVERAGE
+                    icu::RegexMatcher aMatcher2("\\bMEAN\\b", sInput, UREGEX_CASE_INSENSITIVE, nErr);
+                    sToken = aMatcher2.replaceAll(icu::UnicodeString("AVERAGE"), nErr).getTerminatedBuffer();
                 }
             }