sc/qa/uitest/validity/tdf150098.py | 47 +++++++++
sc/source/core/data/validat.cxx | 175 +++++++++++++++++++++++++++++++++----
2 files changed, 205 insertions(+), 17 deletions(-)
New commits:
commit 5f2d7db094fc0f4e7ae40987c3c6762b11184419
Author: Balazs Varga <balazs.varga.ext...@allotropia.de>
AuthorDate: Mon Jan 23 16:48:04 2023 +0100
Commit: Balazs Varga <balazs.varga.ext...@allotropia.de>
CommitDate: Wed Jan 25 11:01:27 2023 +0000
tdf#150098 sc validation: allowing formulas for validity test
Calculate the formula results, before checking the validity test.
Change-Id: I7420982a8cbcd2df6ab0adea6e3cf61aaccb1600
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/146011
Tested-by: Jenkins
Tested-by: Gabor Kelemen <kelem...@ubuntu.com>
Reviewed-by: Gabor Kelemen <kelem...@ubuntu.com>
Reviewed-by: Balazs Varga <balazs.varga.ext...@allotropia.de>
diff --git a/sc/qa/uitest/validity/tdf150098.py
b/sc/qa/uitest/validity/tdf150098.py
new file mode 100644
index 000000000000..5d29a4afaadf
--- /dev/null
+++ b/sc/qa/uitest/validity/tdf150098.py
@@ -0,0 +1,47 @@
+# -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+from uitest.framework import UITestCase
+from uitest.uihelper.calc import enter_text_to_cell
+from libreoffice.calc.document import get_cell_by_position
+from uitest.uihelper.common import select_by_text, select_pos
+
+from libreoffice.uno.propertyvalue import mkPropertyValues
+
+
+class EvaluateFormulaInputs(UITestCase):
+
+ def test_inputs_with_formula(self):
+ with self.ui_test.create_doc_in_start_center("calc") as document:
+ xCalcDoc = self.xUITest.getTopFocusWindow()
+ gridwin = xCalcDoc.getChild("grid_window")
+ enter_text_to_cell(gridwin, "A1", "5")
+ enter_text_to_cell(gridwin, "A2", "7")
+ enter_text_to_cell(gridwin, "A3", "12")
+
+ #Select the cells to be validated
+ gridwin.executeAction("SELECT", mkPropertyValues({"CELL": "A4"}))
+ #Apply Data > Validity ... > Whole Numbers
+ with
self.ui_test.execute_dialog_through_command(".uno:Validation") as xDialog:
+ xTabs = xDialog.getChild("tabcontrol")
+ select_pos(xTabs, "0")
+ xallow = xDialog.getChild("allow")
+ xallowempty = xDialog.getChild("allowempty")
+ xdata = xDialog.getChild("data")
+ xmin = xDialog.getChild("min")
+
+ select_by_text(xallow, "Whole Numbers")
+ xallowempty.executeAction("CLICK", tuple())
+ select_by_text(xdata, "equal")
+ xmin.executeAction("TYPE", mkPropertyValues({"TEXT":"A3"}))
+
+ enter_text_to_cell(gridwin, "A4", "=SUM(A1:A2)")
+ # without the fix in place, an error message would have appeared
+ self.assertEqual(get_cell_by_position(document, 0, 0,
3).getValue(), 12.0)
+
+# vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/source/core/data/validat.cxx b/sc/source/core/data/validat.cxx
index 08f77ad4f862..2cdafa132594 100644
--- a/sc/source/core/data/validat.cxx
+++ b/sc/source/core/data/validat.cxx
@@ -51,6 +51,7 @@
#include <scmatrix.hxx>
#include <cellvalue.hxx>
#include <comphelper/lok.hxx>
+#include <simpleformulacalc.hxx>
#include <math.h>
#include <memory>
@@ -438,15 +439,85 @@ bool ScValidationData::IsDataValidCustom(
if (rTest.isEmpty()) // check whether empty cells are allowed
return IsIgnoreBlank();
- if (rTest[0] == '=') // formulas do not pass the validity test
- return false;
+ SvNumberFormatter* pFormatter = nullptr;
+ sal_uInt32 nFormat = 0;
+ double nVal = 0.0;
+ OUString rStrResult = "";
+ bool bIsVal = false;
- SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
+ if (rTest[0] == '=')
+ {
+ std::optional<ScSimpleFormulaCalculator> pFCell(std::in_place, *mpDoc,
rPos, rTest, true);
+ pFCell->SetLimitString(true);
- // get the value if any
- sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
- double nVal;
- bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
+ 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
+ {
+ return false;
+ }
+ }
+ else
+ {
+ pFormatter = GetDocument()->GetFormatTable();
+
+ // get the value if any
+ nFormat = rPattern.GetNumberFormat(pFormatter);
+ bIsVal = pFormatter->IsNumberFormat(rTest, nFormat, nVal);
+ rStrResult = rTest;
+ }
ScRefCellValue aTmpCell;
svl::SharedString aSS;
@@ -456,7 +527,7 @@ bool ScValidationData::IsDataValidCustom(
}
else
{
- aSS = mpDoc->GetSharedStringPool().intern(rTest);
+ aSS = mpDoc->GetSharedStringPool().intern(rStrResult);
aTmpCell = ScRefCellValue(&aSS);
}
@@ -525,25 +596,95 @@ bool ScValidationData::IsDataValid(
if (rTest.isEmpty()) // check whether empty cells are allowed
return IsIgnoreBlank();
+ SvNumberFormatter* pFormatter = nullptr;
+ sal_uInt32 nFormat = 0;
+ double nVal = 0.0;
+ OUString rStrResult = "";
+ bool bIsVal = false;
+
if (rTest[0] == '=') // formulas do not pass the validity test
- return false;
+ {
+ std::optional<ScSimpleFormulaCalculator> pFCell(std::in_place, *mpDoc,
rPos, rTest, true);
+ pFCell->SetLimitString(true);
- SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
+ 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
+ {
+ return false;
+ }
+ }
+ else
+ {
+ pFormatter = GetDocument()->GetFormatTable();
- // get the value if any
- sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
- double nVal;
- bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
+ // get the value if any
+ nFormat = rPattern.GetNumberFormat(pFormatter);
+ bIsVal = pFormatter->IsNumberFormat(rTest, nFormat, nVal);
+ rStrResult = rTest;
+ }
bool bRet;
if (SC_VALID_TEXTLEN == eDataMode)
{
if (!bIsVal)
- bRet = IsDataValidTextLen( rTest, rPos, nullptr);
+ bRet = IsDataValidTextLen( rStrResult, rPos, nullptr);
else
{
ScValidationDataIsNumeric aDataNumeric( nVal, pFormatter, nFormat);
- bRet = IsDataValidTextLen( rTest, rPos, &aDataNumeric);
+ bRet = IsDataValidTextLen( rStrResult, rPos, &aDataNumeric);
}
}
else
@@ -555,7 +696,7 @@ bool ScValidationData::IsDataValid(
}
else
{
- svl::SharedString aSS = mpDoc->GetSharedStringPool().intern(rTest);
+ svl::SharedString aSS = mpDoc->GetSharedStringPool().intern(
rStrResult );
ScRefCellValue aTmpCell(&aSS);
bRet = IsDataValid(aTmpCell, rPos);
}
