[Issue] Bug in Calc CellRange.Filter

[FJCC]

At the risk of making a fool of myself, I'd like to see if someone can explain this behavior. There is a post on the Spanish forum that asks about a possible bug in the filtering of rows in Calc. The case presented is pretty simple. The macro defines a cell range A2:A1000. Cells A2:A100 have the numbers 1 - 99 entered directly, i.e not with any formula. The macro is set up with a loop to filter for each number sequentially. This all works fine except for values of 66 and 67. When the loop gets to 66, the filter hides all of the cells with numbers but does show the cells in the range that are empty. For 67 the filter shows all the cells with numbers and hides the empty ones. I messed with the cell contents, adding cells with values beyond A100 and making blank cells in A2:A100. In every case, 66 hides all numbers (or text) and 67 hides the empty cells. I did these tests with a slightly modified code where I removed the loop (because it is tedious to step through all 99 numbers) and a MsgBox call. My code is below and I've attached to original file. Am I missing something obvious or is this a bug in the CellRange.Filter() method?

Code: Select all

Dim iContador As Integer
Dim Campos(0) as New com.sun.star.sheet.TableFilterField
Sheet = ThisComponent.Sheets.getByName("FLT")
Cellrange = Sheet.getCellRangeByPosition(0,1,0,999)

'For iContador = 1 to 99 Step 1
iContador = 67

FDescript = Cellrange.createFilterDescriptor(True)

Campos(0).Field = 0
Campos(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
Campos(0).IsNumeric = TRUE
Campos(0).NumericValue = iContador 
FDescript.setFilterFields(Campos())
Cellrange.Filter(FDescript)

'MsgBox(iContador,16,"Filtro")
'Next

[B Marcelly]

Hi,
I confirm the strange results, both on OOo 3.1.1 and on the old version 1.1.5
I used a slightly better code on a new document, and with a shorter range.

Code: Select all

sub Test
Dim iContador As Double
Dim Campos(0) as New com.sun.star.sheet.TableFilterField
Dim Sheet As Object, CellRange As Object
Dim FDescript As Object

Sheet = ThisComponent.Sheets.getByName("bb")
Cellrange = Sheet.getCellRangeByName("A2:A15")

iContador = InputBox("Special values : 66, 67, clear=0","Enter filter value", 33)
FDescript = Cellrange.createFilterDescriptor(True)
if iContador > 0  then
  Campos(0).Field = 0
  Campos(0).Operator = com.sun.star.sheet.FilterOperator.EQUAL
  Campos(0).IsNumeric = TRUE
  Campos(0).NumericValue = iContador 
  FDescript.setFilterFields(Campos())
end if

Cellrange.Filter(FDescript)
MsgBox(iContador,0,"Filtro")
end sub

In sheet "bb" put any value in the cell range, keep some empty cells. Then run the macro.
You can input the desired value. Value 0 or Cancel will suppress the filter.

You should check if there is an Issue on filter API.
______
Bernard

[FJCC]

Bernard - Thank you for confirming this behavior. I strongly suspected that I was overlooking something, as the failure is so strange and the numbers that trigger it don't seem special in any way. I searched the issue tracker and did not find anything, so I submitted a new issue 107184

[shardalule]

Hi,
This macro will be very useful for me if you give me the idea how to do for the String content for example
if we have the content as a ,b and c in the column it should filter all the a and then b and then c....

Please reply thanks