tdf#127301 XLSX import: hide hidden named range of autofilter

Create but not insert/add hidden FILTER_CRITERIA (_xlnm._FilterDatabase)
to ScRangeName, because no need for them, so avoid of creating
extra named ranges by autofilter re-range (shown in Sheet ->
Named Ranges and Expressions -> Manage...).

See also commit 3a2263a20629b0a3e9b4935dd43dc59817d0241c
"mib17: #163146# XLS import: do not skip hidden names[...]".

Change-Id: Ibfd86942a99b251a59394955bc890e6dbe0390f3
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/115189
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/130414
Tested-by: Jenkins CollaboraOffice <jenkinscollaboraoffice@gmail.com>
Reviewed-by: Andras Timar <andras.timar@collabora.com>

5 files changed, 44 insertions, 18 deletions

diff --git a/sc/qa/unit/data/xlsx/autofilternamedrange.xlsx b/sc/qa/unit/data/xlsx/autofilternamedrange.xlsx
new file mode 100644
index 000000000000..51490d9229ea
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/autofilternamedrange.xlsx
diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx
index 8f558b4e7f3f..158faa95228f 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -301,6 +301,7 @@ public:
     void testTdf129940();
     void testTdf139763ShapeAnchor();
     void testEscapedUnicodeXLSX();
+    void testAutofilterNamedRangesXLSX();
 
     CPPUNIT_TEST_SUITE(ScFiltersTest);
     CPPUNIT_TEST(testCondFormatOperatorsSameRangeXLSX);
@@ -487,6 +488,7 @@ public:
     CPPUNIT_TEST(testTdf129940);
     CPPUNIT_TEST(testTdf139763ShapeAnchor);
     CPPUNIT_TEST(testEscapedUnicodeXLSX);
+    CPPUNIT_TEST(testAutofilterNamedRangesXLSX);
 
     CPPUNIT_TEST_SUITE_END();
 
@@ -5469,6 +5471,26 @@ void ScFiltersTest::testEscapedUnicodeXLSX()
     xDocSh->DoClose();
 }
 
