Openpyxl: Merge cells with x y. How to transform id to Excel columns letters?

Viewed 167

I'm using openpyxl, I need to merge cells in a dynamic way so here is my difficulty.

Here is the code to merge cells with openpyxl:

wb = Workbook()
sheet = wb. active.
sheet. merge_cells('A1:B2')

Because I need to do it dynamically in for loops, I need to transform [(1,1),(2,2)] to A1:B2 to so, to be more precise: 1 -> A, 2 -> B, 705 -> AAC

def get_idx_of_letter_in_alphabet(letter):
    """ B -> 1 """
    return "abcdefghijklmnopqrstuvwxyz".index(letter.lower())

def get_idx_from_col_letters(letters):
    """ AB -> """
    idx = 0
    for i,l in enumerate(letters):
        print("letter:",l,"id",len(letters)-i-1, "adding",(get_idx_of_letter_in_alphabet(l) + 1) * pow(26, len(letters)-i-1))
        idx += (get_idx_of_letter_in_alphabet(l) + 1) * pow(26, len(letters)-i-1)  #xlsx column A begin with 1, not 0
    return idx

def idx_to_letter(i):
    """1 <= i <= 26"""
    return "abcdefghijklmnopqrstuvwxyz"[i-1]

def get_letters_from_idx(i):
    """ TO DO !!! """
    if i == 3: return "C"
    elif i == 174: return "FR"
    elif i == 175: return "FS"
    elif i == 705: return "AAC"
    return "AAAAAA"

print(get_idx_of_letter_in_alphabet("C"))
print(get_idx_from_col_letters("FR"))
print(idx_to_letter(3))
print(get_letters_from_idx(205))
1 Answers

Sorry, for the bother I just found out the answer.

ws.merge_cells(start_row=1, start_column=1, end_row=2, end_column=4)

SO solution

The algorithm is still an interesting question thought :)

Related