[Solved] How to sort and filter a table ?

[doublehp]

In short, I want to create a "stock table".

For example, I will have about 100 products. Each product has a name, reference, and price (in practice, there will be a dozen of criteria). I want to be able to:
- sort the whole sheet according to each criteria
- filter the table with keywords by each criteria

So, in each column, there will be: the Label, a button, and a text box.

Some how, a bit like Thunderbird for emails.

This is my first time in OOCalc. Up to now, I have crated a new sheet (year, I am good \o/ ) put my labels in the first line/row, created a few buttons, text boxes and put some values around.

When I tried to assign HelloWorld to any button, pressing the button returns me a Python error (something like "passing one argument when none were expected", from memory). When I try to assign any thing else, there is no visible effect.

I have tried to record macros, with actions "select cells", "data->sort->col->OK" , but, when replaying them, I dont have any result (replaying as single macro, or as attached to buttons).

http://www.tutorialsforopenoffice.org/c ... sheet.html did not help, since he explains how to use Calc for calculations, and, gettings things appear in specific cell, whereas I want the whole sheet to be recomputed.

Like in thunderbird, I want the filter functions to have effect as I type any key. I dont need multiple filtering, so, only the last use text box should have effect. The A column may contain this kind of "misc features".

I dont want functions to get shuffled when inserting a new colomn in the middle.

I have also noticed that when not selecting any cell, sort functions work on all the sheet, including the first lines that contain the labels, buttons, and text box, even when they are protected, and in protected mode: getting some data lines moving under the buttons is not handy at all.

I did not find in tutos how to use the content of a etxt box as argument for a function, nor how to include SQL commands in macros, or peform this kind of global operations on a compleet sheet.

Example of sort feature: http://extjs.com/deploy/ext/examples/gr ... -grid.html

[TerryE]

Have you read up on the Filter and AutoFilter functions? If this what you want?

[doublehp]

Can you describe me where they are ? and how to include them in a macro, a function, or something I can bind to a button ?

[Villeroy]

Some how, a bit like Thunderbird for emails.

You want a database rather than a spreadsheet.

[doublehp]

4 people on IRC told me I need a spreadsheet.

Villeroy if you think I need a DB, could you consider http://forums.gentoo.org/viewtopic-p-46 ... ml#4634288 ?

[TerryE]

Can you describe me where they are ? and how to include them in a macro, a function, or something I can bind to a button ?

You'll find them under the Tools->Filter menu. To find out how to use them read the online help: Hit F1 select the find tab and type "Filter". The first entry called "Filter" points you to all the sections

[doublehp]

In my version of OOo, I have Filter feature only under the Data menu.

I know how to use those filters, the manual way, but, I have problems setting filtering actions in macros, and binding macros to a button. Something breaks in the way, and, when I click the button, filtering "does not happen".

[TerryE]

Merry Christmas. It's all right; its on the data menu on my OOo as well. I w\s just having a mind-fart.

If your issues are with macro programming the filters, then the method is somewhat different. See http://api.openoffice.org/docs/Develope ... _Selection

There are also some examples in the wiki.

[Villeroy]

4 people on IRC told me I need a spreadsheet.

Villeroy if you think I need a DB, could you consider http://forums.gentoo.org/viewtopic-p-46 ... ml#4634288 ?

]

I need a software solution to handle data; there will be 100 to 200 lines ...
I love CSV format, ...

Some combination of Gnu-utils (grep, sort) wrapped in bash script can do this most efficiently. For a point and click operation you need a larger hammer like a database frontend, spreadsheet or a development platform like Java.
PhpMyAdmin, mentioned in the gentoo forum, is a frontend for the MySQL database engine. It is implemented by a set of php-scripts, which generate html output from sql queries, so you can use it with any browser from any system.
OK, 100 to 200 lines of non-relational data. That should be managable easily with a spreadsheet but you won't get a single-click operation nor filter-as-you-type without heavy scripting. Without scripting you may get as far as type-until-match and then filter-by-matched-value.
Select data range, Data>Filter>Define... give a name to the data range
Data>Filter>Standard... define filter once.
Create a button on a toolbar or a hyperlink =HYPERLINK(".uno:DataFilterStandardFilter";"Click Me")

Next time when you want to change the filter:
Select a cell in the list or the entire list (Data>Select)
Click button or hyperlink on sheet
Edit the criteria in the dialog's list box
I can think of a more flexible way with "advanced filter" by criteria range.

http://www.tutorialsforopenoffice.org/c ... sheet.html did not help, since he explains how to use Calc for calculations, and, gettings things appear in specific cell, whereas I want the whole sheet to be recomputed.

