resolved fdo#37978 - Date formatting in Spreadsheet is inconsistent

* SvNumberFormatter::IsNumberFormat() ISO 8601 date input results in
  yyyy-mm-dd format if no other date format was set.
* SvNumberFormatter::GetInputLineString() preserves ISO 8601 date format for
  editing if such was set.

3 files changed, 70 insertions, 44 deletions

diff --git a/svl/inc/svl/zforlist.hxx b/svl/inc/svl/zforlist.hxx
index afcb3268acd8..ffae6290fcf8 100644
--- a/svl/inc/svl/zforlist.hxx
+++ b/svl/inc/svl/zforlist.hxx
@@ -595,6 +595,9 @@ public:
     /// Whether nFIndex is a special builtin format
     bool IsSpecialStandardFormat( sal_uInt32 nFIndex, LanguageType eLnge );
 
+    /** Return the corresponding edit format of a format. */
+    sal_uInt32 GetEditFormat( double fNumber, sal_uInt32 nFIndex, short eType, LanguageType eLnge );
+
     /// Return the reference date
     Date* GetNullDate();
     /// Return the standard decimal precision
diff --git a/svl/source/numbers/zforfind.hxx b/svl/source/numbers/zforfind.hxx
index d949217cf828..9c557b7e8303 100644
--- a/svl/source/numbers/zforfind.hxx
+++ b/svl/source/numbers/zforfind.hxx
@@ -69,6 +69,10 @@ public:
     /// get threshold of two-digit year input
     sal_uInt16  GetYear2000() const { return nYear2000; }
 
+    // Whether input may be a ISO 8601 date format, yyyy-mm-dd...
+    // checks if at least 3 numbers and first number>31
+    bool MayBeIso8601();
+
 private:
     SvNumberFormatter*  pFormatter;
     String* pUpperMonthText;                    // Array of month names, uppercase
@@ -256,10 +260,6 @@ private:
             const String& rString,
             const SvNumberformat* pFormat = NULL );
 
