user selectable string conversion models, related fdo#37132 fdo#74622

Determines how to treat text when encountered as operand in an arithmetic
operation or as argument to a function that expects a number instead.

Selectable under Tools->Options->Calc->Formula "Detailed calculation settings"
"Custom" from "Conversion from text to number" are:

Generate #VALUE! error:         =1+"1" or =1+"x" give #VALUE!
Treat as zero:                  =1+"1" or =1+"x" give 1
Convert only unambiguous:       =1+"1" gives 2, but =1+"1.000" or =1+"x" give #VALUE!
Convert also locale dependent:  =1+"1.000" may be 2 or 1001 ... =1+"x" gives #VALUE!

For "Generate #VALUE! error" and "Treat as zero" the "Treat empty string as
zero" option follows these settings, for "Convert only unambiguous" and
"Convert also locale dependent" it can be set independently.

When reading documents created by other spreadsheet applications or older
versions of LibreOffice, and to interchange documents between different locales
the "Convert only unambiguous" with "Treat empty string as zero = True" setting
is recommended, though LibreOffice so far acted as "Convert also locale
dependent" with "Treat empty string as zero = False", which is the reason that
option is kept as default.

The best setting to create new documents that can be interpreted by all
spreadsheet applications without on-the-fly string conversion is
"Generate #VALUE! error". Not having to convert strings during
calculation ist also faster, of course.

Change-Id: Ie6dc34a00a82064a2d862b2178ce715fab945f85

1 files changed, 154 insertions, 10 deletions

diff --git a/sc/source/ui/optdlg/calcoptionsdlg.cxx b/sc/source/ui/optdlg/calcoptionsdlg.cxx
index 0b5fd4cf2db4..82083cc7dc9b 100644
--- a/sc/source/ui/optdlg/calcoptionsdlg.cxx
+++ b/sc/source/ui/optdlg/calcoptionsdlg.cxx
@@ -22,9 +22,10 @@
 namespace {
 
 typedef enum {
-    CALC_OPTION_REF_SYNTAX    = 0,
-    CALC_OPTION_EMPTY_AS_ZERO = 1,
-    CALC_OPTION_ENABLE_OPENCL = 2
+    CALC_OPTION_STRING_CONVERSION = 0,
+    CALC_OPTION_EMPTY_AS_ZERO     = 1,
+    CALC_OPTION_REF_SYNTAX        = 2,
+    CALC_OPTION_ENABLE_OPENCL     = 3
 } CalcOptionOrder;
 
 class OptionString : public SvLBoxString
@@ -101,6 +102,23 @@ formula::FormulaGrammar::AddressConvention toAddressConvention(sal_Int32 nPos)
     return formula::FormulaGrammar::CONV_UNSPECIFIED;
 }
 
+ScCalcConfig::StringConversion toStringConversion(sal_Int32 nPos)
+{
+    switch (nPos)
+    {
+        case 0:
+            return ScCalcConfig::STRING_CONVERSION_AS_ERROR;
+        case 1:
+            return ScCalcConfig::STRING_CONVERSION_AS_ZERO;
+        case 2:
+            return ScCalcConfig::STRING_CONVERSION_UNAMBIGUOUS;
+        case 3:
+            return ScCalcConfig::STRING_CONVERSION_LOCALE_DEPENDENT;
+    }
+
+    return ScCalcConfig::STRING_CONVERSION_AS_ERROR;
+}
+
 }
 
 ScCalcOptionsDialog::ScCalcOptionsDialog(Window* pParent, const ScCalcConfig& rConfig)
