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, 10 insertions, 0 deletions

diff --git a/sc/inc/calcconfig.hxx b/sc/inc/calcconfig.hxx
index 94a72cc8b6a9..c477e726410a 100644
--- a/sc/inc/calcconfig.hxx
+++ b/sc/inc/calcconfig.hxx
@@ -28,7 +28,17 @@ enum ScRecalcOptions
  */
 struct SC_DLLPUBLIC ScCalcConfig
 {
+    // from most stringent to most relaxed
+    enum StringConversion
+    {
+        STRING_CONVERSION_AS_ERROR = 0,     ///<  =1+"1" or =1+"x" give #VALUE!
+        STRING_CONVERSION_AS_ZERO,          ///<  =1+"1" or =1+"x" give 1
+        STRING_CONVERSION_UNAMBIGUOUS,      ///<  =1+"1" gives 2, but =1+"1.000" or =1+"x" give #VALUE!
+        STRING_CONVERSION_LOCALE_DEPENDENT  ///<  =1+"1.000" may be 2 or 1001 ... =1+"x" gives #VALUE!
+    };
+
     formula::FormulaGrammar::AddressConvention meStringRefAddressSyntax;
+    StringConversion meStringConversion;
     bool mbEmptyStringAsZero:1;
     bool mbOpenCLEnabled:1;
     bool mbOpenCLAutoSelect:1;