tdf#79998 FILESAVE: XLSX export with long sheet names (length > 31 characters)

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

4 files changed, 154 insertions, 0 deletions

diff --git a/sc/qa/unit/data/ods/tdf79998.ods b/sc/qa/unit/data/ods/tdf79998.ods
new file mode 100644
index 000000000000..201cca140585
--- /dev/null
+++ b/sc/qa/unit/data/ods/tdf79998.ods
diff --git a/sc/qa/unit/filters-test.cxx b/sc/qa/unit/filters-test.cxx
index dc097180cbbd..43c3483bb281 100644
--- a/sc/qa/unit/filters-test.cxx
+++ b/sc/qa/unit/filters-test.cxx
@@ -70,6 +70,7 @@ public:
     void testSharedFormulaXLSX();
     void testSharedFormulaRefUpdateXLSX();
     void testSheetNamesXLSX();
+    void testTdf79998();
     void testLegacyCellAnchoredRotatedShape();
     void testEnhancedProtectionXLS();
     void testEnhancedProtectionXLSX();
@@ -96,6 +97,7 @@ public:
     CPPUNIT_TEST(testSharedFormulaXLSX);
     CPPUNIT_TEST(testSharedFormulaRefUpdateXLSX);
     CPPUNIT_TEST(testSheetNamesXLSX);
+    CPPUNIT_TEST(testTdf79998);
     CPPUNIT_TEST(testLegacyCellAnchoredRotatedShape);
     CPPUNIT_TEST(testEnhancedProtectionXLS);
     CPPUNIT_TEST(testEnhancedProtectionXLSX);
@@ -468,6 +470,24 @@ void ScFiltersTest::testSheetNamesXLSX()
     xDocSh->DoClose();
 }
 
+// FILESAVE: XLSX export with long sheet names (length > 31 characters)
+void ScFiltersTest::testTdf79998()
+{
+    // check: original document has tab name > 31 characters
+    ScDocShellRef xDocSh = loadDoc("tdf79998.", FORMAT_ODS);
+    ScDocument& rDoc1 = xDocSh->GetDocument();
+    const std::vector<OUString> aTabNames1 = rDoc1.GetAllTableNames();
+    CPPUNIT_ASSERT_EQUAL(OUString("Utilities (FX Kurse, Kreditkarten etc)"), aTabNames1[1]);
+
+    // check: saved XLSX document has truncated tab name
+    xDocSh = saveAndReload( &(*xDocSh), FORMAT_XLSX);
+    ScDocument& rDoc2 = xDocSh->GetDocument();
+    const std::vector<OUString> aTabNames2 = rDoc2.GetAllTableNames();
+    CPPUNIT_ASSERT_EQUAL(OUString("Utilities (FX Kurse, Kreditkart"), aTabNames2[1]);
+
+    xDocSh->DoClose();
+}
+
 static void impl_testLegacyCellAnchoredRotatedShape( ScDocument& rDoc, const tools::Rectangle& aRect, const ScDrawObjData& aAnchor, long TOLERANCE = 30 /* 30 hmm */ )
 {
     ScDrawLayer* pDrawLayer = rDoc.GetDrawLayer();
diff --git a/sc/source/filter/excel/xestream.cxx b/sc/source/filter/excel/xestream.cxx
index 6b1e29a79a66..968a057f9f58 100644
--- a/sc/source/filter/excel/xestream.cxx
+++ b/sc/source/filter/excel/xestream.cxx
@@ -32,6 +32,7 @@
 #include <tools/urlobj.hxx>
 #include <vcl/svapp.hxx>
 #include <vcl/settings.hxx>
+#include <officecfg/Office/Calc.hxx>
 
 #include <docuno.hxx>
 #include <xestream.hxx>
@@ -49,6 +50,7 @@
 #include <globstr.hrc>
 #include <scresid.hxx>
 #include <root.hxx>
+#include <sfx2/app.hxx>
 
 #include <docsh.hxx>
 #include <viewdata.hxx>
@@ -1002,6 +1004,13 @@ bool XclExpXmlStream::exportDocument()
     ScDocument& rDoc = pShell->GetDocument();
     ScRefreshTimerProtector aProt(rDoc.GetRefreshTimerControlAddress());
 
+    const bool bValidateTabNames = officecfg::Office::Calc::Filter::Export::MS_Excel::TruncateLongSheetNames::get();
+    std::vector<OUString> aOriginalTabNames;
+    if (bValidateTabNames)
+    {
+        validateTabNames(aOriginalTabNames);
+    }
+
     uno::Reference<task::XStatusIndicator> xStatusIndicator = getStatusIndicator();
 
     if (xStatusIndicator.is())
@@ -1103,6 +1112,11 @@ bool XclExpXmlStream::exportDocument()
 
     commitStorage();
 
+    if (bValidateTabNames)
+    {
+        restoreTabNames(aOriginalTabNames);
+    }
+
     if (xStatusIndicator.is())
         xStatusIndicator->end();
     mpRoot = nullptr;
@@ -1119,4 +1133,119 @@ OUString XclExpXmlStream::getImplementationName()
     return "TODO";
 }
 
