At the top of the code, notice we have a list of 6 specs specifying the sort.
Code: Select all
#specs = [reverse, text first, ignore case, natural key, ascii collation, by columns]
specs = [0,0,0,0,0,0]
It is currently necessary to edit the macro to change these specs. This is obviously clunky, and a couple of ideas occur:
1) Incorporate a dialog, making this an add-on available from the context menu.
2) Make another add-in function that sorts this way.
Since I don't suppose this feature is all that generally useful, I haven't been inclined to pursue these ideas.
The specs listed should be reasonably self-explanatory, except maybe for the naturalkey, which is discussed in the Array Sort thread, and also maybe the last spec, by columns, which sorts first down columns instead of across rows.
So, for example, to reverse the sort and ignore case, edit the specs as follows
Code: Select all
#specs = [reverse, text first, ignore case, natural key, ascii collation, by columns]
specs = [1,0,1,0,0,0]
As a further note, this sort will also work on formula results within the range, but beware that the formulas will be overwritten by the results after the sort.
Here is the full code, edited as discussed in the following post. Select a CellRange and run SortCellRange.
Code: Select all
import uno
import unohelper
from functools import partial
import itertools
from com.sun.star.lang import Locale
import re
#Regex used by natural key function
NUMBER_RE = re.compile(r'[1-9]\d*')
context = XSCRIPTCONTEXT
def SortCellRange(*dummy):
#specs = [reverse, text first, ignore case, natural key, ascii collation, by columns]
specs = [0,0,1,1,1,0]
ByColumns = specs[5] #spec used in this function.
specs = specs[:5] #Remaining specs used by sort function.
ctx = context.getComponentContext()
desktop = ctx.getServiceManager().createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
oDoc = context.getDocument()
oView = oDoc.getCurrentController()
oSel = oView.getSelection()
if oSel.supportsService("com.sun.star.sheet.SheetCellRange"):
oArray = oSel.getDataArray()
#Store number of columns and rows in range.
cols = len(oArray[0])
rows = len(oArray)
#Make data array a one-dimensional list for sorting.
flatArray = list(itertools.chain(*oArray))
xCollator = ctx.getServiceManager().createInstance("com.sun.star.i18n.Collator")
aLoc = oDoc.getPropertyValue("CharLocale")
#Sort the array.
sortedArray = xCollator_sort(flatArray,specs,xCollator,aLoc)
#Restore to 2D array, transpose if sort is to go down columns.
t = zip(*[iter(sortedArray)]*cols) if not ByColumns else zip(*zip(*[iter(sortedArray)]*rows))
oSel.setDataArray(tuple(t))
return
def blankval(v):
return 1 if type(v) == unicode and len(v) == 0 else 0
def xCollator_sort(rrange,specs,xCollator,aLoc):
rev, TextFirst, ignore_case, isnatural, asciicollate = map(int, specs)
if not asciicollate:
xCollator.loadDefaultCollator(aLoc,ignore_case)
k = partial(cmp2key,xCollator = xCollator,TextFirst = TextFirst, isnatural = isnatural, rev = rev)
rrange.sort(key=k(fncmp),reverse = rev)
else:
if not isnatural:
rrange.sort(key=lambda e: e.lower()
if ignore_case and type(e) == unicode else e, reverse = rev)
else:
rrange.sort(key=lambda e: naturalkey(e.lower())
if ignore_case and type(e) == unicode else naturalkey(e), reverse=rev)
if TextFirst:
rrange = texttofront(rrange, rev)
return rrange
#Comparison function for xCollator. Compare a and b.
def fncmp(a,b,xCollator,TextFirst,isnatural,rev):
acomp = naturalkey(a) if isnatural and type(a) == unicode else (a if type(a) == unicode else a)
bcomp = naturalkey(b) if isnatural and type(b) == unicode else (b if type(b) == unicode else b)
if blankval(acomp) and blankval(bcomp):
return 0
elif blankval(acomp):
return 1 if not rev else -1
elif blankval(bcomp):
return -1 if not rev else 1
elif type(acomp) == unicode and type(bcomp) == unicode:
return xCollator.compareString(acomp,bcomp)
elif type(acomp) == float and type(bcomp) == float:
return numcmp(acomp,bcomp)
elif type(acomp) == float:
return 1 if TextFirst else -1
else:
return -1 if TextFirst else 1
def naturalkey(arg):
if type(arg) == unicode:
nkey = partial(NUMBER_RE.sub, lambda m: '%03d%s' % (len(m.group()), m.group()))
return nkey(arg)
else:
return arg
#Compare two numerics
def numcmp(a,b):
if a < b:
return -1
elif a > b:
return 1
else:
return 0
def cmp2key(mycmp,xCollator,TextFirst,isnatural,rev):
#Convert a cmp= function into a key= function
class K(object):
def __init__(self, obj, *args):
self.obj = obj
def __lt__(self, other):
return mycmp(self.obj, other.obj, xCollator, TextFirst, isnatural, rev) < 0
def __gt__(self, other):
return mycmp(self.obj, other.obj, xCollator, TextFirst, isnatural, rev) > 0
def __eq__(self, other):
return mycmp(self.obj, other.obj, xCollator, TextFirst, isnatural, rev) == 0
def __le__(self, other):
return mycmp(self.obj, other.obj, xCollator, TextFirst, isnatural, rev) <= 0
def __ge__(self, other):
return mycmp(self.obj, other.obj, xCollator, TextFirst, isnatural, rev) >= 0
def __ne__(self, other):
return mycmp(self.obj, other.obj, xCollator, TextFirst, isnatural, rev) != 0
return K
def texttofront(rrange,rev):
if not rev:
i = 0
while type(rrange[i]) == float:
i += 1
else:
i = len(rrange)
while type(rrange[i - 1]) == float:
i -= 1
return rrange[i:] + rrange[:i]
g_exportedScripts = SortCellRange,