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>
diff --git a/sw/qa/extras/ooxmlexport/data/tdf123355.docx b/sw/qa/extras/ooxmlexport/data/tdf123355.docx
new file mode 100644
index 0000000..da36db2
--- /dev/null
+++ b/sw/qa/extras/ooxmlexport/data/tdf123355.docx
Binary files differ
diff --git a/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx b/sw/qa/extras/ooxmlexport/ooxmlexport15.cxx
index b3c3d79..104e867 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);
diff --git a/writerfilter/source/dmapper/DomainMapperTableHandler.cxx b/writerfilter/source/dmapper/DomainMapperTableHandler.cxx
index 8d8724f..896950e 100644
--- a/writerfilter/source/dmapper/DomainMapperTableHandler.cxx
+++ b/writerfilter/source/dmapper/DomainMapperTableHandler.cxx
@@ -27,6 +27,7 @@
#include <com/sun/star/table/XCellRange.hpp>
#include <com/sun/star/text/HoriOrientation.hpp>
#include <com/sun/star/text/SizeType.hpp>
#include <com/sun/star/text/XTextField.hpp>
#include <com/sun/star/text/XTextRangeCompare.hpp>
#include <com/sun/star/beans/XPropertySet.hpp>
#include <com/sun/star/beans/XPropertyState.hpp>
@@ -41,6 +42,8 @@
#include <tools/diagnose_ex.h>
#include <comphelper/sequence.hxx>
#include <comphelper/propertyvalue.hxx>
#include <com/sun/star/lang/IndexOutOfBoundsException.hpp>
#include <boost/lexical_cast.hpp>
#ifdef DBG_UTIL
#include "PropertyMapHelper.hxx"
@@ -1213,6 +1216,147 @@ void DomainMapperTableHandler::ApplyParagraphPropertiesFromTableStyle(TableParag
}
}
// convert formula range identifier ABOVE, BELOW, LEFT and RIGHT
static void lcl_convertFormulaRanges(const uno::Reference<text::XTextTable> & xTable)
{
uno::Reference<table::XCellRange> xCellRange(xTable, uno::UNO_QUERY_THROW);
uno::Reference<container::XIndexAccess> xTableRows(xTable->getRows(), uno::UNO_QUERY_THROW);
sal_Int32 nRows = xTableRows->getCount();
for (sal_Int32 nRow = 0; nRow < nRows; ++nRow)
{
sal_Int16 nCol = 0;
while (++nCol)
{
try
{
uno::Reference<beans::XPropertySet> xCellProperties(xCellRange->getCellByPosition(nCol, nRow), uno::UNO_QUERY_THROW);
uno::Sequence<beans::PropertyValue> aCellGrabBag;
xCellProperties->getPropertyValue("CellInteropGrabBag") >>= aCellGrabBag;
OUString sFormula;
bool bReplace = false;
for (const auto& rProp : std::as_const(aCellGrabBag))
{
if ( rProp.Name == "CellFormulaConverted" )
{
rProp.Value >>= sFormula;
struct RangeDirection
{
OUString m_sName;
sal_Int16 m_nCol;
sal_Int16 m_nRow;
};
static const RangeDirection pDirections[] =
{
{ OUString(" LEFT "), -1, 0},
{ OUString(" RIGHT "), 1, 0},
{ OUString(" ABOVE "), 0, -1},
{ OUString(" BELOW "), 0, 1 }
};
for (const RangeDirection& rRange : pDirections)
{
if ( sFormula.indexOf(rRange.m_sName) > -1 )
{
// range starts at the first cell above/below/left/right, but ends at the
// table border or at the first non-value cell after a value cell
bool bFoundFirst = false;
OUString sNextCell;
OUString sLastCell;
OUString sLastValueCell;
// walk through the cells of the range
try
{
sal_Int32 nCell = 0;
while (++nCell)
{
uno::Reference<beans::XPropertySet> xCell(
xCellRange->getCellByPosition(nCol + nCell * rRange.m_nCol, nRow + nCell * rRange.m_nRow),
uno::UNO_QUERY_THROW);
// empty cell or cell with text content is end of the range
uno::Reference<text::XText> xText(xCell, uno::UNO_QUERY_THROW);
sLastCell = xCell->getPropertyValue("CellName").get<OUString>();
if (sNextCell.isEmpty())
sNextCell = sLastCell;
try
{
// accept numbers with comma and percent
OUString sCellText = xText->getString().replace(',', '.');
if (sCellText.endsWith("%"))
sCellText = sCellText.copy(0, sCellText.getLength()-1);
boost::lexical_cast<double>(sCellText);
}
catch( boost::bad_lexical_cast const& )
{
if ( !bFoundFirst )
{
// still search value cells
continue;
}
else
{
// end of range
break;
}
}
sLastValueCell = sLastCell;
bFoundFirst = true;
}
}
catch ( const lang::IndexOutOfBoundsException & )
{
}
if ( !sNextCell.isEmpty() )
{
OUString sRange = "<" + sNextCell + ":" +
( sLastValueCell.isEmpty() ? sLastCell : sLastValueCell ) + ">";
sFormula = sFormula.replaceAll(rRange.m_sName, sRange);
bReplace = true;
}
}
}
// update formula field
if (bReplace)
{
uno::Reference<text::XText> xCell(xCellRange->getCellByPosition(nCol, nRow), uno::UNO_QUERY);
uno::Reference<container::XEnumerationAccess> xParaEnumAccess(xCell, uno::UNO_QUERY);
uno::Reference<container::XEnumeration> xParaEnum = xParaEnumAccess->createEnumeration();
uno::Reference<container::XEnumerationAccess> xRunEnumAccess(xParaEnum->nextElement(), uno::UNO_QUERY);
uno::Reference<container::XEnumeration> xRunEnum = xRunEnumAccess->createEnumeration();
while ( xRunEnum->hasMoreElements() )
{
uno::Reference<text::XTextRange> xRun(xRunEnum->nextElement(), uno::UNO_QUERY);
uno::Reference< beans::XPropertySet > xRunProperties( xRun, uno::UNO_QUERY_THROW );
if ( xRunProperties->getPropertyValue("TextPortionType") == uno::makeAny(OUString("TextField")) )
{
uno::Reference<text::XTextField> const xField(xRunProperties->getPropertyValue("TextField").get<uno::Reference<text::XTextField>>());
uno::Reference< beans::XPropertySet > xFieldProperties( xField, uno::UNO_QUERY_THROW );
// cell can contain multiple text fields, but only one is handled now (~formula cell)
if ( rProp.Value != xFieldProperties->getPropertyValue("Content") )
continue;
xFieldProperties->setPropertyValue("Content", uno::makeAny(sFormula));
// update grab bag
auto aGrabBag = comphelper::sequenceToContainer< std::vector<beans::PropertyValue> >(aCellGrabBag);
beans::PropertyValue aValue;
aValue.Name = "CellFormulaConverted";
aValue.Value <<= sFormula;
aGrabBag.push_back(aValue);
xCellProperties->setPropertyValue("CellInteropGrabBag", uno::makeAny(comphelper::containerToSequence(aGrabBag)));
}
}
}
}
}
}
catch ( const lang::IndexOutOfBoundsException & )
{
// jump to next table row
break;
}
}
}
}
void DomainMapperTableHandler::endTable(unsigned int nestedTableLevel, bool bTableStartsAtCellStart)
{
#ifdef DBG_UTIL
@@ -1367,6 +1511,9 @@ void DomainMapperTableHandler::endTable(unsigned int nestedTableLevel, bool bTab
}
}
}
// convert special range IDs ABOVE, BELOW, LEFT and RIGHT
lcl_convertFormulaRanges(xTable);
}
}
}
diff --git a/writerfilter/source/dmapper/DomainMapper_Impl.cxx b/writerfilter/source/dmapper/DomainMapper_Impl.cxx
index e7ecfff..6387d22 100644
--- a/writerfilter/source/dmapper/DomainMapper_Impl.cxx
+++ b/writerfilter/source/dmapper/DomainMapper_Impl.cxx
@@ -4276,7 +4276,7 @@ OUString DomainMapper_Impl::convertFieldFormula(const OUString& input) {
usInput = rmatch2.replaceAll(icu::UnicodeString("<$1>"), status);
/* Cell references must be upper case */
icu::RegexMatcher rmatch3("<[a-z]{1,3}[0-9]+>", usInput, rMatcherFlags, status);
icu::RegexMatcher rmatch3("(<[a-z]{1,3}[0-9]+>|\\b(above|below|left|right)\\b)", usInput, rMatcherFlags, status);
icu::UnicodeString replacedCellRefs;
while (rmatch3.find(status) && status.isSuccess()) {
rmatch3.appendReplacement(replacedCellRefs, rmatch3.group(status).toUpper(), status);
@@ -4291,6 +4291,10 @@ OUString DomainMapper_Impl::convertFieldFormula(const OUString& input) {
icu::RegexMatcher rmatch5("\\bDEFINED\\s*\\(<([A-Z]+[0-9]+)>\\)", usInput, rMatcherFlags, status);
usInput = rmatch5.replaceAll(icu::UnicodeString("DEFINED($1)"), status);
/* Prepare replace of ABOVE/BELOW/LEFT/RIGHT by adding spaces around them */
icu::RegexMatcher rmatch6("\\b(ABOVE|BELOW|LEFT|RIGHT)\\b", usInput, rMatcherFlags, status);
usInput = rmatch6.replaceAll(icu::UnicodeString(" $1 "), status);
return OUString(usInput.getTerminatedBuffer());
}