-    // Whether input may be a ISO 8601 date format, yyyy-mm-dd...
-    // checks if at least 3 numbers and first number>31
-    bool MayBeIso8601();
-
     // Compare rString to substring of array indexed by nString
     // nString == 0xFFFF => last substring
     bool ScanStringNumFor(
diff --git a/svl/source/numbers/zforlist.cxx b/svl/source/numbers/zforlist.cxx
index 8901784dc520..295a61884ced 100644
--- a/svl/source/numbers/zforlist.cxx
+++ b/svl/source/numbers/zforlist.cxx
@@ -1155,7 +1155,6 @@ bool SvNumberFormatter::IsNumberFormat(const String& sString,
     const SvNumberformat* pFormat = (SvNumberformat*) aFTable.Get(F_Index);
     if (!pFormat)
     {
-//      OSL_FAIL("SvNumberFormatter:: Unbekanntes altes Zahlformat (2)");
         ChangeIntl(IniLnge);
         FType = NUMBERFORMAT_NUMBER;
     }
@@ -1168,21 +1167,25 @@ bool SvNumberFormatter::IsNumberFormat(const String& sString,
     }
     bool res;
     short RType = FType;
-                                                        // Ergebnistyp
-                                                        // ohne def-Kennung
-    if (RType == NUMBERFORMAT_TEXT)                         // Zahlzelle ->Stringz.
-        res = false;
+    if (RType == NUMBERFORMAT_TEXT)
+        res = false;        // type text preset => no conversion to number
     else
         res = pStringScanner->IsNumberFormat(sString, RType, fOutNumber, pFormat);
 
-    if (res && !IsCompatible(FType, RType))     // unpassender Typ
+    if (res && !IsCompatible(FType, RType))     // non-matching type
     {
         switch ( RType )
         {
+            case NUMBERFORMAT_DATE :
+                // Preserve ISO 8601 input.
+                if (pStringScanner->MayBeIso8601())
+                    F_Index = GetFormatIndex( NF_DATE_DIN_YYYYMMDD, ActLnge );
+                else
+                    F_Index = GetStandardFormat( RType, ActLnge );
+                break;
             case NUMBERFORMAT_TIME :
-            {
                 if ( pStringScanner->GetDecPos() )
-                {   // 100stel Sekunden
+                {   // 100th seconds
                     if ( pStringScanner->GetAnzNums() > 3 || fOutNumber < 0.0 )
                         F_Index = GetFormatIndex( NF_TIME_HH_MMSS00, ActLnge );
                     else
@@ -1192,7 +1195,6 @@ bool SvNumberFormatter::IsNumberFormat(const String& sString,
                     F_Index = GetFormatIndex( NF_TIME_HH_MMSS, ActLnge );
                 else
                     F_Index = GetStandardFormat( RType, ActLnge );
-            }
             break;
             default:
                 F_Index = GetStandardFormat( RType, ActLnge );
@@ -1435,50 +1437,38 @@ sal_uInt32 SvNumberFormatter::GetStandardFormat( double fNumber, sal_uInt32 nFIn
     }
 }
 
-void SvNumberFormatter::GetInputLineString(const double& fOutNumber,
-                                           sal_uInt32 nFIndex,
-                                           String& sOutString)
+sal_uInt32 SvNumberFormatter::GetEditFormat( double fNumber, sal_uInt32 nFIndex, short eType, LanguageType eLang )
 {
-    SvNumberformat* pFormat;
-    Color* pColor;
-    pFormat = (SvNumberformat*) aFTable.Get(nFIndex);
-    if (!pFormat)
-        pFormat = aFTable.Get(ZF_STANDARD);
-    LanguageType eLang = pFormat->GetLanguage();
-    ChangeIntl( eLang );
-    short eType = pFormat->GetType() & ~NUMBERFORMAT_DEFINED;
-    if (eType == 0)
-        eType = NUMBERFORMAT_DEFINED;
-    sal_uInt16 nOldPrec = pFormatScanner->GetStandardPrec();
-    bool bPrecChanged = false;
-    if (eType == NUMBERFORMAT_NUMBER || eType == NUMBERFORMAT_PERCENT
-                                     || eType == NUMBERFORMAT_CURRENCY
-                                     || eType == NUMBERFORMAT_SCIENTIFIC
-                                     || eType == NUMBERFORMAT_FRACTION)
-    {
-        if (eType != NUMBERFORMAT_PERCENT)  // spaeter Sonderbehandlung %
-            eType = NUMBERFORMAT_NUMBER;
-        ChangeStandardPrec(INPUTSTRING_PRECISION);
-        bPrecChanged = true;
-    }
     sal_uInt32 nKey = nFIndex;
     switch ( eType )
     {   // #61619# always edit using 4-digit year
         case NUMBERFORMAT_DATE :
-            if (::rtl::math::approxFloor( fOutNumber) != fOutNumber)
+            if (::rtl::math::approxFloor( fNumber) != fNumber)
                 nKey = GetFormatIndex( NF_DATETIME_SYS_DDMMYYYY_HHMMSS, eLang );
                 // fdo#34977 preserve time when editing even if only date was
                 // displayed.
+                /* FIXME: in case an ISO 8601 format was used, editing should
+                 * also use such. Unfortunately we have no builtin combined
+                 * date+time ISO format defined. Needs also locale data work.
+                 * */
             else
-                nKey = GetFormatIndex( NF_DATE_SYS_DDMMYYYY, eLang );
+            {
+                // Preserve ISO 8601 format.
+                if (    nFIndex == GetFormatIndex( NF_DATE_DIN_YYYYMMDD, eLang) ||
+                        nFIndex == GetFormatIndex( NF_DATE_DIN_YYMMDD, eLang) ||
+                        nFIndex == GetFormatIndex( NF_DATE_DIN_MMDD, eLang))
+                    nKey = GetFormatIndex( NF_DATE_DIN_YYYYMMDD, eLang);
+                else
+                    nKey = GetFormatIndex( NF_DATE_SYS_DDMMYYYY, eLang );
+            }
         break;
         case NUMBERFORMAT_TIME :
-            if (fOutNumber < 0.0 || fOutNumber >= 1.0)
+            if (fNumber < 0.0 || fNumber >= 1.0)
             {
                 /* XXX NOTE: this is a purely arbitrary value within the limits
                  * of a signed 16-bit. 32k hours are 3.7 years ... or
                  * 1903-09-26 if date. */
-                if (fabs( fOutNumber) * 24 < 0x7fff)
+                if (fabs( fNumber) * 24 < 0x7fff)
                     nKey = GetFormatIndex( NF_TIME_HH_MMSS, eLang );
                     // Preserve duration, use [HH]:MM:SS instead of time.
                 else
@@ -1487,14 +1477,47 @@ void SvNumberFormatter::GetInputLineString(const double& fOutNumber,
                     // displayed.
             }
             else
-                nKey = GetStandardFormat( fOutNumber, nFIndex, eType, eLang );
+                nKey = GetStandardFormat( fNumber, nFIndex, eType, eLang );
         break;
         case NUMBERFORMAT_DATETIME :
             nKey = GetFormatIndex( NF_DATETIME_SYS_DDMMYYYY_HHMMSS, eLang );
+            /* FIXME: in case an ISO 8601 format was used, editing should
+             * also use such. Unfortunately we have no builtin combined
+             * date+time ISO format defined. Needs also locale data work. */
         break;
         default:
-            nKey = GetStandardFormat( fOutNumber, nFIndex, eType, eLang );
+            nKey = GetStandardFormat( fNumber, nFIndex, eType, eLang );
+    }
+    return nKey;
+}
+
+void SvNumberFormatter::GetInputLineString(const double& fOutNumber,
+                                           sal_uInt32 nFIndex,
+                                           String& sOutString)
+{
+    SvNumberformat* pFormat;
+    Color* pColor;
+    pFormat = (SvNumberformat*) aFTable.Get(nFIndex);
+    if (!pFormat)
+        pFormat = aFTable.Get(ZF_STANDARD);
+    LanguageType eLang = pFormat->GetLanguage();
+    ChangeIntl( eLang );
+    short eType = pFormat->GetType() & ~NUMBERFORMAT_DEFINED;
+    if (eType == 0)
+        eType = NUMBERFORMAT_DEFINED;
+    sal_uInt16 nOldPrec = pFormatScanner->GetStandardPrec();
+    bool bPrecChanged = false;
+    if (eType == NUMBERFORMAT_NUMBER || eType == NUMBERFORMAT_PERCENT
+                                     || eType == NUMBERFORMAT_CURRENCY
+                                     || eType == NUMBERFORMAT_SCIENTIFIC
+                                     || eType == NUMBERFORMAT_FRACTION)
+    {
+        if (eType != NUMBERFORMAT_PERCENT)  // spaeter Sonderbehandlung %
+            eType = NUMBERFORMAT_NUMBER;
+        ChangeStandardPrec(INPUTSTRING_PRECISION);
+        bPrecChanged = true;
     }
+    sal_uInt32 nKey = GetEditFormat( fOutNumber, nFIndex, eType, eLang);
     if ( nKey != nFIndex )
         pFormat = (SvNumberformat*) aFTable.Get( nKey );
     if (pFormat)