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>
diff --git a/sc/inc/validat.hxx b/sc/inc/validat.hxx
index 79eb9b7..6f64b84 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 2d6194b..4db07b0 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.
