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 <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>

5 files changed, 74 insertions, 5 deletions

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 {