getting the row and column numbers from coordinate value in openpyxl

Question

I'm trying to covert a coordinate value in excel to a row number and column number in openpyxl.

For example if my cell coordinate is D4 I want to find the corresponding row and column numbers to use for future operations, in the case row = 3, column = 3. I can get the row number easily using ws.cell('D4').row which returns 4 then it's just a matter of subtracting 1. But a similar argument ws.cell('D4').column returns D and I don't know how to easily get this into int form for subsequent operations. So I turn to you wise folks of stackoverflow. Can you help me?

Answer 1

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_string xy = coordinate_from_string('A4') # returns ('A',4) col = column_index_from_string(xy[0]) # returns 1 row = xy[1] 

Answer 2

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

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

1 --> A

50 --> AX

1234-- AUL

I have been using it like:

from openpyxl import Workbook from openpyxl.utils import get_column_letter  #create excel type item wb = Workbook() # select the active worksheet ws = wb.active  counter = 0 for 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)] = counter  wb.save("sample.xlsx")