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, 92 insertions, 12 deletions

diff --git a/sc/uiconfig/scalc/ui/formulacalculationoptions.ui b/sc/uiconfig/scalc/ui/formulacalculationoptions.ui
index 305e8e3e536d..b0442c26950d 100644
--- a/sc/uiconfig/scalc/ui/formulacalculationoptions.ui
+++ b/sc/uiconfig/scalc/ui/formulacalculationoptions.ui
@@ -188,10 +188,10 @@
               </packing>
             </child>
             <child>
-              <object class="GtkLabel" id="ref_syntax_caption">
+              <object class="GtkLabel" id="string_conversion_caption">
                 <property name="can_focus">False</property>
                 <property name="no_show_all">True</property>
-                <property name="label" translatable="yes">Reference syntax for string reference</property>
+                <property name="label" translatable="yes">Conversion from text to number</property>
               </object>
               <packing>
                 <property name="left_attach">0</property>
@@ -201,10 +201,10 @@
               </packing>
             </child>
             <child>
-              <object class="GtkLabel" id="ref_syntax_desc">
+              <object class="GtkLabel" id="string_conversion_desc">
                 <property name="can_focus">False</property>
                 <property name="no_show_all">True</property>
-                <property name="label" translatable="yes">Formula syntax to use when parsing references given in string parameters. This affects built-in functions such as INDIRECT that takes a reference as a string value.</property>
+                <property name="label" translatable="yes">How to treat text when encountered as operand in an arithmetic operation or as argument to a function that expects a number instead. Unambiguous conversion is possible for integer numbers including exponents and ISO 8601 dates and times in their extended formats with separators. Fractional numeric values with decimal separators or dates other than ISO 8601 are locale dependent. Note that in locale dependent conversions the resulting numeric value may differ between locales!</property>
                 <property name="wrap">True</property>
                 <property name="max_width_chars">56</property>
               </object>
@@ -216,10 +216,10 @@
               </packing>
             </child>
             <child>
-              <object class="GtkLabel" id="use_formula_syntax">
+              <object class="GtkLabel" id="string_conversion_as_error">
                 <property name="can_focus">False</property>
                 <property name="no_show_all">True</property>
-                <property name="label" translatable="yes">Use formula syntax</property>
+                <property name="label" translatable="yes">Generate #VALUE! error</property>
               </object>
               <packing>
                 <property name="left_attach">0</property>
@@ -229,6 +229,45 @@
               </packing>
             </child>
             <child>
+              <object class="GtkLabel" id="string_conversion_as_zero">
+                <property name="can_focus">False</property>
+                <property name="no_show_all">True</property>
+                <property name="label" translatable="yes">Treat as zero</property>
+              </object>
+              <packing>
+                <property name="left_attach">0</property>
+                <property name="top_attach">7</property>
+                <property name="width">1</property>
+                <property name="height">1</property>
+              </packing>
+            </child>
+            <child>
+              <object class="GtkLabel" id="string_conversion_unambiguous">
+                <property name="can_focus">False</property>
+                <property name="no_show_all">True</property>
+                <property name="label" translatable="yes">Convert only unambiguous</property>
+              </object>
+              <packing>
+                <property name="left_attach">0</property>
+                <property name="top_attach">8</property>
+                <property name="width">1</property>
+                <property name="height">1</property>
+              </packing>
+            </child>
+            <child>
+              <object class="GtkLabel" id="string_conversion_locale_dependent">
+                <property name="can_focus">False</property>
+                <property name="no_show_all">True</property>
+                <property name="label" translatable="yes">Convert also locale dependent</property>
+              </object>
+              <packing>
+                <property name="left_attach">0</property>
+                <property name="top_attach">9</property>
+                <property name="width">1</property>
+                <property name="height">1</property>
+              </packing>
+            </child>
+            <child>
               <object class="GtkLabel" id="empty_str_as_zero_caption">
                 <property name="can_focus">False</property>
                 <property name="no_show_all">True</property>
@@ -236,7 +275,7 @@
               </object>
               <packing>
                 <property name="left_attach">0</property>
-                <property name="top_attach">7</property>
+                <property name="top_attach">10</property>
                 <property name="width">1</property>
                 <property name="height">1</property>
               </packing>
@@ -245,13 +284,54 @@
               <object class="GtkLabel" id="empty_str_as_zero_desc">
                 <property name="can_focus">False</property>
                 <property name="no_show_all">True</property>
-                <property name="label" translatable="yes">This option determines whether or not an empty string is to be treated as having a value of zero when used in arithmetic.</property>
+                <property name="label" translatable="yes">This option determines whether an empty string is to be treated as having a value of zero when used in arithmetic or generates an error. It is disabled if conversion from text to number is set to always generate an error or always treat text as zero and then follows that value.</property>
                 <property name="wrap">True</property>
                 <property name="max_width_chars">56</property>
               </object>
               <packing>
                 <property name="left_attach">0</property>
-                <property name="top_attach">8</property>
+                <property name="top_attach">11</property>
+                <property name="width">1</property>
+                <property name="height">1</property>
+              </packing>
+            </child>
+            <child>
+              <object class="GtkLabel" id="ref_syntax_caption">
+                <property name="can_focus">False</property>
+                <property name="no_show_all">True</property>
+                <property name="label" translatable="yes">Reference syntax for string reference</property>
+              </object>
+              <packing>
+                <property name="left_attach">0</property>
+                <property name="top_attach">12</property>
+                <property name="width">1</property>
+                <property name="height">1</property>
+              </packing>
+            </child>
+            <child>
+              <object class="GtkLabel" id="ref_syntax_desc">
+                <property name="can_focus">False</property>
+                <property name="no_show_all">True</property>
+                <property name="label" translatable="yes">Formula syntax to use when parsing references given in string parameters. This affects built-in functions such as INDIRECT that takes a reference as a string value.</property>
+                <property name="wrap">True</property>
+                <property name="max_width_chars">56</property>
+              </object>
+              <packing>
+                <property name="left_attach">0</property>
+                <property name="top_attach">13</property>
+                <property name="width">1</property>
+                <property name="height">1</property>
+              </packing>
+            </child>
+            <child>
+              <object class="GtkLabel" id="use_formula_syntax">
+                <property name="can_focus">False</property>
+                <property name="no_show_all">True</property>
+                <property name="label" translatable="yes">Use formula syntax</property>
+              </object>
+              <packing>
+                <property name="left_attach">0</property>
+                <property name="top_attach">14</property>
                 <property name="width">1</property>
                 <property name="height">1</property>
               </packing>
@@ -264,7 +344,7 @@
               </object>
               <packing>
                 <property name="left_attach">0</property>
-                <property name="top_attach">9</property>
+                <property name="top_attach">15</property>
                 <property name="width">1</property>
                 <property name="height">1</property>
               </packing>
@@ -279,7 +359,7 @@
               </object>
               <packing>
                 <property name="left_attach">0</property>
-                <property name="top_attach">10</property>
+                <property name="top_attach">16</property>
                 <property name="width">1</property>
                 <property name="height">1</property>
               </packing>
@@ -470,7 +550,7 @@
               </object>
               <packing>
                 <property name="left_attach">0</property>
-                <property name="top_attach">11</property>
+                <property name="top_attach">17</property>
                 <property name="width">1</property>
                 <property name="height">1</property>
               </packing>