[Libreoffice-bugs] [Bug 129105] New: MACRO: SCRIPT: "getFilterDescriptor()" broken on fresh loaded files?

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=129105

            Bug ID: 129105
           Summary: MACRO: SCRIPT: "getFilterDescriptor()" broken on fresh
                    loaded files?
           Product: LibreOffice
           Version: 4.1.6.2 release
          Hardware: All
                OS: Windows (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: newbie-02 at gmx.de

Description:
hi @all,

i have a little issue with a function? method? in a macro, i asked other users
to check it out before filing a bug at
https://ask.libreoffice.org/en/question/218463/macro-script-getfilterdescriptor-broken-on-fresh-loaded-files/,

in short: "getFilterDescriptor()" gets wrong values for 'field' as property of
'filterfields(x)' in the 'filterfields' part of the struct, once two conditions
are met: - in a fresh loaded file where filters are set, - if the 'defined
range' it works in has an 'offset' from cell A1,

in long: i have a macro that 'steps through filtering values' of the autofilter
for a column in order to check subsets of the table quite fast :-) - see
attached code,

it produces erroneous results with the abovementioned conditions,

to check let it run on the different sheets of the attached file - see next
comment,

"getFilterDescriptor()", in this case called as "oFilterDesc =
oRange.getFilterDescriptor()" gets the number of the column as value for
"fields" on sheets 1,2,3, and the number of the column reduced by the 'vertical
offset' (start row) of the 'defined range' it's working in on sheets 4 and 5.

but "referredcells.filter(oFilterDesc)" does other calculations for the column,
thus applying it to wrong columns in sheet 2,3,4, sheet 5 looks healthy, but
i'm guessing only because two mistakes cancel each other out,

after 'restart' of the autofilter - autofilter off - autofilter on - the value
for 'field' is always calculated relative to the 'defined range', and
everything works as intended,

file with macro to play with it:

- see next comment - 

the macro code:

***********
sub PreviousFilterShortcut

' this macro - steps back - by one value in the range of possible autofilter '
values for the actual column. useful to cycle through subranges of datasets '
and e.g. check their results, e.g. in calculated subtotals of columns, '
(subtotal(9,(´range´)). ' ' the only requirements are that you have a 'database
range' defined ' in the table under <data -="" define="" range="">, that you
have selected a single cell, ' and that this cell, the 'focus', is within the
defined range,

oDoc     = ThisComponent
oControl = oDoc.CurrentController
oSheet   = oControl.getActiveSheet
oCell    = oDoc.getCurrentSelection
Column  = oCell.CellAddress.Column

' search for database range the focus is in
oDBRanges = oDoc.DatabaseRanges
for i = 0 to oDBRanges.Count-1
    oDBrange   = oDBRanges.getByIndex(i)
    oCellrange = oDbrange.ReferredCells
    if oCellrange.queryIntersection(oCell.RangeAddress).Count > 0 then
        oRange = oDBrange
    End If
next

' add a messagebox for errors, 
if isempty(oRange) then msgbox "actual cell not within a database range",0,""
if isempty(oRange) then exit sub

' define new filter-description
Dim oNeu As New com.sun.star.sheet.TableFilterField
With oNeu
 .Field = Column - oRange.ReferredCells.RangeAddress.StartColumn  '
filter-column (other position in code than in datasurfer?) 
 .IsNumeric = False
 .StringValue = ""
 .Operator = com.sun.star.sheet.FilterOperator.NOT_EQUAL
End With    

'analyse existing filter     
oFilterDesc = oRange.getFilterDescriptor()
aFields()=oFilterDesc.getFilterFields
n=-1
sAlt=""
Do while sAlt="" and n<ubound(aFields())
    n=n+1
    if aFields(n).Field=Column - oRange.ReferredCells.RangeAddress.StartColumn
then
        'column is actually filtered
        sAlt=aFields(n).StringValue
        aFields(n)=oNeu 'filter "off"
    end if
loop

' filter without filtering actual column
oRange.AutoFilter = True
oFilterDesc.setFilterFields(aFields())
oFilterDesc.ContainsHeader=true
oRange.referredcells.filter(oFilterDesc)

' some presets
if sAlt="" then 
    n=n+1
    Redim Preserve aFields(n)
    aFields(n)=oNeu
end if

' get all possible values for the actual column, respecting filtering done in
other columns, 
zellen=
oRange.ReferredCells.queryVisiblecells.queryintersection(oCell.columns(0).RangeAddress).cells.createenumeration

' search for max of smaller values 
sNeu=""
sMax=sAlt

zellen.nextelement
do while zellen.hasmoreelements
    sTemp=zellen.nextelement.string
    if sTemp>sMax then sMax=sTemp ' find max
    if sNeu="" then 
        if sTemp<sAlt then sNeu=sTemp ' 
    else
        if sTemp<sAlt and sTemp>sNeu then sNeu=sTemp
    end if
loop
if sNeu="" then sNeu=sMax

' set new filter criteria
aFields(n).StringValue=sNeu
aFields(n).Operator = com.sun.star.sheet.FilterOperator.EQUAL

' apply filtering
oFilterDesc.setFilterFields(aFields())
oRange.referredcells.filter(oFilterDesc)

End Sub 'PreviousFilterShortcut
***********

the macro is one of four which do quite handy things for me, (toggle autofilter
on/off, modify autofilter according value of selected cell (datasurfer), step
forward through the filtering values in one column, step backwards) which i'd
like to give to others and 'the community' once they run well,

the last three suffer from mentioned problem, i'd provide 'backwards' as
example reg. it's 'short' and 'in english', 'forward' is partly german,

credits to https://www.libreoffice-forum.de/ and esp. 'mikele' who helped
coming so far ...

reg. 



b. 

Steps to Reproduce:
see description above and test file in next comment, 

Actual Results:
'field' in filterfields() of filterfields is calculated relative to column 1 of
the sheet in fresh loaded files, affects database ranges with an offset to A1,

Expected Results:
the value for 'field' being calculated relative to the database range the
filter is defined in, 


Reproducible: Always


User Profile Reset: No



Additional Info:
different flav's from4.1.6.2 to 6.5.0.0 show identical behaviour,

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20191129/2dab1401/attachment-0001.html>