tdf#137653 tdf#137624 sc: fix autofill user list sequences

Improve FillAuto, FillAnalyse to discover the step between values
of linear sequences of user list (sort list) values in 1x1 cells,
and continue it. (Sort lists are special string lists with values
like day names Monday, Tuesday, ... see
Tools->Options...->Calc->Sort list)

Note: The unit test is not language dependent, as it clears
all user lists and replace it with its own list. Also fixed
this in the unit test of tdf#137625.

Co-authored-by: Tibor Nagy (NISZ)

Change-Id: I1c8b0df9ad29f91a6080e56e5f2ebe0029a10a08
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/105259
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>

3 files changed, 110 insertions, 23 deletions

diff --git a/sc/qa/unit/copy_paste_test.cxx b/sc/qa/unit/copy_paste_test.cxx
index 131954a0eb2c..03d10e90aa08 100644
--- a/sc/qa/unit/copy_paste_test.cxx
+++ b/sc/qa/unit/copy_paste_test.cxx
@@ -19,6 +19,7 @@
 #include <viewfunc.hxx>
 #include <scitems.hxx>
 #include <attrib.hxx>
+#include <userlist.hxx>
 
 #include <com/sun/star/frame/Desktop.hpp>
 #include <com/sun/star/frame/XModel2.hpp>
@@ -47,6 +48,7 @@ public:
     void testTdf88782_autofillLinearNumbersInMergedCells();
     void tdf137621_autofillMergedBool();
     void tdf137205_autofillDatesInMergedCells();
+    void tdf137653_137654_autofillUserlist();
     void tdf137625_autofillMergedUserlist();
 
     CPPUNIT_TEST_SUITE(ScCopyPasteTest);
@@ -61,12 +63,14 @@ public:
     CPPUNIT_TEST(testTdf88782_autofillLinearNumbersInMergedCells);
     CPPUNIT_TEST(tdf137621_autofillMergedBool);
     CPPUNIT_TEST(tdf137205_autofillDatesInMergedCells);
+    CPPUNIT_TEST(tdf137653_137654_autofillUserlist);
     CPPUNIT_TEST(tdf137625_autofillMergedUserlist);
     CPPUNIT_TEST_SUITE_END();
 
 private:
 
     ScDocShellRef loadDocAndSetupModelViewController(const OUString& rFileName, sal_Int32 nFormat, bool bReadWrite);
+    void addToUserList(const OUString& rStr);
     uno::Reference<uno::XInterface> m_xCalcComponent;
 };
 
@@ -753,6 +757,69 @@ void ScCopyPasteTest::tdf137205_autofillDatesInMergedCells()
     }
 }
 
