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 <kelemeng@ubuntu.com>
Reviewed-by: Gabor Kelemen <kelemeng@ubuntu.com>
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>
(cherry picked from commit 5f2d7db094fc0f4e7ae40987c3c6762b11184419)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/146084
Reviewed-by: Xisco Fauli <xiscofauli@libreoffice.org>
diff --git a/sc/qa/uitest/validity/tdf150098.py b/sc/qa/uitest/validity/tdf150098.py
new file mode 100644
index 0000000..5d29a4a
--- /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 79f21d8..2d6194b 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>
@@ -435,15 +436,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;
@@ -453,7 +524,7 @@ bool ScValidationData::IsDataValidCustom(
}
else
{
aSS = mpDoc->GetSharedStringPool().intern(rTest);
aSS = mpDoc->GetSharedStringPool().intern(rStrResult);
aTmpCell = ScRefCellValue(&aSS);
}
@@ -522,25 +593,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;
}
// get the value if any
sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
double nVal;
bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
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;
}
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
@@ -552,7 +693,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);
}
