tdf#155402 - sc improving CELL() function return value in case of OOXML

In case of Excel/OOXML formula syntax:
Before the patch =CELL("FILENAME";D2) returns 'file:///X:/dr/own.ods'#$Sheet1
After the patch =CELL("FILENAME";D2) returns file:///X:/dr/[own.ods]Sheet1

Change-Id: I34c805aef813632c45ac3fe930d690ae3c1d5d96
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/153098
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

3 files changed, 56 insertions, 7 deletions

diff --git a/sc/qa/unit/data/xlsx/tdf155402.xlsx b/sc/qa/unit/data/xlsx/tdf155402.xlsx
new file mode 100644
index 000000000000..c1f73562547f
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/tdf155402.xlsx
diff --git a/sc/qa/unit/subsequent_filters_test4.cxx b/sc/qa/unit/subsequent_filters_test4.cxx
index d6b853607e51..646b1e66f5b7 100644
--- a/sc/qa/unit/subsequent_filters_test4.cxx
+++ b/sc/qa/unit/subsequent_filters_test4.cxx
@@ -1905,6 +1905,19 @@ CPPUNIT_TEST_FIXTURE(ScFiltersTest4, testForcepoint107)
     pDocSh->DoHardRecalc();
 }
 
+CPPUNIT_TEST_FIXTURE(ScFiltersTest4, testTdf155402)
+{
+    createScDoc("xlsx/tdf155402.xlsx");
+    ScDocument* pDoc = getScDoc();
+
+    // Before the fix =CELL("FILENAME"; D2) returns 'file:///X:/dr/own.ods'#$Sheet1
+    // After the fix =CELL("FILENAME"; D2) returns file :///X:/dr/[own.ods]Sheet1
+    OUString aFilename = pDoc->GetString(1, 0, 0);
+    sal_Int32 nPos = aFilename.lastIndexOf('/');
+    aFilename = OUString::Concat(aFilename.subView(nPos));
+    CPPUNIT_ASSERT_EQUAL(OUString("/[tdf155402.xlsx]Sheet1"), aFilename);
+}
+
 ScFiltersTest4::ScFiltersTest4()
     : ScModelTestBase("sc/qa/unit/data")
 {
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 2f48398827ed..bf8c89d0d125 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -2331,7 +2331,7 @@ void ScInterpreter::ScCell()
             PushString(aStr);
         }
         else if( aInfoType == "FILENAME" )
-        {   // file name and table name: 'FILENAME'#$TABLE
+        {
             SCTAB nTab = aCellPos.Tab();
             OUString aFuncResult;
             if( nTab < mrDoc.GetTableCount() )
@@ -2346,9 +2346,27 @@ void ScInterpreter::ScCell()
                         const INetURLObject& rURLObj = pShell->GetMedium()->GetURLObject();
                         OUString aTabName;
                         mrDoc.GetName( nTab, aTabName );
-                        aFuncResult = "'"
-                            + rURLObj.GetMainURL(INetURLObject::DecodeMechanism::Unambiguous)
-                            + "'#$" + aTabName;
+
+                        FormulaGrammar::AddressConvention eConv = maCalcConfig.meStringRefAddressSyntax;
+                        if (eConv == FormulaGrammar::CONV_UNSPECIFIED)
+                            eConv = mrDoc.GetAddressConvention();
+
+                        if (eConv == FormulaGrammar::CONV_XL_A1 ||
+                            eConv == FormulaGrammar::CONV_XL_R1C1 ||
+                            eConv == FormulaGrammar::CONV_XL_OOX)
+                        {
+                            // file name and table name: FILEPATH/[FILENAME]TABLE
+                            aFuncResult = rURLObj.GetPartBeforeLastName()
+                                + "[" + rURLObj.GetLastName(INetURLObject::DecodeMechanism::Unambiguous)
+                                + "]" + aTabName;
+                        }
+                        else
+                        {
+                            // file name and table name: 'FILEPATH/FILENAME'#$TABLE
+                            aFuncResult = "'"
+                                + rURLObj.GetMainURL(INetURLObject::DecodeMechanism::Unambiguous)
+                                + "'#$" + aTabName;
+                        }
                     }
                 }
             }
@@ -2515,8 +2533,6 @@ void ScInterpreter::ScCellExternal()
     }
     else if ( aInfoType == "FILENAME" )
     {
-        // 'file URI'#$SheetName
-
         const OUString* p = pRefMgr->getExternalFileName(nFileId);
         if (!p)
         {
@@ -2525,7 +2541,27 @@ void ScInterpreter::ScCellExternal()
             return;
         }
 
-        OUString aBuf = "'" + *p + "'#$" + aTabName;
+        OUString aBuf;
+        FormulaGrammar::AddressConvention eConv = maCalcConfig.meStringRefAddressSyntax;
+        if (eConv == FormulaGrammar::CONV_UNSPECIFIED)
+            eConv = mrDoc.GetAddressConvention();
+
+        if (eConv == FormulaGrammar::CONV_XL_A1 ||
+            eConv == FormulaGrammar::CONV_XL_R1C1 ||
+            eConv == FormulaGrammar::CONV_XL_OOX)
+        {
+            // 'file URI/[FileName]SheetName
+            sal_Int32 nPos = p->lastIndexOf('/');
+            aBuf = OUString::Concat(p->subView(0, nPos + 1))
+                + "[" + p->subView(nPos + 1) + "]"
+                + aTabName;
+        }
+        else
+        {
+            // 'file URI'#$SheetName
+            aBuf = "'" + *p + "'#$" + aTabName;
+        }
+
         PushString(aBuf);
     }
     else if ( aInfoType == "CONTENTS" )