Related: tdf#150098 sc validation: allowing formulas for validity test

Clean-up and a little optimization.

Change-Id: Ib56d959188912f4b18acb5466ce55bc7b5b4ee4b
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/146391
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>
(cherry picked from commit d8ae6d1388f28c405c4de2dfe93dbfe2d8acd470)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/146696
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>

2 files changed, 79 insertions, 121 deletions

diff --git a/sc/inc/validat.hxx b/sc/inc/validat.hxx
index 79eb9b75a334..6f64b842daba 100644
--- a/sc/inc/validat.hxx
+++ b/sc/inc/validat.hxx
@@ -152,6 +152,10 @@ public:
 
     bool IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos ) const;
 
+    /** Test, if formula is valid. */
+    bool isFormulaResultsValidatable(const OUString& rTest, const ScAddress& rPos, SvNumberFormatter* pFormatter,
+        OUString& rStrResult, double& nVal, sal_uInt32& nFormat, bool& bIsVal) const;
+
                     // TRUE -> break
     bool DoError(weld::Window* pParent, const OUString& rInput, const ScAddress& rPos) const;
     void DoCalcError( ScFormulaCell* pCell ) const;
diff --git a/sc/source/core/data/validat.cxx b/sc/source/core/data/validat.cxx
index 2d6194baf588..4db07b09abc7 100644
--- a/sc/source/core/data/validat.cxx
+++ b/sc/source/core/data/validat.cxx
@@ -444,67 +444,12 @@ bool ScValidationData::IsDataValidCustom(
 
     if (rTest[0] == '=')
     {
-        std::optional<ScSimpleFormulaCalculator> pFCell(std::in_place, *mpDoc, rPos, rTest, true);
-        pFCell->SetLimitString(true);
-
-        bool bColRowName = pFCell->HasColRowName();
-        if (bColRowName)
-        {
-            // ColRowName from RPN-Code?
-            if (pFCell->GetCode()->GetCodeLen() <= 1)
-            {   // ==1: area
-                // ==0: would be an area if...
-                OUString aBraced = "(" + rTest + ")";
-                pFCell.emplace(*mpDoc, rPos, aBraced, true);
-                pFCell->SetLimitString(true);
-            }
-            else
-                bColRowName = false;
-        }
-
-        FormulaError nErrCode = pFCell->GetErrCode();
-        if (nErrCode == FormulaError::NONE || pFCell->IsMatrix())
-        {
-            pFormatter = mpDoc->GetFormatTable();
-            const Color* pColor;
-            if (pFCell->IsMatrix())
-            {
-                rStrResult = pFCell->GetString().getString();
-            }
-            else if (pFCell->IsValue())
-            {
-                nVal = pFCell->GetValue();
-                nFormat = pFormatter->GetStandardFormat(nVal, 0,
-                    pFCell->GetFormatType(), ScGlobal::eLnge);
-                pFormatter->GetOutputString(nVal, nFormat, rStrResult, &pColor);
-                bIsVal = true;
-            }
-            else
-            {
-                nFormat = pFormatter->GetStandardFormat(
-                    pFCell->GetFormatType(), ScGlobal::eLnge);
-                pFormatter->GetOutputString(pFCell->GetString().getString(), nFormat,
-                    rStrResult, &pColor);
-                // Indicate it's a string, so a number string doesn't look numeric.
-                // Escape embedded quotation marks first by doubling them, as
-                // usual. Actually the result can be copy-pasted from the result
-                // box as literal into a formula expression.
-                rStrResult = "\"" + rStrResult.replaceAll("\"", "\"\"") + "\"";
-            }
-
-            ScRange aTestRange;
-            if (bColRowName || (aTestRange.Parse(rTest, *mpDoc) & ScRefFlags::VALID))
-                rStrResult += " ...";
-            // area
-
-            // check whether empty cells are allowed
-            if (rStrResult.isEmpty())
-                return IsIgnoreBlank();
-        }
-        else
-        {
+        if (!isFormulaResultsValidatable(rTest, rPos, pFormatter, rStrResult, nVal, nFormat, bIsVal))
             return false;
-        }
+
+        // check whether empty cells are allowed
+        if (rStrResult.isEmpty())
+            return IsIgnoreBlank();
     }
     else
     {
@@ -599,69 +544,14 @@ bool ScValidationData::IsDataValid(
     OUString rStrResult = "";
     bool bIsVal = false;
 
-    if (rTest[0] == '=')   // formulas do not pass the validity test
+    if (rTest[0] == '=')
     {
-        std::optional<ScSimpleFormulaCalculator> pFCell(std::in_place, *mpDoc, rPos, rTest, true);
-        pFCell->SetLimitString(true);
-
-        bool bColRowName = pFCell->HasColRowName();
-        if (bColRowName)
-        {
-            // ColRowName from RPN-Code?
-            if (pFCell->GetCode()->GetCodeLen() <= 1)
-            {   // ==1: area
-                // ==0: would be an area if...
-                OUString aBraced = "(" + rTest + ")";
-                pFCell.emplace(*mpDoc, rPos, aBraced, true);
-                pFCell->SetLimitString(true);
-            }
-            else
-                bColRowName = false;
-        }
-
-        FormulaError nErrCode = pFCell->GetErrCode();
-        if (nErrCode == FormulaError::NONE || pFCell->IsMatrix())
-        {
-            pFormatter = mpDoc->GetFormatTable();
-            const Color* pColor;
-            if (pFCell->IsMatrix())
-            {
-                rStrResult = pFCell->GetString().getString();
-            }
-            else if (pFCell->IsValue())
-            {
-                nVal = pFCell->GetValue();
-                nFormat = pFormatter->GetStandardFormat(nVal, 0,
-                    pFCell->GetFormatType(), ScGlobal::eLnge);
-                pFormatter->GetOutputString(nVal, nFormat, rStrResult, &pColor);
-                bIsVal = true;
-            }
-            else
-            {
-                nFormat = pFormatter->GetStandardFormat(
-                    pFCell->GetFormatType(), ScGlobal::eLnge);
-                pFormatter->GetOutputString(pFCell->GetString().getString(), nFormat,
-                    rStrResult, &pColor);
-                // Indicate it's a string, so a number string doesn't look numeric.
-                // Escape embedded quotation marks first by doubling them, as
-                // usual. Actually the result can be copy-pasted from the result
-                // box as literal into a formula expression.
-                rStrResult = "\"" + rStrResult.replaceAll("\"", "\"\"") + "\"";
-            }
-
-            ScRange aTestRange;
-            if (bColRowName || (aTestRange.Parse(rTest, *mpDoc) & ScRefFlags::VALID))
-                rStrResult += " ...";
-            // area
-
-            // check whether empty cells are allowed
-            if (rStrResult.isEmpty())
-                return IsIgnoreBlank();
-        }
-        else
-        {
+        if (!isFormulaResultsValidatable(rTest, rPos, pFormatter, rStrResult, nVal, nFormat, bIsVal))
             return false;
-        }
+
+        // check whether empty cells are allowed
+        if (rStrResult.isEmpty())
+            return IsIgnoreBlank();
     }
     else
     {
@@ -776,6 +666,70 @@ bool ScValidationData::IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos
     return bOk;
 }
 
+bool ScValidationData::isFormulaResultsValidatable(const OUString& rTest, const ScAddress& rPos, SvNumberFormatter* pFormatter,
+    OUString& rStrResult, double& nVal, sal_uInt32& nFormat, bool& bIsVal) const
+{
+    std::optional<ScSimpleFormulaCalculator> pFCell(std::in_place, *mpDoc, rPos, rTest, true);
+    pFCell->SetLimitString(true);
+
+    bool bColRowName = pFCell->HasColRowName();
+    if (bColRowName)
+    {
+        // ColRowName from RPN-Code?
+        if (pFCell->GetCode()->GetCodeLen() <= 1)
+        {   // ==1: area
+            // ==0: would be an area if...
+            OUString aBraced = "(" + rTest + ")";
+            pFCell.emplace(*mpDoc, rPos, aBraced, true);
+            pFCell->SetLimitString(true);
+        }
+        else
+            bColRowName = false;
+    }
+
+    FormulaError nErrCode = pFCell->GetErrCode();
+    if (nErrCode == FormulaError::NONE || pFCell->IsMatrix())
+    {
+        pFormatter = mpDoc->GetFormatTable();
+        const Color* pColor;
+        if (pFCell->IsMatrix())
+        {
+            rStrResult = pFCell->GetString().getString();
+        }
+        else if (pFCell->IsValue())
+        {
+            nVal = pFCell->GetValue();
+            nFormat = pFormatter->GetStandardFormat(nVal, 0,
+                pFCell->GetFormatType(), ScGlobal::eLnge);
+            pFormatter->GetOutputString(nVal, nFormat, rStrResult, &pColor);
+            bIsVal = true;
+        }
+        else
+        {
+            nFormat = pFormatter->GetStandardFormat(
+                pFCell->GetFormatType(), ScGlobal::eLnge);
+            pFormatter->GetOutputString(pFCell->GetString().getString(), nFormat,
+                rStrResult, &pColor);
+            // Indicate it's a string, so a number string doesn't look numeric.
+            // Escape embedded quotation marks first by doubling them, as
+            // usual. Actually the result can be copy-pasted from the result
+            // box as literal into a formula expression.
+            rStrResult = "\"" + rStrResult.replaceAll("\"", "\"\"") + "\"";
+        }
+
+        ScRange aTestRange;
+        if (bColRowName || (aTestRange.Parse(rTest, *mpDoc) & ScRefFlags::VALID))
+            rStrResult += " ...";
+        // area
+
+        return true;
+    }
+    else
+    {
+        return false;
+    }
+}
+
 namespace {
 
 /** Token array helper. Iterates over all string tokens.