sc/inc/sc.hrc | 1 sc/source/core/data/validat.cxx | 46 +++++++++++++++++++++++-- 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/dbgui/validate.cxx | 27 ++++++++++---- sc/source/ui/inc/validate.hxx | 1 sc/source/ui/view/cellsh2.cxx | 2 - sc/uiconfig/scalc/ui/validationcriteriapage.ui | 4 ++ 10 files changed, 91 insertions(+), 12 deletions(-)
New commits: commit 935552e403da4ec93ce74b28e6cb9997560fc781 Author: Marco Cecchetti <marco.cecche...@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/49979 Reviewed-by: Jan Holesovsky <ke...@collabora.com> Tested-by: Jan Holesovsky <ke...@collabora.com> diff --git a/sc/inc/sc.hrc b/sc/inc/sc.hrc index 64ae387c84e3..20849ac3cc90 100644 --- a/sc/inc/sc.hrc +++ b/sc/inc/sc.hrc @@ -954,6 +954,7 @@ #define SCSTR_VALID_LIST (STR_START + 318) #define SCSTR_SELECT (STR_START + 319) +#define SCSTR_VALID_FORMULA (STR_START + 320) // media shell #define SCSTR_MEDIASHELL (STR_START + 401) diff --git a/sc/source/core/data/validat.cxx b/sc/source/core/data/validat.cxx index acc192fcc61e..f311d152a666 100644 --- a/sc/source/core/data/validat.cxx +++ b/sc/source/core/data/validat.cxx @@ -524,9 +524,49 @@ bool ScValidationData::IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos break; case SC_VALID_CUSTOM: - // for Custom, it must be eOp == SC_COND_DIRECT - //TODO: the value must be in the document !!! - bOk = IsCellValid(rCell, rPos); + { + // for Custom, it must be eOp == ScConditionMode::Direct + // the value must be in the document !!! + + // so we save the original value + OUString aStrVal = mpDoc->GetString(rPos); + svl::SharedString aSS = mpDoc->GetSharedStringPool().intern(aStrVal); + std::unique_ptr<EditTextObject> pEditTextVal; + std::unique_ptr<ScFormulaCell> pFormulaVal; + + ScRefCellValue aOriginalCellValue; + aOriginalCellValue.meType = mpDoc->GetCellType(rPos); + switch (aOriginalCellValue.meType) + { + case CELLTYPE_VALUE: + aOriginalCellValue.mfValue = mpDoc->GetValue(rPos); + break; + case CELLTYPE_STRING: + aOriginalCellValue.mpString = &aSS; + break; + case CELLTYPE_EDIT: + { + pEditTextVal.reset(new EditTextObject(*(mpDoc->GetEditText(rPos)))); + aOriginalCellValue.mpEditText = pEditTextVal.get(); + } + break; + case CELLTYPE_FORMULA: + { + pFormulaVal.reset(mpDoc->GetFormulaCell(rPos)->Clone()); + aOriginalCellValue.mpFormula = pFormulaVal.get(); + } + break; + case CELLTYPE_NONE: + break; + } + + // set cell value to current input + rCell.commit(*mpDoc, rPos); + // check if the new value is valid + bOk = IsCellValid(rCell, rPos); + // and restore the original value + aOriginalCellValue.commit(*mpDoc, rPos); + } break; case SC_VALID_TEXTLEN: diff --git a/sc/source/filter/excel/xicontent.cxx b/sc/source/filter/excel/xicontent.cxx index c1226bcfaf21..1c0503e3fa7c 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 = SC_COND_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 978571508a08..6f75635dd32f 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> @@ -1131,7 +1132,10 @@ void WorksheetGlobals::finalizeValidationRanges() const { // condition operator Reference< XSheetCondition2 > xSheetCond( xValidation, UNO_QUERY_THROW ); - xSheetCond->setConditionOperator( CondFormatBuffer::convertToApiOperator( aIt->mnOperator ) ); + if( eType == ValidationType_CUSTOM ) + xSheetCond->setConditionOperator( ConditionOperator2::FORMULA ); + else + xSheetCond->setConditionOperator( CondFormatBuffer::convertToApiOperator( aIt->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 06dd75fd522c..5d4734c9912c 100644 --- a/sc/source/filter/xml/XMLStylesExportHelper.cxx +++ b/sc/source/filter/xml/XMLStylesExportHelper.cxx @@ -205,12 +205,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 8fdd9af90848..2220ffe6257f 100644 --- a/sc/source/filter/xml/xmlcvali.cxx +++ b/sc/source/filter/xml/xmlcvali.cxx @@ -339,6 +339,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/dbgui/validate.cxx b/sc/source/ui/dbgui/validate.cxx index d496c6f6d8cf..15935623d7f9 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 SC_COND_NOTEQUAL: nLbPos = SC_VALIDDLG_DATA_NOTEQUAL; break; case SC_COND_BETWEEN: nLbPos = SC_VALIDDLG_DATA_VALIDRANGE; break; case SC_COND_NOTBETWEEN: nLbPos = SC_VALIDDLG_DATA_INVALIDRANGE; break; + case SC_COND_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 = SC_COND_NOTEQUAL; break; case SC_VALIDDLG_DATA_VALIDRANGE: eCondMode = SC_COND_BETWEEN; break; case SC_VALIDDLG_DATA_INVALIDRANGE: eCondMode = SC_COND_NOTBETWEEN; break; + case SC_VALIDDLG_DATA_DIRECT: eCondMode = SC_COND_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,13 @@ bool ScTPValidationValue::FillItemSet( SfxItemSet* rArgSet ) (m_pCbSort->IsChecked() ? ValidListType::SORTEDASCENDING : ValidListType::UNSORTED) : ValidListType::INVISIBLE; - rArgSet->Put( SfxAllEnumItem( FID_VALID_MODE, sal::static_int_cast<sal_uInt16>( - lclGetValModeFromPos( m_pLbAllow->GetSelectEntryPos() ) ) ) ); - rArgSet->Put( SfxAllEnumItem( FID_VALID_CONDMODE, sal::static_int_cast<sal_uInt16>( - lclGetCondModeFromPos( m_pLbValue->GetSelectEntryPos() ) ) ) ); + const sal_Int32 nLbPos = m_pLbAllow->GetSelectEntryPos(); + bool bCustom = (nLbPos == SC_VALIDDLG_ALLOW_CUSTOM); + ScConditionMode eCondMode = bCustom ? + SC_COND_DIRECT : lclGetCondModeFromPos( m_pLbValue->GetSelectEntryPos() ); + + rArgSet->Put( SfxAllEnumItem( FID_VALID_MODE, sal::static_int_cast<sal_uInt16>( 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 +618,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 +630,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->GetSelectEntryPos() ) @@ -647,8 +660,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 350e32a612c8..270b2fd00990 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 c3073f36c173..b492633ed875 100644 --- a/sc/source/ui/view/cellsh2.cxx +++ b/sc/source/ui/view/cellsh2.cxx @@ -855,7 +855,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 9c5efefdb16b..4954a20a263e 100644 --- a/sc/uiconfig/scalc/ui/validationcriteriapage.ui +++ b/sc/uiconfig/scalc/ui/validationcriteriapage.ui @@ -43,6 +43,10 @@ <col id="0" translatable="yes">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"> _______________________________________________ Libreoffice-commits mailing list libreoffice-comm...@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits