mib19: #163664# text values for VBA range Value property must be parsed (always English)

5 files changed, 79 insertions, 5 deletions

diff --git a/sc/inc/cellsuno.hxx b/sc/inc/cellsuno.hxx
index a53f167b164a..33113f18859e 100644
--- a/sc/inc/cellsuno.hxx
+++ b/sc/inc/cellsuno.hxx
@@ -205,7 +205,6 @@ private:
     const ScPatternAttr*    GetCurrentAttrsFlat();
     const ScPatternAttr*    GetCurrentAttrsDeep();
     SfxItemSet*             GetCurrentDataSet(bool bNoDflt = false);
-    const ScMarkData*       GetMarkData();
     void                    ForgetMarkData();
     void                    ForgetCurrentAttrs();
 
@@ -218,6 +217,8 @@ private:
                                     const ScAddress* pLastPos);
 
 protected:
+    const ScMarkData*       GetMarkData();
+
     // GetItemPropertyMap for derived classes must contain all entries, including base class
     virtual const SfxItemPropertyMap* GetItemPropertyMap();
     virtual ::com::sun::star::beans::PropertyState GetOnePropertyState(
@@ -874,6 +875,8 @@ public:
                                 const ::rtl::OUString& rFormulaNmsp, const formula::FormulaGrammar::Grammar );
     const ScAddress&        GetPosition() const { return aCellPos; }
 