+void XclExpXmlStream::validateTabNames(std::vector<OUString>& aOriginalTabNames)
+{
+    const int MAX_TAB_NAME_LENGTH = 31;
+
+    ScDocShell* pShell = getDocShell();
+    ScDocument& rDoc = pShell->GetDocument();
+
+    // get original names
+    aOriginalTabNames.resize(rDoc.GetTableCount());
+    for (SCTAB nTab=0; nTab < rDoc.GetTableCount(); nTab++)
+    {
+        rDoc.GetName(nTab, aOriginalTabNames[nTab]);
+    }
+
+    // new tab names
+    std::vector<OUString> aNewTabNames;
+    aNewTabNames.reserve(rDoc.GetTableCount());
+
+    // check and rename
+    for (SCTAB nTab=0; nTab < rDoc.GetTableCount(); nTab++)
+    {
+        const OUString& rOriginalName = aOriginalTabNames[nTab];
+        if (rOriginalName.getLength() > MAX_TAB_NAME_LENGTH)
+        {
+            OUString aNewName;
+
+            // let's try just truncate "<first 31 chars>"
+            if (aNewName.isEmpty())
+            {
+                aNewName = rOriginalName.copy(0, MAX_TAB_NAME_LENGTH);
+                if (aNewTabNames.end() != std::find(aNewTabNames.begin(), aNewTabNames.end(), aNewName) ||
+                    aOriginalTabNames.end() != std::find(aOriginalTabNames.begin(), aOriginalTabNames.end(), aNewName))
+                {
+                    // was found => let's use another tab name
+                    aNewName.clear();
+                }
+            }
+
+            // let's try "<first N chars>-XXX" template
+            for (int digits=1; digits<10 && aNewName.isEmpty(); digits++)
+            {
+                const int rangeStart = pow(10, digits - 1);
+                const int rangeEnd = pow(10, digits);
+
+                for (int i=rangeStart; i<rangeEnd && aNewName.isEmpty(); i++)
+                {
+                    aNewName = rOriginalName.copy(0, MAX_TAB_NAME_LENGTH - 1 - digits).concat("-").concat(OUString::number(i));
+                    if (aNewTabNames.end() != std::find(aNewTabNames.begin(), aNewTabNames.end(), aNewName) ||
+                        aOriginalTabNames.end() != std::find(aOriginalTabNames.begin(), aOriginalTabNames.end(), aNewName))
+                    {
+                        // was found => let's use another tab name
+                        aNewName.clear();
+                    }
+                }
+            }
+
+            if (!aNewName.isEmpty())
+            {
+                // new name was created => rename
+                renameTab(nTab, aNewName);
+                aNewTabNames.push_back(aNewName);
+            }
+            else
+            {
+                // default: do not rename
+                aNewTabNames.push_back(rOriginalName);
+            }
+        }
+        else
+        {
+            // default: do not rename
+            aNewTabNames.push_back(rOriginalName);
+        }
+    }
+}
+
+void XclExpXmlStream::restoreTabNames(const std::vector<OUString>& aOriginalTabNames)
+{
+    ScDocShell* pShell = getDocShell();
+    ScDocument& rDoc = pShell->GetDocument();
+
+    for (SCTAB nTab=0; nTab < rDoc.GetTableCount(); nTab++)
+    {
+        const OUString& rOriginalName = aOriginalTabNames[nTab];
+
+        OUString rModifiedName;
+        rDoc.GetName(nTab, rModifiedName);
+
+        if (rOriginalName != rModifiedName)
+        {
+            renameTab(nTab, rOriginalName);
+        }
+    }
+}
+
+void XclExpXmlStream::renameTab(SCTAB aTab, OUString aNewName)
+{
+    ScDocShell* pShell = getDocShell();
+    ScDocument& rDoc = pShell->GetDocument();
+
+    bool bAutoCalcShellDisabled = rDoc.IsAutoCalcShellDisabled();
+    bool bIdleEnabled = rDoc.IsIdleEnabled();
+
+    rDoc.SetAutoCalcShellDisabled( true );
+    rDoc.EnableIdle(false);
+
+    if (rDoc.RenameTab(aTab, aNewName))
+    {
+        SfxGetpApp()->Broadcast(SfxHint(SfxHintId::ScTablesChanged));
+    }
+
+    rDoc.SetAutoCalcShellDisabled( bAutoCalcShellDisabled );
+    rDoc.EnableIdle(bIdleEnabled);
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/filter/inc/xestream.hxx b/sc/source/filter/inc/xestream.hxx
index 124aacb0b27e..5ab660989af8 100644
--- a/sc/source/filter/inc/xestream.hxx
+++ b/sc/source/filter/inc/xestream.hxx
@@ -29,6 +29,7 @@
 #include <tools/stream.hxx>
 #include <formula/errorcodes.hxx>
 #include "ftools.hxx"
+#include <types.hxx>
 
 #include <filter/msfilter/mscodec.hxx>
 #include <vector>
@@ -338,6 +339,10 @@ private:
             WriteAttribute(nAttr, OUString(sVal, strlen(sVal), RTL_TEXTENCODING_UTF8));
     }
 
+    void validateTabNames(std::vector<OUString>& aOriginalTabNames);
+    void restoreTabNames(const std::vector<OUString>& aOriginalTabNames);
+    void renameTab(SCTAB aTab, OUString aNewName);
+
     typedef std::map< OUString,
         std::pair< OUString,
             sax_fastparser::FSHelperPtr > >     XclExpXmlPathToStateMap;