+void ScCopyPasteTest::addToUserList(const OUString& rStr)
+{
+    ScUserListData* aListData = new ScUserListData(rStr);
+    ScGlobal::GetUserList()->push_back(aListData);
+}
+
+void ScCopyPasteTest::tdf137653_137654_autofillUserlist()
+{
+    ScDocShellRef xDocSh = loadDocAndSetupModelViewController("tdf137653_137654_autofillUserlist.", FORMAT_ODS, true);
+    ScDocument& rDoc = xDocSh->GetDocument();
+
+    // Get the document controller
+    ScTabViewShell* pView = xDocSh->GetBestViewShell(false);
+    CPPUNIT_ASSERT(pView != nullptr);
+
+    // delete every userlist to make sure there won't be any string that is in 2 different userlist
+    ScGlobal::GetUserList()->clear();
+    addToUserList({ "January,February,March,April,May,June,July,August,September,October,November,December" });
+    const ScUserListData* pListData = ScGlobal::GetUserList()->GetData("January");
+    sal_uInt16 nIdx1 = 0, nIdx2 = 0;
+    bool bHasIdx1, bHasIdx2;
+    bool bMatchCase = false;
+
+    // fillauto userlist, these areas contain only merged cells
+    pView->FillAuto(FILL_TO_RIGHT, 4, 5, 6, 7, 3);   //E6:G8
+    pView->FillAuto(FILL_TO_LEFT, 4, 5, 6, 7, 3);    //E6:G8
+    pView->FillAuto(FILL_TO_BOTTOM, 1, 18, 3, 19, 2); //B19:D20
+    pView->FillAuto(FILL_TO_TOP, 1, 18, 3, 19, 2);    //B19:D20
+
+    // 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 <= 9; 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));
+            bHasIdx1 = pListData->GetSubIndex(rDoc.GetString(nCol, nRow, 0), nIdx1, bMatchCase);
+            bHasIdx2 = pListData->GetSubIndex(rDoc.GetString(nCol, nRow + 4, 0), nIdx2, bMatchCase);
+
+            CPPUNIT_ASSERT_EQUAL(nType1, nType2);
+            CPPUNIT_ASSERT(bHasIdx1 && bHasIdx2);
+            CPPUNIT_ASSERT_EQUAL(nIdx1, nIdx2);   // userlist index value of cells
+        }
+    }
+
+    // compare the results of fill-up / -down
+    for (int nCol = 1; nCol <= 3; nCol++)
+    {
+        for (int nRow = 16; nRow <= 21; nRow++)
+        {
+            CellType nType1 = rDoc.GetCellType(ScAddress(nCol, nRow, 0));
+            CellType nType2 = rDoc.GetCellType(ScAddress(nCol + 4, nRow, 0));
+            bHasIdx1 = pListData->GetSubIndex(rDoc.GetString(nCol, nRow, 0), nIdx1, bMatchCase);
+            bHasIdx2 = pListData->GetSubIndex(rDoc.GetString(nCol + 4, nRow, 0), nIdx2, bMatchCase);
+
+            CPPUNIT_ASSERT_EQUAL(nType1, nType2);
+            CPPUNIT_ASSERT(bHasIdx1 && bHasIdx2);
+            CPPUNIT_ASSERT_EQUAL(nIdx1, nIdx2);   // userlist index value of cells
+        }
+    }
+}
+
 void ScCopyPasteTest::tdf137625_autofillMergedUserlist()
 {
     ScDocShellRef xDocSh = loadDocAndSetupModelViewController("tdf137625_autofillMergedUserlist.", FORMAT_ODS, true);
@@ -762,6 +829,14 @@ void ScCopyPasteTest::tdf137625_autofillMergedUserlist()
     ScTabViewShell* pView = xDocSh->GetBestViewShell(false);
     CPPUNIT_ASSERT(pView != nullptr);
 
+    // delete every userlist to make sure there won't be any string that is in 2 different userlist
+    ScGlobal::GetUserList()->clear();
+    addToUserList({ "January,February,March,April,May,June,July,August,September,October,November,December" });
+    const ScUserListData* pListData = ScGlobal::GetUserList()->GetData("January");
+    sal_uInt16 nIdx1 = 0, nIdx2 = 0;
+    bool bHasIdx1, bHasIdx2;
+    bool bMatchCase = false;
+
     // fillauto userlist, 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
@@ -776,14 +851,13 @@ void ScCopyPasteTest::tdf137625_autofillMergedUserlist()
         {
             CellType nType1 = rDoc.GetCellType(ScAddress(nCol, nRow, 0));
             CellType nType2 = rDoc.GetCellType(ScAddress(nCol, nRow + 4, 0));
-            double* pValue1 = rDoc.GetValueCell(ScAddress(nCol, nRow, 0));
-            double* pValue2 = rDoc.GetValueCell(ScAddress(nCol, nRow + 4, 0));
+            bHasIdx1 = pListData->GetSubIndex(rDoc.GetString(nCol, nRow, 0), nIdx1, bMatchCase);
+            bHasIdx2 = pListData->GetSubIndex(rDoc.GetString(nCol, nRow + 4, 0), nIdx2, bMatchCase);
 
             CPPUNIT_ASSERT_EQUAL(nType1, nType2);
-            if (pValue2 != nullptr)
-                CPPUNIT_ASSERT_EQUAL(*pValue1, *pValue2);   //cells with userlist value
-            else
-                CPPUNIT_ASSERT_EQUAL(pValue1, pValue2);     //empty cells
+            CPPUNIT_ASSERT_EQUAL(bHasIdx1, bHasIdx2);
+            if (bHasIdx1)
+                CPPUNIT_ASSERT_EQUAL(nIdx1, nIdx2);   //cells with userlist value
         }
     }
 
@@ -794,14 +868,13 @@ void ScCopyPasteTest::tdf137625_autofillMergedUserlist()
         {
             CellType nType1 = rDoc.GetCellType(ScAddress(nCol, nRow, 0));
             CellType nType2 = rDoc.GetCellType(ScAddress(nCol + 4, nRow, 0));
-            double* pValue1 = rDoc.GetValueCell(ScAddress(nCol, nRow, 0));
-            double* pValue2 = rDoc.GetValueCell(ScAddress(nCol + 4, nRow, 0));
+            bHasIdx1 = pListData->GetSubIndex(rDoc.GetString(nCol, nRow, 0), nIdx1, bMatchCase);
+            bHasIdx2 = pListData->GetSubIndex(rDoc.GetString(nCol + 4, nRow, 0), nIdx2, bMatchCase);
 
             CPPUNIT_ASSERT_EQUAL(nType1, nType2);
-            if (pValue2 != nullptr)
-                CPPUNIT_ASSERT_EQUAL(*pValue1, *pValue2);   //cells with userlist value
-            else
-                CPPUNIT_ASSERT_EQUAL(pValue1, pValue2);     //empty cells
+            CPPUNIT_ASSERT_EQUAL(bHasIdx1, bHasIdx2);
+            if (bHasIdx1)
+                CPPUNIT_ASSERT_EQUAL(nIdx1, nIdx2);   //cells with userlist value
         }
     }
 }