+void ScFiltersTest::testAutofilterNamedRangesXLSX()
+{
+    ScDocShellRef xDocSh = loadDoc(u"autofilternamedrange.", FORMAT_XLSX);
+    CPPUNIT_ASSERT_MESSAGE("Failed to load autofilternamedrange.xlsx", xDocSh.is());
+
+    ScDocument& rDoc = xDocSh->GetDocument();
+    const ScDBData* pDBData = rDoc.GetDBCollection()->GetDBNearCursor(0, 0, 0);
+    CPPUNIT_ASSERT(pDBData);
+    ScRange aRange;
+    pDBData->GetArea(aRange);
+    CPPUNIT_ASSERT_EQUAL(ScRange(0, 0, 0, 0, 3, 0), aRange);
+    OUString aPosStr;
+    // test there is no '_xlnm._FilterDatabase' named range on the filter area
+    const ScRangeData* pRData = rDoc.GetRangeAtBlock(aRange, &aPosStr);
+    CPPUNIT_ASSERT(!pRData);
+    CPPUNIT_ASSERT_EQUAL(OUString(), aPosStr);
+
+    xDocSh->DoClose();
+}
+
 ScFiltersTest::ScFiltersTest()
       : ScBootstrapFixture( "sc/qa/unit/data" )
 {
diff --git a/sc/source/filter/inc/workbookhelper.hxx b/sc/source/filter/inc/workbookhelper.hxx
index 0fd9ca5ebc64..7110e5ba47ff 100644
--- a/sc/source/filter/inc/workbookhelper.hxx
+++ b/sc/source/filter/inc/workbookhelper.hxx
@@ -168,7 +168,7 @@ public:
                             OUString& orName,
                             const css::uno::Sequence< css::sheet::FormulaToken>& rTokens,
                             sal_Int32 nIndex,
-                            sal_Int32 nNameFlags ) const;
+                            sal_Int32 nNameFlags, bool bHidden ) const;
 
     /** Creates and returns a defined name on-the-fly in the sheet.
         The name will not be buffered in the global defined names buffer.
@@ -177,7 +177,7 @@ public:
                             OUString& orName,
                             const css::uno::Sequence< css::sheet::FormulaToken>& rTokens,
                             sal_Int32 nIndex,
-                            sal_Int32 nNameFlags, sal_Int32 nTab ) const;
+                            sal_Int32 nNameFlags, sal_Int32 nTab, bool bHidden ) const;
 
     /** Creates and returns a database range on-the-fly in the Calc document.
         The range will not be buffered in the global table buffer.
diff --git a/sc/source/filter/oox/defnamesbuffer.cxx b/sc/source/filter/oox/defnamesbuffer.cxx
index 769e36f24939..efd4636acb14 100644
--- a/sc/source/filter/oox/defnamesbuffer.cxx
+++ b/sc/source/filter/oox/defnamesbuffer.cxx
@@ -225,16 +225,17 @@ void DefinedName::createNameObject( sal_Int32 nIndex )
     using namespace ::com::sun::star::sheet::NamedRangeFlag;
     if( !isGlobalName() ) switch( mcBuiltinId )
     {
-        case BIFF_DEFNAME_CRITERIA:     nNameFlags = FILTER_CRITERIA;               break;
-        case BIFF_DEFNAME_PRINTAREA:    nNameFlags = PRINT_AREA;                    break;
-        case BIFF_DEFNAME_PRINTTITLES:  nNameFlags = COLUMN_HEADER | ROW_HEADER;    break;
+        case BIFF_DEFNAME_CRITERIA:
+        case BIFF_DEFNAME_FILTERDATABASE: nNameFlags = FILTER_CRITERIA;               break;
+        case BIFF_DEFNAME_PRINTAREA:      nNameFlags = PRINT_AREA;                    break;
+        case BIFF_DEFNAME_PRINTTITLES:    nNameFlags = COLUMN_HEADER | ROW_HEADER;    break;
     }
 
     // create the name and insert it into the document, maCalcName will be changed to the resulting name
     if (maModel.mnSheet >= 0)
-        mpScRangeData = createLocalNamedRangeObject( maCalcName, ApiTokenSequence(), nIndex, nNameFlags, maModel.mnSheet );
+        mpScRangeData = createLocalNamedRangeObject( maCalcName, ApiTokenSequence(), nIndex, nNameFlags, maModel.mnSheet, maModel.mbHidden );
     else
-        mpScRangeData = createNamedRangeObject( maCalcName, ApiTokenSequence(), nIndex, nNameFlags );
+        mpScRangeData = createNamedRangeObject( maCalcName, ApiTokenSequence(), nIndex, nNameFlags, maModel.mbHidden );
     mnTokenIndex = nIndex;
 }
 
diff --git a/sc/source/filter/oox/workbookhelper.cxx b/sc/source/filter/oox/workbookhelper.cxx
index 10b2fa3fa741..c1fae95bef21 100644
--- a/sc/source/filter/oox/workbookhelper.cxx
+++ b/sc/source/filter/oox/workbookhelper.cxx
@@ -150,9 +150,9 @@ public:
     /** Returns the specified cell or page style from the Calc document. */
     Reference< XStyle > getStyleObject( const OUString& rStyleName, bool bPageStyle ) const;
     /** Creates and returns a defined name on-the-fly in the Calc document. */
-    ScRangeData* createNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags );
+    ScRangeData* createNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, bool bHidden );
     /** Creates and returns a defined name on the-fly in the correct Calc sheet. */
-    ScRangeData* createLocalNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, sal_Int32 nTab );
+    ScRangeData* createLocalNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, sal_Int32 nTab, bool bHidden );
     /** Creates and returns a database range on-the-fly in the Calc document. */
     Reference< XDatabaseRange > createDatabaseRangeObject( OUString& orName, const ScRange& rRangeAddr );
     /** Creates and returns an unnamed database range on-the-fly in the Calc document. */
