Alex Thurgood wrote:
Besser wäres natürlich, wenn ich die Dateien nicht vergessen hätte :-o
Alex
REM ***** BASIC *****
option explicit
sub SWsortUp()
thisComponent.lockcontrollers
SWSort true
thisComponent.unlockcontrollers
end sub
sub SWsortDown()
thisComponent.lockcontrollers
SWSort false
thisComponent.unlockcontrollers
end sub
sub SWsort(blnUpDown)
Dim oSheet ' The chosen
Calc Sheet
Dim oListe as Object ' The area to be sorted
Dim intListeStartSpalte '
Dim intListeEndSpalte '
Dim lngListeStartZeile '
Dim lngListeEndZeile '
Dim intListeAnzSpalten '
Dim lngListeAnzZeilen '
Dim intKritSpalte as Integer ' Number of Columns to be used for
sorting
Dim blnUeberschriften ' Does the list include column
headers?
Dim i as Integer ' Runtime integer
variable
Dim oRange as Object ' Helper variable for the
selected cell area
Dim aSortFields(1) as New com.sun.star.table.TableSortField
Dim aSortDesc(1) as New com.sun.star.beans.PropertyValue
'The chosen Calc sheet
oSheet = ThisComponent.CurrentController.ActiveSheet
' The area selected by the user
oListe = thisComponent.CurrentSelection
' Only one selected area is allowed, multiple zones won't work
if oListe.supportsService("com.sun.star.sheet.SheetCellRanges") then
msgbox "Multi-zone sorting non-functional!",,"© Ingenieurbüro
Weigel"
exit sub
end if
'Column determination for the active cell
oRange =
thisComponent.createInstance("com.sun.star.sheet.SheetCellRanges")
ThisComponent.CurrentController.Select(oRange)
intKritSpalte = ThisComponent.CurrentSelection.getCellAddress.Column
ThisComponent.CurrentController.Select(oListe)
'Selection of the list area, if exactly one cell has already been
selected
'(Tip: use Calc's own Sort recognition procedure)
SelectCurrentRange
'Lines and columns of the area to be sorted
intListeStartSpalte =
ThisComponent.CurrentSelection.getRangeAddress.StartColumn
intListeEndSpalte =
ThisComponent.CurrentSelection.getRangeAddress.EndColumn
intListeAnzSpalten = intListeEndSpalte - intListeStartSpalte
lngListeStartZeile =
ThisComponent.CurrentSelection.getRangeAddress.StartRow
lngListeEndZeile = ThisComponent.CurrentSelection.getRangeAddress.EndRow
lngListeAnzZeilen = lngListeEndZeile - lngListeStartZeile + 1
'Number of sort columns within the area to be sorted
intKritSpalte = intKritSpalte - intListeStartSpalte
if lngListeAnzZeilen = 1 then exit sub
'Column headers ?
blnUeberschriften = false
'The first line is interpreted as the column headers if the cells of
the first and second lines contain different data types
for i=intListeStartSpalte to intListeEndSpalte
if
oSheet.getCellByPosition(i,lngListeStartZeile).FormulaResultType <>
oSheet.getCellByPosition(i,lngListeStartZeile+1).FormulaResultType and _
oSheet.getCellByPosition(i,lngListeStartZeile).FormulaResultType <> 0 and _
oSheet.getCellByPosition(i,lngListeStartZeile+1).FormulaResultType <> 0 then
blnUeberschriften = true
exit for
end if
next i
if blnUeberschriften = false then
'The first line is also interpreted as the column headers,
'if the cells of the first and second lines contain the same data
types, but have different formatting styles
for i=intListeStartSpalte to intListeEndSpalte
if
oSheet.getCellByPosition(i,lngListeStartZeile).CellStyle <>
oSheet.getCellByPosition(i,lngListeStartZeile+1).CellStyle then
blnUeberschriften = true
exit for
end if
next i
end if
If blnUeberschriften and lngListeAnzZeilen > 1 then
lngListeStartZeile = lngListeStartZeile + 1
lngListeAnzZeilen = lngListeAnzZeilen -1
end if
if lngListeAnzZeilen = 1 then exit sub
'Insert a helper column
oSheet.Columns.insertByIndex(intListeEndSpalte+1,1)
'enumerate the existing order in the helper column
for i=lngListeStartZeile to lngListeEndZeile
oSheet.getCellByPosition(intListeEndSpalte+1,i).value=i
next i
oListe
=oSheet.getCellRangeByPosition(intListeStartSpalte,lngListeStartZeile,intListeEndSpalte+1,lngListeEndZeile)
'Sort properties
aSortFields(0).Field = intKritSpalte 'Columns in which the
user has set the active cell
aSortFields(0).IsAscending = blnUpDown
aSortFields(0).IsCaseSensitive = false
aSortFields(1).Field = intListeEndSpalte+1 'Helper column with existing
order
aSortFields(1).IsAscending = true
aSortFields(1).IsCaseSensitive = false
aSortDesc(0).Name = "SortFields"
aSortDesc(0).Value = aSortFields()
aSortDesc(1).Name = "ContainsHeader"
aSortDesc(1).Value = false
oListe.sort(aSortDesc())
'remove helper column
oSheet.Columns.removeByIndex(intListeEndSpalte+1,1)
oListe
=oSheet.getCellRangeByPosition(intListeStartSpalte,lngListeStartZeile,intListeEndSpalte,lngListeEndZeile)
ThisComponent.CurrentController.Select(oListe)
end sub
sub SelectCurrentRange
dim oDisp as object
dim oDoc as object
dim Array()
oDoc = ThisComponent.CurrentController.Frame
oDisp = createUnoService("com.sun.star.frame.DispatchHelper")
oDisp.executeDispatch(oDoc, ".uno:SortAscending", "", 0, Array())
oDisp.executeDispatch(ThisComponent.CurrentController.Frame,".uno:Undo", "",0,
Array())
End Sub
Whilst migrating from Microsoft Excel to OpenOffice.org, I discovered that Calc
can only sort Lists using at the most three simultaneous criteria.
The problem stems from the fact that Calc doesn't use any reliable sort
algorithm. Reliable sort algorithms take into account the relative order of
elements which, when compared to their position are equivalent, and do not
change, whereas unreliable sort procedures do not guarantee such a behaviour.
This means, and contrary to what is found in Excel, that with Calc you can not
build on a previously constructed sort order.
As an example to clarify the situation:
If you carry out several subsequent sorts in Excel, irrespective of whether
this is done via the menu Data|Sort or via the button in the toolbar, the
subsequent sorts all build on the order established by the previous sort. In
this way it is possible to organise any desired sort hierarchy according to
multiple sort criteria.
Exercise:
A business turnover list should be sorted according to 5 criteria,
... the first line should sort the Sales Rep
... within the Sales Rep list by Region
... within the Region by Article Group
... within the Article Group by Customer Group
... within the Customer Group by Date.
Solution A using the Sort Toolbar button:
Sort Liste
... first using the Date
... then using the Customer Group
... then using the Article Group
... then using the Region
... then the Sales Rep
Solution B using the Data|Sort menu:
Sort List
... first using 1. Article Group, 2. Customer Group, 3. Date
... then again with 1. Sales Rep, 2. Region
If you try to do this with Calc, Solution A fails because of Issue 7277 (sorts
are always carried out from the first column and not from the column in which
the active cell is to be found).
Solution B also fails because Calc throws away the pre-existing sort order at
every new sort command. This comes down to the fact that Calc uses an
unreliable sort method (Issue 20491).
A further disadvantage also rears its ugly head when using Calc:
Although Calc, like Excel, is capable of automatically recognising the area of
the worksheet in which the list is to be found, for this to work properly, the
user has to have previously selected a single cell within the list range (and
not a zone covering several cells). Once the cell has been selected, the sort
program attempts to determine the limits of the list to be sorted in all four
directions. In such a case, the program either meets the boundary of the sheet
(for example, upper left), or it finds a line or column that has no entries
whatsoever corresponding to the height or breadth of the list.
Calc can also detect whether the list contains a column header in the first
line, so that this line is not included in the sort procedure. To do this, it
compares the data types and the formats of the first and second lines. If the
two are not the same, Calc interprets the first as the column header.
The problem with Calc is that this recognition only works if the user goes
through the menu entry Data|Sort. In this case, the option "Zone includes
column headers" is automatically activated. If you use the toolbar button
however, Calc always sorts the first line as well as the rest. This odd
behaviour can only be avoided by pre-selecting the area to be sorted, and
voluntarily avoiding selection of the column headers. Not only is this way of
doing things time consuming, but also goes against the basic rules established
in Excel, whereby the user only has to explicitly select one cell within the
list. These basic rules are followed by Calc with the AutoFilter, Datapilot and
Sort using the menu entry Data|Sort, but not when attempting to the same thing
using the Sort toolbar button.
The macro presented here using OpenOffice.org Basic proposes an improved sort
mechanism that has the following improvements:
o reliable sorts
o the possibility of using multiple sort criteria by determining the
pre-existing sort order of previous sorts
o sorting using the column in which the active cell has been selected
o recognises column headers, if present
The macro works as follows:
Reliables sorts are not conditioned on the use of a preprogrammed sort
algorithm. The macro uses a feature that is already present in the unreliable
Calc sort algorithm in order to obtain reliable results. From the user's point
of view, the macro only ever sorts according to one criterium per sort
operation. In reality, the macro creates a temporary extra column, in which the
order obtained from the previous sort operation is enumerated. This column
serves as a second criterium for each sort operation. In this way, the
pre-existing sort order is maintained, so long as the sort order isn't modified
by the current sort criterium. This is known as reliable sorting.
Once reliable sorting has been established, it is possible to use as many sort
criteria as is wished. The only drawback is that you have to carry out more
sort operations, and use the criteria in reverse order hierarchy. (See Solution
A above)
The sort criteria to be used in the current sort operation is always defined as
the column in which the currently active cell is to be found. A sort operation
is therefore usually attainable using just two mouse clicks: (1) click on any
single cell within the list and in the column to be used as sort criterium. (2)
click in the toolbar, to get the macro sort in ascending or descending order.
It is only now necessary to (painstakingly) preselect the zone to be sorted in
exceptional circumstances, for example, when that zone is not to be found in a
list surrounded by empty cells.
The macro also recognises whether the first line contains headers. It does this
by comparing the data types of the cell content of every column in the first
and second lines. If these are not all identical, then they are considered to
be headers. In some rare cases, a list may contain identical data types in all
columns to those of your headers (e.g. all text content). The macro thus also
checks to see whether there is a difference in formatting styles between the
first and second lines, which corresponds to general use case scenario within
OpenOffice.org, whereby different structural elements of a document should have
different formatting styles attributed to them.
Unfortunately, execution of the macro can not be undone, unlike mouse click
functions. It is possible to find out more about this in the "Cancel"-History
of the individual steps that were carried out by the macro (see Issue 53097).
Future versions of Calc may bring improvements in this area.
Pimp my Calc : how do I integrate this macro into my OpenOffice.org
installation ?
(1) You'll find the source code for the macro here : PimpMyCalc.bas
(2) Within OpenOffice.org choose Tools | Macros | Manage Macros |
OpenOffice.org Basic...
(3) Click on Manage...
(4) Select "My Macros" and the click on New...
(5) Enter in any name that you wish for your new Module. Then click on "OK".
(6) Click on Edit... The new module is now open in the Basic IDE.
(7) Replace the existing text in this document with that copied from
PimpMyCalc.bas
(8) Close the Basic-IDE Editor.
(9) In the Tools | Adapt... | Toolbars menu entry choose a toolbar or create a
new one.
(10) Click on Add... and then choose on the left under "OpenOffice.org Macros"
the newly added module. Select the entry "SWsortUp" on the right hand side and
click on Add. This is the macro for ascending sorts. Add the corresponding
"SWsortDown" routine for descending sorts.
(11) Using the Change button and the command Swap Icon... , you can choose
which icon is to be displayed in the toolbar for each of these macros.
And now, have fun trying sorts in Calc!
Licence information: The macro is free and cost-free to use for personal and
non-commercial use. Other uses upon request to the author.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
