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 <kendy@collabora.com>
Tested-by: Jan Holesovsky <kendy@collabora.com>

10 files changed, 91 insertions, 12 deletions

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