Calculating positional data is what a spreadsheet is designed for in the first place. Recomputing (related) lists is what a database is designed for.

File>New>Database... opens another universe of options.
This particular task would involve linking or import of data, add one or two helper tables, one or two queries, a form with a listbox, a sub-form, ... possibly some scripting. Then you edit the listbox until the searched item is matched, hit enter and see the filtered sub-form.

Anyway, what you want to do (filter-by-typing like in thunderbird) is a complicated task, no matter which toolset you use. Thunderbird works more like a database (indexed mail entities with fields sender, recepient, subject,...). Setting up such a filter-by-typing box involves many design considerations. So you won't get away with a simple set of tools.

[doublehp]

Villeroy I dont think I need heavy scripting. C and GTK could do. And, for actions-as-you-type, the text boxes in oocalc support "text modified" event ... what sounds like what I need.

In database course, I was said that MSO include one, and OOo also have one ... but I did not understood where to type SQL commands, neither how to include them in a macro.

I will try to dig the DB part you pointed for me. I have yet to understand how to design a GUI for this. I think it could be very easy for me in C and GTK, but may take several days of dev ... and, maybe it could be more "hard" for me with existing tools, but faster afterwards.

I will re-read your post later, and try to work about DB.

[Villeroy]

OK, OK, you are a programmer. So you propably do not need any office program nor database for simple list processing. My answer referred to this site's topic which is OOo with components. In general I avoid macros since they break compatibility with other programs and most users have no idea how they work.
Anyways, thinking about this issue I noticed that I have everything at hand to do this in Calc. I clicked together the attached spreadsheet.
The helping Basic macro addresses the fact that Data>Refresh refreshes a changed data range without re-reading the underlying criteria range http://www.openoffice.org/issues/show_bug.cgi?id=63750
The only thing I had to do was adding a text box, assign a text-changed-event macro, link it's content to a cell, the cell is referred by a filter criteria range so it is concatenated to .*foo.* [regular expression: "contains foo"]
Then I set up the advanced filter once with option "Use Reglar Expressions".

