tdf#108673 XLSX: Don't export invalid sheet references in cell validation

Change-Id: Id9d88f5e34f3017516f693505df4c3ce82b1890f
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/98479
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>
(cherry picked from commit a3b4831208da615789bd1e2d5660dd130807f504)
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/102127
Reviewed-by: Thorsten Behrens <Thorsten.Behrens@CIB.de>

5 files changed, 63 insertions, 3 deletions

diff --git a/sc/inc/clipparam.hxx b/sc/inc/clipparam.hxx
index ee9422bc27db..575a04526763 100644
--- a/sc/inc/clipparam.hxx
+++ b/sc/inc/clipparam.hxx
@@ -27,7 +27,7 @@
  * This struct stores general clipboard parameters associated with a
  * ScDocument instance created in clipboard mode.
  */
-struct ScClipParam
+struct SC_DLLPUBLIC ScClipParam
 {
     enum Direction { Unspecified, Column, Row };
 
diff --git a/sc/inc/document.hxx b/sc/inc/document.hxx
index 0c5ccc248faf..971377559ce9 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -1560,7 +1560,7 @@ public:
                                                SCTAB nTab, InsertDeleteFlags nDelFlag);
     void                         DeleteAreaTab(const ScRange& rRange, InsertDeleteFlags nDelFlag);
 
-    void                         CopyToClip( const ScClipParam& rClipParam, ScDocument* pClipDoc,
+    SC_DLLPUBLIC void            CopyToClip( const ScClipParam& rClipParam, ScDocument* pClipDoc,
                                              const ScMarkData* pMarks, bool bKeepScenarioFlags,
                                              bool bIncludeObjects );
 
@@ -1611,7 +1611,7 @@ public:
 
     /** If pDestRanges is given it overrides rDestRange, rDestRange in this
         case is the overall encompassing range. */
-    void                CopyFromClip( const ScRange& rDestRange, const ScMarkData& rMark,
+    SC_DLLPUBLIC void   CopyFromClip( const ScRange& rDestRange, const ScMarkData& rMark,
                                         InsertDeleteFlags nInsFlag,
                                         ScDocument* pRefUndoDoc,
                                         ScDocument* pClipDoc,
diff --git a/sc/qa/unit/data/ods/validation-copypaste.ods b/sc/qa/unit/data/ods/validation-copypaste.ods
new file mode 100644
index 000000000000..55f8d823b88b
--- /dev/null
+++ b/sc/qa/unit/data/ods/validation-copypaste.ods
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 2f0a7c7fdd0e..9647d0964277 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -43,6 +43,7 @@
 #include <scmod.hxx>
 #include <dpcache.hxx>
 #include <dpobject.hxx>
+#include <clipparam.hxx>
 
 #include <svx/svdpage.hxx>
 #include <svx/svdograf.hxx>
@@ -247,6 +248,7 @@ public:
     void testTdf126177XLSX();
     void testCommentTextVAlignment();
     void testCommentTextHAlignment();
+    void testValidationCopyPaste();
 
     void testXltxExport();
     void testRotatedImageODS();
@@ -398,6 +400,7 @@ public:
     CPPUNIT_TEST(testTdf126177XLSX);
     CPPUNIT_TEST(testCommentTextVAlignment);
     CPPUNIT_TEST(testCommentTextHAlignment);
+    CPPUNIT_TEST(testValidationCopyPaste);
 
     CPPUNIT_TEST(testXltxExport);
     CPPUNIT_TEST(testRotatedImageODS);
@@ -4797,6 +4800,42 @@ void ScExportTest::testTdf91634XLSX()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testValidationCopyPaste()
+{
+    ScDocShellRef xDocSh = loadDoc("validation-copypaste.", FORMAT_ODS);
+    CPPUNIT_ASSERT(xDocSh.is());
+    ScDocument& rSrcDoc = xDocSh->GetDocument();
+
+    // Copy B1 from src doc to clip
+    ScDocument aClipDoc(SCDOCMODE_CLIP);
+    ScRange aSrcRange(1, 0, 1);
+    ScClipParam aClipParam(aSrcRange, false);
+    ScMarkData aMark(rSrcDoc.GetSheetLimits().mnMaxRow, rSrcDoc.GetSheetLimits().mnMaxCol);
+    aMark.SetMarkArea(aSrcRange);
+    rSrcDoc.CopyToClip(aClipParam, &aClipDoc, &aMark, false, false);
+
+    // Create second document, paste B1 from clip
+    ScDocShell* pShell2
+        = new ScDocShell(SfxModelFlags::EMBEDDED_OBJECT | SfxModelFlags::DISABLE_EMBEDDED_SCRIPTS
+                         | SfxModelFlags::DISABLE_DOCUMENT_RECOVERY);
+    pShell2->DoInitNew();
+    ScDocument& rDestDoc = pShell2->GetDocument();
+    ScRange aDstRange(1, 0, 0);
+    ScMarkData aMark2(rDestDoc.GetSheetLimits().mnMaxRow, rDestDoc.GetSheetLimits().mnMaxCol);
+    aMark2.SetMarkArea(aDstRange);
+    rDestDoc.CopyFromClip(aDstRange, aMark2, InsertDeleteFlags::ALL, nullptr, &aClipDoc);
+
+    // save as XLSX
+    std::shared_ptr<utl::TempFile> pXPathFile
+        = ScBootstrapFixture::exportTo(&(*pShell2), FORMAT_XLSX);
+
+    // check validation
+    xmlDocUniquePtr pDoc
+        = XPathHelper::parseExport(pXPathFile, m_xSFactory, "xl/worksheets/sheet1.xml");
+    CPPUNIT_ASSERT(pDoc);
+    assertXPathContent(pDoc, "/x:worksheet/x:dataValidations/x:dataValidation/x:formula1", "#REF!");
+}
+
 void ScExportTest::testTdf115159()
 {
     ScDocShellRef xShell = loadDoc("tdf115159.", FORMAT_XLSX);
diff --git a/sc/source/core/tool/compiler.cxx b/sc/source/core/tool/compiler.cxx
index 6e12d8100ed3..eeb8beb50556 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -1458,6 +1458,27 @@ struct ConventionXL_OOX : public ConventionXL_A1
             return;
         }
 
+        {
+            ScAddress aAbs1 = rRef.Ref1.toAbs(rLimits, rPos);
+            if (!rLimits.ValidAddress(aAbs1)
+                || o3tl::make_unsigned(aAbs1.Tab()) >= rTabNames.size())
+            {
+                rBuf.append(rErrRef);
+                return;
+            }
+        }
+
+        if (!bSingleRef)
+        {
+            ScAddress aAbs2 = rRef.Ref2.toAbs(rLimits, rPos);
+            if (!rLimits.ValidAddress(aAbs2)
+                || o3tl::make_unsigned(aAbs2.Tab()) >= rTabNames.size())
+            {
+                rBuf.append(rErrRef);
+                return;
+            }
+        }
+
         ConventionXL_A1::makeRefStr( rLimits, rBuf, eGram, aPos, rErrRef, rTabNames, rRef, bSingleRef, bFromRangeName);
     }