getting the row and column numbers from coordinate value in openpyxl

What you want is openpyxl.utils.coordinate_from_string() and openpyxl.utils.column_index_from_string()

from openpyxl.utils.cell import coordinate_from_string, column_index_from_stringxy = coordinate_from_string('A4') # returns ('A',4)col = column_index_from_string(xy[0]) # returns 1row = xy[1]

openpyxl has a function called get_column_letter that converts a number to a column letter.

from openpyxl.utils import get_column_letterprint(get_column_letter(1))

1 --> A

50 --> AX

1234-- AUL

I have been using it like:

from openpyxl import Workbookfrom openpyxl.utils import get_column_letter#create excel type itemwb = Workbook()# select the active worksheetws = wb.activecounter = 0for column in range(1,6):    column_letter = get_column_letter(column)    for row in range(1,11):        counter = counter +1        ws[column_letter + str(row)] = counterwb.save("sample.xlsx")

This is building off of Nathan's answer. Basically, his answer does not work properly when the row and/or column is more than one character wide. Sorry - I went a little over board. Here is the full script:

def main():    from sys import argv, stderr    cells = None    if len(argv) == 1:        cells = ['Ab102', 'C10', 'AFHE3920']    else:        cells = argv[1:]    from re import match as rematch    for cell in cells:        cell = cell.lower()        # generate matched object via regex (groups grouped by parentheses)        m = rematch('([a-z]+)([0-9]+)', cell)        if m is None:            from sys import stderr            print('Invalid cell: {}'.format(cell), file=stderr)        else:            row = 0            for ch in m.group(1):                # ord('a') == 97, so ord(ch) - 96 == 1                row += ord(ch) - 96            col = int(m.group(2))            print('Cell: [{},{}] '.format(row, col))if __name__ == '__main__':    main()

Tl;dr with a bunch of comments...

# make cells with multiple characters in length for row/column# feel free to change these valuescells = ['Ab102', 'C10', 'AFHE3920']# import regexfrom re import match as rematch# run through all the cells we madefor cell in cells:    # make sure the cells are lower-case ... just easier    cell = cell.lower()    # generate matched object via regex (groups grouped by parentheses)    ############################################################################    # [a-z] matches a character that is a lower-case letter    # [0-9] matches a character that is a number    # The + means there must be at least one and repeats for the character it matches    # the parentheses group the objects (useful with .group())    m = rematch('([a-z]+)([0-9]+)', cell)    # if m is None, then there was no match    if m is None:        # let's tell the user that there was no match because it was an invalid cell        from sys import stderr        print('Invalid cell: {}'.format(cell), file=stderr)    else:        # we have a valid cell!        # let's grab the row and column from it        row = 0        # run through all of the characters in m.group(1) (the letter part)        for ch in m.group(1):            # ord('a') == 97, so ord(ch) - 96 == 1            row += ord(ch) - 96        col = int(m.group(2))        # phew! that was a lot of work for one cell ;)        print('Cell: [{},{}] '.format(row, col))print('I hope that helps :) ... of course, you could have just used Adam\'s answer,\but that isn\'t as fun, now is it? ;)')