tdf#142607 XLSX export: keep formatted dates in standard filter

Do not convert the formatted string filter criteria values
to 'double' if they are numbers (dates). Also export the equal
relation criteria into XML_filters tag instead of
XML_customFilters.

Previously the formatted dates replaced with numbers in
standard filter criteria, first during the export, and after
the import, in the standard filter dialog window.

Note: fix and optimize also filtering by formatted dates by
allowing CanOptimizeQueryStringToNumber() to create double
values for them. This unifies the same dates with different
date formatting.

Follow-up to commit 1f755525189884e4b2824889a6b9dea8933402db
"tdf#142402 sc UI: store formatted values in standard filter".

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

5 files changed, 56 insertions, 49 deletions

diff --git a/sc/qa/unit/data/ods/tdf142607.ods b/sc/qa/unit/data/ods/tdf142607.ods
new file mode 100644
index 000000000000..91649056e728
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf142607.ods
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index ae2784a832e7..31c0aae3dd71 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -205,6 +205,7 @@ public:
     void testPreserveTextWhitespaceXLSX();
     void testPreserveTextWhitespace2XLSX();
     void testTdf113646();
+    void testDateStandardfilterXLSX();
 
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
@@ -308,6 +309,7 @@ public:
     CPPUNIT_TEST(testHyperlinkXLSX);
     CPPUNIT_TEST(testMoveCellAnchoredShapesODS);
     CPPUNIT_TEST(testTdf113646);
+    CPPUNIT_TEST(testDateStandardfilterXLSX);
     CPPUNIT_TEST_SUITE_END();
 
 private:
@@ -4184,6 +4186,24 @@ void ScExportTest::testTdf113646()
     xShell->DoClose();
 }
 
+void ScExportTest::testDateStandardfilterXLSX()
+{
+    // XLSX Roundtripping standard filter with date
+    ScDocShellRef xDocSh = loadDoc(u"tdf142607.", FORMAT_ODS);
+    CPPUNIT_ASSERT(xDocSh.is());
+
+    xmlDocUniquePtr pDoc = XPathHelper::parseExport2(*this, *xDocSh, m_xSFactory, "xl/worksheets/sheet1.xml", FORMAT_XLSX);
+    CPPUNIT_ASSERT(pDoc);
+
+    assertXPath(pDoc, "//x:autoFilter", "ref", "A1:B6");
+    assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "day", "03");
+    assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "month", "12");
+    assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "year", "2011");
+    assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "dateTimeGrouping", "day");
+
+    xDocSh->DoClose();
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index 47d1ea2b852d..f8e9aada25b7 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -3055,8 +3055,9 @@ public:
     {
         if (rItem.meType != ScQueryEntry::ByString && rItem.meType != ScQueryEntry::ByDate)
             return;
-
-        if (rItem.mbFormattedValue)
+        // return only if the type is ByString and the values are formatted, in other cases
+        // we have to optimize the filter in CanOptimizeQueryStringToNumber().
+        if (rItem.mbFormattedValue && rItem.meType == ScQueryEntry::ByString)
             return;
 
         sal_uInt32 nIndex = 0;
diff --git a/sc/source/filter/excel/excrecds.cxx b/sc/source/filter/excel/excrecds.cxx
index c1f45823efcd..8c66caebdea5 100644
--- a/sc/source/filter/excel/excrecds.cxx
+++ b/sc/source/filter/excel/excrecds.cxx
@@ -528,8 +528,7 @@ XclExpAutofilterinfo::XclExpAutofilterinfo( const ScAddress& rStartPos, SCCOL nS
 
 ExcFilterCondition::ExcFilterCondition() :
         nType( EXC_AFTYPE_NOTUSED ),
-        nOper( EXC_AFOPER_EQUAL ),
-        fVal( 0.0 )
+        nOper( EXC_AFOPER_EQUAL )
 {
 }
 
@@ -542,32 +541,23 @@ std::size_t ExcFilterCondition::GetTextBytes() const
     return pText ? (1 + pText->GetBufferSize()) : 0;
 }
 
-void ExcFilterCondition::SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, double fV, const OUString* pT )
+void ExcFilterCondition::SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, const OUString* pT )
 {
     nType = nTp;
     nOper = nOp;
-    fVal = fV;
     pText.reset( pT ? new XclExpString( *pT, XclStrFlags::EightBitLength ) : nullptr);
 }
 
 void ExcFilterCondition::Save( XclExpStream& rStrm )
 {
     rStrm << nType << nOper;
-    switch( nType )
+    if (nType == EXC_AFTYPE_STRING)
     {
-        case EXC_AFTYPE_DOUBLE:
-            rStrm << fVal;
-        break;
-        case EXC_AFTYPE_STRING:
-            OSL_ENSURE( pText, "ExcFilterCondition::Save() -- pText is NULL!" );
-            rStrm << sal_uInt32(0) << static_cast<sal_uInt8>(pText->Len()) << sal_uInt16(0) << sal_uInt8(0);
-        break;
-        case EXC_AFTYPE_BOOLERR:
-            rStrm << sal_uInt8(0) << static_cast<sal_uInt8>((fVal != 0) ? 1 : 0) << sal_uInt32(0) << sal_uInt16(0);
-        break;
-        default:
-            rStrm << sal_uInt32(0) << sal_uInt32(0);
+        OSL_ENSURE(pText, "ExcFilterCondition::Save() -- pText is NULL!");
+        rStrm << sal_uInt32(0) << static_cast<sal_uInt8>(pText->Len()) << sal_uInt16(0) << sal_uInt8(0);
     }
+    else
+        rStrm << sal_uInt32(0) << sal_uInt32(0);
 }
 
 static const char* lcl_GetOperator( sal_uInt8 nOper )
