Array Sort add-in function.

This script was published by Charlie Young at the Code Snippets Forum.

For many purposes in Calc, it is pretty easy to define data ranges with sort specifications (sort order, headers, etc.), and to redo a sort with a couple of clicks and/or keystrokes, and of course for many purposes it's better to use a database. But it might also be useful sometimes to have an array function automatically sort a range. I have examples, and others may as well. I think it's probably preferable to the tricks in Sorting and Filtering data with formulas.

Original code

import uno
import unohelper
import locale
import re
from com.sun.star.lang import Locale
from com.pysort import XPySort
from operator import methodcaller
from functools import partial
 
# PySort Calc Add-in implementation.
NUMBER_RE = re.compile(r'[1-9]\d*')
 
class PySortImpl( unohelper.Base, XPySort ):
    def __init__( self, ctx ):
        self.ctx = ctx
 
    def PySort(self, inRange, fieldspecs, HasHeaders, ByRows, CollationType, docProps):
        inRange = list(inRange)
        out = []
        if not fieldspecs:
            fieldspecs = ((0,""),)
        elif type(fieldspecs) == float:
            fieldspecs = ((fieldspecs,""),)
 
        specs = CheckFields(fieldspecs,len(inRange[0]))  #Checks fields for validity
 
        TypeCollation = 0   
        if type(CollationType) == float:
            TypeCollation = int(CollationType) if 0 <= CollationType <=  3 else 0      
 
        if ByRows:
            inRange = zip(*inRange)
        if HasHeaders:
            out.append(inRange[0])
            inRange = inRange[1:]
 
        if TypeCollation == 0:
            locale.setlocale(locale.LC_ALL, "")
            sortrange = pyCollator_sort(inRange,specs)
        elif TypeCollation == 1:
            locale.setlocale(locale.LC_ALL, "")
            sortrange = strxfrm_sort(inRange,specs)
        elif TypeCollation == 2:
            aLoc = docProps.getPropertyValue("CharLocale")  #Use locale of document for collation. This could be a problem with multilingual spreadsheets.
            xCollator = self.ctx.getServiceManager().createInstance("com.sun.star.i18n.Collator")
            sortrange = xCollator_sort(inRange,specs,xCollator,aLoc)
        else:
            sortrange = byte_sort(inRange,specs)
 
        out.extend(sortrange)
 
        if ByRows:
            return tuple(zip(*out))
 
        return tuple(out)
 
def blankval(v):
    return 1 if type(v) == unicode and len(v) == 0 else 0
 
def pyCollator_sort( rrange, specs):
    for spec in specs[::-1]:
        field, rev, textfirst, ignore_case, isnatural = map(int, spec )
        k = partial(pycmp2key,i = field,TextFirst = textfirst, ignore_case = ignore_case, isnatural = isnatural, rev = rev)
        rrange.sort(key = k(pycmp),reverse = rev)
    return rrange
 
