tdf#76047 XLSX import: fix links to external data

in array formulas to avoid loss of cell content.

Without using SetExternalLinks in applyArrayFormulas
the IDs of the linked files were not replaced with
file names.

Co-authored-by: Tibor Nagy (NISZ)

Change-Id: I57c304a46899f4171db9ad12e7e187bd6e4c1c15
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/104049
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>

4 files changed, 28 insertions, 1 deletions

diff --git a/sc/qa/unit/data/xlsx/tdf76047_externalLink.xlsx b/sc/qa/unit/data/xlsx/tdf76047_externalLink.xlsx
new file mode 100644
index 000000000000..66ce8b99daf4
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/tdf76047_externalLink.xlsx
diff --git a/sc/qa/unit/data/xlsx/tdf76047_externalLinkSource.ods b/sc/qa/unit/data/xlsx/tdf76047_externalLinkSource.ods
new file mode 100644
index 000000000000..7f9c4cd898a1
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/tdf76047_externalLinkSource.ods
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index f22747996641..e928785d062e 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -272,6 +272,7 @@ public:
     void testTdf91251_missingOverflowRoundtrip();
     void testTdf137000_handle_upright();
     void testTdf126305_DataValidatyErrorAlert();
+    void testTdf76047_externalLink();
 
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
@@ -438,6 +439,7 @@ public:
     CPPUNIT_TEST(testTdf91251_missingOverflowRoundtrip);
     CPPUNIT_TEST(testTdf137000_handle_upright);
     CPPUNIT_TEST(testTdf126305_DataValidatyErrorAlert);
+    CPPUNIT_TEST(testTdf76047_externalLink);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -5525,6 +5527,29 @@ void ScExportTest::testTdf126305_DataValidatyErrorAlert()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testTdf76047_externalLink()
+{
+    ScDocShellRef pShell = loadDoc("tdf76047_externalLink.", FORMAT_XLSX);
+    CPPUNIT_ASSERT(pShell.is());
+
+    // load data from external links. (tdf76047_externalLinkSource.ods)
+    // that file have to be the same directory as tdf76047_externalLink.xlsx
+    pShell->ReloadAllLinks();
+    ScDocument& rDoc = pShell->GetDocument();
+
+    // compare the loaded data (from external links) to the data copied manually to the testfile
+    for (int nCol = 1; nCol <= 5; nCol++) {
+        for (int nRow = 3; nRow <= 5; nRow++) {
+            OUString aStr1 = rDoc.GetString(ScAddress(nCol, nRow, 0));
+            OUString aStr2 = rDoc.GetString(ScAddress(nCol, nRow + 5, 0));
+            OUString aStr3 = rDoc.GetString(ScAddress(nCol, nRow + 11, 0));
+
+            CPPUNIT_ASSERT_EQUAL(aStr1, aStr3);
+            CPPUNIT_ASSERT_EQUAL(aStr2, aStr3);
+        }
+    }
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/oox/formulabuffer.cxx b/sc/source/filter/oox/formulabuffer.cxx
index d9a8cdb0d194..9d39c831afce 100644
--- a/sc/source/filter/oox/formulabuffer.cxx
+++ b/sc/source/filter/oox/formulabuffer.cxx
@@ -251,6 +251,7 @@ void applyCellFormulas(
 
 void applyArrayFormulas(
     ScDocumentImport& rDoc, SvNumberFormatter& rFormatter,
+    const Sequence<ExternalLinkInfo>& rExternalLinks,
     const std::vector<FormulaBuffer::TokenRangeAddressItem>& rArrays )
 {
     for (const FormulaBuffer::TokenRangeAddressItem& rAddressItem : rArrays)
@@ -259,6 +260,7 @@ void applyArrayFormulas(
 
         ScCompiler aComp(rDoc.getDoc(), aPos, formula::FormulaGrammar::GRAM_OOXML);
         aComp.SetNumberFormatter(&rFormatter);
+        aComp.SetExternalLinks(rExternalLinks);
         std::unique_ptr<ScTokenArray> pArray(aComp.CompileString(rAddressItem.maTokenAndAddress.maTokenStr));
         if (pArray)
             rDoc.setMatrixCells(rAddressItem.maRange, *pArray, formula::FormulaGrammar::GRAM_OOXML);
@@ -327,7 +329,7 @@ void processSheetFormulaCells(
     }
 
     if (rItem.mpArrayFormulas)
-        applyArrayFormulas(rDoc, rFormatter, *rItem.mpArrayFormulas);
+        applyArrayFormulas(rDoc, rFormatter, rExternalLinks, *rItem.mpArrayFormulas);
 
     if (rItem.mpCellFormulaValues)
         applyCellFormulaValues(rDoc, *rItem.mpCellFormulaValues, bGeneratorKnownGood);