@@ -585,15 +575,12 @@ static const char* lcl_GetOperator( sal_uInt8 nOper )
     }
 }
 
-static OString lcl_GetValue( sal_uInt8 nType, double fVal, const XclExpString* pStr )
+static OString lcl_GetValue( sal_uInt8 nType, const XclExpString* pStr )
 {
-    switch( nType )
-    {
-        case EXC_AFTYPE_STRING:     return XclXmlUtils::ToOString( *pStr );
-        case EXC_AFTYPE_DOUBLE:     return OString::number( fVal );
-        case EXC_AFTYPE_BOOLERR:    return OString::number( fVal != 0 ? 1 : 0 );
-        default:                    return OString();
-    }
+    if (nType == EXC_AFTYPE_STRING)
+        return XclXmlUtils::ToOString(*pStr);
+    else
+        return OString();
 }
 
 void ExcFilterCondition::SaveXml( XclExpXmlStream& rStrm )
@@ -603,7 +590,7 @@ void ExcFilterCondition::SaveXml( XclExpXmlStream& rStrm )
 
     rStrm.GetCurrentStream()->singleElement( XML_customFilter,
             XML_operator,   lcl_GetOperator( nOper ),
-            XML_val,        lcl_GetValue(nType, fVal, pText.get()) );
+            XML_val,        lcl_GetValue(nType, pText.get()) );
 }
 
 void ExcFilterCondition::SaveText( XclExpStream& rStrm )
@@ -627,7 +614,7 @@ XclExpAutofilter::XclExpAutofilter( const XclExpRoot& rRoot, sal_uInt16 nC ) :
 }
 
 bool XclExpAutofilter::AddCondition( ScQueryConnect eConn, sal_uInt8 nType, sal_uInt8 nOp,
-                                     double fVal, const OUString* pText, bool bSimple )
+                                     const OUString* pText, bool bSimple )
 {
     if( !aCond[ 1 ].IsEmpty() )
         return false;
@@ -639,7 +626,7 @@ bool XclExpAutofilter::AddCondition( ScQueryConnect eConn, sal_uInt8 nType, sal_
     if( bSimple )
         nFlags |= (nInd == 0) ? EXC_AFFLAG_SIMPLE1 : EXC_AFFLAG_SIMPLE2;
 
-    aCond[ nInd ].SetCondition( nType, nOp, fVal, pText );
+    aCond[ nInd ].SetCondition( nType, nOp, pText );
 
     AddRecSize( aCond[ nInd ].GetTextBytes() );
 
@@ -702,16 +689,14 @@ bool XclExpAutofilter::AddEntry( const ScQueryEntry& rEntry )
         }
     }
 
-    bool bLen = sText.getLength() > 0;
-
     // empty/nonempty fields
     if (rEntry.IsQueryByEmpty())
     {
-        bConflict = !AddCondition(rEntry.eConnect, EXC_AFTYPE_EMPTY, EXC_AFOPER_NONE, 0.0, nullptr, true);
+        bConflict = !AddCondition(rEntry.eConnect, EXC_AFTYPE_EMPTY, EXC_AFOPER_NONE, nullptr, true);
         bHasBlankValue = true;
     }
     else if(rEntry.IsQueryByNonEmpty())