@@ -110,6 +128,7 @@ ScCalcOptionsDialog::ScCalcOptionsDialog(Window* pParent, const ScCalcConfig& rC
     , maExcelA1(ScResId(SCSTR_FORMULA_SYNTAX_XL_A1).toString())
     , maExcelR1C1(ScResId(SCSTR_FORMULA_SYNTAX_XL_R1C1).toString())
     , maConfig(rConfig)
+    , mbSelectedEmptyStringAsZero(rConfig.mbEmptyStringAsZero)
 {
     get(mpLbSettings, "settings");
     get(mpLbOptionEdit, "edit");
@@ -134,8 +153,17 @@ ScCalcOptionsDialog::ScCalcOptionsDialog(Window* pParent, const ScCalcConfig& rC
     maCaptionStringRefSyntax = get<Window>("ref_syntax_caption")->GetText();
     maDescStringRefSyntax = get<Window>("ref_syntax_desc")->GetText();
     maUseFormulaSyntax = get<Window>("use_formula_syntax")->GetText();
+
+    maCaptionStringConversion = get<Window>("string_conversion_caption")->GetText();
+    maDescStringConversion = get<Window>("string_conversion_desc")->GetText();
+    maStringConversionAsError = get<Window>("string_conversion_as_error")->GetText();
+    maStringConversionAsZero = get<Window>("string_conversion_as_zero")->GetText();
+    maStringConversionUnambiguous = get<Window>("string_conversion_unambiguous")->GetText();
+    maStringConversionLocaleDependent = get<Window>("string_conversion_locale_dependent")->GetText();
+
     maCaptionEmptyStringAsZero = get<Window>("empty_str_as_zero_caption")->GetText();
     maDescEmptyStringAsZero = get<Window>("empty_str_as_zero_desc")->GetText();
+
     maCaptionOpenCLEnabled = get<Window>("opencl_enabled")->GetText();
     maDescOpenCLEnabled = get<Window>("opencl_enabled_desc")->GetText();
     maSoftware = get<Window>("software")->GetText();
@@ -237,6 +265,18 @@ void ScCalcOptionsDialog::fillOpenclList()
 
 #endif
 
+
+namespace {
+void addOption( SvTreeList* pModel, OptionString* pItem )
+{
+    SvTreeListEntry* pEntry = new SvTreeListEntry;
+    pEntry->AddItem(new SvLBoxString(pEntry, 0, OUString()));
+    pEntry->AddItem(new SvLBoxContextBmp(pEntry, 0, Image(), Image(), false));
+    pEntry->AddItem(pItem);
+    pModel->Insert(pEntry);
+}
+}
+
 void ScCalcOptionsDialog::FillOptionsList()
 {
     mpLbSettings->SetUpdateMode(false);
@@ -245,17 +285,21 @@ void ScCalcOptionsDialog::FillOptionsList()
     SvTreeList* pModel = mpLbSettings->GetModel();
 
     {
+        // String conversion for arithmetic operations.
+        OptionString* pItem = new OptionString(
+            maCaptionStringConversion, toString(maConfig.meStringConversion));
+        addOption( pModel, pItem);
+    }
+
+    pModel->Insert(createBoolItem(maCaptionEmptyStringAsZero,maConfig.mbEmptyStringAsZero));
+
+    {
         // Syntax for INDIRECT function.
-        SvTreeListEntry* pEntry = new SvTreeListEntry;
-        pEntry->AddItem(new SvLBoxString(pEntry, 0, OUString()));
-        pEntry->AddItem(new SvLBoxContextBmp(pEntry, 0, Image(), Image(), false));
         OptionString* pItem = new OptionString(
             maCaptionStringRefSyntax, toString(maConfig.meStringRefAddressSyntax));
-        pEntry->AddItem(pItem);
-        pModel->Insert(pEntry);
+        addOption( pModel, pItem);
     }
 
-    pModel->Insert(createBoolItem(maCaptionEmptyStringAsZero,maConfig.mbEmptyStringAsZero));
 #if HAVE_FEATURE_OPENCL
     pModel->Insert(createBoolItem(maCaptionOpenCLEnabled,maConfig.mbOpenCLEnabled));
     fillOpenclList();
@@ -304,6 +348,38 @@ void ScCalcOptionsDialog::SelectionChanged()
         }
         break;
 
+        case CALC_OPTION_STRING_CONVERSION:
+        {
+            // String conversion for arithmetic operations.
+            mpBtnTrue->Hide();
+            mpBtnFalse->Hide();
+            mpLbOptionEdit->Show();
+            mpOpenclInfoList->GetParent()->Hide();
+
+            mpLbOptionEdit->Clear();
+            mpLbOptionEdit->InsertEntry(maStringConversionAsError);
+            mpLbOptionEdit->InsertEntry(maStringConversionAsZero);
+            mpLbOptionEdit->InsertEntry(maStringConversionUnambiguous);
+            mpLbOptionEdit->InsertEntry(maStringConversionLocaleDependent);
+            switch (maConfig.meStringConversion)
+            {
+                case ScCalcConfig::STRING_CONVERSION_AS_ERROR:
+                    mpLbOptionEdit->SelectEntryPos(0);
+                break;
+                case ScCalcConfig::STRING_CONVERSION_AS_ZERO:
+                    mpLbOptionEdit->SelectEntryPos(1);
+                break;
+                case ScCalcConfig::STRING_CONVERSION_UNAMBIGUOUS:
+                    mpLbOptionEdit->SelectEntryPos(2);
+                break;
+                case ScCalcConfig::STRING_CONVERSION_LOCALE_DEPENDENT:
+                    mpLbOptionEdit->SelectEntryPos(3);
+                break;
+            }
+            mpFtAnnotation->SetText(maDescStringConversion);
+        }
+        break;
+
         // booleans
         case CALC_OPTION_EMPTY_AS_ZERO:
         case CALC_OPTION_ENABLE_OPENCL:
@@ -314,11 +390,22 @@ void ScCalcOptionsDialog::SelectionChanged()
             mpBtnFalse->Show();
 
             bool bValue = false;
+            bool bEnable = true;
             if ( nSelectedPos == CALC_OPTION_EMPTY_AS_ZERO )
             {
                 bValue = maConfig.mbEmptyStringAsZero;
                 mpFtAnnotation->SetText(maDescEmptyStringAsZero);
                 mpOpenclInfoList->GetParent()->Hide();
+                switch (maConfig.meStringConversion)
+                {
+                    case ScCalcConfig::STRING_CONVERSION_AS_ERROR:
+                    case ScCalcConfig::STRING_CONVERSION_AS_ZERO:
+                        bEnable = false;
+                        break;
+                    case ScCalcConfig::STRING_CONVERSION_UNAMBIGUOUS:
+                    case ScCalcConfig::STRING_CONVERSION_LOCALE_DEPENDENT:
+                        break;  // nothing
+                }
             }
             else
             {
@@ -344,6 +431,16 @@ void ScCalcOptionsDialog::SelectionChanged()
                 mpBtnTrue->Check(false);
                 mpBtnFalse->Check(true);
             }
+            if (bEnable)
+            {
+                mpBtnTrue->Enable();
+                mpBtnFalse->Enable();
+            }
+            else
+            {
+                mpBtnTrue->Disable();
+                mpBtnFalse->Disable();
+            }
         }
         break;
         default:
@@ -366,6 +463,36 @@ void ScCalcOptionsDialog::ListOptionValueChanged()
         }
         break;
 
+        case CALC_OPTION_STRING_CONVERSION:
+        {
+            // String conversion for arithmetic operations.
+            sal_Int32 nPos = mpLbOptionEdit->GetSelectEntryPos();
+            maConfig.meStringConversion = toStringConversion(nPos);
+
+            setValueAt(nSelected, toString(maConfig.meStringConversion));
+
+            switch (maConfig.meStringConversion)
+            {
+                case ScCalcConfig::STRING_CONVERSION_AS_ERROR:
+                    maConfig.mbEmptyStringAsZero = false;
+                    setValueAt(CALC_OPTION_EMPTY_AS_ZERO, toString(maConfig.mbEmptyStringAsZero));
+                    mpLbOptionEdit->SelectEntryPos(0);
+                break;
+                case ScCalcConfig::STRING_CONVERSION_AS_ZERO:
+                    maConfig.mbEmptyStringAsZero = true;
+                    setValueAt(CALC_OPTION_EMPTY_AS_ZERO, toString(maConfig.mbEmptyStringAsZero));
+                    mpLbOptionEdit->SelectEntryPos(1);
+                break;
+                case ScCalcConfig::STRING_CONVERSION_UNAMBIGUOUS:
+                case ScCalcConfig::STRING_CONVERSION_LOCALE_DEPENDENT:
+                    // Reset to the value the user selected before.
+                    maConfig.mbEmptyStringAsZero = mbSelectedEmptyStringAsZero;
+                    setValueAt(CALC_OPTION_EMPTY_AS_ZERO, toString(maConfig.mbEmptyStringAsZero));
+                break;
+            }
+        }
+        break;
+
         case CALC_OPTION_EMPTY_AS_ZERO:
         case CALC_OPTION_ENABLE_OPENCL:
             break;
@@ -424,9 +551,10 @@ void ScCalcOptionsDialog::RadioValueChanged()
     switch (nSelected)
     {
         case CALC_OPTION_REF_SYNTAX:
+        case CALC_OPTION_STRING_CONVERSION:
             return;
         case CALC_OPTION_EMPTY_AS_ZERO:
-            maConfig.mbEmptyStringAsZero = bValue;
+            maConfig.mbEmptyStringAsZero = mbSelectedEmptyStringAsZero = bValue;
             break;
         case CALC_OPTION_ENABLE_OPENCL:
             maConfig.mbOpenCLEnabled = bValue;
@@ -458,6 +586,22 @@ OUString ScCalcOptionsDialog::toString(formula::FormulaGrammar::AddressConventio
     return maUseFormulaSyntax;
 }
 
+OUString ScCalcOptionsDialog::toString(ScCalcConfig::StringConversion eConv) const
+{
+    switch (eConv)
+    {
+        case ScCalcConfig::STRING_CONVERSION_AS_ERROR:
+            return maStringConversionAsError;
+        case ScCalcConfig::STRING_CONVERSION_AS_ZERO:
+            return maStringConversionAsZero;
+        case ScCalcConfig::STRING_CONVERSION_UNAMBIGUOUS:
+            return maStringConversionUnambiguous;
+        case ScCalcConfig::STRING_CONVERSION_LOCALE_DEPENDENT:
+            return maStringConversionLocaleDependent;
+    }
+    return maStringConversionAsError;
+}
+
 OUString ScCalcOptionsDialog::toString(bool bVal) const
 {
     return bVal ? maTrue : maFalse;