tdf#128797 sc: fix invalid detective marks cells

If numeric value of cell formatted as text or not and validation
set up as text length > 0 to ensure cells are filled.
When the detective is used to mark invalid data, the value of
cells are marked as invalid.

Co-authored-by: Attila Szűcs (NISZ)
Change-Id: I3481a6c999871f9a5cf73669d2ac73df1fc0ca20
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/102322
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>

3 files changed, 56 insertions, 4 deletions

diff --git a/sc/qa/unit/data/xlsx/textLengthDataValidity.xlsx b/sc/qa/unit/data/xlsx/textLengthDataValidity.xlsx
new file mode 100644
index 000000000000..a007fd9f7146
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/textLengthDataValidity.xlsx
diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx
index 4b8d999dc787..3962adb1ac85 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -280,6 +280,7 @@ public:
     void testShapeRotationImport();
     void testShapeDisplacementOnRotationImport();
     void testTextBoxBodyUpright();
+    void testTextLengthDataValidityXLSX();
 
     CPPUNIT_TEST_SUITE(ScFiltersTest);
     CPPUNIT_TEST(testBooleanFormatXLSX);
@@ -450,6 +451,7 @@ public:
     CPPUNIT_TEST(testShapeRotationImport);
     CPPUNIT_TEST(testShapeDisplacementOnRotationImport);
     CPPUNIT_TEST(testTextBoxBodyUpright);
+    CPPUNIT_TEST(testTextLengthDataValidityXLSX);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -4955,6 +4957,44 @@ void ScFiltersTest::testTextBoxBodyUpright()
     CPPUNIT_ASSERT_EQUAL(sal_Int32(90), nAngle);
 }
 
+void ScFiltersTest::testTextLengthDataValidityXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc("textLengthDataValidity.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load textLengthDataValidity.xlsx", xDocSh.is());
+
+    ScDocument& rDoc = xDocSh->GetDocument();
+
+    const ScValidationData* pData = rDoc.GetValidationEntry(1);
+
+    ScRefCellValue aCellA1; // A1 = 1234(numeric value)
+    ScAddress aValBaseAddrA1( 0,0,0 );
+    aCellA1.assign(rDoc, aValBaseAddrA1);
+    bool bValidA1 = pData->IsDataValid(aCellA1, aValBaseAddrA1);
+
+    ScRefCellValue aCellA2; // A2 = 1234(numeric value format as text)
+    ScAddress aValBaseAddrA2( 0,1,0 );
+    aCellA2.assign(rDoc, aValBaseAddrA2);
+    bool bValidA2 = pData->IsDataValid(aCellA2, aValBaseAddrA2);
+
+    ScRefCellValue aCellA3; // A3 = 1234.00(numeric value)
+    ScAddress aValBaseAddrA3( 0,2,0 );
+    aCellA3.assign(rDoc, aValBaseAddrA3);
+    bool bValidA3 = pData->IsDataValid(aCellA3, aValBaseAddrA3);
+
+    ScRefCellValue aCellA4; // A4 = 12.3(numeric value)
+    ScAddress aValBaseAddrA4( 0,3,0 );
+    aCellA4.assign(rDoc, aValBaseAddrA4);
+    bool bValidA4 = pData->IsDataValid(aCellA4, aValBaseAddrA4);
+
+    // True if text length = 4
+    CPPUNIT_ASSERT_EQUAL(true, bValidA1);
+    CPPUNIT_ASSERT_EQUAL(true, bValidA2);
+    CPPUNIT_ASSERT_EQUAL(true, bValidA3);
+    CPPUNIT_ASSERT_EQUAL(true, bValidA4);
+
+    xDocSh->DoClose();
+}
+
 ScFiltersTest::ScFiltersTest()
       : ScBootstrapFixture( "sc/qa/unit/data" )
 {
diff --git a/sc/source/core/data/validat.cxx b/sc/source/core/data/validat.cxx
index 33baa88b9d4e..2cb6a6aad909 100644
--- a/sc/source/core/data/validat.cxx
+++ b/sc/source/core/data/validat.cxx
@@ -589,14 +589,26 @@ bool ScValidationData::IsDataValid( ScRefCellValue& rCell, const ScAddress& rPos
             break;
 
         case SC_VALID_TEXTLEN:
+        {
+            double nLenVal;
             bOk = !bIsVal;          // only Text
             if ( bOk )
             {
-                double nLenVal = static_cast<double>(aString.getLength());
-                ScRefCellValue aTmpCell(nLenVal);
-                bOk = IsCellValid(aTmpCell, rPos);
+                nLenVal = static_cast<double>(aString.getLength());
             }
-            break;
+            else
+            {
+                const ScPatternAttr* pPattern
+                    = mpDoc->GetPattern(rPos.Col(), rPos.Row(), rPos.Tab());
+                SvNumberFormatter* pFormatter = GetDocument()->GetFormatTable();
+                sal_uInt32 nFormat = pPattern->GetNumberFormat(pFormatter);
+                pFormatter->GetInputLineString(nVal, nFormat, aString);
+                nLenVal = static_cast<double>(aString.getLength());
+            }
+            ScRefCellValue aTmpCell(nLenVal);
+            bOk = IsCellValid(aTmpCell, rPos);
+        }
+        break;
 
         default:
             OSL_FAIL("not yet done");