@@ -351,7 +351,7 @@ Reference< XStyle > WorkbookGlobals::getStyleObject( const OUString& rStyleName,
 
 namespace {
 
-ScRangeData* lcl_addNewByNameAndTokens( ScDocument& rDoc, ScRangeName* pNames, const OUString& rName, const Sequence<FormulaToken>& rTokens, sal_Int16 nIndex, sal_Int32 nUnoType )
+ScRangeData* lcl_addNewByNameAndTokens( ScDocument& rDoc, ScRangeName* pNames, const OUString& rName, const Sequence<FormulaToken>& rTokens, sal_Int16 nIndex, sal_Int32 nUnoType, bool bHidden )
 {
     bool bDone = false;
     ScRangeData::Type nNewType = ScRangeData::Type::Name;
@@ -365,6 +365,9 @@ ScRangeData* lcl_addNewByNameAndTokens( ScDocument& rDoc, ScRangeName* pNames, c
     pNew->GuessPosition();
     if ( nIndex )
         pNew->SetIndex( nIndex );
+    // create but not insert hidden FILTER_CRITERIA named ranges to ScRangeName
+    if ( bHidden && nNewType == ScRangeData::Type::Criteria )
+        return pNew;
     if ( pNames->insert(pNew) )
         bDone = true;
     if (!bDone)
@@ -385,7 +388,7 @@ OUString findUnusedName( const ScRangeName* pRangeName, const OUString& rSuggest
 }
 
 ScRangeData* WorkbookGlobals::createNamedRangeObject(
-    OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags )
+    OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, bool bHidden )
 {
     // create the name and insert it into the Calc document
     ScRangeData* pScRangeData = nullptr;
@@ -396,13 +399,13 @@ ScRangeData* WorkbookGlobals::createNamedRangeObject(
         // find an unused name
         orName = findUnusedName( pNames, orName );
         // create the named range
-        pScRangeData = lcl_addNewByNameAndTokens( rDoc, pNames, orName, rTokens, nIndex, nNameFlags );
+        pScRangeData = lcl_addNewByNameAndTokens( rDoc, pNames, orName, rTokens, nIndex, nNameFlags, bHidden );
     }
     return pScRangeData;
 }
 
 ScRangeData* WorkbookGlobals::createLocalNamedRangeObject(
-    OUString& orName, const Sequence< FormulaToken >&  rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, sal_Int32 nTab )
+    OUString& orName, const Sequence< FormulaToken >&  rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, sal_Int32 nTab, bool bHidden )
 {
     // create the name and insert it into the Calc document
     ScRangeData* pScRangeData = nullptr;
@@ -415,7 +418,7 @@ ScRangeData* WorkbookGlobals::createLocalNamedRangeObject(
         // find an unused name
         orName = findUnusedName( pNames, orName );
         // create the named range
-        pScRangeData = lcl_addNewByNameAndTokens( rDoc, pNames, orName, rTokens, nIndex, nNameFlags );
+        pScRangeData = lcl_addNewByNameAndTokens( rDoc, pNames, orName, rTokens, nIndex, nNameFlags, bHidden );
     }
     return pScRangeData;
 }
@@ -860,14 +863,14 @@ Reference< XStyle > WorkbookHelper::getStyleObject( const OUString& rStyleName,
     return mrBookGlob.getStyleObject( rStyleName, bPageStyle );
 }
 
-ScRangeData* WorkbookHelper::createNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags ) const
+ScRangeData* WorkbookHelper::createNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, bool bHidden ) const
 {
-    return mrBookGlob.createNamedRangeObject( orName, rTokens, nIndex, nNameFlags );
+    return mrBookGlob.createNamedRangeObject( orName, rTokens, nIndex, nNameFlags, bHidden );
 }
 
-ScRangeData* WorkbookHelper::createLocalNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, sal_Int32 nTab ) const
+ScRangeData* WorkbookHelper::createLocalNamedRangeObject( OUString& orName, const Sequence< FormulaToken>& rTokens, sal_Int32 nIndex, sal_Int32 nNameFlags, sal_Int32 nTab, bool bHidden ) const
 {
-    return mrBookGlob.createLocalNamedRangeObject( orName, rTokens, nIndex, nNameFlags, nTab );
+    return mrBookGlob.createLocalNamedRangeObject( orName, rTokens, nIndex, nNameFlags, nTab, bHidden );
 }
 
 Reference< XDatabaseRange > WorkbookHelper::createDatabaseRangeObject( OUString& orName, const ScRange& rRangeAddr ) const