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@collabora.com>
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Eike Rathke <erack@redhat.com>

13 files changed, 130 insertions, 15 deletions

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">