tdf#84874 XLSX export: truncate validation text

Maximum length allowed in Excel is 255 characters
for title and message of validation input and error,
so truncate them, otherwise Excel throws away the
whole message.

Co-authored-by: Attila Szűcs (NISZ)

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

3 files changed, 56 insertions, 16 deletions

diff --git a/sc/qa/unit/data/ods/tdf84874.ods b/sc/qa/unit/data/ods/tdf84874.ods
new file mode 100644
index 000000000000..8eb87761b321
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf84874.ods
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 6eba2bf05e84..b5e382bc0ecf 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -271,6 +271,7 @@ public:
     void testTdf126305_DataValidatyErrorAlert();
     void testTdf76047_externalLink();
     void testTdf129969();
+    void testTdf84874();
 
     CPPUNIT_TEST_SUITE(ScExportTest);
     CPPUNIT_TEST(test);
@@ -440,6 +441,7 @@ public:
     CPPUNIT_TEST(testTdf126305_DataValidatyErrorAlert);
     CPPUNIT_TEST(testTdf76047_externalLink);
     CPPUNIT_TEST(testTdf129969);
+    CPPUNIT_TEST(testTdf84874);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -5551,6 +5553,37 @@ void ScExportTest::testTdf129969()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testTdf84874()
+{
+    ScDocShellRef xShell = loadDoc("tdf84874.", FORMAT_ODS);
+    CPPUNIT_ASSERT(xShell.is());
+
+    ScDocShellRef xDocSh = saveAndReload(xShell.get(), FORMAT_XLSX);
+    xShell->DoClose();
+    CPPUNIT_ASSERT(xDocSh.is());
+
+    ScDocument& rDoc = xDocSh->GetDocument();
+
+    const ScValidationData* pData = rDoc.GetValidationEntry(1);
+    OUString aTitle, aText;
+    pData->GetInput( aTitle, aText );
+    sal_uInt32 nPromptTitleLen = aTitle.getLength();
+    sal_uInt32 nPromptTextLen = aText.getLength();
+
+    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt32>(255), nPromptTitleLen);
+    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt32>(255), nPromptTextLen);
+
+    ScValidErrorStyle eErrStyle = SC_VALERR_STOP;
+    pData->GetErrMsg( aTitle, aText, eErrStyle );
+    sal_uInt32 nErrorTitleLen = aTitle.getLength();
+    sal_uInt32 nErrorTextLen = aText.getLength();
+
+    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt32>(255), nErrorTitleLen);
+    CPPUNIT_ASSERT_EQUAL(static_cast<sal_uInt32>(255), nErrorTextLen);
+
+    xDocSh->DoClose();
+}
+
 CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest);
 
 CPPUNIT_PLUGIN_IMPLEMENT();
diff --git a/sc/source/filter/excel/xecontent.cxx b/sc/source/filter/excel/xecontent.cxx
index 27f07989c530..2692406d9a2d 100644
--- a/sc/source/filter/excel/xecontent.cxx
+++ b/sc/source/filter/excel/xecontent.cxx
@@ -52,6 +52,7 @@
 #include <oox/export/utils.hxx>
 #include <oox/token/namespaces.hxx>
 #include <oox/token/relationship.hxx>
+#include <comphelper/string.hxx>
 
 using namespace ::oox;
 
@@ -1647,6 +1648,24 @@ const char* lcl_GetErrorType( sal_uInt32 nFlags )
     return nullptr;
 }
 
+void lcl_SetValidationText(const OUString& rText, XclExpString& rValidationText)
+{
+    if ( !rText.isEmpty() )
+    {
+        // maximum length allowed in Excel is 255 characters
+        if ( rText.getLength() > 255 )
+        {
+            OUStringBuffer aBuf( rText );
+            rValidationText.Assign(
+                comphelper::string::truncateToLength(aBuf, 255).makeStringAndClear() );
+        }
+        else
+            rValidationText.Assign( rText );
+    }
+    else
+        rValidationText.Assign( '\0' );
+}
+
 } // namespace
 
 XclExpDV::XclExpDV( const XclExpRoot& rRoot, sal_uLong nScHandle ) :
@@ -1660,26 +1679,14 @@ XclExpDV::XclExpDV( const XclExpRoot& rRoot, sal_uLong nScHandle ) :
         // prompt box - empty string represented by single NUL character
         OUString aTitle, aText;
         bool bShowPrompt = pValData->GetInput( aTitle, aText );
-        if( !aTitle.isEmpty() )
-            maPromptTitle.Assign( aTitle );
-        else
-            maPromptTitle.Assign( '\0' );
-        if( !aText.isEmpty() )
-            maPromptText.Assign( aText );
-        else
-            maPromptText.Assign( '\0' );
+        lcl_SetValidationText(aTitle, maPromptTitle);
+        lcl_SetValidationText(aText, maPromptText);
 
         // error box - empty string represented by single NUL character
         ScValidErrorStyle eScErrorStyle;
         bool bShowError = pValData->GetErrMsg( aTitle, aText, eScErrorStyle );
-        if( !aTitle.isEmpty() )
-            maErrorTitle.Assign( aTitle );
-        else
-            maErrorTitle.Assign( '\0' );
-        if( !aText.isEmpty() )
-            maErrorText.Assign( aText );
-        else
-            maErrorText.Assign( '\0' );
+        lcl_SetValidationText(aTitle, maErrorTitle);
+        lcl_SetValidationText(aText, maErrorText);
 
         // flags
         switch( pValData->GetDataMode() )