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/+/102128
Reviewed-by: Thorsten Behrens <Thorsten.Behrens@CIB.de>

5 files changed, 61 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 63009443b919..8091f7e2ffb9 100644
--- a/sc/inc/document.hxx
+++ b/sc/inc/document.hxx
@@ -1536,7 +1536,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 );
 
@@ -1587,7 +1587,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 b7b41d40addb..61f6a46eba19 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -41,6 +41,7 @@
 #include <scmod.hxx>
 #include <dpcache.hxx>
 #include <dpobject.hxx>
+#include <clipparam.hxx>
 
 #include <svx/svdpage.hxx>
 #include <svx/svdograf.hxx>
@@ -233,6 +234,7 @@ public:
     void testTdf126177XLSX();
     void testCommentTextVAlignment();
     void testCommentTextHAlignment();
+    void testValidationCopyPaste();
 
     void testXltxExport();
     void testRotatedImageODS();
@@ -368,6 +370,7 @@ public:
     CPPUNIT_TEST(testTdf126177XLSX);
     CPPUNIT_TEST(testCommentTextVAlignment);
     CPPUNIT_TEST(testCommentTextHAlignment);
+    CPPUNIT_TEST(testValidationCopyPaste);
 
     CPPUNIT_TEST(testXltxExport);
     CPPUNIT_TEST(testRotatedImageODS);
@@ -4409,6 +4412,42 @@ void ScExportTest::testTdf91634XLSX()
     assertXPath(pXmlRels, "/r:Relationships/r:Relationship[@Id='rId1']", "TargetMode", "External");
 }
 
+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.MaxRow(), rSrcDoc.MaxCol());
+    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.MaxRow(), rDestDoc.MaxCol());
+    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
+    xmlDocPtr 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 e31ab2731fa3..e26dd00177f9 100644
--- a/sc/source/core/tool/compiler.cxx
+++ b/sc/source/core/tool/compiler.cxx
@@ -1440,6 +1440,25 @@ struct ConventionXL_OOX : public ConventionXL_A1
             return;
         }
 
+        {
+            ScAddress aAbs1 = rRef.Ref1.toAbs(rPos);
+            if (std::make_unsigned_t<sal_Int16>(aAbs1.Tab()) >= rTabNames.size())
+            {
+                rBuf.append(rErrRef);
+                return;
+            }
+        }
+
+        if (!bSingleRef)
+        {
+            ScAddress aAbs2 = rRef.Ref2.toAbs(rPos);
+            if (std::make_unsigned_t<sal_Int16>(aAbs2.Tab()) >= rTabNames.size())
+            {
+                rBuf.append(rErrRef);
+                return;
+            }
+        }
+
         ConventionXL_A1::makeRefStr( pDoc, rBuf, eGram, aPos, rErrRef, rTabNames, rRef, bSingleRef, bFromRangeName);
     }