def pycmp(i,a,b,TextFirst,ignore_case, isnatural, rev):
    #acomp = a[i].lower().encode("utf8") if ignore_case and type(a[i]) == unicode else (a[i].encode("utf8") if type(a[i]) == unicode else a[i])
    #bcomp = b[i].lower().encode("utf8") if ignore_case and type(b[i]) == unicode else (b[i].encode("utf8") if type(b[i]) == unicode else b[i])
    acomp = a[i].lower() if ignore_case and type(a[i]) == unicode else (a[i] if type(a[i]) == unicode else a[i])
    bcomp = b[i].lower() if ignore_case and type(b[i]) == unicode else (b[i] if type(b[i]) == unicode else b[i])
    acomp = naturalkey(acomp) if isnatural else acomp
    bcomp = naturalkey(bcomp) if isnatural else bcomp
 
    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 locale.strcoll(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 pycmp2key(mycmp,i,TextFirst,ignore_case,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(i,self.obj, other.obj, TextFirst,ignore_case,isnatural,rev) < 0
        def __gt__(self, other):
            return mycmp(i,self.obj, other.obj, TextFirst,ignore_case,isnatural,rev) > 0
        def __eq__(self, other):
            return mycmp(i,self.obj, other.obj, TextFirst,ignore_case,isnatural,rev) == 0
        def __le__(self, other):
            return mycmp(i,self.obj, other.obj, TextFirst,ignore_case,isnatural,rev) <= 0
        def __ge__(self, other):
            return mycmp(i,self.obj, other.obj, TextFirst,ignore_case,isnatural,rev) >= 0
        def __ne__(self, other):
            return mycmp(i,self.obj, other.obj, TextFirst,ignore_case,isnatural,rev) != 0
    return K
 
def blankstoend(rrange,field):
    i = firstblank = 0
    rlen = len(rrange)
    while i < rlen and not blankval(rrange[i][field]):
        i += 1
    firstblank = i
    while i < rlen and blankval(rrange[i][field]):
        i += 1
    lastblank = i
    if firstblank < rlen:
        return rrange[:firstblank] + rrange[lastblank:] + rrange[firstblank:lastblank]
    else:
        return rrange
 
def make_key_func(spec):
 
    def key_func(item):
        key = item[field]
        if isinstance(key, unicode):
            for function in functions:
                key = function(key)
            return (1 if len(key)==0 else 0 , key) # here do we put the <empties> to the end
        else:
            return (number_sort_key,key)
    field, rev, TextFirst, ignore_case, isnatural = map(int, spec)
    number_sort_key = 1 if TextFirst else -1 # Here is the corrected 't' -behavior
    functions = list()
    if ignore_case:
        functions.append(methodcaller('lower'))
    if isnatural:
        functions.append(
            partial(
                NUMBER_RE.sub, lambda m: '%03d%s' % (len(m.group()), m.group())
            )
        )
    functions.append(methodcaller('encode','utf8'))
    functions.append(locale.strxfrm)
    return key_func
 
 
def strxfrm_sort(items, specs):
    for spec in reversed(specs):
        field, rev, TextFirst, ignore_case, isnatural = map(int, spec)
        items.sort(key=make_key_func(spec), reverse = rev)
        items = blankstoend(items,field)
    return items
 
def xCollator_sort(rrange,specs,xCollator,aLoc):
    for spec in specs[::-1]:
        field, rev, TextFirst, ignore_case, isnatural = map(int, spec)
        xCollator.loadDefaultCollator(aLoc,ignore_case)
        k = partial(cmp2key,i = field,xCollator = xCollator,TextFirst = TextFirst, isnatural = isnatural, rev = rev)
        rrange.sort(key=k(fncmp),reverse = rev)
    return rrange
 
#Comparison function.  i is field index, a and b are entire rows.
def fncmp(i,a,b,xCollator,TextFirst,isnatural,rev):
    acomp = naturalkey(a[i]) if isnatural and type(a[i]) == unicode else (a[i] if type(a[i]) == unicode else a[i])
    bcomp = naturalkey(b[i]) if isnatural and type(b[i]) == unicode else (b[i] if type(b[i]) == unicode else b[i])
 
    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,i,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(i,self.obj, other.obj, xCollator, TextFirst, isnatural, rev) < 0
        def __gt__(self, other):
            return mycmp(i,self.obj, other.obj, xCollator, TextFirst, isnatural, rev) > 0
        def __eq__(self, other):
            return mycmp(i,self.obj, other.obj, xCollator, TextFirst, isnatural, rev) == 0
        def __le__(self, other):
            return mycmp(i,self.obj, other.obj, xCollator, TextFirst, isnatural, rev) <= 0
        def __ge__(self, other):
            return mycmp(i,self.obj, other.obj, xCollator, TextFirst, isnatural, rev) >= 0
        def __ne__(self, other):
            return mycmp(i,self.obj, other.obj, xCollator, TextFirst, isnatural, rev) != 0
    return K
 
def texttofront(rrange,field,rev):
    if not rev:
        i = 0
        while type(rrange[i][field]) == float:
            i += 1
    else:
        i = len(rrange)
        while type(rrange[i - 1][field]) == float:
            i -= 1
    return rrange[i:] + rrange[:i]      
 
def byte_sort( rrange, specs):
    for spec in specs[::-1]:
        field, rev, textfirst, ignore_case, isnatural = map(int, spec )
        rrange.sort(key=lambda e: e[field].lower()
                    if ignore_case and type(e[field])==unicode
                    else e[field], reverse=rev)
        if textfirst:
            rrange = texttofront(rrange, field, rev)
    return rrange
 
def CheckFields(inArray,maxfields):
    outArray = []
    speclen = len(inArray)
    speclen0 = len(inArray[0])
 
    fieldnumbersin = [getfieldnumber(inArray[i][0],maxfields) for i in range(speclen)]
    fieldnumbersout = []
 
    for fieldnumber in fieldnumbersin:
        if fieldnumber not in fieldnumbersout:
            fieldnumbersout.append(fieldnumber)
        else:
            fieldnumbersout.append(-1)
 
    testfields = [tuple([fieldnumbersout[i]] + [getfieldvalue(inArray[i][1],j) if speclen0 > 1 else 0 for j in range(0,4)]) for i in range(speclen)]
 
    for i in range(len(testfields)):
        if testfields[i][0] != -1:
            outArray.append(tuple(testfields[i]))
 
    return outArray
 
def getfieldnumber(n,maxfields):
    if type(n) == float:
        return int(n) if 0 <= n < maxfields else -1
    elif type(n) == int:
        return n if 0 <= n < maxfields else -1
    else:
        return -1 
 
def getfieldvalue(s,i):
    flags = ["r","t","c","n"]
    if type(s) == unicode:
        return 1 if flags[i] in s.lower() else 0
    else:
        return 0 
 
def createInstance( ctx ):
    return PySortImpl( ctx )
 
g_ImplementationHelper = unohelper.ImplementationHelper()
g_ImplementationHelper.addImplementation(
   createInstance,"com.pysort.python.PySortImpl",
      ("com.sun.star.sheet.AddIn",),)

Process of the script

This script uses the power of python to re-implement the sort functionality through a pythonesque functionality to enhance and costumize. The code is internationalized so it will work over different locales. It also carries the conversion between the cmp formulas to a key formula which allow us to have a standarized toolset.