getting the row and column numbers from coordinate value in openpyxl

python openpyxl
A Alstone picture A Alstone · Oct 15, 2012 · Viewed 86.3k times · Source

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

Adam Morris picture Adam Morris · Oct 15, 2012

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]

Related questions

No module named 'openpyxl' - Python 3.4 - Ubuntu

I installed openpyxl with $ pip install openpyxl when I try the command from openpyxl import Workbook I get Traceback (most recent call last): File "<pyshell#0>", line 1, in <module> from openpyxl import Workbook ImportError: No module named …

Read More
Pandas: Looking up the list of sheets in an excel file

The new version of Pandas uses the following interface to load Excel files: read_excel('path_to_file.xls', 'Sheet1', index_col=None, na_values=['NA']) but what if I don't know the sheets that are available? For example, …

Read More
openpyxl - adjust column width size

I have following script which is converting a CSV file to an XLSX file, but my column size is very narrow. Each time I have to drag them with mouse to read data. Does anybody know how to set column …

Read More