openpyxl sort function _stackoverflow

openpyxl-sort-function.py

# https://stackoverflow.com/questions/44767554/sorting-with-openpyxl


def sheet_sort_rows(ws, row_start, row_end=0, cols=None, sorter=None, reverse=False):
""" Sorts given rows of the sheet
    row_start   First row to be sorted
    row_end     Last row to be sorted (default last row)
    cols        Columns to be considered in sort
    sorter      Function that accepts a tuple of values and
                returns a sortable key
    reverse     Reverse the sort order
"""

bottom = ws.max_row
if row_end == 0:
    row_end = ws.max_row
right = get_column_letter(ws.max_column)



##### for use, example ####
sheet_sort_rows(ws, 5, 10)  # Sort rows 5-10 using key: A, B, C, ...
sheet_sort_rows(ws, 5, 10, [2, 1])  # Sort rows using B, A
sheet_sort_rows(ws, 5, 10, [2, 1], reverse=True)  # As above in reverse


def sorter(t):
    return t[1] + " " + t[0][::-1]

sheet_sort_rows(ws, 5, 10, sorter=sorter)
if cols is None:
    cols = range(1, ws.max_column+1)

array = {}
for row in range(row_start, row_end+1):
    key = []
    for col in cols:
        key.append(ws.cell(row, col).value)
    key = tuple(key)  # Convert list to tuple
    array[key] = array.get(key, set()).union({row})

order = sorted(array, key=sorter, reverse=reverse)

ws.move_range(f"A{row_start}:{right}{row_end}", bottom)
dest = row_start
for src_key in order:
    for row in array[src_key]:
        src = row + bottom
        dist = dest - src
        ws.move_range(f"A{src}:{right}{src}", dist)
        dest += 1
        
        
###### use

sheet_sort_rows(ws, 5, 20, [3])  # Sort rows 5-20 using key: A, B, C, ... by column[3]

sheet_sort_rows(ws, 5, 10)  # Sort rows 5-10 using key: A, B, C, ...
sheet_sort_rows(ws, 5, 10, [2, 1])  # Sort rows using B, A
sheet_sort_rows(ws, 5, 10, [2, 1], reverse=True)  # As above in reverse


def sorter(t):
    return t[1] + " " + t[0][::-1]

sheet_sort_rows(ws, 5, 10, sorter=sorter)