[Libreoffice-commits] core.git: sc/inc sc/source sc/uiconfig
Marco Cecchetti
marco.cecchetti at collabora.com
Thu May 31 17:08:47 UTC 2018
sc/inc/strings.hrc | 1
sc/inc/validat.hxx | 20 +++++++++
sc/source/core/data/conditio.cxx | 3 +
sc/source/core/data/validat.cxx | 54 ++++++++++++++++++++++---
sc/source/filter/excel/xicontent.cxx | 9 ++++
sc/source/filter/oox/worksheethelper.cxx | 6 ++
sc/source/filter/xml/XMLStylesExportHelper.cxx | 6 ++
sc/source/filter/xml/xmlcvali.cxx | 1
sc/source/ui/app/inputhdl.cxx | 12 +++++
sc/source/ui/dbgui/validate.cxx | 26 +++++++++---
sc/source/ui/inc/validate.hxx | 1
sc/source/ui/view/cellsh2.cxx | 2
sc/uiconfig/scalc/ui/validationcriteriapage.ui | 4 +
13 files changed, 130 insertions(+), 15 deletions(-)
New commits:
commit f15c4b0f3c2cdfa7a72c97301ff32cc1764c54da
Author: Marco Cecchetti <marco.cecchetti at collabora.com>
Date: Thu Feb 1 12:28:54 2018 +0100
tdf#96698 - calc: add support for custom validation (through a formula)
Now it is possible to select a `custom` validation in the validation
dialog: this type of validation let's the user to define a formula,
the cell content is valid when the formula is evaluted to true, and
not valid when evaluated to false.
The `cutom` validation is correctly saved and restored for ods
documents, and is correctly imported and exported to xlsx documents
This patch contains an adaptation of a preliminary work of Justin Luth
for importing custom validation from xlsx documents. Thanks Justin!
Change-Id: Idc26654ba69a6f73d1b208d63acdad4b880c776d
Reviewed-on: https://gerrit.libreoffice.org/49084
Reviewed-by: Michael Meeks <michael.meeks at collabora.com>
Tested-by: Jenkins <ci at libreoffice.org>
Reviewed-by: Eike Rathke <erack at redhat.com>
diff --git a/sc/inc/strings.hrc b/sc/inc/strings.hrc
index ed0fc8df5663..ad3a8fa583dd 100644
--- a/sc/inc/strings.hrc
+++ b/sc/inc/strings.hrc
@@ -65,6 +65,7 @@
#define SCSTR_VALID_MINIMUM NC_("SCSTR_VALID_MINIMUM", "~Minimum")
#define SCSTR_VALID_MAXIMUM NC_("SCSTR_VALID_MAXIMUM", "~Maximum")
#define SCSTR_VALID_VALUE NC_("SCSTR_VALID_VALUE", "~Value")
+#define SCSTR_VALID_FORMULA NC_("SCSTR_VALID_FORMULA", "~Formula")
#define SCSTR_VALID_RANGE NC_("SCSTR_VALID_RANGE", "~Source")
#define SCSTR_VALID_LIST NC_("SCSTR_VALID_LIST", "~Entries")
// for dialogues:
diff --git a/sc/inc/validat.hxx b/sc/inc/validat.hxx
index bc13663a7e6d..92e87901fc9d 100644
--- a/sc/inc/validat.hxx
+++ b/sc/inc/validat.hxx
@@ -132,6 +132,26 @@ public:
bool IsDataValid(
const OUString& rTest, const ScPatternAttr& rPattern, const ScAddress& rPos ) const;
+ // Custom validations (SC_VALID_CUSTOM) should be validated using this specific method.
+ // Take care that internally this method commits to the to be validated cell the new input,
+ // in order to be able to interpret the validating boolean formula on the new input.
+ // After the formula has been evaluated the original cell content is restored.
+ // At present is only used in ScInputHandler::EnterHandler: handling this case in the
+ // regular IsDataValid method would have been unsafe since it can be invoked
+ // by ScFormulaCell::InterpretTail.
+
+ struct CustomValidationPrivateAccess
+ {
+ // so IsDataValidCustom can be invoked only by ScInputHandler methods
+ friend class ScInputHandler;
+ private:
+ CustomValidationPrivateAccess() {}
+ };
+
+ bool IsDataValidCustom(
+ const OUString& rTest, const ScPatternAttr& rPattern,
+ const ScAddress& rPos, const CustomValidationPrivateAccess& ) const;
+
bool IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos ) const;
// TRUE -> break
diff --git a/sc/source/core/data/conditio.cxx b/sc/source/core/data/conditio.cxx
index d07c98ebbf8d..173aa43dc763 100644
--- a/sc/source/core/data/conditio.cxx
+++ b/sc/source/core/data/conditio.cxx
@@ -1242,6 +1242,9 @@ bool ScConditionEntry::IsCellValid( ScRefCellValue& rCell, const ScAddress& rPos
{
const_cast<ScConditionEntry*>(this)->Interpret(rPos); // Evaluate formula
+ if ( eOp == ScConditionMode::Direct )
+ return nVal1 != 0.0;
+
double nArg = 0.0;
OUString aArgStr;
bool bVal = lcl_GetCellContent( rCell, bIsStr1, nArg, aArgStr, mpDoc );
diff --git a/sc/source/core/data/validat.cxx b/sc/source/core/data/validat.cxx
index 01e1fa41ca38..813c9940ff9f 100644
--- a/sc/source/core/data/validat.cxx
+++ b/sc/source/core/data/validat.cxx
@@ -426,6 +426,51 @@ bool ScValidationData::DoError(weld::Window* pParent, const OUString& rInput,
return ( eErrorStyle == SC_VALERR_STOP || nRet == RET_CANCEL );
}
+bool ScValidationData::IsDataValidCustom(
+ const OUString& rTest,
+ const ScPatternAttr& rPattern,
+ const ScAddress& rPos,
+ const CustomValidationPrivateAccess& ) const
+{
+ OSL_ENSURE(GetDataMode() == SC_VALID_CUSTOM,
+ "ScValidationData::IsDataValidCustom invoked for a non-custom validation");
+
+ 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 = GetDocument()->GetFormatTable();
+
+ // get the value if any
+ sal_uInt32 nFormat = rPattern.GetNumberFormat( pFormatter );
+ double nVal;
+ bool bIsVal = pFormatter->IsNumberFormat( rTest, nFormat, nVal );
+
+ ScRefCellValue aTmpCell;
+ svl::SharedString aSS;
+ if (bIsVal)
+ {
+ aTmpCell.meType = CELLTYPE_VALUE;
+ aTmpCell.mfValue = nVal;
+ }
+ else
+ {
+ aTmpCell.meType = CELLTYPE_STRING;
+ aSS = mpDoc->GetSharedStringPool().intern(rTest);
+ aTmpCell.mpString = &aSS;
+ }
+
+ ScCellValue aOriginalCellValue(ScRefCellValue(*GetDocument(), rPos));
+
+ aTmpCell.commit(*GetDocument(), rPos);
+ bool bRet = IsCellValid(aTmpCell, rPos);
+ aOriginalCellValue.commit(*GetDocument(), rPos);
+
+ return bRet;
+}
+
bool ScValidationData::IsDataValid(
const OUString& rTest, const ScPatternAttr& rPattern, const ScAddress& rPos ) const
{
@@ -487,6 +532,9 @@ bool ScValidationData::IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos
if( eDataMode == SC_VALID_LIST )
return IsListValid(rCell, rPos);
+ if ( eDataMode == SC_VALID_CUSTOM )
+ return IsCellValid(rCell, rPos);
+
double nVal = 0.0;
OUString aString;
bool bIsVal = true;
@@ -535,12 +583,6 @@ bool ScValidationData::IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos
bOk = IsCellValid(rCell, rPos);
break;
- case SC_VALID_CUSTOM:
- // for Custom, it must be eOp == ScConditionMode::Direct
- //TODO: the value must be in the document !!!
- bOk = IsCellValid(rCell, rPos);
- break;
-
case SC_VALID_TEXTLEN:
bOk = !bIsVal; // only Text
if ( bOk )
diff --git a/sc/source/filter/excel/xicontent.cxx b/sc/source/filter/excel/xicontent.cxx
index 91244a77bca1..f8a98508b4cd 100644
--- a/sc/source/filter/excel/xicontent.cxx
+++ b/sc/source/filter/excel/xicontent.cxx
@@ -886,6 +886,15 @@ void XclImpValidationManager::ReadDV( XclImpStream& rStrm )
// No valid validation found. Bail out.
return;
+ // The default value for comparision is _BETWEEN. However, custom
+ // rules are a formula, and thus the comparator should be ignored
+ // and only a true or false from the formula is evaluated. In Calc,
+ // formulas use comparison SC_COND_DIRECT.
+ if( eValMode == SC_VALID_CUSTOM )
+ {
+ eCondMode = ScConditionMode::Direct;
+ }
+
// first range for base address for relative references
const ScRange& rScRange = aScRanges.front(); // aScRanges is not empty
diff --git a/sc/source/filter/oox/worksheethelper.cxx b/sc/source/filter/oox/worksheethelper.cxx
index 907e7b399753..f2f2d211030a 100644
--- a/sc/source/filter/oox/worksheethelper.cxx
+++ b/sc/source/filter/oox/worksheethelper.cxx
@@ -25,6 +25,7 @@
#include <com/sun/star/awt/Point.hpp>
#include <com/sun/star/awt/Size.hpp>
#include <com/sun/star/drawing/XDrawPageSupplier.hpp>
+#include <com/sun/star/sheet/ConditionOperator2.hpp>
#include <com/sun/star/sheet/TableValidationVisibility.hpp>
#include <com/sun/star/sheet/ValidationType.hpp>
#include <com/sun/star/sheet/ValidationAlertStyle.hpp>
@@ -1124,7 +1125,10 @@ void WorksheetGlobals::finalizeValidationRanges() const
{
// condition operator
Reference< XSheetCondition2 > xSheetCond( xValidation, UNO_QUERY_THROW );
- xSheetCond->setConditionOperator( CondFormatBuffer::convertToApiOperator( validation.mnOperator ) );
+ if( eType == ValidationType_CUSTOM )
+ xSheetCond->setConditionOperator( ConditionOperator2::FORMULA );
+ else
+ xSheetCond->setConditionOperator( CondFormatBuffer::convertToApiOperator( validation.mnOperator ) );
// condition formulas
Reference< XMultiFormulaTokens > xTokens( xValidation, UNO_QUERY_THROW );
diff --git a/sc/source/filter/xml/XMLStylesExportHelper.cxx b/sc/source/filter/xml/XMLStylesExportHelper.cxx
index e2c87e4c7073..57accced6e18 100644
--- a/sc/source/filter/xml/XMLStylesExportHelper.cxx
+++ b/sc/source/filter/xml/XMLStylesExportHelper.cxx
@@ -201,12 +201,18 @@ OUString ScMyValidationsContainer::GetCondition(ScXMLExport& rExport, const ScMy
case sheet::ValidationType_WHOLE :
sCondition += "cell-content-is-whole-number()";
break;
+ case sheet::ValidationType_CUSTOM :
+ sCondition += "is-true-formula(";
+ sCondition += aValidation.sFormula1;
+ sCondition += ")";
+ break;
default:
{
// added to avoid warnings
}
}
if (aValidation.aValidationType != sheet::ValidationType_LIST &&
+ aValidation.aValidationType != sheet::ValidationType_CUSTOM &&
(!aValidation.sFormula1.isEmpty() ||
((aValidation.aOperator == sheet::ConditionOperator_BETWEEN ||
aValidation.aOperator == sheet::ConditionOperator_NOT_BETWEEN) &&
diff --git a/sc/source/filter/xml/xmlcvali.cxx b/sc/source/filter/xml/xmlcvali.cxx
index 69473160ecee..b91b2f2d0048 100644
--- a/sc/source/filter/xml/xmlcvali.cxx
+++ b/sc/source/filter/xml/xmlcvali.cxx
@@ -326,6 +326,7 @@ void ScXMLContentValidationContext::GetCondition( ScMyImportValidation& rValidat
case XML_COND_TEXTLENGTH_ISBETWEEN: // condition is 'cell-content-text-length-is-between(<expression1>,<expression2>)'
case XML_COND_TEXTLENGTH_ISNOTBETWEEN: // condition is 'cell-content-text-length-is-not-between(<expression1>,<expression2>)'
case XML_COND_ISINLIST: // condition is 'cell-content-is-in-list(<expression>)'
+ case XML_COND_ISTRUEFORMULA: // condition is 'is-true-formula(<expression>)'
rValidation.aValidationType = aParseResult.meValidation;
rValidation.aOperator = aParseResult.meOperator;
break;
diff --git a/sc/source/ui/app/inputhdl.cxx b/sc/source/ui/app/inputhdl.cxx
index 54f6acb4bbeb..25adf2963ec1 100644
--- a/sc/source/ui/app/inputhdl.cxx
+++ b/sc/source/ui/app/inputhdl.cxx
@@ -2658,7 +2658,17 @@ void ScInputHandler::EnterHandler( ScEnterMode nBlockMode )
{
// #i67990# don't use pLastPattern in EnterHandler
const ScPatternAttr* pPattern = pDoc->GetPattern( aCursorPos.Col(), aCursorPos.Row(), aCursorPos.Tab() );
- bool bOk = pData->IsDataValid( aString, *pPattern, aCursorPos );
+
+ bool bOk;
+
+ if (pData->GetDataMode() == SC_VALID_CUSTOM)
+ {
+ bOk = pData->IsDataValidCustom( aString, *pPattern, aCursorPos, ScValidationData::CustomValidationPrivateAccess() );
+ }
+ else
+ {
+ bOk = pData->IsDataValid( aString, *pPattern, aCursorPos );
+ }
if (!bOk)
{
diff --git a/sc/source/ui/dbgui/validate.cxx b/sc/source/ui/dbgui/validate.cxx
index 1161b2a90e48..99c5b05132ea 100644
--- a/sc/source/ui/dbgui/validate.cxx
+++ b/sc/source/ui/dbgui/validate.cxx
@@ -59,6 +59,7 @@
#define SC_VALIDDLG_ALLOW_RANGE 5
#define SC_VALIDDLG_ALLOW_LIST 6
#define SC_VALIDDLG_ALLOW_TEXTLEN 7
+#define SC_VALIDDLG_ALLOW_CUSTOM 8
/* Position indexes for "Data" list box.
They do not map directly to ScConditionMode and can safely be modified to
@@ -71,6 +72,7 @@
#define SC_VALIDDLG_DATA_NOTEQUAL 5
#define SC_VALIDDLG_DATA_VALIDRANGE 6
#define SC_VALIDDLG_DATA_INVALIDRANGE 7
+#define SC_VALIDDLG_DATA_DIRECT 8
namespace ValidListType = css::sheet::TableValidationVisibility;
@@ -200,7 +202,7 @@ sal_uInt16 lclGetPosFromValMode( ScValidationMode eValMode )
case SC_VALID_TIME: nLbPos = SC_VALIDDLG_ALLOW_TIME; break;
case SC_VALID_TEXTLEN: nLbPos = SC_VALIDDLG_ALLOW_TEXTLEN; break;
case SC_VALID_LIST: nLbPos = SC_VALIDDLG_ALLOW_RANGE; break;
- case SC_VALID_CUSTOM: nLbPos = SC_VALIDDLG_ALLOW_ANY; break; // not supported
+ case SC_VALID_CUSTOM: nLbPos = SC_VALIDDLG_ALLOW_CUSTOM; break;
default: OSL_FAIL( "lclGetPosFromValMode - unknown validity mode" );
}
return nLbPos;
@@ -220,6 +222,7 @@ ScValidationMode lclGetValModeFromPos( sal_uInt16 nLbPos )
case SC_VALIDDLG_ALLOW_RANGE: eValMode = SC_VALID_LIST; break;
case SC_VALIDDLG_ALLOW_LIST: eValMode = SC_VALID_LIST; break;
case SC_VALIDDLG_ALLOW_TEXTLEN: eValMode = SC_VALID_TEXTLEN; break;
+ case SC_VALIDDLG_ALLOW_CUSTOM: eValMode = SC_VALID_CUSTOM; break;
default: OSL_FAIL( "lclGetValModeFromPos - invalid list box position" );
}
return eValMode;
@@ -240,6 +243,7 @@ sal_uInt16 lclGetPosFromCondMode( ScConditionMode eCondMode )
case ScConditionMode::NotEqual: nLbPos = SC_VALIDDLG_DATA_NOTEQUAL; break;
case ScConditionMode::Between: nLbPos = SC_VALIDDLG_DATA_VALIDRANGE; break;
case ScConditionMode::NotBetween: nLbPos = SC_VALIDDLG_DATA_INVALIDRANGE; break;
+ case ScConditionMode::Direct: nLbPos = SC_VALIDDLG_DATA_DIRECT; break;
default: OSL_FAIL( "lclGetPosFromCondMode - unknown condition mode" );
}
return nLbPos;
@@ -259,6 +263,7 @@ ScConditionMode lclGetCondModeFromPos( sal_uInt16 nLbPos )
case SC_VALIDDLG_DATA_NOTEQUAL: eCondMode = ScConditionMode::NotEqual; break;
case SC_VALIDDLG_DATA_VALIDRANGE: eCondMode = ScConditionMode::Between; break;
case SC_VALIDDLG_DATA_INVALIDRANGE: eCondMode = ScConditionMode::NotBetween; break;
+ case SC_VALIDDLG_DATA_DIRECT: eCondMode = ScConditionMode::Direct; break;
default: OSL_FAIL( "lclGetCondModeFromPos - invalid list box position" );
}
return eCondMode;
@@ -323,6 +328,7 @@ ScTPValidationValue::ScTPValidationValue( vcl::Window* pParent, const SfxItemSet
, maStrMin(ScResId(SCSTR_VALID_MINIMUM))
, maStrMax(ScResId(SCSTR_VALID_MAXIMUM))
, maStrValue(ScResId(SCSTR_VALID_VALUE))
+ , maStrFormula(ScResId(SCSTR_VALID_FORMULA))
, maStrRange(ScResId(SCSTR_VALID_RANGE))
, maStrList(ScResId(SCSTR_VALID_LIST))
, m_pRefEdit(nullptr)
@@ -464,10 +470,14 @@ bool ScTPValidationValue::FillItemSet( SfxItemSet* rArgSet )
(m_pCbSort->IsChecked() ? ValidListType::SORTEDASCENDING : ValidListType::UNSORTED) :
ValidListType::INVISIBLE;
+ const sal_Int32 nLbPos = m_pLbAllow->GetSelectedEntryPos();
+ bool bCustom = (nLbPos == SC_VALIDDLG_ALLOW_CUSTOM);
+ ScConditionMode eCondMode = bCustom ?
+ ScConditionMode::Direct : lclGetCondModeFromPos( m_pLbValue->GetSelectedEntryPos() );
+
rArgSet->Put( SfxAllEnumItem( FID_VALID_MODE, sal::static_int_cast<sal_uInt16>(
- lclGetValModeFromPos( m_pLbAllow->GetSelectedEntryPos() ) ) ) );
- rArgSet->Put( SfxAllEnumItem( FID_VALID_CONDMODE, sal::static_int_cast<sal_uInt16>(
- lclGetCondModeFromPos( m_pLbValue->GetSelectedEntryPos() ) ) ) );
+ lclGetValModeFromPos( nLbPos ) ) ) );
+ rArgSet->Put( SfxAllEnumItem( FID_VALID_CONDMODE, sal::static_int_cast<sal_uInt16>( eCondMode ) ) );
rArgSet->Put( SfxStringItem( FID_VALID_VALUE1, GetFirstFormula() ) );
rArgSet->Put( SfxStringItem( FID_VALID_VALUE2, GetSecondFormula() ) );
rArgSet->Put( SfxBoolItem( FID_VALID_BLANK, m_pCbAllow->IsChecked() ) );
@@ -609,6 +619,7 @@ IMPL_LINK_NOARG(ScTPValidationValue, SelectHdl, ListBox&, void)
bool bEnable = (nLbPos != SC_VALIDDLG_ALLOW_ANY);
bool bRange = (nLbPos == SC_VALIDDLG_ALLOW_RANGE);
bool bList = (nLbPos == SC_VALIDDLG_ALLOW_LIST);
+ bool bCustom = (nLbPos == SC_VALIDDLG_ALLOW_CUSTOM);
m_pCbAllow->Enable( bEnable ); // Empty cell
m_pFtValue->Enable( bEnable );
@@ -620,10 +631,13 @@ IMPL_LINK_NOARG(ScTPValidationValue, SelectHdl, ListBox&, void)
m_pEdMax->Enable( bEnable );
bool bShowMax = false;
+
if( bRange )
m_pFtMin->SetText( maStrRange );
else if( bList )
m_pFtMin->SetText( maStrList );
+ else if( bCustom )
+ m_pFtMin->SetText( maStrFormula );
else
{
switch( m_pLbValue->GetSelectedEntryPos() )
@@ -647,8 +661,8 @@ IMPL_LINK_NOARG(ScTPValidationValue, SelectHdl, ListBox&, void)
m_pCbShow->Show( bRange || bList );
m_pCbSort->Show( bRange || bList );
- m_pFtValue->Show( !bRange && !bList );
- m_pLbValue->Show( !bRange && !bList );
+ m_pFtValue->Show( !bRange && !bList && !bCustom);
+ m_pLbValue->Show( !bRange && !bList && !bCustom );
m_pEdMin->Show( !bList );
m_pEdList->Show( bList );
m_pMinGrid->set_vexpand( bList );
diff --git a/sc/source/ui/inc/validate.hxx b/sc/source/ui/inc/validate.hxx
index e05e3ec63b21..b32aad732baf 100644
--- a/sc/source/ui/inc/validate.hxx
+++ b/sc/source/ui/inc/validate.hxx
@@ -119,6 +119,7 @@ private:
OUString maStrMin;
OUString maStrMax;
OUString maStrValue;
+ OUString maStrFormula;
OUString maStrRange;
OUString maStrList;
sal_Unicode mcFmlaSep; /// List separator in formulas.
diff --git a/sc/source/ui/view/cellsh2.cxx b/sc/source/ui/view/cellsh2.cxx
index bbab68d2e952..632dad30442c 100644
--- a/sc/source/ui/view/cellsh2.cxx
+++ b/sc/source/ui/view/cellsh2.cxx
@@ -878,7 +878,7 @@ void ScCellShell::ExecuteDB( SfxRequest& rReq )
}
// cell range picker
- ScopedVclPtrInstance<ScValidationDlg> pDlg(nullptr, &aArgSet, pTabViewShell);
+ ScopedVclPtrInstance<ScValidationDlg> pDlg(GetViewData()->GetActiveWin(), &aArgSet, pTabViewShell);
short nResult = pDlg->Execute();
if ( nResult == RET_OK )
diff --git a/sc/uiconfig/scalc/ui/validationcriteriapage.ui b/sc/uiconfig/scalc/ui/validationcriteriapage.ui
index 46ecc2464d0d..2bf0bab17955 100644
--- a/sc/uiconfig/scalc/ui/validationcriteriapage.ui
+++ b/sc/uiconfig/scalc/ui/validationcriteriapage.ui
@@ -43,6 +43,10 @@
<col id="0" translatable="yes" context="validationcriteriapage|liststore1">Text length</col>
<col id="1">7</col>
</row>
+ <row>
+ <col id="0" translatable="yes" context="validationcriteriapage|liststore1">Custom</col>
+ <col id="1">8</col>
+ </row>
</data>
</object>
<object class="GtkListStore" id="liststore2">
More information about the Libreoffice-commits
mailing list