tdf#137626 XLSX import: fix missing datetime filters

by convert string representation of the datetime data
to ISO 8601 (with blank instead of T) datetime to
eliminate locale dependent behaviour when filtering
for datetimes.

Follow-up of commit 0e751d0cb816197f15a2448ec36c57df17387e40
(tdf#116818 sc,offapi,XLSX import: fix autofiltered date columns).

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

5 files changed, 56 insertions, 5 deletions

diff --git a/sc/qa/uitest/autofilter/autofilter.py b/sc/qa/uitest/autofilter/autofilter.py
index ca1871ee933c..2c4f36947929 100644
--- a/sc/qa/uitest/autofilter/autofilter.py
+++ b/sc/qa/uitest/autofilter/autofilter.py
@@ -309,4 +309,19 @@ class AutofilterTest(UITestCase):
         xOkBtn.executeAction("CLICK", tuple())
 
         self.ui_test.close_doc()
+
+    def test_tdf137626(self):
+        doc = self.ui_test.load_file(get_url_for_data_file("tdf137626.xlsx"))
+
+        xGridWin = self.xUITest.getTopFocusWindow().getChild("grid_window")
+
+        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(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/tdf137626.xlsx b/sc/qa/uitest/data/autofilter/tdf137626.xlsx
new file mode 100644
index 000000000000..eb5ce4da7b98
--- /dev/null
+++ b/sc/qa/uitest/data/autofilter/tdf137626.xlsx
diff --git a/sc/source/core/data/column3.cxx b/sc/source/core/data/column3.cxx
index edf99f02bef5..2285a859d75b 100644
--- a/sc/source/core/data/column3.cxx
+++ b/sc/source/core/data/column3.cxx
@@ -2472,6 +2472,14 @@ class FilterEntriesHandler
             sal_uInt32 nIndex = pFormatter->GetFormatIndex( NF_DATE_DIN_YYYYMMDD);
             pFormatter->GetInputLineString( fVal, nIndex, aStr);
         }
+        else if (nType == SvNumFormatType::DATETIME)
+        {
+            // special case for datetime values.
+            // Convert string representation to ISO 8601 (with blank instead of T) datetime
+            // to eliminate locale dependent behaviour later when filtering for datetimes.
+            sal_uInt32 nIndex = pFormatter->GetFormatIndex(NF_DATETIME_ISO_YYYYMMDD_HHMMSS);
+            pFormatter->GetInputLineString(fVal, nIndex, aStr);
+        }
         // maybe extend ScTypedStrData enum is also an option here
         mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, ScTypedStrData::Value,bDate));
     }
diff --git a/sc/source/filter/oox/autofilterbuffer.cxx b/sc/source/filter/oox/autofilterbuffer.cxx
index a9ec62c4e655..e09bd084e7f3 100644
--- a/sc/source/filter/oox/autofilterbuffer.cxx
+++ b/sc/source/filter/oox/autofilterbuffer.cxx
@@ -240,23 +240,50 @@ void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAt
             // it is just a fallback, we do not need the XML_day as default value,
             // because if the dateGroupItem exists also XML_dateTimeGrouping exists!
             sal_uInt16 nToken = rAttribs.getToken(XML_dateTimeGrouping, XML_day);
-            if( nToken == XML_year || nToken == XML_month || nToken == XML_day )
+            if( nToken == XML_year || nToken == XML_month || nToken == XML_day ||
+                nToken == XML_hour || nToken == XML_min || nToken == XML_second )
             {
                 aDateValue = rAttribs.getString(XML_year, OUString());
 
-                if( nToken == XML_month || nToken == XML_day )
+                if( nToken == XML_month || nToken == XML_day || nToken == XML_hour ||
+                    nToken == XML_min || nToken == XML_second )
                 {
                     OUString aMonthName = rAttribs.getString(XML_month, OUString());
                     if( aMonthName.getLength() == 1 )
                         aMonthName = "0" + aMonthName;
                     aDateValue += "-" + aMonthName;
 
-                    if( nToken == XML_day )
+                    if( nToken == XML_day || nToken == XML_hour || nToken == XML_min ||
+                        nToken == XML_second )
                     {
                         OUString aDayName = rAttribs.getString(XML_day, OUString());
                         if( aDayName.getLength() == 1 )
                             aDayName = "0" + aDayName;
                         aDateValue += "-" + aDayName;
+
+                        if( nToken == XML_hour || nToken == XML_min || nToken == XML_second )
+                        {
+                            OUString aHourName = rAttribs.getString(XML_hour, OUString());
+                            if( aHourName.getLength() == 1 )
+                                aHourName = "0" + aHourName;
+                            aDateValue += " " + aHourName;
+
+                            if( nToken == XML_min || nToken == XML_second )
+                            {
+                                OUString aMinName = rAttribs.getString(XML_min, OUString());
+                                if( aMinName.getLength() == 1 )
+                                    aMinName = "0" + aMinName;
+                                aDateValue += ":" + aMinName;
+
+                                if( nToken == XML_second )
+                                {
+                                    OUString aSecName = rAttribs.getString(XML_second, OUString());
+                                    if( aSecName.getLength() == 1 )
+                                        aSecName = "0" + aSecName;
+                                    aDateValue += ":" + aSecName;
+                                }
+                            }
+                        }
                     }
                 }
             }
diff --git a/sc/source/ui/unoobj/datauno.cxx b/sc/source/ui/unoobj/datauno.cxx
index 6828d503a3d8..af6f493934bd 100644
--- a/sc/source/ui/unoobj/datauno.cxx
+++ b/sc/source/ui/unoobj/datauno.cxx
@@ -1135,8 +1135,9 @@ void fillQueryParam(
                     aItem.maString = rPool.intern(aStr);
                 }
 
-                // filter all dates starting with the given date filter YYYY or YYYY-MM
-                if( aItem.meType == ScQueryEntry::ByDate && aItem.maString.getLength() < 10 )
+                // filter all dates starting with the given date filter YYYY or YYYY-MM and filter all datetimes
+                // starting with the given datetime filter YYYY-MM-DD, YYYY-MM-DD HH, or YYYY-MM-DD HH:MM
+                if( aItem.meType == ScQueryEntry::ByDate && aItem.maString.getLength() < 19 )
                 {
                     ScFilterEntries aFilterEntries;
                     pDoc->GetFilterEntries(rEntry.nField, rParam.nRow1, rParam.nTab, aFilterEntries);