+    SC_DLLPUBLIC void       InputEnglishString( const ::rtl::OUString& rText );
+
                             // XText
     virtual void SAL_CALL   insertTextContent( const ::com::sun::star::uno::Reference<
                                     ::com::sun::star::text::XTextRange >& xRange,
diff --git a/sc/source/ui/docshell/docfunc.cxx b/sc/source/ui/docshell/docfunc.cxx
index adbe26abf157..02dbc7546a38 100644
--- a/sc/source/ui/docshell/docfunc.cxx
+++ b/sc/source/ui/docshell/docfunc.cxx
@@ -986,7 +986,7 @@ ScTokenArray* lcl_ScDocFunc_CreateTokenArrayXML( const String& rText, const Stri
 
 
 ScBaseCell* ScDocFunc::InterpretEnglishString( const ScAddress& rPos,
-        const String& rText, const String& rFormulaNmsp, const formula::FormulaGrammar::Grammar eGrammar )
+        const String& rText, const String& rFormulaNmsp, const formula::FormulaGrammar::Grammar eGrammar, short* pRetFormatType )
 {
     ScDocument* pDoc = rDocShell.GetDocument();
     ScBaseCell* pNewCell = NULL;
@@ -1020,7 +1020,12 @@ ScBaseCell* ScDocFunc::InterpretEnglishString( const ScAddress& rPos,
         sal_uInt32 nEnglish = pFormatter->GetStandardIndex(LANGUAGE_ENGLISH_US);
         double fVal;
         if ( pFormatter->IsNumberFormat( rText, nEnglish, fVal ) )
+        {
             pNewCell = new ScValueCell( fVal );
+            // return the format type from the English format, so a localized format can be created
+            if ( pRetFormatType )
+                *pRetFormatType = pFormatter->GetType( nEnglish );
+        }
         else if ( rText.Len() )
             pNewCell = ScBaseCell::CreateTextCell( rText, pDoc );
 
diff --git a/sc/source/ui/inc/docfunc.hxx b/sc/source/ui/inc/docfunc.hxx
index c92cc082986d..e36e1a9af92f 100644
--- a/sc/source/ui/inc/docfunc.hxx
+++ b/sc/source/ui/inc/docfunc.hxx
@@ -92,7 +92,8 @@ public:
 
                     // creates a new cell for use with PutCell
     ScBaseCell*     InterpretEnglishString( const ScAddress& rPos, const String& rText,
-                        const String& rFormulaNmsp, const formula::FormulaGrammar::Grammar eGrammar );
+                        const String& rFormulaNmsp, const formula::FormulaGrammar::Grammar eGrammar,
+                        short* pRetFormatType = NULL );
 
     bool            ShowNote( const ScAddress& rPos, bool bShow = true );
     inline bool     HideNote( const ScAddress& rPos ) { return ShowNote( rPos, false ); }
diff --git a/sc/source/ui/unoobj/cellsuno.cxx b/sc/source/ui/unoobj/cellsuno.cxx
index ffef1a914a29..53ac57ee2318 100644
--- a/sc/source/ui/unoobj/cellsuno.cxx
+++ b/sc/source/ui/unoobj/cellsuno.cxx
@@ -6272,6 +6272,53 @@ void ScCellObj::SetFormulaWithGrammar( const ::rtl::OUString& rFormula,
     }
 }
 
+void ScCellObj::InputEnglishString( const ::rtl::OUString& rText )
+{
+    // This is like a mixture of setFormula and property FormulaLocal:
+    // The cell's number format is checked for "text", a new cell format may be set,
+    // but all parsing is in English.
+
+    ScDocShell* pDocSh = GetDocShell();
+    if ( pDocSh )
+    {
+        String aString(rText);
+        ScDocument* pDoc = pDocSh->GetDocument();
+        SvNumberFormatter* pFormatter = pDoc->GetFormatTable();
+        sal_uInt32 nOldFormat = pDoc->GetNumberFormat( aCellPos );
+        if ( pFormatter->GetType( nOldFormat ) == NUMBERFORMAT_TEXT )
+        {
+            SetString_Impl(aString, FALSE, FALSE);      // text cell
+        }
+        else
+        {
+            ScDocFunc aFunc(*pDocSh);
+            short nFormatType = 0;
+            ScBaseCell* pNewCell = aFunc.InterpretEnglishString( aCellPos, aString,
+                                    EMPTY_STRING, formula::FormulaGrammar::GRAM_PODF_A1, &nFormatType );
+            if (pNewCell)
+            {
+                if ( ( nOldFormat % SV_COUNTRY_LANGUAGE_OFFSET ) == 0 && nFormatType != 0 )
+                {
+                    // apply a format for the recognized type and the old format's language
+                    sal_uInt32 nNewFormat = ScGlobal::GetStandardFormat( *pFormatter, nOldFormat, nFormatType );
+                    if ( nNewFormat != nOldFormat )
+                    {
+                        ScPatternAttr aPattern( pDoc->GetPool() );
+                        aPattern.GetItemSet().Put( SfxUInt32Item( ATTR_VALUE_FORMAT, nNewFormat ) );
+                        // ATTR_LANGUAGE_FORMAT remains unchanged
+                        aFunc.ApplyAttributes( *GetMarkData(), aPattern, TRUE, TRUE );
+                    }
+                }
+                // put the cell into the document
+                // (after applying the format, so possible formula recalculation already uses the new format)
+                (void)aFunc.PutCell( aCellPos, pNewCell, TRUE );
+            }
+            else
+                SetString_Impl(aString, FALSE, FALSE);      // no cell from InterpretEnglishString, probably empty string
+        }
+    }
+}
+
 //  XText
 
 uno::Reference<text::XTextCursor> SAL_CALL ScCellObj::createTextCursor()
diff --git a/sc/source/ui/vba/vbarange.cxx b/sc/source/ui/vba/vbarange.cxx
index 891a5a36ecfd..5f97aaac506a 100755
--- a/sc/source/ui/vba/vbarange.cxx
+++ b/sc/source/ui/vba/vbarange.cxx
@@ -740,8 +740,26 @@ CellValueSetter::processValue( const uno::Any& aValue, const uno::Reference< tab
             rtl::OUString aString;
             if ( aValue >>= aString )
             {
-                uno::Reference< text::XTextRange > xTextRange( xCell, uno::UNO_QUERY_THROW );
-                xTextRange->setString( aString );
+                // The required behavior for a string value is:
+                // 1. If the first character is a single quote, use the rest as a string cell, regardless of the cell's number format.
+                // 2. Otherwise, if the cell's number format is "text", use the string value as a string cell.
+                // 3. Otherwise, parse the string value in English locale, and apply a corresponding number format with the cell's locale
+                //    if the cell's number format was "General".
+                // Case 1 is handled here, the rest in ScCellObj::InputEnglishString
+
+                if ( aString.toChar() == '\'' )     // case 1 - handle with XTextRange
+                {
+                    rtl::OUString aRemainder( aString.copy(1) );    // strip the quote
+                    uno::Reference< text::XTextRange > xTextRange( xCell, uno::UNO_QUERY_THROW );
+                    xTextRange->setString( aRemainder );
+                }
+                else
+                {
+                    // call implementation method InputEnglishString
+                    ScCellObj* pCellObj = dynamic_cast< ScCellObj* >( xCell.get() );
+                    if ( pCellObj )
+                        pCellObj->InputEnglishString( aString );
+                }
             }
             else
                 isExtracted = false;