tdf#137624 sc: autofill mixed sequences in merged cells

Improve FillAnalyse, FillSeries to continue linear sequences
of mixed values in merged cells (mixed values are string + number,
like 'a1','a2' or '1a').

It discovers the linear sequence only if the string parts are
identical. It recognizes user lists only if all elements are in
the same user list.

Co-authored-by: Tibor Nagy (NISZ)

Change-Id: I8810a0f1d637436222e3d0b9219da38ccb7c6346
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/105784
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>
(cherry picked from commit 09eccdc87db5258ea779e2bcc12437f802624ee5)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/111583
Tested-by: Gabor Kelemen <kelemen.gabor2@nisz.hu>
Reviewed-by: Gabor Kelemen <kelemen.gabor2@nisz.hu>

3 files changed, 105 insertions, 22 deletions

diff --git a/sc/qa/unit/copy_paste_test.cxx b/sc/qa/unit/copy_paste_test.cxx
index eb07928e05e4..9b0a472496a0 100644
--- a/sc/qa/unit/copy_paste_test.cxx
+++ b/sc/qa/unit/copy_paste_test.cxx
@@ -50,6 +50,7 @@ public:
     void tdf137205_autofillDatesInMergedCells();
     void tdf137653_137654_autofillUserlist();
     void tdf137625_autofillMergedUserlist();
+    void tdf137624_autofillMergedMixed();
 
     CPPUNIT_TEST_SUITE(ScCopyPasteTest);
     CPPUNIT_TEST(testCopyPasteXLS);
@@ -65,6 +66,7 @@ public:
     CPPUNIT_TEST(tdf137205_autofillDatesInMergedCells);
     CPPUNIT_TEST(tdf137653_137654_autofillUserlist);
     CPPUNIT_TEST(tdf137625_autofillMergedUserlist);
+    CPPUNIT_TEST(tdf137624_autofillMergedMixed);
     CPPUNIT_TEST_SUITE_END();
 
 private:
@@ -880,7 +882,57 @@ void ScCopyPasteTest::tdf137625_autofillMergedUserlist()
     }
 }
 
+void ScCopyPasteTest::tdf137624_autofillMergedMixed()
+{
+    ScDocShellRef xDocSh = loadDocAndSetupModelViewController("tdf137624_autofillMergedMixed.", FORMAT_ODS, true);
+    ScDocument& rDoc = xDocSh->GetDocument();
+
+    // Get the document controller
+    ScTabViewShell* pView = xDocSh->GetBestViewShell(false);
+    CPPUNIT_ASSERT(pView != nullptr);
+
+    // add 1aa,2bb,3cc,4dd,5ee,6ff to userlist, to test that autofill won't confuse it with 1aa,3aa
+    // delete every userlist to make sure there won't be any string that is in 2 different userlist
+    ScGlobal::GetUserList()->clear();
+    addToUserList({ "1aa,2bb,3cc,4dd,5ee,6ff" });
+
+    // fillauto mixed (string + number), these areas contain only merged cells
+    pView->FillAuto(FILL_TO_RIGHT, 7, 5, 12, 7, 6);   //H6:M8
+    pView->FillAuto(FILL_TO_LEFT, 7, 5, 12, 7, 6);    //H6:M8
+    pView->FillAuto(FILL_TO_BOTTOM, 1, 20, 3, 23, 4); //B21:D24
+    pView->FillAuto(FILL_TO_TOP, 1, 20, 3, 23, 4);    //B21:D24
 
+    // compare the results of fill-right / -left with the reference stored in the test file
+    // this compares the whole area blindly, for specific test cases, check the test file
+    for (int nCol = 1; nCol <= 18; nCol++)
+    {
+        for (int nRow = 5; nRow <= 7; nRow++)
+        {
+            CellType nType1 = rDoc.GetCellType(ScAddress(nCol, nRow, 0));
+            CellType nType2 = rDoc.GetCellType(ScAddress(nCol, nRow + 4, 0));
+            OUString aStr1 = rDoc.GetString(nCol, nRow, 0);
+            OUString aStr2 = rDoc.GetString(nCol, nRow + 4, 0);
+
+            CPPUNIT_ASSERT_EQUAL(nType1, nType2);
+            CPPUNIT_ASSERT_EQUAL(aStr1, aStr2);
+        }
+    }
+
+    // compare the results of fill-up / -down
+    for (int nCol = 1; nCol <= 3; nCol++)
+    {
+        for (int nRow = 16; nRow <= 27; nRow++)
+        {
+            CellType nType1 = rDoc.GetCellType(ScAddress(nCol, nRow, 0));
+            CellType nType2 = rDoc.GetCellType(ScAddress(nCol + 4, nRow, 0));
+            OUString aStr1 = rDoc.GetString(nCol, nRow, 0);
+            OUString aStr2 = rDoc.GetString(nCol + 4, nRow, 0);
+
+            CPPUNIT_ASSERT_EQUAL(nType1, nType2);
+            CPPUNIT_ASSERT_EQUAL(aStr1, aStr2);
+        }
+    }
+}
 
 ScCopyPasteTest::ScCopyPasteTest()
       : ScBootstrapFixture( "sc/qa/unit/data" )
