tdf#137205 sc: autofill date sequences in merged cells

Improve FillAnalyse to discover and continue linear sequences of dates
with the only differences of months or years in merged cells by skipping
the empty overlapped cells of the merged area.

Follow-up of commit Ib431e8968f5d71e321b0e57cfb173534a0f5da31
(tdf#88782 sc: autofill number sequences in merged cells)

Co-authored-by: Tibor Nagy (NISZ)
Change-Id: I1e37efd34858f53691bf867ebefc2fe26e39e676
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/103967
Tested-by: Jenkins
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>

3 files changed, 143 insertions, 32 deletions

diff --git a/sc/qa/unit/copy_paste_test.cxx b/sc/qa/unit/copy_paste_test.cxx
index 325a883d212d..12e0b64a3532 100644
--- a/sc/qa/unit/copy_paste_test.cxx
+++ b/sc/qa/unit/copy_paste_test.cxx
@@ -45,6 +45,7 @@ public:
     void testTdf40993_fillMergedCells();
     void testTdf43958_clickSelectOnMergedCells();
     void testTdf88782_autofillLinearNumbersInMergedCells();
+    void tdf137205_autofillDatesInMergedCells();
 
     CPPUNIT_TEST_SUITE(ScCopyPasteTest);
     CPPUNIT_TEST(testCopyPasteXLS);
@@ -56,6 +57,7 @@ public:
     CPPUNIT_TEST(testTdf40993_fillMergedCells);
     CPPUNIT_TEST(testTdf43958_clickSelectOnMergedCells);
     CPPUNIT_TEST(testTdf88782_autofillLinearNumbersInMergedCells);
+    CPPUNIT_TEST(tdf137205_autofillDatesInMergedCells);
     CPPUNIT_TEST_SUITE_END();
 
 private:
@@ -638,7 +640,7 @@ void ScCopyPasteTest::testTdf88782_autofillLinearNumbersInMergedCells()
     aMergeOptions.maTabs.insert(0);
     xDocSh->GetDocFunc().MergeCells(aMergeOptions, false, true, true, false);
 
-    // fillauto numbers, these areas contains mostly merged cells
+    // fillauto numbers, these areas contain mostly merged cells
     pView->FillAuto(FILL_TO_BOTTOM, 1, 8, 3, 14, 7);    // B9:D15 ->  B9:D22
     pView->FillAuto(FILL_TO_BOTTOM, 5, 8, 7, 17, 10);   // F9:H18 ->  F9:H28
     pView->FillAuto(FILL_TO_BOTTOM, 9, 8, 10, 13, 6);   // J9:K14 ->  J9:K20
@@ -679,6 +681,35 @@ void ScCopyPasteTest::testTdf88782_autofillLinearNumbersInMergedCells()
         }
     }
 }
+void ScCopyPasteTest::tdf137205_autofillDatesInMergedCells()
+{
+    ScDocShellRef xDocSh = loadDocAndSetupModelViewController("tdf137205_AutofillDatesInMergedCells.", FORMAT_ODS, true);
+    ScDocument& rDoc = xDocSh->GetDocument();
+
+    // Get the document controller
+    ScTabViewShell* pView = xDocSh->GetBestViewShell(false);
+    CPPUNIT_ASSERT(pView != nullptr);
+
+    // fillauto dates, this areas contain only merged cells
+    pView->FillAuto(FILL_TO_RIGHT, 1, 5, 4, 7, 8);   //B6:E8
+
+    // compare the results of fill-right with the reference stored in the test file
+    // this compare the whole area blindly, for concrete test cases, check the test file
+    for (int nCol = 5; nCol <= 12; nCol++) {
+        for (int nRow = 5; nRow <= 7; nRow++) {
+            CellType nType1 = rDoc.GetCellType(ScAddress(nCol, nRow, 0));
+            CellType nType2 = rDoc.GetCellType(ScAddress(nCol, nRow + 5, 0));
+            double* pValue1 = rDoc.GetValueCell(ScAddress(nCol, nRow, 0));
+            double* pValue2 = rDoc.GetValueCell(ScAddress(nCol, nRow + 5, 0));
+
+            CPPUNIT_ASSERT_EQUAL(nType1, nType2);
+            if (pValue2 != nullptr)
+                CPPUNIT_ASSERT_EQUAL(*pValue1, *pValue2);   //cells with number value
+            else
+                CPPUNIT_ASSERT_EQUAL(pValue1, pValue2);     //empty cells
+        }
+    }
+}
 
 ScCopyPasteTest::ScCopyPasteTest()
       : ScBootstrapFixture( "sc/qa/unit/data" )
