你需要的是 openpyxl.utils.coordinate_from_string() 和 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]
openpyxl有一个名为get_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")
在openpyxl.utils.cell模块中有一个方法可以满足所需功能。该方法名为openpyxl.utils.cell.coordinate_to_tuple()，它将字母数字格式的Excel坐标作为字符串输入，并将这些坐标作为整数元组返回。
openpyxl.utils.cell.coordinate_to_tuple('B1')
>> (1, 2)
这是基于Nathan的回答。基本上，当行和/或列超过一个字符宽度时，他的答案无法正常工作。抱歉-我有点过了。以下是完整的脚本：
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()
# make cells with multiple characters in length for row/column
# feel free to change these values
cells = ['Ab102', 'C10', 'AFHE3920']
# import regex
from re import match as rematch
# run through all the cells we made
for 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? ;)')
老话题了，但答案不正确！
dylnmc 方法是个好方法，但有一些错误。计算单元格坐标如 "AA1" 或者 "AAB1" 的结果是不正确的。
以下是修正版本作为一个函数。
注意：这个函数返回真实的坐标。如果你想在 ExcelWriter 中使用它，ROW 和 COL 都应该减去一。所以用return(row-1,col-1)替换最后一行。
例如 'AA1' 是 [1,27]，'AAA1' 是 [1,703]；但在 Python 中它们必须是 [0,26] 和 [0,702]。
import re
def coord2num(coord):
cell = coord.lower()
# generate matched object via regex (groups grouped by parentheses)
m = re.match('([a-z]+)([0-9]+)', cell)
if m is None:
print('Invalid cell: {}'.format(cell))
return [None,None]
else:
col = 0
for i,ch in enumerate(m.group(1)[::-1]):
n = ord(ch)-96
col+=(26**i)*(n)
row = int(m.group(2))
return[row,col]
这将给出列号
col = "BHF"
num = 0
for i in range(len(col)):
num = num + (ord(col[i]) - 64) * pow(26, len(col) - 1 - i)
print(num)
def col_row(s):
""" 'AA13' -> (27, 13) """
def col_num(col):
""" 'AA' -> 27 """
s = 0
for i, char in enumerate(reversed(col)):
p = (26 ** i)
s += (1 + ord(char.upper()) - ord('A')) * p
return s
def split_A1(s):
""" 'AA13' -> (AA, 13) """
for i, char in enumerate(s):
if char.isdigit():
return s[:i], int(s[i:])
col, row = split_A1(s)
return col_num(col), row
col_row('ABC13')
# Out[124]: (731, 13)
你可以直接使用纯Python：
cell = "D4"
col = ord(cell[0]) - 65
row = int(cell[1:]) - 1
这使用了 ord 函数，它接受一个字符并返回其 ASCII 码。在 ASCII 中，字母 A 是 65，B 是 66，依此类推。
openpyxl-utilities==0.5（2019年）之后，您可以通过使用from openpyxl.utils.cell import coordinate_from_string, column_index_from_string进行导入。 - mwag