-        bConflict = !AddCondition( rEntry.eConnect, EXC_AFTYPE_NOTEMPTY, EXC_AFOPER_NONE, 0.0, nullptr, true );
+        bConflict = !AddCondition( rEntry.eConnect, EXC_AFTYPE_NOTEMPTY, EXC_AFOPER_NONE, nullptr, true );
     else if (rEntry.IsQueryByTextColor() || rEntry.IsQueryByBackgroundColor())
     {
         AddColorEntry(rEntry);
@@ -719,13 +704,6 @@ bool XclExpAutofilter::AddEntry( const ScQueryEntry& rEntry )
     // other conditions
     else
     {
-        double  fVal    = 0.0;
-        sal_uInt32  nIndex  = 0;
-        bool bIsNum  = !bLen || GetFormatter().IsNumberFormat( sText, nIndex, fVal );
-        OUString* pText = nullptr;
-        if (!bIsNum)
-            pText = &sText;
-
         // top10 flags
         sal_uInt16 nNewFlags = 0x0000;
         switch( rEntry.eOp )
@@ -751,14 +729,24 @@ bool XclExpAutofilter::AddEntry( const ScQueryEntry& rEntry )
         {
             if( bNewTop10 )
             {
-                if( fVal < 0 )      fVal = 0;
-                if( fVal >= 501 )   fVal = 500;
+                sal_uInt32  nIndex = 0;
+                double  fVal = 0.0;
+                if (GetFormatter().IsNumberFormat(sText, nIndex, fVal))
+                {
+                    if (fVal < 0)      fVal = 0;
+                    if (fVal >= 501)   fVal = 500;
+                }
                 nFlags |= (nNewFlags | static_cast<sal_uInt16>(fVal) << 7);
             }
             // normal condition
             else
             {
-                sal_uInt8 nType = bIsNum ? EXC_AFTYPE_DOUBLE : EXC_AFTYPE_STRING;
+                if (GetOutput() != EXC_OUTPUT_BINARY && rEntry.eOp == SC_EQUAL)
+                {
+                    AddMultiValueEntry(rEntry);
+                    return false;
+                }
+
                 sal_uInt8 nOper = EXC_AFOPER_NONE;
 
                 switch( rEntry.eOp )
@@ -779,7 +767,7 @@ bool XclExpAutofilter::AddEntry( const ScQueryEntry& rEntry )
                                             nOper = EXC_AFOPER_NOTEQUAL;        break;
                     default:;
                 }
-                bConflict = !AddCondition( rEntry.eConnect, nType, nOper, fVal, pText );
+                bConflict = !AddCondition( rEntry.eConnect, EXC_AFTYPE_STRING, nOper, &sText);
             }
         }
     }
diff --git a/sc/source/filter/inc/excrecds.hxx b/sc/source/filter/inc/excrecds.hxx
index e997be8958e6..2e4885a8856d 100644
--- a/sc/source/filter/inc/excrecds.hxx
+++ b/sc/source/filter/inc/excrecds.hxx
@@ -338,7 +338,6 @@ class ExcFilterCondition
 private:
     sal_uInt8               nType;
     sal_uInt8               nOper;
-    double                  fVal;
     std::unique_ptr<XclExpString>
                             pText;
 
@@ -350,7 +349,7 @@ public:
     bool             IsEmpty() const     { return (nType == EXC_AFTYPE_NOTUSED); }
     std::size_t             GetTextBytes() const;
 
-    void                    SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, double fV, const OUString* pT );
+    void                    SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, const OUString* pT );
 
     void                    Save( XclExpStream& rStrm );
     void                    SaveXml( XclExpXmlStream& rStrm );
@@ -376,8 +375,7 @@ private:
     std::vector<std::pair<::Color, bool>> maColorValues; // first->Color, second->bIsBackgroundColor (vs. TextColor)
 
     bool                    AddCondition( ScQueryConnect eConn, sal_uInt8 nType,
-                                sal_uInt8 nOp, double fVal, const OUString* pText,
-                                bool bSimple = false );
+                                sal_uInt8 nOp, const OUString* pText, bool bSimple = false );
 
     virtual void            WriteBody( XclExpStream& rStrm ) override;