diff --git a/sc/qa/unit/data/ods/tdf137653_137654_autofillUserlist.ods b/sc/qa/unit/data/ods/tdf137653_137654_autofillUserlist.ods
new file mode 100644
index 000000000000..52b3bb4a7f63
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf137653_137654_autofillUserlist.ods
diff --git a/sc/source/core/data/table4.cxx b/sc/source/core/data/table4.cxx
index 7d556031429d..9dc648f510ad 100644
--- a/sc/source/core/data/table4.cxx
+++ b/sc/source/core/data/table4.cxx
@@ -445,7 +445,7 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                     bool bMatchCase = false;
                     (void)rListData->GetSubIndex(aStr, rListIndex, bMatchCase);
                     size_t nListStrCount = rListData->GetSubCount();
-                    sal_uInt16 nPrevListIndex, nInc = 0;
+                    sal_uInt16 nPrevListIndex, nInc = 1;
                     for (SCSIZE i = 1; i < nValueCount && rListData; i++)
                     {
                         nColCurr = nCol1 + rNonOverlappedCellIdx[i] * nAddX;
@@ -641,16 +641,31 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
         {
             bool bMatchCase = false;
             (void)rListData->GetSubIndex(aStr, rListIndex, bMatchCase);
+            size_t nListStrCount = rListData->GetSubCount();
+            sal_uInt16 nPrevListIndex, nInc = 1;
             nCol = sal::static_int_cast<SCCOL>( nCol + nAddX );
             nRow = sal::static_int_cast<SCROW>( nRow + nAddY );
             for (SCSIZE i=1; i<nCount && rListData; i++)
             {
+                nPrevListIndex = rListIndex;
                 GetString(nCol, nRow, aStr);
                 if (!rListData->GetSubIndex(aStr, rListIndex, bMatchCase))
                     rListData = nullptr;
+                else
+                {
+                    sal_Int32 nIncCurr = rListIndex - nPrevListIndex;
+                    if (nIncCurr < 0)
+                        nIncCurr += nListStrCount;
+                    if (i == 1)
+                        nInc = nIncCurr;
+                    else if (nInc != nIncCurr)
+                        rListData = nullptr;
+                }
                 nCol = sal::static_int_cast<SCCOL>( nCol + nAddX );
                 nRow = sal::static_int_cast<SCROW>( nRow + nAddY );
             }
+            if (rListData)
+                rInc = nInc;
         }
         else if ( nCount > 1 )
         {
@@ -1107,12 +1122,11 @@ void ScTable::FillAuto( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                 if (!bPositive)
                 {
                     //  nListIndex of FillAnalyse points to the last entry -> adjust
-                    sal_uLong nSub = nISrcStart - nISrcEnd;
-                    for (sal_uLong i = 0; i < nSub; i++)
-                    {
-                        if (nListIndex == 0) nListIndex = nListCount;
-                        --nListIndex;
-                    }
+                    sal_Int64 nAdjust = nListIndex - (nISrcStart - nISrcEnd) * nInc;
+                    nAdjust = nAdjust % nListCount;
+                    if (nAdjust < 0)
+                        nAdjust += nListCount;
+                    nListIndex = nAdjust;
                 }
 
                 rInner = nIStart;
@@ -1122,13 +1136,13 @@ void ScTable::FillAuto( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                     {
                         if (bPositive)
                         {
-                            ++nListIndex;
-                            if (nListIndex >= nListCount) nListIndex = 0;
+                            nListIndex += nInc;
+                            if (nListIndex >= nListCount) nListIndex -= nListCount;
                         }
                         else
                         {
-                            if (nListIndex == 0) nListIndex = nListCount;
-                            --nListIndex;
+                            if (nListIndex < nInc) nListIndex += nListCount;
+                            nListIndex -= nInc;
                         }
                         aCol[nCol].SetRawString(static_cast<SCROW>(nRow), pListData->GetSubStr(nListIndex));
                     }