[Issue] Bug in Calc CellRange.Filter

Creating a macro - Writing a Script - Using the API

[Issue] Bug in Calc CellRange.Filter

Postby FJCC » Tue Nov 24, 2009 4:02 am

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   Expand viewCollapse view
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
Attachments
Prufiltro.ods
(17.75 KiB) Downloaded 150 times
Last edited by FJCC on Wed Nov 25, 2009 4:02 pm, edited 2 times in total.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8220
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Bug in Calc CellRange.Filter?

Postby B Marcelly » Tue Nov 24, 2009 10:02 am

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   Expand viewCollapse view
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
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Bug in Calc CellRange.Filter?

Postby FJCC » Wed Nov 25, 2009 2:20 am

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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8220
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Issue] Bug in Calc CellRange.Filter

Postby shardalule » Thu Feb 11, 2010 9:51 am

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
shardalule
 
Posts: 83
Joined: Tue Sep 15, 2009 9:44 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 11 guests