xlsx-dir-map/xlsx_dir_map/spreadsheet.py

from openpyxl import load_workbook

ALPHA = {
    'A': 1,
    'B': 2,
    'C': 3,
    'D': 4,
    'E': 5,
    'F': 6,
    'G': 7,
    'H': 8,
    'I': 9,
    'J': 10,
    'K': 11,
    'L': 12,
    'M': 13,
    'N': 14,
    'O': 15,
    'P': 16,
    'Q': 17,
    'R': 18,
    'S': 19,
    'T': 20,
    'U': 21,
    'V': 22,
    'W': 23,
    'X': 24,
    'Y': 25,
    'Z': 26
}

def parse_range(cellrange):
    cell1 = cellrange.split(":")[0]
    cell2 = cellrange.split(":")[1]

    min_col = ALPHA[cell1[0]]
    min_row = int(cell1[1:])

    max_col = int(ALPHA[cell2[0]])
    max_row = int(cell2[1:])

    return (min_row, min_col, max_row, max_col)

def get_value(row, colrange):
    cols = colrange.split(":")
    indices = [custom_index(row, lambda cell: cell.column_letter == col) for col in cols]
    
    name = " ".join([str(row[index].value) for index in indices])
    return name


def custom_index(array, compare_function):
    for i, v in enumerate(array):
        if compare_function(v):
            return i

        
def get_names_list(filename, cellrange, column_groups):
    wb = load_workbook(filename = filename)
    names = []

    min_row = min_col = max_row = max_col = None

    if cellrange:
        min_row, min_col, max_row, max_col = parse_range(cellrange)

    for row in wb.active.iter_rows(min_row=min_row, min_col=min_col, max_row=max_row, max_col=max_col):
        cells = [cell for cell in row if cell is not None]
        paths = []
        
        for colgroup in column_groups:
            path = get_value(cells, colgroup) 
            if "None" not in path:
                paths.append(path)
    
        # if "None" not in paths:
        names.append("/".join(filter(lambda x: x != '', paths)))
        
    return names