diff --git a/sc/qa/unit/data/ods/tdf137624_autofillMergedMixed.ods b/sc/qa/unit/data/ods/tdf137624_autofillMergedMixed.ods
new file mode 100644
index 000000000000..cc1b6a7c6ab8
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf137624_autofillMergedMixed.ods
diff --git a/sc/source/core/data/table4.cxx b/sc/source/core/data/table4.cxx
index 16b6c1cbf35f..3daea8217cd4 100644
--- a/sc/source/core/data/table4.cxx
+++ b/sc/source/core/data/table4.cxx
@@ -419,25 +419,9 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
             }
             else if (eCellType == CELLTYPE_STRING || eCellType == CELLTYPE_EDIT)
             {
-                OUString aStr;
+                OUString aStr,aStr2;
                 GetString(nColCurr, nRowCurr, aStr);
 
-                bool bAllSame = true;
-                for (SCSIZE i = 0; i < nValueCount; ++i)
-                {
-                    OUString aTestStr;
-                    GetString(static_cast<SCCOL>(nCol1 + rNonOverlappedCellIdx[i] * nAddX),
-                              static_cast<SCROW>(nRow1 + rNonOverlappedCellIdx[i] * nAddY),
-                              aTestStr);
-                    if (aStr != aTestStr)
-                    {
-                        bAllSame = false;
-                        break;
-                    }
-                }
-                if (bAllSame && nValueCount > 1)
-                    return;
-
                 rListData = const_cast<ScUserListData*>(ScGlobal::GetUserList()->GetData(aStr));
                 if (rListData)
                 {
@@ -449,10 +433,10 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                     {
                         nColCurr = nCol1 + rNonOverlappedCellIdx[i] * nAddX;
                         nRowCurr = nRow1 + rNonOverlappedCellIdx[i] * nAddY;
-                        GetString(nColCurr, nRowCurr, aStr);
+                        GetString(nColCurr, nRowCurr, aStr2);
 
                         nPrevListIndex = rListIndex;
-                        if (!rListData->GetSubIndex(aStr, rListIndex, bMatchCase))
+                        if (!rListData->GetSubIndex(aStr2, rListIndex, bMatchCase))
                             rListData = nullptr;
                         else
                         {
@@ -471,7 +455,45 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                         return;
                     }
                 }
-                // TODO: check / handle if it is a string containing a number
+                short nFlag1, nFlag2;
+                sal_Int32 nVal1, nVal2;
+                nFlag1 = lcl_DecompValueString(aStr, nVal1, &rMinDigits);
+                if (nFlag1)
+                {
+                    bool bVal = true;
+                    rInc = 1;
+                    for (SCSIZE i = 1; i < nValueCount && bVal; i++)
+                    {
+                        nColCurr = nCol1 + rNonOverlappedCellIdx[i] * nAddX;
+                        nRowCurr = nRow1 + rNonOverlappedCellIdx[i] * nAddY;
+                        ScRefCellValue aCell = GetCellValue(nColCurr, nRowCurr);
+                        CellType eType = aCell.meType;
+                        if (eType == CELLTYPE_STRING || eType == CELLTYPE_EDIT)
+                        {
+                            aStr2 = aCell.getString(pDocument);
+                            nFlag2 = lcl_DecompValueString(aStr2, nVal2, &rMinDigits);
+                            if (nFlag1 == nFlag2 && aStr == aStr2)
+                            {
+                                double nDiff = approxDiff(nVal2, nVal1);
+                                if (i == 1)
+                                    rInc = nDiff;
+                                else if (!::rtl::math::approxEqual(nDiff, rInc, 13))
+                                    bVal = false;
+                                nVal1 = nVal2;
+                            }
+                            else
+                                bVal = false;
+                        }
+                        else
+                            bVal = false;
+                    }
+                    if (bVal)
+                    {
+                        rCmd = FILL_LINEAR;
+                        rSkipOverlappedCells = true;
+                        return;
+                    }
+                }
             }
         }
     }
@@ -2356,16 +2378,25 @@ void ScTable::FillSeries( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                     long nIndex = 0;
                     bool bError = false;
                     bool bOverflow = false;
+                    bool bNonEmpty = true;
 
                     bool bIsOrdinalSuffix = aValue == ScGlobal::GetOrdinalSuffix(
                                 static_cast<sal_Int32>(nStartVal));
 
+                    sal_Int32 nFillerIdx = 0;
+                    if (bSkipOverlappedCells && !aIsNonEmptyCell[0])
+                        --nIndex;
                     rInner = nIStart;
                     while (true)
                     {
+                        if (bSkipOverlappedCells)
+                        {
+                            nFillerIdx = (nFillerIdx + 1) % nFillerCount;
+                            bNonEmpty = aIsNonEmptyCell[nFillerIdx];
+                        }
                         if(!ColHidden(nCol) && !RowHidden(nRow))
                         {
-                            if (!bError)
+                            if (!bError && bNonEmpty)
                             {
                                 switch (eFillCmd)
                                 {
@@ -2396,7 +2427,7 @@ void ScTable::FillSeries( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
 
                             if (bError)
                                 aCol[nCol].SetError(static_cast<SCROW>(nRow), FormulaError::NoValue);
-                            else if (!bOverflow)
+                            else if (!bOverflow && bNonEmpty)
                             {
                                 nStringValue = static_cast<sal_Int32>(nVal);
                                 OUString aStr;