P.S. You may call me a fool, since I preached about databases before coming up with a complete solution in a spreadsheet. Please consider that my text box resides on a form layer, which is usually connected to some kind of database. The same functionality on a datbase form might look very similar (update form's filter on text-change event) but I did not have the code at hand. And hey, this is the Calc forum.

[doublehp]

TerryE sounds like C source ... I dont want to hack OOo, I want to understand how to define working macros.

Villeroy in here, I try to see if an existing GUI like OOo could help me. I found Data>Define Range , but not "define filter once". I see defined ranges in "copy result to", but not in the source "filter criteria" or '"field name" ... Same problem with sort.

If entering a word, then clicking a "search" button would easier, I could start with this as a first step; but I still dont see how I could get back the content of a text box; editing the content of a cell may be eaiser. This still dont solve the Sort problem.

I will have a look at Databases; maybe I will understand them better than Calc. Cause I really cant see how calc will help me in this. any time you give me help, I am unable to understand how to do things.

Villeroy I dont mind breaking compat wirh MS suites; I can also install OOo on windows. Considering things from this angle, having a OOo specific solution, that will remain Windows/Linux compatible throught OOo will remain more portable than having to install and run SQL+apache on my workstation, or make my simple thing depend on any web server. I really want to keep the thing 'local'. I am prettu sure there is everything I need in OOo, and thats why I prefer spend some time in there, than starting writing C (CSV parser + GTK fronhead would not be hard for me, but would require to be recompiled when I reinstall my box). Home made C would be the less imaginable portable thing, so, will be the last solution to think about.

Maybe you could email me the file if what you did, so I see if I understand it, and can ispire ? I send you my email i private.

I will spend a few hours looking at DB and things you say in your last message about macros.

[Villeroy]

The example spreadsheet is attached to my previous posting.
Like in Excel there are 3 ways to set up a filter for a database range. No matter which method you choose, from an API point of view it is the same filter.
1. Auto filter: max 8 criteria (c1 AND c2 AND c3 AND ...)
2. Standard filter: max 3 (don't ask why only 3) connectable with AND/OR and many operators
3. Advanced filter: Criteria are read from another cell range. Most flexible since you may use calculated criteria. Max. 8 criteria below a row of matching headers. Each new row implies an OR connection.

My example uses an advanced filter to be set up beforehand, including option "Regular Expressions".

If you want to transfer my solution to your own spreadsheet:
Select your data and create a named database range (Menu:Data>Define...). The example file uses imported data.
Install the macro into your document, so it is portable:
Tools>Macros>Organize>Basic... button [Organizer...]
Copy (Ctrl+Drag) the modules from Library "Standard" in my example document "typefilter", to library "Standard" in your own document (or any other library you may create in your document).
Follow the instructions in the file and adjust the hardcoded range names in the macro.

[doublehp]

< useless message deleted >

[doublehp]

I had a problem when defining the criteria: filling << criteria >> in the field says "invalid sheet reference", then "invalid something else". I have to enter << $Sheet1.$D14:$D15 >> to be allowed to validate the popup. I had defined previously D14:D15 as criteria, exactly the way you told me (even if it took me 20mn to understand that the box name is NOT the box content ^^ ) ... I also tried to mess with $criteria, and triple checked the spelling of << criteria >> ... I always got invalid sheet reference.

I dont understand what you mean with "pasted already existing Basic Code into ...".

Linking works (when I hit in box, things come out in linked cell).

[...]

[doublehp]

I got it. You do not use macros, but, Basic.

And everything I need to play with it is in the end of your last message.

I think you gave me everything I need to accomplish my filters. Thanks. I have a break, and I will spend a few more hours to work on all this.

Now that I am in a Basic editor, I understand the use of the API posted by someone on first page of this thread.

Last things:
- can you comment about my problem when aplying a filter ? I said that in the GUI, putting << criteria > did not work, I had to pu the fill reference of it, in the form $Sheet1.$D14:$D15 to be allowed to apply ...
- is there a way to pass more arguments to the BAsic script ? for example, the coordinates of the TextBox, or maybe it's name, or something like this, the generic script could take as argument, and that would be defined anywhere ... so that the same BAsic script is used for many columns

BTW: the filter works fine: when I fill the box of the linked cell, filter happens expected way.

Thanks a lot.

[doublehp]

Here we are. I have copied refreshAdvFiler, and created my own specifics, understood how to copy, edit, and create Basics ... and got now interactivity between the text box and the sheet.

But, as you can imagine, I got a problem.

Consider text box in F3 only.

sel_2 selects B4:G8 ; I expect the action to use D14 as column selector for filter, that will become column F in my selection. When I type "2" in the etxt box in F3 ... everything fades away, when I was expecting lines containing 12 2 and 23 to stay around.

I also expected that when I empty the box (remove the "2") I would recover the original file, like with yours ...

Can you have a look at this ? and explain me why it does not work ?

I put everything in different cells on purposes, to see what setting has which effect.

[doublehp]

second attempt, same problems.

Did everything as you said, except the step about applying manually the filter. Does not want to work even this second tie, when I started from scratch in a blank doc.

Here is a bug in your proposed file: supress column G to P, type text in the filter box, enjoy the columns'r back. More fun: insert a column in the middle of the data fields, and fill it with some values. Hit one letter in the etxt box, enjoy the brand new data going away Of course, saving and re-opening the document in the midle of the process does not alter the result Means, dispite save/record and re-opening, ther is a back-record of the column count and content ...

How did you create that large B1 cell ?

[Villeroy]

Did everything as you said, except the step about applying manually the filter.

Why not? The macro does not handle all options of the filter. It is rather generic and refreshes a given database range after applying a previously set or optionally passed criteria range. With an empty text box the criteria cell shows .*.* which is a literal string unless you tell the filter to handle regular expressions.
So use the linked cell's value directly without the surrounding wildcards .*.* or set the regex option below Menu:Data>Filter>Advanced...[More Options].
Third option:

Code: Select all

Sub refreshAdvancedFilter()
...
        oDBRange = oDoc.DatabaseRanges.getByName("sel_2")
	oDBRange.FilterDescriptor.UseRegularExpressions = True
...

Do you really want to match unique ID numbers with this kind of form control? Filter numbers containing the digits "23"? I think filtering by pattern-matching a typed string makes sence with long strings such as names or remarks.

[doublehp]

I said ... because typing << criteria>> in the advanced filter box can not be validated when I click OK. See previous posts.

I have several DataRanges for tests, they are called sel_1 sel_2 sel_3 ...

I tried to do everything like you did in typefilter.ods ; failures have been detailed in previous posts. I have been working on that for hours yesterday, and I now need you to read them all, and answer all of them. For things that have been solved in the mean time, or, things I understood recently, I have edited my posts, removed useless points and question, and added new problems.

[Villeroy]

"This range dies not include a valid query". Then it highlights your db-range "sel_1" which does not include any headers. Select entire "sel_2" (Data>Select) or remove the intersecting "sel_1" completely (Data>Define...).

Additional hint 1: Instead of typing the fully qualified absolute address $Sheet1.$D$14:$D$15 you may prefer clicking the button next to the address field and point to the desired cells by using the mouse.
Additional hint 2: You can tag named references as "Filter" so they appear in the advanced filter dialog.
Menu:Insert>Name>Define...(Ctrl+F3), pick your range "criteria", [More Options], [X] Filter.
Menu:Data>Filter>Advanced... pick "criteria" from the list.
Btw: Same works with print ranges and their repeating rows/columns. Tag the named references and pick them from the print ranges dialog.

[doublehp]

Understood the problem.

Hint2 works.

Hint1 : I dont understand it.

After deleting columns, the bug does not happen in my personal sheets; but, do you understand why it happens in yours ? maybe because you imported data ?

[doublehp]

Very big progress: when you click on a box, the criteria name is updated. What means, both boxes share the same geenric filter function. It's the fact you click in a box that will change the column to search in.

Ok, there is very much to do, but I now start to understand this funny langage.

I found that
oCell.string = "foobar"
thing in Google. But, is this kind of thing documented in http://api.openoffice.org/ ? After hunting hours, I gave up and went to Google.

I dont think I can avoid having the text copied in all boxes, but if you think of a trick ... I do things this way so that adding a new col will require minimal effort. I will add a feature to clear linked_cell when selecting a box (so that when you clic, you always start with empty keyword). Then, let's go for sort

[Villeroy]

Added a more spreadsheet-like approach without ugly macros on Sheet2. It involves a text box and lookup formulas.

[doublehp]

State of art. It's almost what I asked for when I came in here. But adding new columns is too complex, and renaming a column requires to edit the Basic script.

There may be a way to do things in even more extensible way by using ThisComponent.CurrentSelection . It would result in a single text box at the top, with search/filter performed in the pointed colun. I will develop that tomorrow starting from scratch to see if it opens new options. The concept of linked cells, and keeping a track of the last used criteria may be essential for this.

Villeroy it has been really interesting to chat with you. Thank you for your time. Do you want to keep in touch here to see the final result ? Otherwise, from now on, searching examples of code in http://www.oooforum.org/forum/search.phtml may be enough for me.

[Villeroy]

This year's last lazy sunday afternoon:
Sort Buttons On A Sheet

[doublehp]

On my way for the final version ... Villeroy, I have deeply modified your functions, and I am meeting a last problem before project completion.

things happen in the attached file, in Basic module "private functions". I have removed all your security so that I get errors on screen, and can work them out, on purpose.

When you click on the up-left button, or hit a text in a box, I meet the same trouble!: lines 3 to 32 go away. This button should clear the criteria, and ask rfresh.

Criteria_range is A3:A4.

Thanks for help.

[doublehp]

Here is the code fix:

Code: Select all

Sub refreshAdvancedFilterDynamic()
	oAddr = thisComponent.Sheets(0).getCellRangeByPosition(1,1,getLastUsedColumn,getLastUsedRow).getRangeAddress()
	oCriteriaRange = thisComponent.NamedRanges.getByName("criteria_range").getReferredCells()

REM create a temp named range with all cells
	on error goto JumpHere
		thisComponent.DatabaseRanges.removeByName("MyTempNameThatShouldNotBeUsedByUser")
	on error goto 0
JumpHere:
	TempName = thisComponent.DatabaseRanges.addNewByName("MyTempNameThatShouldNotBeUsedByUser",oAddr)
	oDBRange = thisComponent.DatabaseRanges.getByName("MyTempNameThatShouldNotBeUsedByUser")
	refreshDBRange(thisComponent, oDBRange, oCriteriaRange)
	thisComponent.DatabaseRanges.removeByName("MyTempNameThatShouldNotBeUsedByUser")
	
	StdoutCell.value = StdinCell.value + 1
	StdinCell.value = StdoutCell.value
End Sub

Private Sub refreshDBRange(oDoc, oDBRange, Optional oCrit)
'on error goto errUnset
Dim oDBCells, csa, desc, fields
	oDBCells = oDBRange.getReferredCells()
REM I supressed the "never met condition" oCrit=Null ... and getRangeByAddress definition
	csa = oCrit.getRangeAddress()
	if isUnoStruct(csa) then
		desc = oDBRange.FilterDescriptor
		on error goto errDesc
			fields = oCrit.createFilterDescriptorByObject(oDBCells).getFilterFields()
		on error goto 0
		desc.setFilterFields(fields)
		'Xray desc
		desc.UseRegularExpressions = True
		desc.SkipDuplicates = False
		desc.SaveOutputPosition = True
		desc.IsCaseSensitive = False
		desc.ContainsHeader = True
		'Xray fields
		oDBRange.UseFilterCriteriaSource = True
		oDBRange.FilterCriteriaSource = csa
		'Xray oDBRange.FilterCriteriaSource
	endif
	oDBRange.refresh
exit sub
errUnset:
	Msgbox "Module variables not set.", 16, "macro:refreshDBRange"
	exit sub
errDesc:
	Msgbox "Could not read criteria due to missing, duplicate or non-matching headers.", 16, "macro:refreshDBRange"
End sub

My sheet is now 100% operational for the needs described in the first querry. I will post it here in a few days when more data are in.

[doublehp]

Time to publish the result

First, note that I found a more active forum: http://www.oooforum.org/forum/viewtopic.phtml?p=269584

I attached the final result.

The most important bug is that some times, Refresh button does not unhide all lines; this can be worked out by just cliking in either the box or button of the Reference col before refresh. I think the fix should be to alter the RefreshViewPressed() public function, and call ReceivingFocus_ref instead of FlushLinkedCell, or, just put Manifacturer in the criteria name cell, with something similar to:

Code: Select all

cn = thisComponent.NamedRanges.getByName("criteria_name")
c = cn.ReferredCells.getCellByPosition(0, 0)
c.string = "reference"

The most ennting GUI API problem is that supressing a colums just on the right of a column that contains box/button ... most often make those box and button disapear; the workaround this is to previously move those a in a different col (préferably on the left) (activate Design Mode On) and put them back after deletion.

To add a column, insert a new col, copy/paste any box and button from an other col (except the refresh on), go Tools>Macros>Organise>Basic find the Specific_Function familly, and edit any of them. Copy and paste any, change the function name to any name you want, and, change the argument on the second line, to reflect the EXACT colum name/header/label (this is case sensitive !!! )

It's far from perfect, but it does roughy what I need.

Since I marked [SOLVED] it's now code-paste time, so that Google can reference this page ^^ and, add you other peoples searches ... my code that was so hard to Google for previously. I hope this may help some people after me I am not proud of this, and, it's VERY FAR from "good and recommendable", but it works, and may be a nice starting point for people lacking ideas.

Public_functions: they are binded as macros to some events of buttons and text boxs.

Code: Select all

Sub RefreshViewPressed()
	FlushLinkedCell()
End Sub

Sub RefreshViewReleased()
	refreshAdvancedFilterDynamic()
End Sub

Sub SortReleased()
	SortMySheet()
End Sub

Sub BoxModified()
	refreshAdvancedFilterDynamic()
End Sub

Specific_functions: they are colum specific stuff, somehow, my way to "select" a column, and keep the heavy code as much generic in the sheet as possible. I wont paste them all, only the first ones for the example.

Code: Select all

Sub ReceivingFocus_ref()
	ReceivingFocusGeneric("reference")
End Sub

Sub ReceivingFocus_desc()
	ReceivingFocusGeneric("desc")
End Sub

Sub ReceivingFocus_value()
	ReceivingFocusGeneric("value")
End Sub

Sub ReceivingFocus_manif()
	ReceivingFocusGeneric("manifacturer")
End Sub

Sub ReceivingFocus_vinom()
	ReceivingFocusGeneric("Vi nom")

private functions: I did not take time yet to make all of them "private". In most cases,

ThisComponent.Sheets(0)

could be replaced with something like (untested yet):

ThisComponent.getCurrentSelection().getCellAddress().Sheet

After what, to be able to use multiple sheet things, you only need to click in any cell of the viewed sheet before using any function. If you want to have colums having the same name in several sheets ... I think it's just impossible If you really "decide it is", you will need to create a new named cell, containing a sheet reference (name or number) that shall be passed as argument to ReceivingFocusGeneric() the same way I actually pass a col name (new second argument to be implemented).

Code: Select all

REM  *****  BASIC  *****
REM generic Calc stuff

REM If oCrit is missing, then we try to re-read an existing criteria-source.
REM If there is no source-range at all, we simply refresh the db-range.
Private Sub refreshDBRange(oDoc, oDBRange, oCrit)
'on error goto errUnset
Dim oDBCells, csa, desc, fields
	oDBCells = oDBRange.getReferredCells()
REM I supressed the "never met condition" oCrit=Null ... and getRangeByAddress definition
	csa = oCrit.getRangeAddress()
	if isUnoStruct(csa) then
		desc = oDBRange.FilterDescriptor
		on error goto errDesc
			fields = oCrit.createFilterDescriptorByObject(oDBCells).getFilterFields()
		on error goto 0
		desc.setFilterFields(fields)
		desc.UseRegularExpressions = True
		desc.SkipDuplicates = False
		desc.SaveOutputPosition = True
		desc.IsCaseSensitive = False
		desc.ContainsHeader = True
		oDBRange.UseFilterCriteriaSource = True
		oDBRange.FilterCriteriaSource = csa
		'Xray desc
	endif
	oDBRange.refresh
exit sub
errUnset:
	Msgbox "Module variables not set.", 16, "macro:refreshDBRange"
	exit sub
errDesc:
	Msgbox "Could not read criteria due to missing, duplicate or non-matching headers.", 16, "macro:refreshDBRange"
End sub

Function StdinCell()
	iCell = thisComponent.NamedRanges.getByName("stdin").ReferredCells.getCellByPosition(0, 0)
	StdinCell = iCell
End Function

Function StdoutCell()
	oCell = thisComponent.NamedRanges.getByName("stdout").ReferredCells.getCellByPosition(0, 0)
	StdoutCell = oCell
End Function

Function StderrCell()
	oCell = thisComponent.NamedRanges.getByName("stderr").ReferredCells.getCellByPosition(0, 0)
	SterrCell = oCell
End Function

Sub ReceivingFocusGeneric(arg)
	REM When we take focus, we shall update the criteria name
	oRange = thisComponent.NamedRanges.getByName("criteria_name").ReferredCells
	oCell = oRange.getCellByPosition(0, 0)
	oCell.string = arg
	ReceivingFocusGenericB()
End Sub

Sub ReceivingFocusGenericB()
	' When we take focus, we empty linked_cell
	oCell = thisComponent.NamedRanges.getByName("criteria_name").ReferredCells.getCellByPosition(0, 0)
	pCell = thisComponent.NamedRanges.getByName("previous_criteria_name").ReferredCells.getCellByPosition(0, 0)
	if oCell.string = pCell.string then
	else
		FlushLinkedCell()
	'	refreshAdvancedFilterDynamic
	endif
	pCell.string = oCell.string
End Sub

Sub FlushLinkedCell()
	' When we take focus, we empty linked_cell
	oRange = thisComponent.NamedRanges.getByName("linked_cell").ReferredCells
	oCell = oRange.getCellByPosition(0, 0)
	oCell.string = ""
End Sub

Sub refreshAdvancedFilterDynamic()
	oAddr = thisComponent.Sheets(0).getCellRangeByPosition(1,1,getLastUsedColumn,getLastUsedRow).getRangeAddress()
	oCriteriaRange = thisComponent.NamedRanges.getByName("criteria_range").getReferredCells()

REM create a temp named range with all cells
	on error goto JumpHere
		thisComponent.DatabaseRanges.removeByName("MyTempNameThatShouldNotBeUsedByUser")
	on error goto 0
JumpHere:
	TempName = thisComponent.DatabaseRanges.addNewByName("MyTempNameThatShouldNotBeUsedByUser",oAddr)
	oDBRange = thisComponent.DatabaseRanges.getByName("MyTempNameThatShouldNotBeUsedByUser")
	on error goto plopMe
		refreshDBRange(thisComponent, oDBRange, oCriteriaRange)
	on error goto 0
	goto plopHim
plopMe:
	StderrCell.string = "RefreshError"
plopHim:
	thisComponent.DatabaseRanges.removeByName("MyTempNameThatShouldNotBeUsedByUser")
	
	StdoutCell.value = StdinCell.value + 1
	StdinCell.value = StdoutCell.value
End Sub

Sub SortMySheet()
	StdoutCell.value = StdinCell.value + 1
	StdinCell.value = StdoutCell.value
	'on error goto plif
	
	Dim aSortFields(3) as New com.sun.star.util.SortField
	Dim aSortDesc(0) as New com.sun.star.beans.PropertyValue
	'oSortRange = thisComponent.DatabaseRanges.getByName("range_selection_all").ReferredCells 
	oSortRange = thisComponent.Sheets(0).getCellRangeByPosition(1,1,getLastUsedColumn,getLastUsedRow)

' find cell coordinates
	Dim cellZone As Object
	'cellZone = thisComponent.DatabaseRanges.getByName("range_selection_all").ReferredCells.RangeAddress
	cellZone = oSortRange.RangeAddress
	'cellZone.Sheet
	'cellZone.StartColumn
	'cellZone.StartRow
	'cellZone.EndColumn
	'cellZone.EndRow 

	' pic up the column name to find
	oCrit = thisComponent.NamedRanges.getByName("criteria_name").ReferredCells.getCellByPosition(0, 0)
	cName = oCrit.string

	' and it's place
	size = oSortRange.RangeAddress.EndColumn - oSortRange.RangeAddress.StartColumn
	res = -1
	for i = 0 to size
		'a = thisComponent.DatabaseRanges.getByName("range_selection_all").ReferredCells.getCellByPosition(i, 0).string
		a = oSortRange.getCellByPosition(i, 0).string
		if a = cName then
			res = i
		endif
	next

	if res = -1 then
		Msgbox "Error: could not select column to sort", 16, "Sort failure"
		Exit Sub
	endif

	aSortFields(0).Field = res
	aSortFields(0).SortAscending = TRUE
	'aSortFields(0).IsAscending = False 
' second criteria (3 max are supported)
	'aSortFields(1).Field = 0 'col def
	'aSortFields(1).SortAscending = FALSE
	'aSortFields(1).FieldType = com.sun.star.util.SortFieldType.NUMERIC
	'aSortFields(0).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC
	aSortDesc(0).Name = "SortFields"
	aSortDesc(0).Value = aSortFields()
	'aSortDesc(1).Name = "IsSortColumns"
	'aSortDesc(1).Value = False 
	oSortRange.Sort(aSortDesc()) 
		
	StdoutCell.string = "sorted"
	exit sub
plif:
	StdoutCell.string = "error"
End Sub

Function getLastUsedColumn() as Integer
	GetLastUsedColumn = getLastUsedCell.EndColumn
End Function
Function getLastUsedRow() as Integer
	GetLastUsedRow = getLastUsedCell.EndRow
End Function
Function getLastUsedCell() as Variant
	Dim oCell As Object
	Dim oCursor As Object
	Dim aAddress As Variant
	oSheet = ThisComponent.Sheets.getByIndex( 0 )
	oCell = oSheet.GetCellbyPosition( 0, 0 )
	oCursor = oSheet.createCursorByRange(oCell)
	oCursor.GotoEndOfUsedArea(True)
	aAddress = oCursor.RangeAddress
	getLastUsedCell = aAddress
End Function

And for convenience, I add a few "useless" functions, that were nice to play with; I found them in various PDF; also having them in web format may help a few people later on, I hope so

Code: Select all

Sub getRowCol
   oActiveRange = ThisComponent.CurrentSelection.RangeAddress
   nRow = oActiveRange.StartRow
   nColumn = oActiveRange.StartColumn
End Sub

sub PopUpActiveCell()
REM this shows in a pop up the coordinates of pointed cell
REM this crashes when several Cells are selected; there exist workarounds in Google.
	'oCell = ThisComponent.Sheets(0).getCellByPosition(2, 3)
	oCell = ThisComponent.getCurrentSelection()
	on error goto PopUpActiveCellLabelA
		oAddress = oCell.getCellAddress()
	on error goto 0
	MsgBox "Sheet = " & oAddress.Sheet & CHR$(10) & _
		"Column = " & oAddress.Column & CHR$(10) & _
		"Row = " & oAddress.Row
Exit Sub
PopUpActiveCellLabelA:
	MsgBox "Several Cells are selected"
End Sub

Sub testEndColRow
  Dim oSheet
  Dim oCell
  Dim nEndCol As Integer
  Dim nEndRow As Integer
  oSheet = ThisComponent.Sheets.getByIndex( 0 )
  nEndCol = getLastUsedColumn(oSheet)        'see functions below
  nEndRow = getLastUsedRow(oSheet)
  REM Then do as you please, e.g.
  oCell = oSheet.GetCellByPosition( nEndCol + 1, nEndRow + 1 )
  oCell.String = "test"
  ThisComponent.CurrentController.Select(oCell)
End Sub
Function getLastUsedColumnOrig(oSheet as Object) as Integer
  Dim oCell As Object
  Dim oCursor As Object
  Dim aAddress As Variant
  oCell = oSheet.GetCellbyPosition( 0, 0 )
  oCursor = oSheet.createCursorByRange(oCell)
  oCursor.GotoEndOfUsedArea(True)
  aAddress = oCursor.RangeAddress
  GetLastUsedColumn = aAddress.EndColumn
End Function
Function getLastUsedRowOrig(oSheet as Object) as Integer
  Dim oCell As Object
  Dim oCursor As Object
  Dim aAddress As Variant
  oCell = oSheet.GetCellbyPosition( 0, 0 )
  oCursor = oSheet.createCursorByRange(oCell)
  oCursor.GotoEndOfUsedArea(True)
  aAddress = oCursor.RangeAddress
  GetLastUsedRow = aAddress.EndRow
End Function
Sub PopUpSheetSize()
	oSheet = ThisComponent.Sheets.getByIndex( 0 )
	cc = getLastUsedColumn(oSheet) + 1 
	rc = getLastUsedRow(oSheet) + 1
	MsgBox("The sheet has " + cc + " colums and " + rc + " rows.", 0, "Sheet dimensions")
End Sub

Sub PopUpSelectedCells
  oSelect=ThisComponent.CurrentSelection.getRangeAddress
  oSelectColumn=ThisComponent.CurrentSelection.Columns
  oSelectRow=ThisComponent.CurrentSelection.Rows
  CountColumn=oSelectColumn.getCount
  CountRow=oSelectRow.getCount
  oSelectSC=oSelectColumn.getByIndex(0).getName
  oSelectEC=oSelectColumn.getByIndex(CountColumn-1).getName
  oSelectSR=oSelect.StartRow+1
  oSelectER=oSelect.EndRow+1
  NoCell=(CountColumn*CountRow)
  If CountColumn=1 AND CountRow=1 Then
    MsgBox("Cell " + oSelectSC + oSelectSR + chr(13) + "Cell No = " + NoCell,, "SelectedCells")
  Else
    MsgBox("Range(" + oSelectSC + oSelectSR + ":" + oSelectEC + oSelectER + ")" + chr(13) + "Cell No = " + NoCell,, "SelectedCells")
  End If
End Sub



'6.4 Clear a cell
'A list of things that can be cleared can be found at
'http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/CellFlags.html
''******************************************************************
''Author: Andrew Pitonyak
''email:    andrew@pitonyak.org
'Sub ClearDefinedRange
'  Dim oDocument As Object, oSheet As Object, oSheets As Object
'  Dim oCellRange As Object
'  Dim nSheets As Long
'  oDocument = ThisComponent
'  oSheets = oDocument.Sheets
'  nSheets = oDocument.Sheets.Count
'  oSheet = oSheets.getByIndex(2) REM the range is from 0 to n-1
'  oCellRange = oSheet.getCellRangeByName("<range_you_set>") 'for example "A1:B2"
'  oCellRange.clearContents(_
'    com.sun.star.sheet.CellFlags.VALUE | _
'    com.sun.star.sheet.CellFlags.DATETIME | _
'    com.sun.star.sheet.CellFlags.STRING | _
'    com.sun.star.sheet.CellFlags.ANNOTATION | _
'    com.sun.star.sheet.CellFlags.FORMULA | _
'    com.sun.star.sheet.CellFlags.HARDATTR | _
'    com.sun.star.sheet.CellFlags.STYLES | _
'    com.sun.star.sheet.CellFlags.OBJECTS | _
'    com.sun.star.sheet.CellFlags.EDITATTR)
'End Sub



REM this this by side

'	' specify the range that you are going to filter (example A1:F12)
'	xfilter = thiscomponent.sheets(0).getcellrangebyposition(0,0,5,11) ' source range of data to be filtered
'	xfilterDesc=xFilter.createFilterDescriptor(true) ' the filter rules are set up in this
'	' The filter specs are set up as an array (one element in this example) set the dimension accordingly
'	dim aFilterFields(0)  as  new com.sun.star.sheet.TableFilterField
'	' set the filter rules up (one element per column)
'	aFilterFields(0).Field        = 1  ' second column
'	aFilterFields(0).IsNumeric    = true ' numeric comparison
'	aFilterFields(0).Operator     = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
'	aFilterFields(0).NumericValue = 1998  ' value each row is to be compared with
'	' set the filter description up
'	xFilterDesc.setFilterFields(aFilterFields()) 'set the filter rules by assigning the array of fields
'	xfilterDesc.ContainsHeader=true  'there is a header line for the range to be filtered
'	xFilter.filter(xFilterDesc)  'do the filter operation 

The original refreshAdvancedFilter by Vilelroy can be easily found in Google (for web format), or, in the first document he attached to this topic.

Hope this helps people. I really think that a few "methods" sopported by basic lack documentation and "examples". Any one who try to write basic should install and use Xray; really helps a lot; it gives/lists the type of an object, and, all methods and contents that can be used.