diff -cNr sc.old/source/ui/vba/vbarange.cxx sc.new/source/ui/vba/vbarange.cxx *** sc.old/source/ui/vba/vbarange.cxx Fri Mar 7 23:11:12 2008 --- sc.new/source/ui/vba/vbarange.cxx Wed May 7 11:43:10 2008 *************** *** 82,88 **** --- 82,91 ---- #include #include #include + #include #include + #include + #include #include #include #include *************** *** 3844,3849 **** --- 3847,3938 ---- rFilterField.StringValue = sCriteria1; } + void lcl_setTableFieldsFromCriteria2( rtl::OUString& sCriteria1, uno::Reference< beans::XPropertySet >& xDescProps, sheet::TableFilterField2& rFilterField ) + { + // #TODO make this more efficient and cycle through + // sCriteria1 character by character to pick up <,<>,=, * etc. + // right now I am more concerned with just getting it to work right + + sCriteria1 = sCriteria1.trim(); + // table of translation of criteria text to FilterOperators + // <>searchtext - NOT_EQUAL + // =searchtext - EQUAL + // *searchtext - startwith + // <>*searchtext - doesn't startwith + // *searchtext* - contains + // <>*searchtext* - doesn't contain + // [>|>=|<=|...]searchtext for GREATER_value, GREATER_EQUAL_value etc. + sal_Int32 nPos = 0; + bool bIsNumeric = false; + if ( ( nPos = sCriteria1.indexOf( EQUALS ) ) == 0 ) + { + if ( sCriteria1.getLength() == EQUALS.getLength() ) + rFilterField.Operator = sheet::FilterOperator2::EMPTY; + else + { + rFilterField.Operator = sheet::FilterOperator2::EQUAL; + sCriteria1 = sCriteria1.copy( EQUALS.getLength() ); + sCriteria1 = VBAToRegexp( sCriteria1 ); + // UseRegularExpressions + if ( xDescProps.is() ) + xDescProps->setPropertyValue( rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "UseRegularExpressions" ) ), uno::Any( sal_True ) ); + } + + } + else if ( ( nPos = sCriteria1.indexOf( NOTEQUALS ) ) == 0 ) + { + if ( sCriteria1.getLength() == NOTEQUALS.getLength() ) + rFilterField.Operator = sheet::FilterOperator2::NOT_EMPTY; + else + { + rFilterField.Operator = sheet::FilterOperator2::NOT_EQUAL; + sCriteria1 = sCriteria1.copy( NOTEQUALS.getLength() ); + sCriteria1 = VBAToRegexp( sCriteria1 ); + // UseRegularExpressions + if ( xDescProps.is() ) + xDescProps->setPropertyValue( rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "UseRegularExpressions" ) ), uno::Any( sal_True ) ); + } + } + else if ( ( nPos = sCriteria1.indexOf( GREATERTHAN ) ) == 0 ) + { + bIsNumeric = true; + if ( ( nPos = sCriteria1.indexOf( GREATERTHANEQUALS ) ) == 0 ) + { + sCriteria1 = sCriteria1.copy( GREATERTHANEQUALS.getLength() ); + rFilterField.Operator = sheet::FilterOperator2::GREATER_EQUAL; + } + else + { + sCriteria1 = sCriteria1.copy( GREATERTHAN.getLength() ); + rFilterField.Operator = sheet::FilterOperator2::GREATER; + } + + } + else if ( ( nPos = sCriteria1.indexOf( LESSTHAN ) ) == 0 ) + { + bIsNumeric = true; + if ( ( nPos = sCriteria1.indexOf( LESSTHANEQUALS ) ) == 0 ) + { + sCriteria1 = sCriteria1.copy( LESSTHANEQUALS.getLength() ); + rFilterField.Operator = sheet::FilterOperator2::LESS_EQUAL; + } + else + { + sCriteria1 = sCriteria1.copy( LESSTHAN.getLength() ); + rFilterField.Operator = sheet::FilterOperator2::LESS; + } + + } + else + rFilterField.Operator = sheet::FilterOperator2::EQUAL; + + if ( bIsNumeric ) + { + rFilterField.IsNumeric= sal_True; + rFilterField.NumericValue = sCriteria1.toDouble(); + } + rFilterField.StringValue = sCriteria1; + } void SAL_CALL ScVbaRange::AutoFilter( const uno::Any& Field, const uno::Any& Criteria1, const uno::Any& Operator, const uno::Any& Criteria2, const uno::Any& VisibleDropDown ) throw (uno::RuntimeException) { *************** *** 3943,4048 **** bool bAll = false;; if ( ( Field >>= nField ) ) { ! uno::Sequence< sheet::TableFilterField > sTabFilts; ! uno::Reference< sheet::XSheetFilterDescriptor > xDesc = xDataBaseRange->getFilterDescriptor(); ! uno::Reference< beans::XPropertySet > xDescProps( xDesc, uno::UNO_QUERY_THROW ); ! if ( Criteria1.hasValue() ) ! { ! sTabFilts.realloc( 1 ); ! sTabFilts[0].Operator = sheet::FilterOperator_EQUAL;// sensible default ! if ( !bCritHasNumericValue ) ! { ! Criteria1 >>= sCriteria1; ! sTabFilts[0].IsNumeric = bCritHasNumericValue; ! if ( bHasCritValue && sCriteria1.getLength() ) ! lcl_setTableFieldsFromCriteria( sCriteria1, xDescProps, sTabFilts[0] ); ! else ! bAll = true; ! } ! else // numeric ! { ! sTabFilts[0].IsNumeric = sal_True; ! sTabFilts[0].NumericValue = nCriteria1; ! } ! } ! else // no value specified ! bAll = true; ! // not sure what the relationship between Criteria1 and Operator is, ! // e.g. can you have a Operator without a Criteria ? in openoffice it ! if ( Operator.hasValue() && ( Operator >>= nOperator ) ) ! { ! // if its a bottom/top Ten(Percent/Value) and there ! // is no value specified for critera1 set it to 10 ! if ( !bCritHasNumericValue && !sCriteria1.getLength() && ( nOperator != excel::XlAutoFilterOperator::xlOr ) && ( nOperator != excel::XlAutoFilterOperator::xlAnd ) ) ! { ! sTabFilts[0].IsNumeric = sal_True; ! sTabFilts[0].NumericValue = 10; ! bAll = false; ! } ! switch ( nOperator ) ! { ! case excel::XlAutoFilterOperator::xlBottom10Items: ! sTabFilts[0].Operator = sheet::FilterOperator_BOTTOM_VALUES; ! break; ! case excel::XlAutoFilterOperator::xlBottom10Percent: ! sTabFilts[0].Operator = sheet::FilterOperator_BOTTOM_PERCENT; ! break; ! case excel::XlAutoFilterOperator::xlTop10Items: ! sTabFilts[0].Operator = sheet::FilterOperator_TOP_VALUES; ! break; ! case excel::XlAutoFilterOperator::xlTop10Percent: ! sTabFilts[0].Operator = sheet::FilterOperator_TOP_PERCENT; ! break; ! case excel::XlAutoFilterOperator::xlOr: ! nConn = sheet::FilterConnection_OR; ! break; ! case excel::XlAutoFilterOperator::xlAnd: ! nConn = sheet::FilterConnection_AND; ! break; ! default: ! throw uno::RuntimeException( rtl::OUString( RTL_CONSTASCII_USTRINGPARAM("UnknownOption") ), uno::Reference< uno::XInterface >() ); ! ! } ! } ! if ( !bAll ) ! { ! sTabFilts[0].Connection = sheet::FilterConnection_AND; ! sTabFilts[0].Field = (nField - 1); ! rtl::OUString sCriteria2; ! if ( Criteria2.hasValue() ) // there is a Criteria2 ! { ! sTabFilts.realloc(2); ! sTabFilts[1].Field = sTabFilts[0].Field; ! sTabFilts[1].Connection = nConn; ! if ( Criteria2 >>= sCriteria2 ) ! { ! if ( sCriteria2.getLength() > 0 ) ! { ! uno::Reference< beans::XPropertySet > xProps; ! lcl_setTableFieldsFromCriteria( sCriteria2, xProps, sTabFilts[1] ); ! sTabFilts[1].IsNumeric = sal_False; ! } ! } ! else // numeric ! { ! Criteria2 >>= sTabFilts[1].NumericValue; ! sTabFilts[1].IsNumeric = sal_True; ! sTabFilts[1].Operator = sheet::FilterOperator_EQUAL; ! } ! } ! } ! xDesc->setFilterFields( sTabFilts ); ! if ( !bAll ) ! { ! xDataBaseRange->refresh(); ! } ! else ! // was 0 based now seems to be 1 ! lcl_SetAllQueryForField( pShell, nField, nSheet ); } else { --- 4032,4244 ---- bool bAll = false;; if ( ( Field >>= nField ) ) { ! uno::Reference< sheet::XSheetFilterDescriptor > xDesc = xDataBaseRange->getFilterDescriptor(); ! uno::Reference< sheet::XSheetFilterDescriptor2 > xDesc2( ! xDesc, uno::UNO_QUERY ); ! if ( xDesc2.is() ) ! { ! uno::Sequence< sheet::TableFilterField2 > sTabFilts2; ! uno::Reference< beans::XPropertySet > xDescProps( xDesc2, uno::UNO_QUERY_THROW ); ! if ( Criteria1.hasValue() ) ! { ! sTabFilts2.realloc( 1 ); ! sTabFilts2[0].Operator = sheet::FilterOperator2::EQUAL;// sensible default ! if ( !bCritHasNumericValue ) ! { ! Criteria1 >>= sCriteria1; ! sTabFilts2[0].IsNumeric = bCritHasNumericValue; ! if ( bHasCritValue && sCriteria1.getLength() ) ! lcl_setTableFieldsFromCriteria2( sCriteria1, xDescProps, sTabFilts2[0] ); ! else ! bAll = true; ! } ! else // numeric ! { ! sTabFilts2[0].IsNumeric = sal_True; ! sTabFilts2[0].NumericValue = nCriteria1; ! } ! } ! else // no value specified ! bAll = true; ! // not sure what the relationship between Criteria1 and Operator is, ! // e.g. can you have a Operator without a Criteria ? in openoffice it ! if ( Operator.hasValue() && ( Operator >>= nOperator ) ) ! { ! // if its a bottom/top Ten(Percent/Value) and there ! // is no value specified for critera1 set it to 10 ! if ( !bCritHasNumericValue && !sCriteria1.getLength() && ( nOperator != excel::XlAutoFilterOperator::xlOr ) && ( nOperator != excel::XlAutoFilterOperator::xlAnd ) ) ! { ! sTabFilts2[0].IsNumeric = sal_True; ! sTabFilts2[0].NumericValue = 10; ! bAll = false; ! } ! switch ( nOperator ) ! { ! case excel::XlAutoFilterOperator::xlBottom10Items: ! sTabFilts2[0].Operator = sheet::FilterOperator2::BOTTOM_VALUES; ! break; ! case excel::XlAutoFilterOperator::xlBottom10Percent: ! sTabFilts2[0].Operator = sheet::FilterOperator2::BOTTOM_PERCENT; ! break; ! case excel::XlAutoFilterOperator::xlTop10Items: ! sTabFilts2[0].Operator = sheet::FilterOperator2::TOP_VALUES; ! break; ! case excel::XlAutoFilterOperator::xlTop10Percent: ! sTabFilts2[0].Operator = sheet::FilterOperator2::TOP_PERCENT; ! break; ! case excel::XlAutoFilterOperator::xlOr: ! nConn = sheet::FilterConnection_OR; ! break; ! case excel::XlAutoFilterOperator::xlAnd: ! nConn = sheet::FilterConnection_AND; ! break; ! default: ! throw uno::RuntimeException( rtl::OUString( RTL_CONSTASCII_USTRINGPARAM("UnknownOption") ), uno::Reference< uno::XInterface >() ); ! } ! } ! if ( !bAll ) ! { ! sTabFilts2[0].Connection = sheet::FilterConnection_AND; ! sTabFilts2[0].Field = (nField - 1); ! rtl::OUString sCriteria2; ! if ( Criteria2.hasValue() ) // there is a Criteria2 ! { ! sTabFilts2.realloc(2); ! sTabFilts2[1].Field = sTabFilts2[0].Field; ! sTabFilts2[1].Connection = nConn; ! ! if ( Criteria2 >>= sCriteria2 ) ! { ! if ( sCriteria2.getLength() > 0 ) ! { ! uno::Reference< beans::XPropertySet > xProps; ! lcl_setTableFieldsFromCriteria2( sCriteria2, xProps, sTabFilts2[1] ); ! sTabFilts2[1].IsNumeric = sal_False; ! } ! } ! else // numeric ! { ! Criteria2 >>= sTabFilts2[1].NumericValue; ! sTabFilts2[1].IsNumeric = sal_True; ! sTabFilts2[1].Operator = sheet::FilterOperator2::EQUAL; ! } ! } ! } ! xDesc2->setFilterFields2( sTabFilts2 ); ! if ( !bAll ) ! { ! xDataBaseRange->refresh(); ! } ! else ! // was 0 based now seems to be 1 ! lcl_SetAllQueryForField( pShell, nField, nSheet ); ! } ! else ! { ! uno::Sequence< sheet::TableFilterField > sTabFilts; ! uno::Reference< beans::XPropertySet > xDescProps( xDesc, uno::UNO_QUERY_THROW ); ! if ( Criteria1.hasValue() ) ! { ! sTabFilts.realloc( 1 ); ! sTabFilts[0].Operator = sheet::FilterOperator_EQUAL;// sensible default ! if ( !bCritHasNumericValue ) ! { ! Criteria1 >>= sCriteria1; ! sTabFilts[0].IsNumeric = bCritHasNumericValue; ! if ( bHasCritValue && sCriteria1.getLength() ) ! lcl_setTableFieldsFromCriteria( sCriteria1, xDescProps, sTabFilts[0] ); ! else ! bAll = true; ! } ! else // numeric ! { ! sTabFilts[0].IsNumeric = sal_True; ! sTabFilts[0].NumericValue = nCriteria1; ! } ! } ! else // no value specified ! bAll = true; ! // not sure what the relationship between Criteria1 and Operator is, ! // e.g. can you have a Operator without a Criteria ? in openoffice it ! if ( Operator.hasValue() && ( Operator >>= nOperator ) ) ! { ! // if its a bottom/top Ten(Percent/Value) and there ! // is no value specified for critera1 set it to 10 ! if ( !bCritHasNumericValue && !sCriteria1.getLength() && ( nOperator != excel::XlAutoFilterOperator::xlOr ) && ( nOperator != excel::XlAutoFilterOperator::xlAnd ) ) ! { ! sTabFilts[0].IsNumeric = sal_True; ! sTabFilts[0].NumericValue = 10; ! bAll = false; ! } ! switch ( nOperator ) ! { ! case excel::XlAutoFilterOperator::xlBottom10Items: ! sTabFilts[0].Operator = sheet::FilterOperator_BOTTOM_VALUES; ! break; ! case excel::XlAutoFilterOperator::xlBottom10Percent: ! sTabFilts[0].Operator = sheet::FilterOperator_BOTTOM_PERCENT; ! break; ! case excel::XlAutoFilterOperator::xlTop10Items: ! sTabFilts[0].Operator = sheet::FilterOperator_TOP_VALUES; ! break; ! case excel::XlAutoFilterOperator::xlTop10Percent: ! sTabFilts[0].Operator = sheet::FilterOperator_TOP_PERCENT; ! break; ! case excel::XlAutoFilterOperator::xlOr: ! nConn = sheet::FilterConnection_OR; ! break; ! case excel::XlAutoFilterOperator::xlAnd: ! nConn = sheet::FilterConnection_AND; ! break; ! default: ! throw uno::RuntimeException( rtl::OUString( RTL_CONSTASCII_USTRINGPARAM("UnknownOption") ), uno::Reference< uno::XInterface >() ); ! ! } ! ! } ! if ( !bAll ) ! { ! sTabFilts[0].Connection = sheet::FilterConnection_AND; ! sTabFilts[0].Field = (nField - 1); ! ! rtl::OUString sCriteria2; ! if ( Criteria2.hasValue() ) // there is a Criteria2 ! { ! sTabFilts.realloc(2); ! sTabFilts[1].Field = sTabFilts[0].Field; ! sTabFilts[1].Connection = nConn; ! ! if ( Criteria2 >>= sCriteria2 ) ! { ! if ( sCriteria2.getLength() > 0 ) ! { ! uno::Reference< beans::XPropertySet > xProps; ! lcl_setTableFieldsFromCriteria( sCriteria2, xProps, sTabFilts[1] ); ! sTabFilts[1].IsNumeric = sal_False; ! } ! } ! else // numeric ! { ! Criteria2 >>= sTabFilts[1].NumericValue; ! sTabFilts[1].IsNumeric = sal_True; ! sTabFilts[1].Operator = sheet::FilterOperator_EQUAL; ! } ! } ! } ! ! xDesc->setFilterFields( sTabFilts ); ! if ( !bAll ) ! { ! xDataBaseRange->refresh(); ! } ! else ! // was 0 based now seems to be 1 ! lcl_SetAllQueryForField( pShell, nField, nSheet ); ! } } else {