diff --git a/sc/qa/unit/data/ods/tdf137205_AutofillDatesInMergedCells.ods b/sc/qa/unit/data/ods/tdf137205_AutofillDatesInMergedCells.ods
new file mode 100644
index 000000000000..9a62d575dbd2
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf137205_AutofillDatesInMergedCells.ods
diff --git a/sc/source/core/data/table4.cxx b/sc/source/core/data/table4.cxx
index e2d86480cc72..11570e3153a4 100644
--- a/sc/source/core/data/table4.cxx
+++ b/sc/source/core/data/table4.cxx
@@ -254,15 +254,15 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
     {
         bool bHasOverlappedCells = false;
         bool bSkipOverlappedCells = true;
-        SCCOL nColAkt = nCol1;
-        SCROW nRowAkt = nRow1;
+        SCCOL nColCurr = nCol1;
+        SCROW nRowCurr = nRow1;
 
         // collect cells that are not empty or not overlapped
         rNonOverlappedCellIdx.resize(nCount);
         SCSIZE nValueCount = 0;
         for (SCSIZE i = 0; i < nCount; ++i)
         {
-            const ScPatternAttr* pPattern = GetPattern(nColAkt, nRowAkt);
+            const ScPatternAttr* pPattern = GetPattern(nColCurr, nRowCurr);
             bool bOverlapped
                 = pPattern->GetItemSet().GetItemState(ATTR_MERGE_FLAG, false) == SfxItemState::SET
                   && pPattern->GetItem(ATTR_MERGE_FLAG).IsOverlapped();
@@ -270,7 +270,7 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
             if (bOverlapped)
                 bHasOverlappedCells = true;
 
-            if (!bOverlapped || GetCellValue(nColAkt, nRowAkt).meType != CELLTYPE_NONE)
+            if (!bOverlapped || GetCellValue(nColCurr, nRowCurr).meType != CELLTYPE_NONE)
             {
                 rNonOverlappedCellIdx[nValueCount++] = i;
                 // if there is at least 1 non empty overlapped cell, then no cell should be skipped
@@ -278,8 +278,8 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                     bSkipOverlappedCells = false;
             }
 
-            nColAkt += nAddX;
-            nRowAkt += nAddY;
+            nColCurr += nAddX;
+            nRowCurr += nAddY;
         }
         rNonOverlappedCellIdx.resize(nValueCount);
 
@@ -293,26 +293,106 @@ void ScTable::FillAnalyse( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
 
         if (bSkipOverlappedCells)
         {
-            nColAkt = nCol1 + rNonOverlappedCellIdx[0] * nAddX;
-            nRowAkt = nRow1 + rNonOverlappedCellIdx[0] * nAddY;
-            ScRefCellValue aPrevCell, aAktCell;
-            aAktCell = GetCellValue(nColAkt, nRowAkt);
-            CellType eCellType = aAktCell.meType;
+            nColCurr = nCol1 + rNonOverlappedCellIdx[0] * nAddX;
+            nRowCurr = nRow1 + rNonOverlappedCellIdx[0] * nAddY;
+            ScRefCellValue aPrevCell, aCurrCell;
+            aCurrCell = GetCellValue(nColCurr, nRowCurr);
+            CellType eCellType = aCurrCell.meType;
             if (eCellType == CELLTYPE_VALUE)
             {
-                // TODO: Check / handle special cases of number formats: like date, boolean
+                // TODO: Check / handle special cases of number formats like boolean
                 bool bVal = true;
-                if (nValueCount >= 2)
+                SvNumFormatType nCurrCellFormatType
+                    = rDocument.GetFormatTable()->GetType(GetNumberFormat(nColCurr, nRowCurr));
+                if (nCurrCellFormatType == SvNumFormatType::DATE)
+                {
+                    if (nValueCount >= 2)
+                    {
+                        long nCmpInc = 0;
+                        FillDateCmd eType = FILL_YEAR;  // just some temporary default values
+                        long nDDiff = 0, nMDiff = 0, nYDiff = 0; // to avoid warnings
+                        Date aNullDate = rDocument.GetFormatTable()->GetNullDate();
+                        Date aCurrDate = aNullDate, aPrevDate = aNullDate;
+                        aCurrDate.AddDays(aCurrCell.mfValue);
+                        for (SCSIZE i = 1; i < nValueCount && bVal; i++)
+                        {
+                            aPrevCell = aCurrCell;
+                            aPrevDate = aCurrDate;
+                            nColCurr = nCol1 + rNonOverlappedCellIdx[i] * nAddX;
+                            nRowCurr = nRow1 + rNonOverlappedCellIdx[i] * nAddY;
+                            aCurrCell = GetCellValue(nColCurr, nRowCurr);
+                            if (aCurrCell.meType == CELLTYPE_VALUE)
+                            {
+                                aCurrDate = aNullDate + static_cast<sal_Int32>(aCurrCell.mfValue);
+                                if (eType != FILL_DAY) {
+                                    nDDiff = aCurrDate.GetDay()
+                                             - static_cast<long>(aPrevDate.GetDay());
+                                    nMDiff = aCurrDate.GetMonth()
+                                             - static_cast<long>(aPrevDate.GetMonth());
+                                    nYDiff = aCurrDate.GetYear()
+                                             - static_cast<long>(aPrevDate.GetYear());
+                                }
+                                if (i == 1)
+                                {
+                                    if (nDDiff != 0)
+                                    {
+                                        eType = FILL_DAY;
+                                        nCmpInc = aCurrDate - aPrevDate;
+                                    }
+                                    else
+                                    {
+                                        eType = FILL_MONTH;
+                                        nCmpInc = nMDiff + 12 * nYDiff;
+                                    }
+                                }
+                                else if (eType == FILL_DAY)
+                                {
+                                    if (aCurrDate - aPrevDate != nCmpInc)
+                                        bVal = false;
+                                }
+                                else
+                                {
+                                    if (nDDiff || (nMDiff + 12 * nYDiff != nCmpInc))
+                                        bVal = false;
+                                }
+                            }
+                            else
+                                bVal = false;   // No date is also not ok
+                        }
+                        if (bVal)
+                        {
+                            if (eType == FILL_MONTH && (nCmpInc % 12 == 0))
+                            {
+                                eType = FILL_YEAR;
+                                nCmpInc /= 12;
+                            }
+                            rCmd = FILL_DATE;
+                            rDateCmd = eType;
+                            rInc = nCmpInc;
+                            rSkipOverlappedCells = true;
+                            return;
+                        }
+                    }
+                    else
+                    {
+                        rCmd = FILL_DATE;
+                        rDateCmd = FILL_DAY;
+                        rInc = 1.0;
+                        rSkipOverlappedCells = true;
+                        return;
+                    }
+                }
+                else if (nValueCount >= 2)
                 {
                     for (SCSIZE i = 1; i < nValueCount && bVal; i++)
                     {
-                        aPrevCell = aAktCell;
-                        nColAkt = nCol1 + rNonOverlappedCellIdx[i] * nAddX;
-                        nRowAkt = nRow1 + rNonOverlappedCellIdx[i] * nAddY;
-                        aAktCell = GetCellValue(nColAkt, nRowAkt);
-                        if (aAktCell.meType == CELLTYPE_VALUE)
+                        aPrevCell = aCurrCell;
+                        nColCurr = nCol1 + rNonOverlappedCellIdx[i] * nAddX;
+                        nRowCurr = nRow1 + rNonOverlappedCellIdx[i] * nAddY;
+                        aCurrCell = GetCellValue(nColCurr, nRowCurr);
+                        if (aCurrCell.meType == CELLTYPE_VALUE)
                         {
-                            double nDiff = approxDiff(aAktCell.mfValue, aPrevCell.mfValue);
+                            double nDiff = approxDiff(aCurrCell.mfValue, aPrevCell.mfValue);
                             if (i == 1)
                                 rInc = nDiff;
                             if (!::rtl::math::approxEqual(nDiff, rInc, 13))
@@ -892,18 +972,18 @@ void ScTable::FillAuto( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
         sal_uInt16 nMinDigits;
         ScUserListData* pListData = nullptr;
         sal_uInt16 nListIndex;
-        bool nSkipOverlappedCells;
+        bool bSkipOverlappedCells;
         std::vector<sal_Int32> aNonOverlappedCellIdx;
         if (bVertical)
             FillAnalyse(static_cast<SCCOL>(nCol),nRow1,
                     static_cast<SCCOL>(nCol),nRow2, eFillCmd,eDateCmd,
                     nInc, nMinDigits, pListData, nListIndex,
-                    bHasFiltered, nSkipOverlappedCells, aNonOverlappedCellIdx);
+                    bHasFiltered, bSkipOverlappedCells, aNonOverlappedCellIdx);
         else
             FillAnalyse(nCol1,static_cast<SCROW>(nRow),
                     nCol2,static_cast<SCROW>(nRow), eFillCmd,eDateCmd,
                     nInc, nMinDigits, pListData, nListIndex,
-                    bHasFiltered, nSkipOverlappedCells, aNonOverlappedCellIdx);
+                    bHasFiltered, bSkipOverlappedCells, aNonOverlappedCellIdx);
 
         if (pListData)
         {
@@ -961,12 +1041,12 @@ void ScTable::FillAuto( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
                 FillSeries( static_cast<SCCOL>(nCol), nRow1,
                         static_cast<SCCOL>(nCol), nRow2, nFillCount, eFillDir,
                         eFillCmd, eDateCmd, nInc, nEndVal, nMinDigits, false,
-                        pProgress, nSkipOverlappedCells, &aNonOverlappedCellIdx);
+                        pProgress, bSkipOverlappedCells, &aNonOverlappedCellIdx);
             else
                 FillSeries( nCol1, static_cast<SCROW>(nRow), nCol2,
                         static_cast<SCROW>(nRow), nFillCount, eFillDir,
                         eFillCmd, eDateCmd, nInc, nEndVal, nMinDigits, false,
-                        pProgress, nSkipOverlappedCells, &aNonOverlappedCellIdx);
+                        pProgress, bSkipOverlappedCells, &aNonOverlappedCellIdx);
             if (pProgress)
                 nProgress = pProgress->GetState();
         }
@@ -1020,7 +1100,7 @@ OUString ScTable::GetAutoFillPreview( const ScRange& rSource, SCCOL nEndX, SCROW
         sal_uInt16 nMinDigits;
         ScUserListData* pListData = nullptr;
         sal_uInt16 nListIndex;
-        bool nSkipOverlappedCells;
+        bool bSkipOverlappedCells;
         std::vector<sal_Int32> aNonOverlappedCellIdx;
 
         // Todo: update this function to calculate with merged cell fills,
@@ -1028,7 +1108,7 @@ OUString ScTable::GetAutoFillPreview( const ScRange& rSource, SCCOL nEndX, SCROW
         // Now FillAnalyse called as if there are filtered rows, so it will work in the old way.
         FillAnalyse(nCol1, nRow1, nCol2, nRow2, eFillCmd, eDateCmd,
                     nInc, nMinDigits, pListData, nListIndex,
-                    true, nSkipOverlappedCells, aNonOverlappedCellIdx);
+                    true, bSkipOverlappedCells, aNonOverlappedCellIdx);
 
         if ( pListData )                            // user defined list
         {
@@ -1963,25 +2043,25 @@ void ScTable::FillSeries( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
             // create a vector to make it easier to decide if a cell need to be filled, or skipped.
             aIsNonEmptyCell.resize(nFillerCount, false);
 
-            SCCOLROW nfirstValueIdx;
+            SCCOLROW nFirstValueIdx;
             if (bPositive)
             {
-                nfirstValueIdx = nISource + (*pNonOverlappedCellIdx)[0];
+                nFirstValueIdx = nISource + (*pNonOverlappedCellIdx)[0];
                 for (auto i : (*pNonOverlappedCellIdx))
                     aIsNonEmptyCell[i] = true;
             }
             else
             {
-                nfirstValueIdx = nISource - (nFillerCount - 1 - (*pNonOverlappedCellIdx).back());
+                nFirstValueIdx = nISource - (nFillerCount - 1 - (*pNonOverlappedCellIdx).back());
                 for (auto i : (*pNonOverlappedCellIdx))
                     aIsNonEmptyCell[nFillerCount - 1 - i] = true;
             }
 
             //Set the real source cell
             if (bVertical)
-                aSrcCell = aCol[nOStart].GetCellValue(static_cast<SCROW>(nfirstValueIdx));
+                aSrcCell = aCol[nOStart].GetCellValue(static_cast<SCROW>(nFirstValueIdx));
             else
-                aSrcCell = aCol[nfirstValueIdx].GetCellValue(static_cast<SCROW>(nOStart));
+                aSrcCell = aCol[nFirstValueIdx].GetCellValue(static_cast<SCROW>(nOStart));
         }
 
         const ScPatternAttr* pSrcPattern = aCol[nCol].GetPattern(static_cast<SCROW>(nRow));