tdf#123355 DOCX import: fix cell range ABOVE, BELOW,

LEFT and RIGHT by converting them to Writer cell ranges.

These ranges start at the first cell above/below/right/left
(including empty cells or cells with text content!) and end
at the end of the first value cell range. If there is no
value cell range, they end at the table border.

Note: In MSO, table functions COUNT(), AVERAGE() etc. use
non-value cells, as zero with ABOVE, BELOW, LEFT and RIGHT,
but skip them using A1:A10 like range names.

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

2 files changed, 47 insertions, 0 deletions

diff --git a/sw/qa/extras/ooxmlexport/data/tdf123355.docx b/sw/qa/extras/ooxmlexport/data/tdf123355.docx
new file mode 100644
index 000000000000..da36db29f7fe
--- /dev/null
+++ b/sw/qa/extras/ooxmlexport/data/tdf123355.docx
diff --git a/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx b/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx
index b3c3d79962a0..104e867a33ba 100644
--- a/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx
+++ b/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx
@@ -255,6 +255,53 @@ DECLARE_OOXMLEXPORT_TEST(testTdf123354, "tdf123354.fodt")
     CPPUNIT_ASSERT_EQUAL(OUString("0"), xEnumerationAccess3->getPresentation(false).trim());
 }
 
+DECLARE_OOXMLEXPORT_TEST(testTdf123355, "tdf123355.docx")
+{
+    uno::Reference<text::XTextFieldsSupplier> xTextFieldsSupplier(mxComponent, uno::UNO_QUERY);
+    uno::Reference<container::XEnumerationAccess> xFieldsAccess(xTextFieldsSupplier->getTextFields());
+    uno::Reference<container::XEnumeration> xFields(xFieldsAccess->createEnumeration());
+
+    // Tests conversion of range IDs ABOVE, BELOW, LEFT and RIGHT
+    uno::Reference<text::XTextField> xEnumerationAccess1(xFields->nextElement(), uno::UNO_QUERY);
+    // Note: range ends at B4 here, which is a cell with text content
+    CPPUNIT_ASSERT_EQUAL(OUString("average( <B2:B3> )"), xEnumerationAccess1->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("5,5"), xEnumerationAccess1->getPresentation(false).trim());
+
+    // range ends at the end of the empty cells
+    uno::Reference<text::XTextField> xEnumerationAccess6(xFields->nextElement(), uno::UNO_QUERY);
+    CPPUNIT_ASSERT_EQUAL(OUString("SUM(<C6:A6>)"), xEnumerationAccess6->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("0"), xEnumerationAccess6->getPresentation(false).trim());
+
+    // range starts at the first cell above D5
+    uno::Reference<text::XTextField> xEnumerationAccess2(xFields->nextElement(), uno::UNO_QUERY);
+    CPPUNIT_ASSERT_EQUAL(OUString("AVERAGE(<D4:D1>)"), xEnumerationAccess2->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("5,33"), xEnumerationAccess2->getPresentation(false).trim());
+
+    uno::Reference<text::XTextField> xEnumerationAccess3(xFields->nextElement(), uno::UNO_QUERY);
+    CPPUNIT_ASSERT_EQUAL(OUString("AVERAGE(<C2:C1>)"), xEnumerationAccess3->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("3,5"), xEnumerationAccess3->getPresentation(false).trim());
+
+    uno::Reference<text::XTextField> xEnumerationAccess4(xFields->nextElement(), uno::UNO_QUERY);
+    CPPUNIT_ASSERT_EQUAL(OUString("AVERAGE(<D2:D2>)"), xEnumerationAccess4->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("5"), xEnumerationAccess4->getPresentation(false).trim());
+
+    uno::Reference<text::XTextField> xEnumerationAccess5(xFields->nextElement(), uno::UNO_QUERY);
+    CPPUNIT_ASSERT_EQUAL(OUString("AVERAGE(<A2:A2>)"), xEnumerationAccess5->getPresentation(true).trim());
+    CPPUNIT_ASSERT_EQUAL(OUString("4"), xEnumerationAccess5->getPresentation(false).trim());
+
+    xmlDocUniquePtr pXmlDoc = parseExport();
+    if (!pXmlDoc)
+        return;
+
+    // keep original formula IDs
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[1]/w:tc[2]/w:p/w:r[2]/w:instrText", " =average( below )");
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[2]/w:tc[2]/w:p/w:r[2]/w:instrText", " =AVERAGE(LEFT)");
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[2]/w:tc[3]/w:p/w:r[2]/w:instrText", " =AVERAGE(RIGHT)");
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[3]/w:tc[3]/w:p/w:r[2]/w:instrText", " =AVERAGE(ABOVE)");
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[5]/w:tc[4]/w:p/w:r[2]/w:instrText", " =AVERAGE(ABOVE)");
+    assertXPathContent(pXmlDoc, "/w:document/w:body/w:tbl/w:tr[6]/w:tc[4]/w:p/w:r[2]/w:instrText", " =SUM(LEFT)");
+}
+
 DECLARE_OOXMLEXPORT_TEST(testTdf98000_changePageStyle, "tdf98000_changePageStyle.odt")
 {
     uno::Reference<frame::XModel> xModel(mxComponent, uno::UNO_QUERY);