tdf#109240, tdf#112571: don't export dupe built-in named ranges

XclTools::GetBuiltInDefNameIndex(...) only checked for prefix used in
binary Excel format, and didn't recognize OOXML built-in names, which
resulted in saving them twice in OOXML files.

Adapt to check both binary and OOXML prefixes, similarly to
XclTools::IsBuiltInStyleName(...).

Saving "bad" files after the fix will purge bad "_0", "_0_0" etc.
suffixed built-in names due to how GetBuiltInDefNameIndex(...) works.

Change-Id: I1bbe11f9c654a142a4626003df4cb0fd2a0f9c71
Reviewed-on: https://gerrit.libreoffice.org/45381
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

4 files changed, 35 insertions, 3 deletions

diff --git a/sc/qa/unit/data/xlsx/built-in_ranges.xlsx b/sc/qa/unit/data/xlsx/built-in_ranges.xlsx
new file mode 100644
index 000000000000..b18a4862fdd6
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/built-in_ranges.xlsx
diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx
index 0e7a3913ba35..06ed9dcdf9ad 100644
--- a/sc/qa/unit/subsequent_export-test.cxx
+++ b/sc/qa/unit/subsequent_export-test.cxx
@@ -103,6 +103,7 @@ public:
     void testConditionalFormatRangeListXLSX();
     void testMiscRowHeightExport();
     void testNamedRangeBugfdo62729();
+    void testBuiltinRangesXLSX();
     void testRichTextExportODS();
     void testRichTextCellFormatXLSX();
     void testFormulaRefSheetNameODS();
@@ -212,6 +213,7 @@ public:
     CPPUNIT_TEST(testConditionalFormatRangeListXLSX);
     CPPUNIT_TEST(testMiscRowHeightExport);
     CPPUNIT_TEST(testNamedRangeBugfdo62729);
+    CPPUNIT_TEST(testBuiltinRangesXLSX);
     CPPUNIT_TEST(testRichTextExportODS);
     CPPUNIT_TEST(testRichTextCellFormatXLSX);
     CPPUNIT_TEST(testFormulaRefSheetNameODS);
@@ -1159,6 +1161,32 @@ void ScExportTest::testNamedRangeBugfdo62729()
     xDocSh->DoClose();
 }
 
+void ScExportTest::testBuiltinRangesXLSX()
+{
+    ScDocShellRef xShell = loadDoc("built-in_ranges.", FORMAT_XLSX);
+    CPPUNIT_ASSERT(xShell.is());
+    ScDocShellRef xDocSh = saveAndReload(xShell.get(), FORMAT_XLSX);
+    CPPUNIT_ASSERT(xDocSh.is());
+    xShell->DoClose();
+
+    xmlDocPtr pDoc = XPathHelper::parseExport(*xDocSh, m_xSFactory, "xl/workbook.xml", FORMAT_XLSX);
+    CPPUNIT_ASSERT(pDoc);
+
+    //assert the existing OOXML built-in names are still there
+    assertXPathContent(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm._FilterDatabase'][@localSheetId='0']", "'Sheet1 Test'!$A$1:$A$5");
+    assertXPathContent(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm._FilterDatabase'][@localSheetId='1']", "'Sheet2 Test'!$K$10:$K$14");
+    assertXPathContent(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm.Print_Area'][@localSheetId='0']", "'Sheet1 Test'!$A$1:$A$5");
+    assertXPathContent(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm.Print_Area'][@localSheetId='1']", "'Sheet2 Test'!$K$10:$M$18");
+
+    //...and that no extra ones are added (see tdf#112571)
+    assertXPath(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm._FilterDatabase_0'][@localSheetId='0']", 0);
+    assertXPath(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm._FilterDatabase_0'][@localSheetId='1']", 0);
+    assertXPath(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm.Print_Area_0'][@localSheetId='0']", 0);
+    assertXPath(pDoc, "/x:workbook/x:definedNames/x:definedName[@name='_xlnm.Print_Area_0'][@localSheetId='1']", 0);
+
+    xDocSh->DoClose();
+}
+
 void ScExportTest::testRichTextExportODS()
 {
     struct
diff --git a/sc/source/filter/excel/xltools.cxx b/sc/source/filter/excel/xltools.cxx
index 34e572460f4a..cffb346ec27b 100644
--- a/sc/source/filter/excel/xltools.cxx
+++ b/sc/source/filter/excel/xltools.cxx
@@ -482,8 +482,12 @@ OUString XclTools::GetBuiltInDefNameXml( sal_Unicode cBuiltIn )
 
 sal_Unicode XclTools::GetBuiltInDefNameIndex( const OUString& rDefName )
 {
-    sal_Int32 nPrefixLen = strlen(maDefNamePrefix);
+    sal_Int32 nPrefixLen = 0;
     if( rDefName.startsWithIgnoreAsciiCase( maDefNamePrefix ) )
+        nPrefixLen = strlen(maDefNamePrefix);
+    else if( rDefName.startsWithIgnoreAsciiCase( maDefNamePrefixXml ) )
+        nPrefixLen = strlen(maDefNamePrefixXml);
+    if( nPrefixLen > 0 )
     {
         for( sal_Unicode cBuiltIn = 0; cBuiltIn < EXC_BUILTIN_UNKNOWN; ++cBuiltIn )
         {
diff --git a/sc/source/filter/inc/xltools.hxx b/sc/source/filter/inc/xltools.hxx
index a4a930e9fe47..497cdf9b3c60 100644
--- a/sc/source/filter/inc/xltools.hxx
+++ b/sc/source/filter/inc/xltools.hxx
@@ -189,8 +189,8 @@ public:
     static OUString GetBuiltInDefNameXml( sal_Unicode cBuiltIn );
     /** Returns the Excel built-in name index of the passed defined name from Calc.
         @descr  Ignores any characters following a valid representation of a built-in name.
-        @param pcBuiltIn  (out-param) If not 0, the index of the built-in name will be returned here.
-        @return  true = passed string is a built-in name; false = user-defined name. */
+        @param rDefName  raw English UI representation of a built-in defined name used in NAME records.
+        @return  the index of the built-in name, or EXC_BUILTIN_UNKNOWN if it is not a built-in name. */
     static sal_Unicode  GetBuiltInDefNameIndex( const OUString& rDefName );
 
     // built-in style names ---------------------------------------------------