sc/inc/typedstrdata.hxx | 7 ++-- sc/qa/uitest/autofilter/autofilter.py | 33 +++++++++++++++++++ sc/qa/uitest/data/autofilter/tdf140968.xlsx |binary sc/source/core/data/column3.cxx | 6 ++- sc/source/core/tool/typedstrdata.cxx | 34 +++++++++++++++----- sc/source/filter/xml/XMLExportDatabaseRanges.cxx | 39 ++++++++++++++++++++--- sc/source/filter/xml/xmlfilti.cxx | 21 ++++++++++-- sc/source/ui/cctrl/checklistmenu.cxx | 10 +++++ sc/source/ui/inc/checklistmenu.hxx | 14 +++++++- sc/source/ui/unoobj/datauno.cxx | 11 ++++-- sc/source/ui/view/gridwin.cxx | 8 ++-- sc/source/ui/view/gridwin2.cxx | 4 +- 12 files changed, 156 insertions(+), 31 deletions(-)
New commits: commit d5c2584bf36d21580db677b231c57f99f49aa2cb Author: Balazs Varga <balazs.varga...@gmail.com> AuthorDate: Thu Apr 22 10:23:41 2021 +0200 Commit: László Németh <nem...@numbertext.org> CommitDate: Tue May 4 10:13:27 2021 +0200 Related: tdf#140968 avoid duplicated filter values Group and filter based on the actual cell format, like MSO does, to simplify filtering of values rounded by the cell format (e.g. selecting the single AutoFilter condition "1.0" to filter both 1.01 and 0.99). Followed up of 4fd1333ba4bb4f2311e9098291154772bd310429 (tdf#140968 tdf#140978 XLSX import: fix lost rounded filters) tdf#141916: odf export: Indicating the formatted filter values, by export the XML_DATA_TYPE with XML_TEXT, to avoid bad filtering of formatted filter values. tdf#141775: fix regression of 4fd1333ba4bb4f2311e9098291154772bd310429 (tdf#140968 tdf#140978 XLSX import: fix lost rounded filters) tdf#141915: fix OOXML import of 0.0 filter values of filter conditions by setting it byValue filtering in datauno.cxx. Change-Id: I1c840249ee1ef95aff33a4740b8bf2ebc47f2325 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/113314 Tested-by: Jenkins Tested-by: László Németh <nem...@numbertext.org> Reviewed-by: László Németh <nem...@numbertext.org> diff --git a/sc/inc/typedstrdata.hxx b/sc/inc/typedstrdata.hxx index 944a13bdad0a..50a7effea87e 100644 --- a/sc/inc/typedstrdata.hxx +++ b/sc/inc/typedstrdata.hxx @@ -24,13 +24,14 @@ public: Header = 4 }; - ScTypedStrData( const OUString& rStr, double nVal = 0.0, - StringType eType = Standard, bool bDate = false ); + ScTypedStrData( const OUString& rStr, double nVal = 0.0, StringType eType = Standard, + bool bDate = false, bool mbIsFormatted = false, bool bDuplicated = false ); bool IsDate() const { return mbIsDate;} const OUString& GetString() const { return maStrValue;} StringType GetStringType() const { return meStrType;} double GetValue() const { return mfValue; } + bool IsDuplicated() const { return mbIsDuplicated; } struct LessCaseSensitive { @@ -59,6 +60,8 @@ private: double mfValue; StringType meStrType; bool mbIsDate; + bool mbIsFormatted; // true if the cell value is a formatted filter value + bool mbIsDuplicated; // true if the cell has a formatted filter value and has at least one duplicate formatted value. }; class FindTypedStrData diff --git a/sc/qa/uitest/autofilter/autofilter.py b/sc/qa/uitest/autofilter/autofilter.py index ccfd23a9d295..7b1c043c67e2 100644 --- a/sc/qa/uitest/autofilter/autofilter.py +++ b/sc/qa/uitest/autofilter/autofilter.py @@ -333,5 +333,38 @@ class AutofilterTest(UITestCase): xOkBtn = xFloatWindow.getChild("cancel") xOkBtn.executeAction("CLICK", tuple()) + self.ui_test.close_doc() + + def test_tdf140968(self): + doc = self.ui_test.load_file(get_url_for_data_file("tdf140968.xlsx")) + + xGridWin = self.xUITest.getTopFocusWindow().getChild("grid_window") + + xGridWin.executeAction("LAUNCH", mkPropertyValues({"AUTOFILTER": "", "COL": "0", "ROW": "0"})) + xFloatWindow = self.xUITest.getFloatWindow() + xCheckListMenu = xFloatWindow.getChild("check_list_menu") + xTreeList = xCheckListMenu.getChild("check_list_box") + self.assertEqual(4, len(xTreeList.getChildren())) + self.assertEqual("0.000", get_state_as_dict(xTreeList.getChild('0'))['Text']) + self.assertEqual("0.046", get_state_as_dict(xTreeList.getChild('1'))['Text']) + self.assertEqual("0.365", get_state_as_dict(xTreeList.getChild('2'))['Text']) + self.assertEqual("0.516", get_state_as_dict(xTreeList.getChild('3'))['Text']) + + xFirstEntry = xTreeList.getChild("0") + xFirstEntry.executeAction("CLICK", tuple()) + xFirstEntry = xTreeList.getChild("1") + xFirstEntry.executeAction("CLICK", tuple()) + + xOkBtn = xFloatWindow.getChild("ok") + xOkBtn.executeAction("CLICK", tuple()) + + self.assertFalse(is_row_hidden(doc, 0)) + self.assertTrue(is_row_hidden(doc, 1)) + self.assertTrue(is_row_hidden(doc, 2)) + self.assertTrue(is_row_hidden(doc, 3)) + self.assertTrue(is_row_hidden(doc, 4)) + self.assertFalse(is_row_hidden(doc, 5)) + self.assertFalse(is_row_hidden(doc, 6)) + self.ui_test.close_doc() # vim: set shiftwidth=4 softtabstop=4 expandtab: diff --git a/sc/qa/uitest/data/autofilter/tdf140968.xlsx b/sc/qa/uitest/data/autofilter/tdf140968.xlsx new file mode 100644 index 000000000000..b2b5b2a3e654 Binary files /dev/null and b/sc/qa/uitest/data/autofilter/tdf140968.xlsx differ diff --git a/sc/source/core/data/column3.cxx b/sc/source/core/data/column3.cxx index 712595aab369..5fd4a5965586 100644 --- a/sc/source/core/data/column3.cxx +++ b/sc/source/core/data/column3.cxx @@ -2495,8 +2495,10 @@ class FilterEntriesHandler sal_uInt32 nIndex = pFormatter->GetFormatIndex(NF_DATETIME_ISO_YYYYMMDD_HHMMSS); pFormatter->GetInputLineString(fVal, nIndex, aStr); } - // maybe extend ScTypedStrData enum is also an option here - mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, ScTypedStrData::Value,bDate)); + /* use string compare later for formatted and filtered cell values + to avoid duplicates in the filter lists with setting the mbIsFormatted */ + bool bFormFiltVal = mrColumn.HasFiltering() && nFormat; + mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, ScTypedStrData::Value, bDate, bFormFiltVal)); mrFilterEntries.addTextColor(textColor); mrFilterEntries.addBackgroundColor(backgroundColor); } diff --git a/sc/source/core/tool/typedstrdata.cxx b/sc/source/core/tool/typedstrdata.cxx index a114b280293c..0420b359ab76 100644 --- a/sc/source/core/tool/typedstrdata.cxx +++ b/sc/source/core/tool/typedstrdata.cxx @@ -47,14 +47,22 @@ bool ScTypedStrData::EqualCaseSensitive::operator() (const ScTypedStrData& left, if (left.meStrType != right.meStrType) return false; - if (left.meStrType == Value && left.mfValue != right.mfValue) + if (left.meStrType == Value && left.mfValue != right.mfValue && + !left.mbIsFormatted) return false; if (left.mbIsDate != right.mbIsDate ) return false; - return ScGlobal::GetCaseCollator()->compareString( - left.maStrValue, right.maStrValue) == 0; + if (ScGlobal::GetCaseCollator()->compareString( + left.maStrValue, right.maStrValue) == 0) + { + // hack: it's possible, because we only compare values of the same filter range + const_cast<bool&>(left.mbIsDuplicated) = true; + return true; + } + else + return false; } bool ScTypedStrData::EqualCaseInsensitive::operator() (const ScTypedStrData& left, const ScTypedStrData& right) const @@ -62,14 +70,22 @@ bool ScTypedStrData::EqualCaseInsensitive::operator() (const ScTypedStrData& lef if (left.meStrType != right.meStrType) return false; - if (left.meStrType == Value && left.mfValue != right.mfValue) + if (left.meStrType == Value && left.mfValue != right.mfValue && + !left.mbIsFormatted) return false; if (left.mbIsDate != right.mbIsDate ) return false; - return ScGlobal::GetCollator()->compareString( - left.maStrValue, right.maStrValue) == 0; + if (ScGlobal::GetCollator()->compareString( + left.maStrValue, right.maStrValue) == 0) + { + // hack: it's possible, because we only compare values of the same filter range + const_cast<bool&>(left.mbIsDuplicated) = true; + return true; + } + else + return false; } bool ScTypedStrData::operator< (const ScTypedStrData& r) const @@ -79,11 +95,13 @@ bool ScTypedStrData::operator< (const ScTypedStrData& r) const } ScTypedStrData::ScTypedStrData( - const OUString& rStr, double nVal, StringType nType, bool bDate ) : + const OUString& rStr, double nVal, StringType nType, bool bDate, bool bFormatted, bool bDuplicated ) : maStrValue(rStr), mfValue(nVal), meStrType(nType), - mbIsDate( bDate ) {} + mbIsDate( bDate ), + mbIsFormatted( bFormatted ), + mbIsDuplicated( bDuplicated ) {} FindTypedStrData::FindTypedStrData(const ScTypedStrData& rVal, bool bCaseSens) : maVal(rVal), mbCaseSens(bCaseSens) {} diff --git a/sc/source/filter/xml/XMLExportDatabaseRanges.cxx b/sc/source/filter/xml/XMLExportDatabaseRanges.cxx index bbcba23903e9..d329bdf2385f 100644 --- a/sc/source/filter/xml/XMLExportDatabaseRanges.cxx +++ b/sc/source/filter/xml/XMLExportDatabaseRanges.cxx @@ -415,11 +415,25 @@ private: class WriteSetItem { ScXMLExport& mrExport; + const ScDocument* mpDoc; public: - explicit WriteSetItem(ScXMLExport& r) : mrExport(r) {} + explicit WriteSetItem(ScXMLExport& r, const ScDocument* pDoc) : mrExport(r), mpDoc(pDoc) {} void operator() (const ScQueryEntry::Item& rItem) const { - mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString()); + if (rItem.meType == ScQueryEntry::ByValue) + { + OUString aValStr; + SvNumberFormatter* pFormatter = mpDoc->GetFormatTable(); + pFormatter->GetInputLineString(rItem.mfVal, 0, aValStr); + mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, aValStr); + } + else + { + // Indicating the formatted filter values, by export the XML_DATA_TYPE with XML_TEXT + if (rItem.meType == ScQueryEntry::ByString && rItem.mbFormattedValue) + mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT); + mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString()); + } SvXMLElementExport aElem(mrExport, XML_NAMESPACE_TABLE, XML_FILTER_SET_ITEM, true, true); } }; @@ -443,7 +457,12 @@ private: // Single item condition. const ScQueryEntry::Item& rItem = rItems.front(); if (rItem.meType == ScQueryEntry::ByString) + { + // Indicating the formatted filter values, by export the XML_DATA_TYPE with XML_TEXT + if (rItem.mbFormattedValue) + mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT); mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString()); + } else { mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_NUMBER); @@ -462,11 +481,23 @@ private: // Store the 1st value for backward compatibility. const ScQueryEntry::Item& rItem = rItems.front(); - mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString()); + if (rItem.meType == ScQueryEntry::ByValue) + { + OUString aValStr; + SvNumberFormatter* pFormatter = mpDoc->GetFormatTable(); + pFormatter->GetInputLineString(rItem.mfVal, 0, aValStr); + mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, aValStr); + } + else + { + if (rItem.mbFormattedValue) + mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATA_TYPE, XML_TEXT); + mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_VALUE, rItem.maString.getString()); + } mrExport.AddAttribute(XML_NAMESPACE_TABLE, XML_OPERATOR, OUString("=")); SvXMLElementExport aElemC(mrExport, XML_NAMESPACE_TABLE, XML_FILTER_CONDITION, true, true); - std::for_each(rItems.begin(), rItems.end(), WriteSetItem(mrExport)); + std::for_each(rItems.begin(), rItems.end(), WriteSetItem(mrExport, mpDoc)); } } diff --git a/sc/source/filter/xml/xmlfilti.cxx b/sc/source/filter/xml/xmlfilti.cxx index 5b5708e7a68b..7e22395a90d9 100644 --- a/sc/source/filter/xml/xmlfilti.cxx +++ b/sc/source/filter/xml/xmlfilti.cxx @@ -281,7 +281,7 @@ ScXMLConditionContext::ScXMLConditionContext( ScXMLImportContext( rImport ), mrQueryParam(rParam), pFilterContext(pTempFilterContext), - sDataType(GetXMLToken(XML_TEXT)), + sDataType(OUString()), nField(0), bIsCaseSensitive(false) { @@ -430,6 +430,8 @@ void SAL_CALL ScXMLConditionContext::endFastElement( sal_Int32 /*nElement*/ ) svl::SharedStringPool& rPool = GetScImport().GetDocument()->GetSharedStringPool(); rItem.maString = rPool.intern(sConditionValue); rItem.meType = ScQueryEntry::ByString; + if (IsXMLToken(sDataType, XML_TEXT)) + rItem.mbFormattedValue = true; } } else @@ -444,22 +446,31 @@ ScXMLSetItemContext::ScXMLSetItemContext( if ( !rAttrList.is() ) return; + ScQueryEntry::Item aItem; + bool bAddSetItem = false; + for (auto &aIter : *rAttrList) { switch (aIter.getToken()) { + case XML_ELEMENT( TABLE, XML_DATA_TYPE ): + { + aItem.mbFormattedValue = IsXMLToken(aIter.toString(), XML_TEXT); + } + break; case XML_ELEMENT( TABLE, XML_VALUE ): { svl::SharedStringPool& rPool = GetScImport().GetDocument()->GetSharedStringPool(); - ScQueryEntry::Item aItem; aItem.maString = rPool.intern(aIter.toString()); aItem.meType = ScQueryEntry::ByString; aItem.mfVal = 0.0; - rParent.AddSetItem(aItem); + bAddSetItem = true; } break; } } + if (bAddSetItem) + rParent.AddSetItem(aItem); } ScXMLSetItemContext::~ScXMLSetItemContext() @@ -649,7 +660,7 @@ ScXMLDPConditionContext::ScXMLDPConditionContext( ScXMLImport& rImport, ScXMLDPFilterContext* pTempFilterContext) : ScXMLImportContext( rImport ), pFilterContext(pTempFilterContext), - sDataType(GetXMLToken(XML_TEXT)), + sDataType(OUString()), nField(0), bIsCaseSensitive(false) { @@ -762,6 +773,8 @@ void SAL_CALL ScXMLDPConditionContext::endFastElement( sal_Int32 /*nElement*/ ) rItem.maString = rPool.intern(sConditionValue); rItem.meType = ScQueryEntry::ByString; rItem.mfVal = 0.0; + if (IsXMLToken(sDataType, XML_TEXT)) + rItem.mbFormattedValue = true; } } pFilterContext->AddFilterField(aFilterField); diff --git a/sc/source/ui/cctrl/checklistmenu.cxx b/sc/source/ui/cctrl/checklistmenu.cxx index f825335f5af6..3c8920190eb9 100644 --- a/sc/source/ui/cctrl/checklistmenu.cxx +++ b/sc/source/ui/cctrl/checklistmenu.cxx @@ -424,6 +424,8 @@ ScCheckListMember::ScCheckListMember() : mbVisible(true) , mbDate(false) , mbLeaf(false) + , mbValue(false) + , mbDuplicated(false) , meDatePartType(YEAR) { } @@ -996,14 +998,17 @@ void ScCheckListMenuControl::addDateMember(const OUString& rsName, double nVal, mpChecks->thaw(); } -void ScCheckListMenuControl::addMember(const OUString& rName, bool bVisible) +void ScCheckListMenuControl::addMember(const OUString& rName, const double nVal, bool bVisible, bool bValue, bool bDuplicated) { ScCheckListMember aMember; // tdf#46062 - indicate hidden whitespaces using quotes aMember.maName = rName.trim() != rName ? "\"" + rName + "\"" : rName; aMember.maRealName = rName; + aMember.mnValue = nVal; aMember.mbDate = false; aMember.mbLeaf = true; + aMember.mbValue = bValue; + aMember.mbDuplicated = bDuplicated; aMember.mbVisible = bVisible; aMember.mxParent.reset(); maMembers.emplace_back(std::move(aMember)); @@ -1359,7 +1364,10 @@ void ScCheckListMenuControl::getResult(ResultType& rResult) ResultEntry aResultEntry; aResultEntry.bValid = bState; aResultEntry.aName = maMembers[i].maRealName; + aResultEntry.nValue = maMembers[i].mnValue; aResultEntry.bDate = maMembers[i].mbDate; + aResultEntry.bValue = maMembers[i].mbValue; + aResultEntry.bDuplicated = maMembers[i].mbDuplicated; aResult.insert(aResultEntry); } } diff --git a/sc/source/ui/inc/checklistmenu.hxx b/sc/source/ui/inc/checklistmenu.hxx index 926e56397afc..a97f519d1fd7 100644 --- a/sc/source/ui/inc/checklistmenu.hxx +++ b/sc/source/ui/inc/checklistmenu.hxx @@ -35,9 +35,12 @@ struct ScCheckListMember OUString maName; // node name OUString maRealName; + double mnValue; // number value of filter condition bool mbVisible; bool mbDate; bool mbLeaf; + bool mbValue; // true if the filter condition is value + bool mbDuplicated; // true if there were duplicated values in the filter list DatePartType meDatePartType; // To store Year and Month if the member if DAY type std::vector<OUString> maDateParts; @@ -66,8 +69,11 @@ public: struct ResultEntry { OUString aName; + double nValue; // number value of filter condition bool bValid; bool bDate; + bool bValue; // true if the filter condition is value + bool bDuplicated; // true if there were duplicated values in the filter list bool operator<(const ResultEntry& rhs) const { @@ -78,7 +84,10 @@ public: { return aName == rhs.aName && bValid == rhs.bValid && - bDate == rhs.bDate; + bDate == rhs.bDate && + bValue == rhs.bValue && + nValue == rhs.nValue && + bDuplicated == rhs.bDuplicated; } }; typedef std::set<ResultEntry> ResultType; @@ -126,7 +135,8 @@ public: void setMemberSize(size_t n); void addDateMember(const OUString& rName, double nVal, bool bVisible); - void addMember(const OUString& rName, bool bVisible); + void addMember(const OUString& rName, const double nVal, bool bVisible, + bool bValue = false, bool bDuplicated = false); size_t initMembers(int nMaxMemberWidth = -1); void setConfig(const Config& rConfig); diff --git a/sc/source/ui/unoobj/datauno.cxx b/sc/source/ui/unoobj/datauno.cxx index f5b910d2f5b0..3d149d3567d2 100644 --- a/sc/source/ui/unoobj/datauno.cxx +++ b/sc/source/ui/unoobj/datauno.cxx @@ -1135,9 +1135,14 @@ void fillQueryParam( bool bNumber = pDoc->GetFormatTable()->IsNumberFormat(rVal.StringValue, nIndex, aItem.mfVal); if (bNumber) { - OUString aStr; - pDoc->GetFormatTable()->GetInputLineString(aItem.mfVal, nIndex, aStr); - aItem.maString = rPool.intern(aStr); + if (aItem.mfVal != 0.0) + { + OUString aStr; + pDoc->GetFormatTable()->GetInputLineString(aItem.mfVal, nIndex, aStr); + aItem.maString = rPool.intern(aStr); + } + else + aItem.meType = ScQueryEntry::ByValue; } } else if (aItem.meType == ScQueryEntry::ByValue) diff --git a/sc/source/ui/view/gridwin.cxx b/sc/source/ui/view/gridwin.cxx index 3a60311691b5..1580640dd40a 100644 --- a/sc/source/ui/view/gridwin.cxx +++ b/sc/source/ui/view/gridwin.cxx @@ -535,8 +535,10 @@ public: { ScQueryEntry::Item aNew; aNew.maString = mrPool.intern(rEntry.aName); - aNew.meType = rEntry.bDate ? ScQueryEntry::ByDate : ScQueryEntry::ByString; - aNew.mfVal = 0.0; + // set the filter type to ByValue, if the filter condition is value and not a duplicated value + aNew.meType = rEntry.bDate ? ScQueryEntry::ByDate : rEntry.bValue && !rEntry.bDuplicated ? ScQueryEntry::ByValue : ScQueryEntry::ByString; + aNew.mbFormattedValue = rEntry.bDuplicated; + aNew.mfVal = rEntry.nValue; mrItems.push_back(aNew); } } @@ -686,7 +688,7 @@ void ScGridWindow::LaunchAutoFilterMenu(SCCOL nCol, SCROW nRow) if ( rEntry.IsDate() ) rControl.addDateMember( aStringVal, rEntry.GetValue(), bSelected ); else - rControl.addMember(aStringVal, bSelected); + rControl.addMember( aStringVal, aDoubleVal, bSelected, rEntry.GetStringType() == ScTypedStrData::Value, rEntry.IsDuplicated() ); } // Populate the menu. diff --git a/sc/source/ui/view/gridwin2.cxx b/sc/source/ui/view/gridwin2.cxx index 839c0fc6b6d9..06b9a88bdaaf 100644 --- a/sc/source/ui/view/gridwin2.cxx +++ b/sc/source/ui/view/gridwin2.cxx @@ -489,9 +489,9 @@ void ScGridWindow::DPLaunchFieldPopupMenu(const Point& rScrPos, const Size& rScr OUString aName = rMem.getDisplayName(); if (aName.isEmpty()) // Use special string for an empty name. - rControl.addMember(ScResId(STR_EMPTYDATA), rMem.mbVisible); + rControl.addMember(ScResId(STR_EMPTYDATA), 0.0, rMem.mbVisible); else - rControl.addMember(rMem.getDisplayName(), rMem.mbVisible); + rControl.addMember(rMem.getDisplayName(), 0.0, rMem.mbVisible); } } _______________________________________________ Libreoffice-commits mailing list libreoffice-comm...@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits