keep conflicting named ranges working with 16k columns
Named ranges named e.g. 'num1' are actually valid cell addresses
when using 16k columns. We prevent naming ranges in a way that would
make them conflict, but it's possible to read them from a saved
file that was created with fewer columns, and in such cases formulas
using them would silently refer to those cells instead of to
the named range. I don't see anything in the ODF spec, but OOXML
in 18.2.5 recommends this in case there are conflicts (only outside
of the normal Excel range of A1-XFD1048576, inside they are always
meant to be references, but our normal range currently is only 1k
columns, and it's simpler and probably harmless to always resolve
a conflict this way). I can optimize performance of this in another
commit if needed.
Change-Id: I46aef54b069700e7bf268b50fdc1a88989f3ee29
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/130891
Tested-by: Jenkins
Reviewed-by: Luboš Luňák <l.lunak@collabora.com>
diff --git a/sc/inc/compiler.hxx b/sc/inc/compiler.hxx
index c2bdb29..15b3823 100644
--- a/sc/inc/compiler.hxx
+++ b/sc/inc/compiler.hxx
@@ -357,7 +357,7 @@ private:
bool ParsePredetectedReference( const OUString& rSymbol );
bool ParsePredetectedErrRefReference( const OUString& rName, const OUString* pErrRef );
bool ParseMacro( const OUString& );
bool ParseNamedRange( const OUString& );
bool ParseNamedRange( const OUString&, bool onlyCheck = false );
bool ParseExternalNamedRange( const OUString& rSymbol, bool& rbInvalidExternalNameRange );
bool ParseDBRange( const OUString& );
bool ParseColRowName( const OUString& );
diff --git a/sc/qa/unit/data/ods/named-range-conflict.ods b/sc/qa/unit/data/ods/named-range-conflict.ods
new file mode 100644
index 0000000..b014cad
--- /dev/null
+++ b/sc/qa/unit/data/ods/named-range-conflict.ods
Binary files differ
diff --git a/sc/qa/unit/jumbosheets-test.cxx b/sc/qa/unit/jumbosheets-test.cxx
index 76f2235..deb516a 100644
--- a/sc/qa/unit/jumbosheets-test.cxx
+++ b/sc/qa/unit/jumbosheets-test.cxx
@@ -45,6 +45,7 @@ public:
void testRoundtripColumn2000Xlsx();
void testRoundtripColumnRange();
void testRoundtripNamedRanges();
void testNamedRangeNameConflict();
void testTdf134553();
void testTdf134392();
void testTdf147509();
@@ -57,6 +58,7 @@ public:
CPPUNIT_TEST(testRoundtripColumn2000Xlsx);
CPPUNIT_TEST(testRoundtripColumnRange);
CPPUNIT_TEST(testRoundtripNamedRanges);
CPPUNIT_TEST(testNamedRangeNameConflict);
CPPUNIT_TEST(testTdf134553);
CPPUNIT_TEST(testTdf134392);
CPPUNIT_TEST(testTdf147509);
@@ -222,6 +224,30 @@ void ScJumboSheetsTest::testRoundtripNamedRanges()
xDocSh3->DoClose();
}
void ScJumboSheetsTest::testNamedRangeNameConflict()
{
// The document contains named ranges named 'num1' and 'num2', that should be still treated
// as named references even though with 16k columns those are normally NUM1 and NUM2 cells.
ScDocShellRef xDocSh = loadDoc(u"named-range-conflict.", FORMAT_ODS);
CPPUNIT_ASSERT(xDocSh.is());
ScDocument& rDoc = xDocSh->GetDocument();
rDoc.CalcAll();
CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(10022, 0, 0)); // NUM1
CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(10022, 1, 0)); // NUM2
CPPUNIT_ASSERT_EQUAL(2.0, rDoc.GetValue(0, 0, 0)); // = num1
CPPUNIT_ASSERT_EQUAL(3.0, rDoc.GetValue(0, 1, 0)); // = sheet2.num2
CPPUNIT_ASSERT_EQUAL(0.0, rDoc.GetValue(0, 2, 0)); // = SUM(NUM1:NUM2) (not named ranges)
rDoc.SetValue(10022, 0, 0, 100); // NUM1
rDoc.SetValue(10022, 1, 0, 200); // NUM2
rDoc.CalcAll();
// First two are the same, the sum changes.
CPPUNIT_ASSERT_EQUAL(2.0, rDoc.GetValue(0, 0, 0));
CPPUNIT_ASSERT_EQUAL(3.0, rDoc.GetValue(0, 1, 0));
CPPUNIT_ASSERT_EQUAL(300.0, rDoc.GetValue(0, 2, 0));
xDocSh->DoClose();
}
void ScJumboSheetsTest::testTdf134553()
{
ScDocShellRef xDocSh = loadDocAndSetupModelViewController(u"tdf134553.", FORMAT_XLSX);
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 0b0dce3..fca54da 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -3344,6 +3344,18 @@ bool ScCompiler::ParseSingleReference( const OUString& rName, const OUString* pE
return false;
}
// A named range named e.g. 'num1' is valid with 1k columns, but would become a reference
// when the document is opened later with 16k columns. Resolve the conflict by not
// considering it a reference.
OUString aUpper;
bool bAsciiUpper = ToUpperAsciiOrI18nIsAscii( aUpper, rName );
if (bAsciiUpper || mbCharClassesDiffer)
aUpper = ScGlobal::getCharClass().uppercase( rName );
mnCurrentSheetTab = aAddr.Tab(); // temporarily set for ParseNamedRange()
if(ParseNamedRange( aUpper, true )) // only check
return false;
mnCurrentSheetTab = -1;
ScSingleRefData aRef;
aRef.InitAddress( aAddr );
aRef.SetColRel( (nFlags & ScRefFlags::COL_ABS) == ScRefFlags::ZERO );
@@ -3574,7 +3586,7 @@ const ScRangeData* ScCompiler::GetRangeData( SCTAB& rSheet, const OUString& rUpp
return pData;
}
bool ScCompiler::ParseNamedRange( const OUString& rUpperName )
bool ScCompiler::ParseNamedRange( const OUString& rUpperName, bool onlyCheck )
{
// ParseNamedRange is called only from NextNewToken, with an upper-case string
@@ -3582,7 +3594,8 @@ bool ScCompiler::ParseNamedRange( const OUString& rUpperName )
const ScRangeData* pData = GetRangeData( nSheet, rUpperName);
if (pData)
{
maRawToken.SetName( nSheet, pData->GetIndex());
if (!onlyCheck)
maRawToken.SetName( nSheet, pData->GetIndex());
return true;
}
@@ -3596,7 +3609,8 @@ bool ScCompiler::ParseNamedRange( const OUString& rUpperName )
pData = pRangeName->findByUpperName(aName);
if (pData)
{
maRawToken.SetName( mnCurrentSheetTab, pData->GetIndex());
if (!onlyCheck)
maRawToken.SetName( mnCurrentSheetTab, pData->GetIndex());
return true;
}
}
