Add new formula config option in the UI for "treat empty string as zero."

Change-Id: I116857ee8b377c80707efbc76fcfa06c3e46201d

6 files changed, 193 insertions, 36 deletions

diff --git a/sc/inc/calcconfig.hxx b/sc/inc/calcconfig.hxx
index 48d474616799..2006ba2314ee 100644
--- a/sc/inc/calcconfig.hxx
+++ b/sc/inc/calcconfig.hxx
@@ -38,6 +38,7 @@
 struct SC_DLLPUBLIC ScCalcConfig
 {
     formula::FormulaGrammar::AddressConvention meStringRefAddressSyntax;
+    bool mbEmptyStringAsZero:1;
 
     ScCalcConfig();
 
diff --git a/sc/source/core/tool/calcconfig.cxx b/sc/source/core/tool/calcconfig.cxx
index 660869a70a2d..eab14678c1d7 100644
--- a/sc/source/core/tool/calcconfig.cxx
+++ b/sc/source/core/tool/calcconfig.cxx
@@ -29,7 +29,8 @@
 #include "calcconfig.hxx"
 
 ScCalcConfig::ScCalcConfig() :
-    meStringRefAddressSyntax(formula::FormulaGrammar::CONV_UNSPECIFIED) {}
+    meStringRefAddressSyntax(formula::FormulaGrammar::CONV_UNSPECIFIED),
+    mbEmptyStringAsZero(false) {}
 
 void ScCalcConfig::reset()
 {
@@ -38,7 +39,8 @@ void ScCalcConfig::reset()
 
 bool ScCalcConfig::operator== (const ScCalcConfig& r) const
 {
-    return meStringRefAddressSyntax == r.meStringRefAddressSyntax;
+    return meStringRefAddressSyntax == r.meStringRefAddressSyntax &&
+        mbEmptyStringAsZero == r.mbEmptyStringAsZero;
 }
 
 bool ScCalcConfig::operator!= (const ScCalcConfig& r) const
diff --git a/sc/source/ui/optdlg/calcoptionsdlg.cxx b/sc/source/ui/optdlg/calcoptionsdlg.cxx
index 2b75082c6ade..4fdffb8febfe 100644
--- a/sc/source/ui/optdlg/calcoptionsdlg.cxx
+++ b/sc/source/ui/optdlg/calcoptionsdlg.cxx
@@ -87,16 +87,22 @@ ScCalcOptionsDialog::ScCalcOptionsDialog(Window* pParent, const ScCalcConfig& rC
     maLbSettings(this, ScResId(LB_SETTINGS)),
     maFtOptionEditCaption(this, ScResId(FT_OPTION_EDIT_CAPTION)),
     maLbOptionEdit(this, ScResId(LB_OPTION_EDIT)),
+    maBtnTrue(this, ScResId(BTN_OPTION_TRUE)),
+    maBtnFalse(this, ScResId(BTN_OPTION_FALSE)),
     maFlAnnotation(this, ScResId(FL_ANNOTATION)),
     maFtAnnotation(this, ScResId(FT_ANNOTATION)),
     maBtnOK(this, ScResId(BTN_OK)),
     maBtnCancel(this, ScResId(BTN_CANCEL)),
+    maTrue(ScResId(STR_TRUE).toString()),
+    maFalse(ScResId(STR_FALSE).toString()),
     maCalcA1(ScResId(SCSTR_FORMULA_SYNTAX_CALC_A1).toString()),
     maExcelA1(ScResId(SCSTR_FORMULA_SYNTAX_XL_A1).toString()),
     maExcelR1C1(ScResId(SCSTR_FORMULA_SYNTAX_XL_R1C1).toString()),
     maCaptionStringRefSyntax(ScResId(STR_STRING_REF_SYNTAX_CAPTION).toString()),
     maDescStringRefSyntax(ScResId(STR_STRING_REF_SYNTAX_DESC).toString()),
     maUseFormulaSyntax(ScResId(STR_USE_FORMULA_SYNTAX).toString()),
+    maCaptionEmptyStringAsZero(ScResId(STR_EMPTY_STRING_AS_ZERO_CAPTION).toString()),
+    maDescEmptyStringAsZero(ScResId(STR_EMPTY_STRING_AS_ZERO_DESC).toString()),
     maConfig(rConfig)
 {
     maLbSettings.SetStyle(maLbSettings.GetStyle() | WB_CLIPCHILDREN | WB_FORCE_MAKEVISIBLE);
@@ -106,6 +112,12 @@ ScCalcOptionsDialog::ScCalcOptionsDialog(Window* pParent, const ScCalcConfig& rC
     maLbSettings.SetSelectHdl(aLink);
     maLbOptionEdit.SetSelectHdl(aLink);
 
+    aLink = LINK(this, ScCalcOptionsDialog, BtnToggleHdl);
+    maBtnTrue.SetToggleHdl(aLink); // Set handler only to the 'True' button.
+
+    maBtnTrue.SetText(maTrue);
+    maBtnFalse.SetText(maFalse);
+
     FillOptionsList();
     FreeResource();
     SelectionChanged();
@@ -136,58 +148,134 @@ void ScCalcOptionsDialog::FillOptionsList()
         pModel->Insert(pEntry);
     }
 
+    {
+        // Treat empty string as zero.
+        SvLBoxEntry* pEntry = new SvLBoxEntry;
+        pEntry->AddItem(new SvLBoxString(pEntry, 0, rtl::OUString()));
+        pEntry->AddItem(new SvLBoxContextBmp(pEntry, 0, Image(), Image(), 0));
+        OptionString* pItem = new OptionString(
+            maCaptionEmptyStringAsZero, toString(maConfig.mbEmptyStringAsZero));
+        pEntry->AddItem(pItem);
+        pModel->Insert(pEntry);
+    }
+
     maLbSettings.SetUpdateMode(true);
 }
 
 void ScCalcOptionsDialog::SelectionChanged()
 {
-    if (true)
+    sal_uInt16 nSelectedPos = maLbSettings.GetSelectEntryPos();
+    switch (nSelectedPos)
     {
-        // Formula syntax for INDIRECT function.
-        maLbOptionEdit.Clear();
-        maLbOptionEdit.InsertEntry(maUseFormulaSyntax);
-        maLbOptionEdit.InsertEntry(maCalcA1);
-        maLbOptionEdit.InsertEntry(maExcelA1);
-        maLbOptionEdit.InsertEntry(maExcelR1C1);
-        switch (maConfig.meStringRefAddressSyntax)
+        case 0:
         {
-            case formula::FormulaGrammar::CONV_OOO:
-                maLbOptionEdit.SelectEntryPos(1);
-            break;
-            case formula::FormulaGrammar::CONV_XL_A1:
-                maLbOptionEdit.SelectEntryPos(2);
-            break;
-            case formula::FormulaGrammar::CONV_XL_R1C1:
-                maLbOptionEdit.SelectEntryPos(3);
-            break;
-            case formula::FormulaGrammar::CONV_UNSPECIFIED:
-            default:
-                maLbOptionEdit.SelectEntryPos(0);
+            // Formula syntax for INDIRECT function.
+            maBtnTrue.Hide();
+            maBtnFalse.Hide();
+            maLbOptionEdit.Show();
+
+            maLbOptionEdit.Clear();
+            maLbOptionEdit.InsertEntry(maUseFormulaSyntax);
+            maLbOptionEdit.InsertEntry(maCalcA1);
+            maLbOptionEdit.InsertEntry(maExcelA1);
+            maLbOptionEdit.InsertEntry(maExcelR1C1);
+            switch (maConfig.meStringRefAddressSyntax)
+            {
+                case formula::FormulaGrammar::CONV_OOO:
+                    maLbOptionEdit.SelectEntryPos(1);
+                break;
+                case formula::FormulaGrammar::CONV_XL_A1:
+                    maLbOptionEdit.SelectEntryPos(2);
+                break;
+                case formula::FormulaGrammar::CONV_XL_R1C1:
+                    maLbOptionEdit.SelectEntryPos(3);
+                break;
+                case formula::FormulaGrammar::CONV_UNSPECIFIED:
+                default:
+                    maLbOptionEdit.SelectEntryPos(0);
+            }
+            maFtAnnotation.SetText(maDescStringRefSyntax);
+        }
+        break;
+        case 1:
+        {
+            // Treat empty string as zero.
+            maLbOptionEdit.Hide();
+            maBtnTrue.Show();
+            maBtnFalse.Show();
+
+            if (maConfig.mbEmptyStringAsZero)
+            {
+                maBtnTrue.Check(true);
+                maBtnFalse.Check(false);
+            }
+            else
+            {
+                maBtnTrue.Check(false);
+                maBtnFalse.Check(true);
+            }
         }
-        maFtAnnotation.SetText(maDescStringRefSyntax);
+        break;
+        default:
+            ;
     }
 }
 
 void ScCalcOptionsDialog::ListOptionValueChanged()
 {
-    if (true)
+    sal_uInt16 nSelected = maLbSettings.GetSelectEntryPos();
+    switch (nSelected)
     {
-        // Formula syntax for INDIRECT function.
-        sal_uInt16 nPos = maLbOptionEdit.GetSelectEntryPos();
-        maConfig.meStringRefAddressSyntax = toAddressConvention(nPos);
+        case 0:
+        {
+            // Formula syntax for INDIRECT function.
+            sal_uInt16 nPos = maLbOptionEdit.GetSelectEntryPos();
+            maConfig.meStringRefAddressSyntax = toAddressConvention(nPos);
 
-        maLbSettings.SetUpdateMode(false);
+            maLbSettings.SetUpdateMode(false);
 
-        SvLBoxTreeList* pModel = maLbSettings.GetModel();
-        SvLBoxEntry* pEntry = pModel->GetEntry(NULL, 0);
-        if (!pEntry)
-            return;
+            SvLBoxTreeList* pModel = maLbSettings.GetModel();
+            SvLBoxEntry* pEntry = pModel->GetEntry(NULL, 0);
+            if (!pEntry)
+                return;
 
-        OptionString* pItem = new OptionString(
-            maCaptionStringRefSyntax, toString(maConfig.meStringRefAddressSyntax));
-        pEntry->ReplaceItem(pItem, 2);
+            OptionString* pItem = new OptionString(
+                maCaptionStringRefSyntax, toString(maConfig.meStringRefAddressSyntax));
+            pEntry->ReplaceItem(pItem, 2);
 
-        maLbSettings.SetUpdateMode(true);
+            maLbSettings.SetUpdateMode(true);
+        }
+        break;
+        default:
+            ;
+    }
+}
+
+void ScCalcOptionsDialog::RadioValueChanged()
+{
+    sal_uInt16 nSelected = maLbSettings.GetSelectEntryPos();
+    switch (nSelected)
+    {
+        case 1:
+        {
+            // Treat empty string as zero.
+            maConfig.mbEmptyStringAsZero = maBtnTrue.IsChecked();
+            maLbSettings.SetUpdateMode(false);
+
+            SvLBoxTreeList* pModel = maLbSettings.GetModel();
+            SvLBoxEntry* pEntry = pModel->GetEntry(NULL, 1);
+            if (!pEntry)
+                return;
+
+            OptionString* pItem = new OptionString(
+                maCaptionEmptyStringAsZero, toString(maConfig.mbEmptyStringAsZero));
+            pEntry->ReplaceItem(pItem, 2);
+
+            maLbSettings.SetUpdateMode(true);
+        }
+        break;
+        default:
+            ;
     }
 }
 
@@ -208,6 +296,11 @@ rtl::OUString ScCalcOptionsDialog::toString(formula::FormulaGrammar::AddressConv
     return maUseFormulaSyntax;
 }
 
+rtl::OUString ScCalcOptionsDialog::toString(bool bVal) const
+{
+    return bVal ? maTrue : maFalse;
+}
+
 IMPL_LINK(ScCalcOptionsDialog, SettingsSelHdl, Control*, pCtrl)
 {
     if (pCtrl == &maLbSettings)
@@ -218,4 +311,11 @@ IMPL_LINK(ScCalcOptionsDialog, SettingsSelHdl, Control*, pCtrl)
     return 0;
 }
 
+IMPL_LINK(ScCalcOptionsDialog, BtnToggleHdl, RadioButton*, pBtn)
+{
+    RadioValueChanged();
+    return 0;
+}
+
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/optdlg/calcoptionsdlg.hrc b/sc/source/ui/optdlg/calcoptionsdlg.hrc
index 6d878683b6be..401f32daecbb 100644
--- a/sc/source/ui/optdlg/calcoptionsdlg.hrc
+++ b/sc/source/ui/optdlg/calcoptionsdlg.hrc
@@ -34,6 +34,8 @@
 
 #define FT_OPTION_EDIT_CAPTION 4
 #define LB_OPTION_EDIT 5
+#define BTN_OPTION_TRUE 6
+#define BTN_OPTION_FALSE 7
 
 #define FL_ANNOTATION 20
 #define FT_ANNOTATION 21
@@ -43,4 +45,11 @@
 
 #define STR_USE_FORMULA_SYNTAX 24
 
+#define STR_TRUE 25
+#define STR_FALSE 26
+
+#define STR_EMPTY_STRING_AS_ZERO_CAPTION 27
+#define STR_EMPTY_STRING_AS_ZERO_DESC 28
+
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/optdlg/calcoptionsdlg.hxx b/sc/source/ui/optdlg/calcoptionsdlg.hxx
index a1e3cfc5c448..85a62db6a274 100644
--- a/sc/source/ui/optdlg/calcoptionsdlg.hxx
+++ b/sc/source/ui/optdlg/calcoptionsdlg.hxx
@@ -43,6 +43,7 @@ public:
     virtual ~ScCalcOptionsDialog();
 
     DECL_LINK( SettingsSelHdl, Control* );
+    DECL_LINK( BtnToggleHdl, RadioButton* );
 
     const ScCalcConfig& GetConfig() const;
 
@@ -50,14 +51,18 @@ private:
     void FillOptionsList();
     void SelectionChanged();
     void ListOptionValueChanged();
+    void RadioValueChanged();
 
     rtl::OUString toString(formula::FormulaGrammar::AddressConvention eConv) const;
+    rtl::OUString toString(bool bVal) const;
 
 private:
     SvxCheckListBox maLbSettings;
 
     FixedText maFtOptionEditCaption;
     ListBox maLbOptionEdit;
+    RadioButton maBtnTrue;
+    RadioButton maBtnFalse;
 
     FixedLine maFlAnnotation;
     FixedText maFtAnnotation;
@@ -65,6 +70,9 @@ private:
     OKButton maBtnOK;
     CancelButton maBtnCancel;
 
+    rtl::OUString maTrue;
+    rtl::OUString maFalse;
+
     rtl::OUString maCalcA1;
     rtl::OUString maExcelA1;
     rtl::OUString maExcelR1C1;
@@ -73,6 +81,9 @@ private:
     rtl::OUString maDescStringRefSyntax;
     rtl::OUString maUseFormulaSyntax;
 
+    rtl::OUString maCaptionEmptyStringAsZero;
+    rtl::OUString maDescEmptyStringAsZero;
+
     ScCalcConfig maConfig;
 };
 
diff --git a/sc/source/ui/optdlg/calcoptionsdlg.src b/sc/source/ui/optdlg/calcoptionsdlg.src
index 58fa7509508c..235ec0e36feb 100644
--- a/sc/source/ui/optdlg/calcoptionsdlg.src
+++ b/sc/source/ui/optdlg/calcoptionsdlg.src
@@ -60,6 +60,20 @@ ModalDialog RID_SCDLG_FORMULA_CALCOPTIONS
         DropDown = TRUE ;
     };
 
+    RadioButton BTN_OPTION_TRUE
+    {
+        Pos = MAP_APPFONT ( 50, 83 ) ;
+        Size = MAP_APPFONT ( 50, 14 ) ;
+        TabStop = TRUE ;
+    };
+
+    RadioButton BTN_OPTION_FALSE
+    {
+        Pos = MAP_APPFONT ( 110, 83 ) ;
+        Size = MAP_APPFONT ( 50, 14 ) ;
+        TabStop = TRUE ;
+    };
+
     FixedLine FL_ANNOTATION
     {
         Pos = MAP_APPFONT ( 6 , 98 ) ;
@@ -103,4 +117,24 @@ ModalDialog RID_SCDLG_FORMULA_CALCOPTIONS
     {
         Text [ en-US ] = "Use formula syntax";
     };
+
+    String STR_EMPTY_STRING_AS_ZERO_CAPTION
+    {
+        Text [ en-US ] = "Treat empty string as zero";
+    };
+
+    String STR_EMPTY_STRING_AS_ZERO_DESC
+    {
+        Text [ en-US ] = "Blah.";
+    };
+
+    String STR_TRUE
+    {
+        Text [ en-US ] = "True";
+    };
+
+    String STR_FALSE
+    {
+        Text [ en-US ] = "False";
+    };
 };