problem accessing filters in calc sheets by a macro [closed]

asked 2020-04-25 15:35:38 +0100

newbie-02 gravatar image

updated 2020-07-20 10:07:17 +0100

Alex Kemp gravatar image

--- kindly asking for help for a cross check, it's simple ---


  • pls. check the two attached pics, they show different and partly wrong values for the filtering of a table in calc, that blocks processing the filtering situation by a macro.

  • pls. check attached spreadsheet, 'Zeige Filterfield' shows the value .field for the first applied filter, see that while not! removing and re-applying autofilter the value for col. B and col. E are '1', you may ignore 'Filtrierung korrigieren', it doesn't work, the macro behind it gives some snippets of code to deal with the values ...

long: while working on macros for fast easy filtering i have a strange effect:

the values for .field accessible by macros are partly wrong, depending on the row- and column offset of the database range against cell A1. one special case which blocks me from correcting the values in the macro is the following:

when filtering a database area whose header is partly left / below and partly right / above the '45 degree diagonal' A1-B2-C3-D4-E5... and in which one column is filtered with 'header' on the left and one with header on the right of the diagonal, the following is stored in the saved file C:\fakepath\mikele_test_filterdescriptor_test13c.ods](/upfiles/15878181118669652.ods) for the definition of the filtration: see figure:


the two indented lines and there 'field-number="-2"' and 'field-number="1" ... if you do a more complex calculation you can work out '0' and '3' for the filter columns relative to the database area. This is what calc does, it shows the two columns B and E as filtered, and on the screen / with the mouse you can continue working with the values and filterings.

But! if you access the filtering by macro with the methods 'getfilterdescriptor' and 'getfilterfields' other values are taken for .field, and e.g. for the example table two! Filterfield entries with '1' for .field are displayed, see attached screenshot:


I see - so far - no way to distinguish between them and decide for which the value of .field has to be corrected and how. In the example this is easy to spot for humans via the value in the cell, 'name2' is column B or '0' and 'vorname2' is column E or 3 relative to the database area, but this is not suitable for automatic or for larger projects.

(imho because somewhere a bug a la confusion of line and column offset happened)

as soon as two different filter columns are converted to the same value for .field, a correction calculation 'with onboard tools' (macros) is no longer possible.

I haven't found the filterfields under 'thiscomponent' or 'oDoc', although they should be there? but there are too many classes, superclasses, types, methods ... I found them under 'orange' after 'oDBRanges.getByIndex', and there already 'wrong', before 'getfilterdescriptor' and 'getfilterfields',

I could use some help right now:

  • that other people check the behavior on other computers, not that i'll file bugs for singular problems,

  • if someone knows where the ...

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-07-20 10:07:49.034711


filed bug for that:

filesave: fileopen: macro: xml: calc filtered ranges wrongly saved in .ods format?

appreciate any help / test / hints ...

newbie-02 gravatar imagenewbie-02 ( 2020-04-30 14:13:33 +0100 )edit

hello @Alex,

besides that you are one of the 'pros' for this site and i'm just a 'newbie', and thus i should respect your decision,

i'd like to know what was the reason for closing this question? the problem still persits, see bug about table:orientation and since 'the developers' don't care much about it so far - although moving from ODF 1.2 to ODF 1.3 would be a unique opportunity to make a correction with little collateral damage -

i consider it appropriate to:

  • ask users for support, maybe someone has a good idea,

  • warn users so that if someone else steps in this trap he shouldn't lose as much time as i did trying to understand an obscure phenomenon, but could be informed that ther is a bug and (s)he has to deal with it,

and - imho - these points are hindered by the ...(more)

newbie-02 gravatar imagenewbie-02 ( 2020-07-21 08:40:21 +0100 )edit