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>
diff --git a/sc/qa/unit/copy_paste_test.cxx b/sc/qa/unit/copy_paste_test.cxx
index 325a883..12e0b64 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 0000000..9a62d57
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf137205_AutofillDatesInMergedCells.ods
Binary files differ
diff --git a/sc/source/core/data/table4.cxx b/sc/source/core/data/table4.cxx
index e2d8648..11570e3 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));
