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>
diff --git a/sc/qa/unit/copy_paste_test.cxx b/sc/qa/unit/copy_paste_test.cxx
index 62ee0bc..87d8311 100644
--- a/sc/qa/unit/copy_paste_test.cxx
+++ b/sc/qa/unit/copy_paste_test.cxx
@@ -51,6 +51,7 @@ public:
void tdf137653_137654_autofillUserlist();
void tdf113500_autofillMixed();
void tdf137625_autofillMergedUserlist();
void tdf137624_autofillMergedMixed();
CPPUNIT_TEST_SUITE(ScCopyPasteTest);
CPPUNIT_TEST(testCopyPasteXLS);
@@ -67,6 +68,7 @@ public:
CPPUNIT_TEST(tdf137653_137654_autofillUserlist);
CPPUNIT_TEST(tdf113500_autofillMixed);
CPPUNIT_TEST(tdf137625_autofillMergedUserlist);
CPPUNIT_TEST(tdf137624_autofillMergedMixed);
CPPUNIT_TEST_SUITE_END();
private:
@@ -930,7 +932,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 0000000..cc1b6a7
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf137624_autofillMergedMixed.ods
Binary files differ
diff --git a/sc/source/core/data/table4.cxx b/sc/source/core/data/table4.cxx
index 2e29cec..a777aca 100644
--- a/sc/source/core/data/table4.cxx
+++ b/sc/source/core/data/table4.cxx
@@ -420,25 +420,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)
{
@@ -450,10 +434,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
{
@@ -472,7 +456,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(&rDocument);
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;
}
}
}
}
}
@@ -2341,16 +2363,25 @@ void ScTable::FillSeries( SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2,
tools::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)
{
@@ -2381,7 +2412,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);
if ( nHeadNoneTail < 0 )
