sc/inc/compiler.hxx | 2 +-
sc/qa/unit/data/ods/named-range-conflict.ods |binary
sc/qa/unit/jumbosheets-test.cxx | 26 ++++++++++++++++++++++++++
sc/source/core/tool/compiler.cxx | 20 +++++++++++++++++---
4 files changed, 44 insertions(+), 4 deletions(-)
New commits:
commit 582fc887f1faafe8ff5f16a13a0208483a93353f
Author: Luboš Luňák <l.lu...@collabora.com>
AuthorDate: Wed Mar 2 17:30:30 2022 +0100
Commit: Luboš Luňák <l.lu...@collabora.com>
CommitDate: Fri Mar 4 18:16:43 2022 +0100
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.lu...@collabora.com>
diff --git a/sc/inc/compiler.hxx b/sc/inc/compiler.hxx
index c2bdb29b478d..15b3823f649d 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 000000000000..b014cad532ec
Binary files /dev/null and b/sc/qa/unit/data/ods/named-range-conflict.ods differ
diff --git a/sc/qa/unit/jumbosheets-test.cxx b/sc/qa/unit/jumbosheets-test.cxx
index 76f223564b9a..deb516ae5b91 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 0b0dce39134f..fca54da17f9c 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;
}
}
