sc/qa/unit/data/ods/tdf142607.ods |binary sc/qa/unit/subsequent_export-test.cxx | 20 +++++++++ sc/source/core/data/table3.cxx | 5 +- sc/source/filter/excel/excrecds.cxx | 74 ++++++++++++++-------------------- sc/source/filter/inc/excrecds.hxx | 6 -- 5 files changed, 56 insertions(+), 49 deletions(-)
New commits: commit 9de782058964bb98363a8492d73ed70cbd8ba94c Author: Balazs Varga <[email protected]> AuthorDate: Wed Jun 2 09:17:02 2021 +0200 Commit: Gabor Kelemen <[email protected]> CommitDate: Mon Aug 30 15:20:03 2021 +0200 tdf#142607 XLSX export: keep formatted dates in standard filter Do not convert the formatted string filter criteria values to 'double' if they are numbers (dates). Also export the equal relation criteria into XML_filters tag instead of XML_customFilters. Previously the formatted dates replaced with numbers in standard filter criteria, first during the export, and after the import, in the standard filter dialog window. Note: fix and optimize also filtering by formatted dates by allowing CanOptimizeQueryStringToNumber() to create double values for them. This unifies the same dates with different date formatting. Follow-up to commit 1f755525189884e4b2824889a6b9dea8933402db "tdf#142402 sc UI: store formatted values in standard filter". Change-Id: If4c22e8b0142720ccfda038f89367061058693aa Reviewed-on: https://gerrit.libreoffice.org/c/core/+/116566 Tested-by: László Németh <[email protected]> Reviewed-by: László Németh <[email protected]> (cherry picked from commit 11cc770ad2af1f31c1e5c9512e5688dff38f009b) Reviewed-on: https://gerrit.libreoffice.org/c/core/+/117559 Tested-by: Jenkins (cherry picked from commit 5c76d6ec6efe94877fa1b158d9ef9262dddea463) Reviewed-on: https://gerrit.libreoffice.org/c/core/+/121273 Tested-by: Gabor Kelemen <[email protected]> Reviewed-by: Gabor Kelemen <[email protected]> diff --git a/sc/qa/unit/data/ods/tdf142607.ods b/sc/qa/unit/data/ods/tdf142607.ods new file mode 100644 index 000000000000..91649056e728 Binary files /dev/null and b/sc/qa/unit/data/ods/tdf142607.ods differ diff --git a/sc/qa/unit/subsequent_export-test.cxx b/sc/qa/unit/subsequent_export-test.cxx index 3b6a16d98b3c..1f3c177ca26e 100644 --- a/sc/qa/unit/subsequent_export-test.cxx +++ b/sc/qa/unit/subsequent_export-test.cxx @@ -285,6 +285,7 @@ public: void testTdf136721_paper_size(); void testTdf139258_rotated_image(); void testTdf140431(); + void testDateStandardfilterXLSX(); CPPUNIT_TEST_SUITE(ScExportTest); CPPUNIT_TEST(test); @@ -469,6 +470,7 @@ public: CPPUNIT_TEST(testTdf139258_rotated_image); CPPUNIT_TEST(testTdf140431); + CPPUNIT_TEST(testDateStandardfilterXLSX); CPPUNIT_TEST_SUITE_END(); private: @@ -5927,6 +5929,24 @@ void ScExportTest::testTdf140431() xDocSh->DoClose(); } +void ScExportTest::testDateStandardfilterXLSX() +{ + // XLSX Roundtripping standard filter with date + ScDocShellRef xDocSh = loadDoc(u"tdf142607.", FORMAT_ODS); + CPPUNIT_ASSERT(xDocSh.is()); + + xmlDocUniquePtr pDoc = XPathHelper::parseExport2(*this, *xDocSh, m_xSFactory, "xl/worksheets/sheet1.xml", FORMAT_XLSX); + CPPUNIT_ASSERT(pDoc); + + assertXPath(pDoc, "//x:autoFilter", "ref", "A1:B6"); + assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "day", "03"); + assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "month", "12"); + assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "year", "2011"); + assertXPath(pDoc, "//x:autoFilter/x:filterColumn/x:filters/x:dateGroupItem[1]", "dateTimeGrouping", "day"); + + xDocSh->DoClose(); +} + CPPUNIT_TEST_SUITE_REGISTRATION(ScExportTest); CPPUNIT_PLUGIN_IMPLEMENT(); diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx index d1c7b01419aa..a813b91885c2 100644 --- a/sc/source/core/data/table3.cxx +++ b/sc/source/core/data/table3.cxx @@ -3018,8 +3018,9 @@ public: { if (rItem.meType != ScQueryEntry::ByString && rItem.meType != ScQueryEntry::ByDate) return; - - if (rItem.mbFormattedValue) + // return only if the type is ByString and the values are formatted, in other cases + // we have to optimize the filter in CanOptimizeQueryStringToNumber(). + if (rItem.mbFormattedValue && rItem.meType == ScQueryEntry::ByString) return; sal_uInt32 nIndex = 0; diff --git a/sc/source/filter/excel/excrecds.cxx b/sc/source/filter/excel/excrecds.cxx index 2ce8888ede68..1ef6f60c6cf4 100644 --- a/sc/source/filter/excel/excrecds.cxx +++ b/sc/source/filter/excel/excrecds.cxx @@ -527,8 +527,7 @@ XclExpAutofilterinfo::XclExpAutofilterinfo( const ScAddress& rStartPos, SCCOL nS ExcFilterCondition::ExcFilterCondition() : nType( EXC_AFTYPE_NOTUSED ), - nOper( EXC_AFOPER_EQUAL ), - fVal( 0.0 ) + nOper( EXC_AFOPER_EQUAL ) { } @@ -541,32 +540,23 @@ std::size_t ExcFilterCondition::GetTextBytes() const return pText ? (1 + pText->GetBufferSize()) : 0; } -void ExcFilterCondition::SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, double fV, const OUString* pT ) +void ExcFilterCondition::SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, const OUString* pT ) { nType = nTp; nOper = nOp; - fVal = fV; pText.reset( pT ? new XclExpString( *pT, XclStrFlags::EightBitLength ) : nullptr); } void ExcFilterCondition::Save( XclExpStream& rStrm ) { rStrm << nType << nOper; - switch( nType ) + if (nType == EXC_AFTYPE_STRING) { - case EXC_AFTYPE_DOUBLE: - rStrm << fVal; - break; - case EXC_AFTYPE_STRING: - OSL_ENSURE( pText, "ExcFilterCondition::Save() -- pText is NULL!" ); - rStrm << sal_uInt32(0) << static_cast<sal_uInt8>(pText->Len()) << sal_uInt16(0) << sal_uInt8(0); - break; - case EXC_AFTYPE_BOOLERR: - rStrm << sal_uInt8(0) << static_cast<sal_uInt8>((fVal != 0) ? 1 : 0) << sal_uInt32(0) << sal_uInt16(0); - break; - default: - rStrm << sal_uInt32(0) << sal_uInt32(0); + OSL_ENSURE(pText, "ExcFilterCondition::Save() -- pText is NULL!"); + rStrm << sal_uInt32(0) << static_cast<sal_uInt8>(pText->Len()) << sal_uInt16(0) << sal_uInt8(0); } + else + rStrm << sal_uInt32(0) << sal_uInt32(0); } static const char* lcl_GetOperator( sal_uInt8 nOper ) @@ -584,15 +574,12 @@ static const char* lcl_GetOperator( sal_uInt8 nOper ) } } -static OString lcl_GetValue( sal_uInt8 nType, double fVal, const XclExpString* pStr ) +static OString lcl_GetValue( sal_uInt8 nType, const XclExpString* pStr ) { - switch( nType ) - { - case EXC_AFTYPE_STRING: return XclXmlUtils::ToOString( *pStr ); - case EXC_AFTYPE_DOUBLE: return OString::number( fVal ); - case EXC_AFTYPE_BOOLERR: return OString::number( ( fVal != 0 ? 1 : 0 ) ); - default: return OString(); - } + if (nType == EXC_AFTYPE_STRING) + return XclXmlUtils::ToOString(*pStr); + else + return OString(); } void ExcFilterCondition::SaveXml( XclExpXmlStream& rStrm ) @@ -602,7 +589,7 @@ void ExcFilterCondition::SaveXml( XclExpXmlStream& rStrm ) rStrm.GetCurrentStream()->singleElement( XML_customFilter, XML_operator, lcl_GetOperator( nOper ), - XML_val, lcl_GetValue(nType, fVal, pText.get()) ); + XML_val, lcl_GetValue(nType, pText.get()) ); } void ExcFilterCondition::SaveText( XclExpStream& rStrm ) @@ -626,7 +613,7 @@ XclExpAutofilter::XclExpAutofilter( const XclExpRoot& rRoot, sal_uInt16 nC ) : } bool XclExpAutofilter::AddCondition( ScQueryConnect eConn, sal_uInt8 nType, sal_uInt8 nOp, - double fVal, const OUString* pText, bool bSimple ) + const OUString* pText, bool bSimple ) { if( !aCond[ 1 ].IsEmpty() ) return false; @@ -638,7 +625,7 @@ bool XclExpAutofilter::AddCondition( ScQueryConnect eConn, sal_uInt8 nType, sal_ if( bSimple ) nFlags |= (nInd == 0) ? EXC_AFFLAG_SIMPLE1 : EXC_AFFLAG_SIMPLE2; - aCond[ nInd ].SetCondition( nType, nOp, fVal, pText ); + aCond[ nInd ].SetCondition( nType, nOp, pText ); AddRecSize( aCond[ nInd ].GetTextBytes() ); @@ -701,26 +688,17 @@ bool XclExpAutofilter::AddEntry( const ScQueryEntry& rEntry ) } } - bool bLen = sText.getLength() > 0; - // empty/nonempty fields if (rEntry.IsQueryByEmpty()) { - bConflict = !AddCondition(rEntry.eConnect, EXC_AFTYPE_EMPTY, EXC_AFOPER_NONE, 0.0, nullptr, true); + bConflict = !AddCondition(rEntry.eConnect, EXC_AFTYPE_EMPTY, EXC_AFOPER_NONE, nullptr, true); bHasBlankValue = true; } else if(rEntry.IsQueryByNonEmpty()) - bConflict = !AddCondition( rEntry.eConnect, EXC_AFTYPE_NOTEMPTY, EXC_AFOPER_NONE, 0.0, nullptr, true ); + bConflict = !AddCondition( rEntry.eConnect, EXC_AFTYPE_NOTEMPTY, EXC_AFOPER_NONE, nullptr, true ); // other conditions else { - double fVal = 0.0; - sal_uInt32 nIndex = 0; - bool bIsNum = !bLen || GetFormatter().IsNumberFormat( sText, nIndex, fVal ); - OUString* pText = nullptr; - if (!bIsNum) - pText = &sText; - // top10 flags sal_uInt16 nNewFlags = 0x0000; switch( rEntry.eOp ) @@ -746,14 +724,24 @@ bool XclExpAutofilter::AddEntry( const ScQueryEntry& rEntry ) { if( bNewTop10 ) { - if( fVal < 0 ) fVal = 0; - if( fVal >= 501 ) fVal = 500; + sal_uInt32 nIndex = 0; + double fVal = 0.0; + if (GetFormatter().IsNumberFormat(sText, nIndex, fVal)) + { + if (fVal < 0) fVal = 0; + if (fVal >= 501) fVal = 500; + } nFlags |= (nNewFlags | static_cast<sal_uInt16>(fVal) << 7); } // normal condition else { - sal_uInt8 nType = bIsNum ? EXC_AFTYPE_DOUBLE : EXC_AFTYPE_STRING; + if (GetOutput() != EXC_OUTPUT_BINARY && rEntry.eOp == SC_EQUAL) + { + AddMultiValueEntry(rEntry); + return false; + } + sal_uInt8 nOper = EXC_AFOPER_NONE; switch( rEntry.eOp ) @@ -774,7 +762,7 @@ bool XclExpAutofilter::AddEntry( const ScQueryEntry& rEntry ) nOper = EXC_AFOPER_NOTEQUAL; break; default:; } - bConflict = !AddCondition( rEntry.eConnect, nType, nOper, fVal, pText ); + bConflict = !AddCondition( rEntry.eConnect, EXC_AFTYPE_STRING, nOper, &sText); } } } diff --git a/sc/source/filter/inc/excrecds.hxx b/sc/source/filter/inc/excrecds.hxx index 5501672bfda9..491ee359040c 100644 --- a/sc/source/filter/inc/excrecds.hxx +++ b/sc/source/filter/inc/excrecds.hxx @@ -339,7 +339,6 @@ class ExcFilterCondition private: sal_uInt8 nType; sal_uInt8 nOper; - double fVal; std::unique_ptr<XclExpString> pText; @@ -351,7 +350,7 @@ public: bool IsEmpty() const { return (nType == EXC_AFTYPE_NOTUSED); } std::size_t GetTextBytes() const; - void SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, double fV, const OUString* pT ); + void SetCondition( sal_uInt8 nTp, sal_uInt8 nOp, const OUString* pT ); void Save( XclExpStream& rStrm ); void SaveXml( XclExpXmlStream& rStrm ); @@ -370,8 +369,7 @@ private: std::vector<std::pair<OUString, bool>> maMultiValues; // first->values, second->bDateFormat bool AddCondition( ScQueryConnect eConn, sal_uInt8 nType, - sal_uInt8 nOp, double fVal, const OUString* pText, - bool bSimple = false ); + sal_uInt8 nOp, const OUString* pText, bool bSimple = false ); virtual void WriteBody( XclExpStream& rStrm ) override;
