tdf#116818 sc,offapi,XLSX import: fix autofiltered date columns

by importing dateGroupItem.

Add property IsDateValue to com::sun::star::sheet::FilterFieldValue.

Note: ODS import/export and XLSX export haven't been supported, yet.

To check/show the fix manually, run the test with

$ (cd sc && make -srj8 UITest_autofilter UITEST_TEST_NAME="autofilter.AutofilterTest.test_tdf116818" SAL_USE_VCLPLUGIN=gen)

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

7 files changed, 98 insertions, 12 deletions

diff --git a/offapi/com/sun/star/sheet/FilterFieldValue.idl b/offapi/com/sun/star/sheet/FilterFieldValue.idl
index 5d1b40837476..2e3ba927dc97 100644
--- a/offapi/com/sun/star/sheet/FilterFieldValue.idl
+++ b/offapi/com/sun/star/sheet/FilterFieldValue.idl
@@ -29,6 +29,13 @@ struct FilterFieldValue
     /** specifies a string value for the condition.
      */
     string StringValue;
+
+    /** specifies whether the TableFilterFieldValue::StringValue
+        is a string value or a date value.
+
+        @since LibreOffice 7.2
+     */
+    boolean IsDateValue;
 };
 
 }; }; }; };
diff --git a/sc/qa/uitest/autofilter/autofilter.py b/sc/qa/uitest/autofilter/autofilter.py
index bef8d43c6ef6..bc6360b2940c 100644
--- a/sc/qa/uitest/autofilter/autofilter.py
+++ b/sc/qa/uitest/autofilter/autofilter.py
@@ -201,4 +201,35 @@ class AutofilterTest(UITestCase):
         self.assertFalse(is_row_hidden(document, 2))
 
         self.ui_test.close_doc()
+
+    def test_tdf116818(self):
+        doc = self.ui_test.load_file(get_url_for_data_file("tdf116818.xlsx"))
+
+        xGridWin = self.xUITest.getTopFocusWindow().getChild("grid_window")
+
+        xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "0", "ROW": "0"}))
+        xFloatWindow = self.xUITest.getFloatWindow()
+        xCheckListMenu = xFloatWindow.getChild("check_list_menu")
+        xTreeList = xCheckListMenu.getChild("check_tree_box")
+        self.assertEqual(3, len(xTreeList.getChildren()))
+        xOkBtn = xFloatWindow.getChild("cancel")
+        xOkBtn.executeAction("CLICK", tuple())
+
+        xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "1", "ROW": "0"}))
+        xFloatWindow = self.xUITest.getFloatWindow()
+        xCheckListMenu = xFloatWindow.getChild("check_list_menu")
+        xTreeList = xCheckListMenu.getChild("check_list_box")
+        self.assertEqual(5, len(xTreeList.getChildren()))
+        xOkBtn = xFloatWindow.getChild("cancel")
+        xOkBtn.executeAction("CLICK", tuple())
+
+        xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "2", "ROW": "0"}))
+        xFloatWindow = self.xUITest.getFloatWindow()
+        xCheckListMenu = xFloatWindow.getChild("check_list_menu")
+        xTreeList = xCheckListMenu.getChild("check_list_box")
+        self.assertEqual(3, len(xTreeList.getChildren()))
+        xOkBtn = xFloatWindow.getChild("cancel")
+        xOkBtn.executeAction("CLICK", tuple())
+
+        self.ui_test.close_doc()
 # vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/qa/uitest/data/autofilter/tdf116818.xlsx b/sc/qa/uitest/data/autofilter/tdf116818.xlsx
new file mode 100644
index 000000000000..060615cc0444
--- /dev/null
+++ b/sc/qa/uitest/data/autofilter/tdf116818.xlsx
diff --git a/sc/source/filter/inc/autofilterbuffer.hxx b/sc/source/filter/inc/autofilterbuffer.hxx
index 469aacdce399..e0270336a10e 100644
--- a/sc/source/filter/inc/autofilterbuffer.hxx
+++ b/sc/source/filter/inc/autofilterbuffer.hxx
@@ -48,7 +48,7 @@ struct ApiFilterSettings
 
     void appendField( bool bAnd, sal_Int32 nOperator, double fValue );
     void appendField( bool bAnd, sal_Int32 nOperator, const OUString& rValue );
-    void appendField( bool bAnd, const std::vector<OUString>& rValues );
+    void appendField( bool bAnd, const std::vector<std::pair<OUString, bool>>& rValues );
 };
 
 /** Base class for specific filter settings for a column in a filtered range.
@@ -86,7 +86,7 @@ public:
 
 private:
 
-    std::vector< OUString > maValues;
+    std::vector<std::pair<OUString, bool>> maValues; // first->values, second->bDatefFormat
     sal_Int32           mnCalendarType;
     bool                mbShowBlank;
 };
diff --git a/sc/source/filter/oox/autofilterbuffer.cxx b/sc/source/filter/oox/autofilterbuffer.cxx
index a3ed4d2cc0e1..b479aef936ab 100644
--- a/sc/source/filter/oox/autofilterbuffer.cxx
+++ b/sc/source/filter/oox/autofilterbuffer.cxx
@@ -175,18 +175,20 @@ void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, const OUStr
     rFilterField.Values[0].StringValue = rValue;
 }
 
-void ApiFilterSettings::appendField( bool bAnd, const std::vector<OUString>& rValues )
+void ApiFilterSettings::appendField( bool bAnd, const std::vector<std::pair<OUString, bool>>& rValues )
 {
     maFilterFields.emplace_back();
     TableFilterField3& rFilterField = maFilterFields.back();
     rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
     rFilterField.Operator = FilterOperator2::EQUAL;
-    size_t n = rValues.size();
-    rFilterField.Values.realloc(n);
-    for (size_t i = 0; i < n; ++i)
+    rFilterField.Values.realloc(rValues.size());
+    size_t i = 0;
+
+    for( auto const& it : rValues )
     {
         rFilterField.Values[i].IsNumeric = false;
-        rFilterField.Values[i].StringValue = rValues[i];
+        rFilterField.Values[i].StringValue = it.first;
+        rFilterField.Values[i++].IsDateValue = it.second;
     }
 }
 
@@ -228,7 +230,36 @@ void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAt
         {
             OUString aValue = rAttribs.getXString( XML_val, OUString() );
             if( !aValue.isEmpty() )
-                maValues.push_back( aValue );
+                maValues.push_back( std::make_pair(aValue, false) );
+        }
+        break;
+
+        case XLS_TOKEN( dateGroupItem ):
+        {
+            OUString aDateValue;
+            sal_uInt16 nToken = rAttribs.getToken(XML_dateTimeGrouping, XML_day);
+            if( nToken == XML_year || nToken == XML_month || nToken == XML_day )
+            {
+                aDateValue = rAttribs.getString(XML_year, OUString());
+
+                if( nToken == XML_month || nToken == XML_day )
+                {
+                    OUString aMonthName = rAttribs.getString(XML_month, OUString());
+                    if( aMonthName.getLength() == 1 )
+                        aMonthName = "0" + aMonthName;
+                    aDateValue += "-" + aMonthName;
+
+                    if( nToken == XML_day )
+                    {
+                        OUString aDayName = rAttribs.getString(XML_day, OUString());
+                        if( aDayName.getLength() == 1 )
+                            aDayName = "0" + aDayName;
+                        aDateValue += "-" + aDayName;
+                    }
+                }
+            }
+            if( !aDateValue.isEmpty() )
+                maValues.push_back( std::make_pair(aDateValue, true) );
         }
         break;
     }
@@ -256,7 +287,7 @@ void DiscreteFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm
         {
             OUString aValue = BiffHelper::readString( rStrm );
             if( !aValue.isEmpty() )
-                maValues.push_back( aValue );
+                maValues.push_back( std::make_pair(aValue, false) );
         }
         break;
     }
diff --git a/sc/source/filter/oox/autofiltercontext.cxx b/sc/source/filter/oox/autofiltercontext.cxx
index 3a1260198fcd..5dd3a6b34b28 100644
--- a/sc/source/filter/oox/autofiltercontext.cxx
+++ b/sc/source/filter/oox/autofiltercontext.cxx
@@ -38,7 +38,7 @@ ContextHandlerRef FilterSettingsContext::onCreateContext( sal_Int32 nElement, co
     switch( getCurrentElement() )
     {
         case XLS_TOKEN( filters ):
-            if( nElement == XLS_TOKEN( filter ) ) return this;
+            if( nElement == XLS_TOKEN( filter ) || nElement == XLS_TOKEN( dateGroupItem )) return this;
         break;
         case XLS_TOKEN( customFilters ):
             if( nElement == XLS_TOKEN( customFilter ) ) return this;
diff --git a/sc/source/ui/unoobj/datauno.cxx b/sc/source/ui/unoobj/datauno.cxx
index 2f2bdae144a4..03f58253c37d 100644
--- a/sc/source/ui/unoobj/datauno.cxx
+++ b/sc/source/ui/unoobj/datauno.cxx
@@ -55,6 +55,7 @@
 #include <dputil.hxx>
 #include <sortparam.hxx>
 #include <dpobject.hxx>
+#include <filterentries.hxx>
 
 #include <comphelper/extract.hxx>
 #include <cppuhelper/supportsservice.hxx>
@@ -1122,7 +1123,7 @@ void fillQueryParam(
             for (const auto& rVal : rVals)
             {
                 ScQueryEntry::Item aItem;
-                aItem.meType   = rVal.IsNumeric ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+                aItem.meType   = rVal.IsNumeric ? ScQueryEntry::ByValue : (rVal.IsDateValue ? ScQueryEntry::ByDate : ScQueryEntry::ByString);
                 aItem.mfVal    = rVal.NumericValue;
                 aItem.maString = rPool.intern(rVal.StringValue);
 
@@ -1133,7 +1134,23 @@ void fillQueryParam(
                     aItem.maString = rPool.intern(aStr);
                 }
 
-                rItems.push_back(aItem);
+                if( aItem.meType == ScQueryEntry::ByDate && aItem.maString.getLength() < 10 )
+                {
+                    ScFilterEntries aFilterEntries;
+                    pDoc->GetFilterEntries(rEntry.nField, rParam.nRow1, rParam.nTab, aFilterEntries);
+                    for( const auto& rFilter : aFilterEntries )
+                    {
+                        if( rFilter.GetString().startsWith(rVal.StringValue) )
+                        {
+                            aItem.maString = rPool.intern(rFilter.GetString());
+                            rItems.push_back(aItem);
+                        }
+                    }
+                }
+                else
+                {
+                    rItems.push_back(aItem);
+                }
             }
         }
     }