[Python] Sort CellRange across rows/coumns.

Creating Extension - Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This forum is not for asking questions about writing your own macros.

[Python] Sort CellRange across rows/coumns.

Postby Charlie Young » Tue Oct 21, 2014 9:27 am

Looking at DrNakah's Sorting frustration post in the Calc forum, it occurred to me that it would be relatively straightforward to adapt the code of the Array Sort add-in to this purpose, so here goes.

At the top of the code, notice we have a list of 6 specs specifying the sort.

Code: Select all   Expand viewCollapse view
   
    #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   Expand viewCollapse view
   
    #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   Expand viewCollapse view
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,
Last edited by Charlie Young on Tue Oct 21, 2014 5:16 pm, edited 2 times in total.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: [Python] Sort CellRange across rows/coumns.

Postby Charlie Young » Tue Oct 21, 2014 5:00 pm

Duh.

Well it's probably an obscure bug, but as written, there is a conflict in the ascii collation option: the ignore case option will override the natural key option in this piece of the code

Code: Select all   Expand viewCollapse view
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:
        rrange.sort(key=lambda e: e.lower()
                    if ignore_case and type(e) == unicode
                    else naturalkey(e)
                    if isnatural and type(e) == unicode
                    else e, reverse=rev)
        if TextFirst:
            rrange = texttofront(rrange, rev)


I think this fixes it, and I'm also going to edit the previous post.

Code: Select all   Expand viewCollapse view

    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)



So here is the full revised code

Code: Select all   Expand viewCollapse view
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,
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 3 guests