sc/qa/unit/data/xlsx/tdf144397.xlsx |binary
sc/qa/unit/subsequent_export_test2.cxx | 62 ++++++++++++++++++++++++++++
sc/source/filter/excel/xeformula.cxx | 1
sc/source/filter/excel/xelink.cxx | 6 ++
sc/source/filter/excel/xename.cxx | 2
sc/source/filter/oox/externallinkbuffer.cxx | 21 +++++++++
sc/source/ui/docshell/externalrefmgr.cxx | 49 ++++++++++++++++++++--
7 files changed, 136 insertions(+), 5 deletions(-)
New commits:
commit 12ee423c7549ddd2b86dfc3fc6fed2c617dcca7f
Author: Balazs Varga <balazs.varga...@gmail.com>
AuthorDate: Mon Sep 13 12:17:37 2021 +0200
Commit: László Németh <nem...@numbertext.org>
CommitDate: Mon Oct 11 09:03:36 2021 +0200
tdf#144397 tdf#144636 XLSX: cache external named ranges and their formulas
XLSX round-trip resulted corrupt XLSX with invalid named
range, triggering Excel file repair, because of incomplete
handling of external file reference of the named ranges
(tdf#144636).
Cache external named ranges and their formulas in case
of updating formulas without data loss. Also we can copy
cell formulas and we get valid results of formulas from
the cached tables, instead of an error type.
Now Calc resolves the external file reference of the named
ranges, e.g. see "rangenameinotherfile" of the unit test
document in Manage Names (Ctrl-F3). After the fix, it contains
full path of the external file, and recalculating
the sheet or changing data in the target file reveals that
the named range works correctly (tdf#144397).
Change-Id: Ic011a29290f8cabcc39fdc4b8d775ecf9d33612f
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/122026
Tested-by: László Németh <nem...@numbertext.org>
Reviewed-by: László Németh <nem...@numbertext.org>
diff --git a/sc/qa/unit/data/xlsx/tdf144397.xlsx
b/sc/qa/unit/data/xlsx/tdf144397.xlsx
new file mode 100644
index 000000000000..bb0eeaa6e14a
Binary files /dev/null and b/sc/qa/unit/data/xlsx/tdf144397.xlsx differ
diff --git a/sc/qa/unit/subsequent_export_test2.cxx
b/sc/qa/unit/subsequent_export_test2.cxx
index 336b3ca7e167..cea7c5e00628 100644
--- a/sc/qa/unit/subsequent_export_test2.cxx
+++ b/sc/qa/unit/subsequent_export_test2.cxx
@@ -204,6 +204,7 @@ public:
void testButtonFormControlXlsxExport();
void testTdf142929_filterLessThanXLSX();
void testInvalidNamedRange();
+ void testExternalDefinedNameXLSX();
void testTdf143220XLSX();
void testTdf142264ManyChartsToXLSX();
void testTdf143929MultiColumnToODS();
@@ -313,6 +314,7 @@ public:
CPPUNIT_TEST(testButtonFormControlXlsxExport);
CPPUNIT_TEST(testTdf142929_filterLessThanXLSX);
CPPUNIT_TEST(testInvalidNamedRange);
+ CPPUNIT_TEST(testExternalDefinedNameXLSX);
CPPUNIT_TEST(testTdf143220XLSX);
CPPUNIT_TEST(testTdf142264ManyChartsToXLSX);
CPPUNIT_TEST(testTdf143929MultiColumnToODS);
@@ -2643,6 +2645,66 @@ void ScExportTest2::testInvalidNamedRange()
CPPUNIT_ASSERT(!xNamedRanges->hasByName("myname"));
}
+void ScExportTest2::testExternalDefinedNameXLSX()
+{
+ ScDocShellRef xShell = loadDoc(u"tdf144397.", FORMAT_XLSX);
+ CPPUNIT_ASSERT(xShell.is());
+ ScDocShellRef xDocSh = saveAndReload(&(*xShell), FORMAT_XLSX);
+ CPPUNIT_ASSERT(xDocSh.is());
+
+ xDocSh->ReloadAllLinks();
+ ScDocument& rDoc = xDocSh->GetDocument();
+ rDoc.CalcAll();
+
+ // "January"
+ {
+ const ScFormulaCell* pFC = rDoc.GetFormulaCell(ScAddress(1, 1, 0));
+ sc::FormulaResultValue aRes = pFC->GetResult();
+ CPPUNIT_ASSERT_EQUAL(sc::FormulaResultValue::String, aRes.meType);
+ CPPUNIT_ASSERT_EQUAL(OUString("January"), aRes.maString.getString());
+ }
+ // "March"
+ {
+ const ScFormulaCell* pFC = rDoc.GetFormulaCell(ScAddress(1, 3, 0));
+ sc::FormulaResultValue aRes = pFC->GetResult();
+ CPPUNIT_ASSERT_EQUAL(sc::FormulaResultValue::String, aRes.meType);
+ CPPUNIT_ASSERT_EQUAL(OUString("March"), aRes.maString.getString());
+ }
+ // "Empty = #N/A"
+ {
+ const ScFormulaCell* pFC = rDoc.GetFormulaCell(ScAddress(1, 5, 0));
+ sc::FormulaResultValue aRes = pFC->GetResult();
+ CPPUNIT_ASSERT_EQUAL(sc::FormulaResultValue::Error, aRes.meType);
+ CPPUNIT_ASSERT_EQUAL(OUString(""), aRes.maString.getString());
+ }
+ // "June"
+ {
+ const ScFormulaCell* pFC = rDoc.GetFormulaCell(ScAddress(1, 6, 0));
+ sc::FormulaResultValue aRes = pFC->GetResult();
+ CPPUNIT_ASSERT_EQUAL(sc::FormulaResultValue::String, aRes.meType);
+ CPPUNIT_ASSERT_EQUAL(OUString("June"), aRes.maString.getString());
+ }
+
+ xmlDocUniquePtr pDoc = XPathHelper::parseExport2(
+ *this, *xDocSh, m_xSFactory, "xl/externalLinks/externalLink1.xml",
FORMAT_XLSX);
+
+ CPPUNIT_ASSERT(pDoc);
+ assertXPath(pDoc,
"/x:externalLink/x:externalBook/x:sheetNames/x:sheetName", "val", "Munka1");
+ assertXPath(pDoc,
"/x:externalLink/x:externalBook/x:definedNames/x:definedName", "name",
+ "MonthNames");
+ // TODO: no need for the [1] external document identifier
+ assertXPath(pDoc,
"/x:externalLink/x:externalBook/x:definedNames/x:definedName", "refersTo",
+ "[1]Munka1!$A$2:$A$13");
+ assertXPath(pDoc,
"/x:externalLink/x:externalBook/x:sheetDataSet/x:sheetData", "sheetId", "0");
+ assertXPath(pDoc,
"/x:externalLink/x:externalBook/x:sheetDataSet/x:sheetData/x:row[2]", "r",
+ "3");
+ assertXPathContent(
+ pDoc,
"/x:externalLink/x:externalBook/x:sheetDataSet/x:sheetData/x:row[2]/x:cell/x:v",
+ "February");
+
+ xDocSh->DoClose();
+}
+
void ScExportTest2::testTdf143220XLSX()
{
ScDocShellRef xDocSh = loadDoc(u"tdf143220.", FORMAT_ODS);
diff --git a/sc/source/filter/excel/xeformula.cxx
b/sc/source/filter/excel/xeformula.cxx
index f829529ca0db..982143d877c4 100644
--- a/sc/source/filter/excel/xeformula.cxx
+++ b/sc/source/filter/excel/xeformula.cxx
@@ -612,6 +612,7 @@ void XclExpFmlaCompImpl::Init( XclFormulaType eType, const
ScTokenArray& rScTokA
// token array iterator (use cloned token array if present)
mxData->maTokArrIt.Init( mxData->mxOwnScTokArr ?
*mxData->mxOwnScTokArr : rScTokArr, false );
mxData->mpRefLog = pRefLog;
+ mxData->mpScBasePos = pScBasePos;
}
}
diff --git a/sc/source/filter/excel/xelink.cxx
b/sc/source/filter/excel/xelink.cxx
index 90a46120974e..593f26695779 100644
--- a/sc/source/filter/excel/xelink.cxx
+++ b/sc/source/filter/excel/xelink.cxx
@@ -1064,9 +1064,13 @@ void XclExpExtName::SaveXml(XclExpXmlStream& rStrm)
{
sax_fastparser::FSHelperPtr pExternalLink = rStrm.GetCurrentStream();
+ /* TODO: mpArray contains external references. It doesn't cause any
problems, but it's enough
+ to export it without the external document identifier. */
+ OUString aFormula = XclXmlUtils::ToOUString(GetCompileFormulaContext(),
ScAddress(0, 0, 0), mpArray.get());
+
pExternalLink->startElement(XML_definedName,
XML_name, maName.toUtf8(),
- XML_refersTo, nullptr,
+ XML_refersTo, aFormula.toUtf8(),
XML_sheetId, nullptr);
pExternalLink->endElement(XML_definedName);
diff --git a/sc/source/filter/excel/xename.cxx
b/sc/source/filter/excel/xename.cxx
index 5881990eb535..ba92ab834299 100644
--- a/sc/source/filter/excel/xename.cxx
+++ b/sc/source/filter/excel/xename.cxx
@@ -648,7 +648,7 @@ sal_uInt16 XclExpNameManagerImpl::CreateName( SCTAB nTab,
const ScRangeData& rRa
}
else
{
- xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME,
*pScTokArr );
+ xTokArr = GetFormulaCompiler().CreateFormula( EXC_FMLATYPE_NAME,
*pScTokArr, &rRangeData.GetPos() );
rRangeData.GetSymbol( sSymbol, ((GetOutput() == EXC_OUTPUT_BINARY)
?
formula::FormulaGrammar::GRAM_ENGLISH_XL_A1 :
formula::FormulaGrammar::GRAM_OOXML));
}
diff --git a/sc/source/filter/oox/externallinkbuffer.cxx
b/sc/source/filter/oox/externallinkbuffer.cxx
index 93f10d4d6264..b0c5ccd77c6f 100644
--- a/sc/source/filter/oox/externallinkbuffer.cxx
+++ b/sc/source/filter/oox/externallinkbuffer.cxx
@@ -18,6 +18,9 @@
*/
#include <externallinkbuffer.hxx>
+#include <externalrefmgr.hxx>
+#include <tokenarray.hxx>
+#include <tokenstringcontext.hxx>
#include <com/sun/star/beans/XPropertySet.hpp>
#include <com/sun/star/sheet/DDELinkInfo.hpp>
@@ -81,8 +84,26 @@ void ExternalName::importDefinedName( const AttributeList&
rAttribs )
{
maModel.maName = rAttribs.getXString( XML_name, OUString() );
OSL_ENSURE( !maModel.maName.isEmpty(), "ExternalName::importDefinedName -
empty name" );
+ maModel.maFormula = rAttribs.getXString(XML_refersTo, OUString());
+ OSL_ENSURE( !maModel.maFormula.isEmpty(), "ExternalName::importDefinedName
- empty formula" );
// zero-based index into sheet list of externalBook
maModel.mnSheet = rAttribs.getInteger( XML_sheetId, -1 );
+ // cache external defined names and formulas
+ ScCompiler aComp(getScDocument(), ScAddress(0, 0, maModel.mnSheet),
formula::FormulaGrammar::GRAM_OOXML);
+ aComp.SetExternalLinks(getExternalLinks().getLinkInfos());
+ std::unique_ptr<ScTokenArray> pArray =
aComp.CompileString(maModel.maFormula);
+ FormulaError nErr = pArray->GetCodeError();
+ aComp.CompileTokenArray();
+ getScDocument().CheckLinkFormulaNeedingCheck(*pArray);
+ pArray->DelRPN();
+ pArray->SetCodeError(nErr);
+
+ if (pArray->HasReferences())
+ {
+ ScExternalRefManager* pRefMgr =
getScDocument().GetExternalRefManager();
+ sal_uInt16 nFileId =
pRefMgr->getExternalFileId(mrParentLink.getTargetUrl());
+ pRefMgr->storeRangeNameTokens(nFileId, maModel.maName, *pArray);
+ }
}
void ExternalName::importDdeItem( const AttributeList& rAttribs )
diff --git a/sc/source/ui/docshell/externalrefmgr.cxx
b/sc/source/ui/docshell/externalrefmgr.cxx
index 6b905cffa4b8..d3b1bdd84d41 100644
--- a/sc/source/ui/docshell/externalrefmgr.cxx
+++ b/sc/source/ui/docshell/externalrefmgr.cxx
@@ -746,8 +746,9 @@ bool ScExternalRefCache::isValidRangeName(sal_uInt16
nFileId, const OUString& rN
if (!pDoc)
return false;
+ OUString aUpperName = ScGlobal::getCharClass().uppercase(rName);
const RangeNameMap& rMap = pDoc->maRangeNames;
- return rMap.count(rName) > 0;
+ return rMap.count(aUpperName) > 0;
}
void ScExternalRefCache::setRangeName(sal_uInt16 nFileId, const OUString&
rName)
@@ -1752,8 +1753,50 @@ void
ScExternalRefManager::setAllCacheTableReferencedStati( bool bReferenced )
void ScExternalRefManager::storeRangeNameTokens(sal_uInt16 nFileId, const
OUString& rName, const ScTokenArray& rArray)
{
- ScExternalRefCache::TokenArrayRef pArray(rArray.Clone());
- maRefCache.setRangeNameTokens(nFileId, rName, pArray);
+ ScExternalRefCache::TokenArrayRef pNewArray;
+ if (!rArray.HasExternalRef())
+ {
+ // Parse all tokens in this external range data, and replace each
absolute
+ // reference token with an external reference token, and cache them.
+ pNewArray = std::make_shared<ScTokenArray>(*new ScDocument());
+ FormulaTokenArrayPlainIterator aIter(rArray);
+ for (const FormulaToken* pToken = aIter.First(); pToken; pToken =
aIter.Next())
+ {
+ bool bTokenAdded = false;
+ switch (pToken->GetType())
+ {
+ case svSingleRef:
+ {
+ const ScSingleRefData& rRef = *pToken->GetSingleRef();
+ OUString aTabName = maRefCache.getTableName(nFileId,
rRef.Tab());
+ ScExternalSingleRefToken aNewToken(nFileId,
svl::SharedString(aTabName), // string not interned
+ *pToken->GetSingleRef());
+ pNewArray->AddToken(aNewToken);
+ bTokenAdded = true;
+ }
+ break;
+ case svDoubleRef:
+ {
+ const ScSingleRefData& rRef = *pToken->GetSingleRef();
+ OUString aTabName = maRefCache.getTableName(nFileId,
rRef.Tab());
+ ScExternalDoubleRefToken aNewToken(nFileId,
svl::SharedString(aTabName), // string not interned
+ *pToken->GetDoubleRef());
+ pNewArray->AddToken(aNewToken);
+ bTokenAdded = true;
+ }
+ break;
+ default:
+ ; // nothing
+ }
+
+ if (!bTokenAdded)
+ pNewArray->AddToken(*pToken);
+ }
+ }
+ else
+ pNewArray = rArray.Clone();
+
+ maRefCache.setRangeNameTokens(nFileId, rName, pNewArray);
}
namespace {
