Python 从openpyxl中的坐标值获取行号和列号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12902621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
getting the row and column numbers from coordinate value in openpyxl
提问by A Alstone
I'm trying to covert a coordinate value in excel to a row number and column number in openpyxl.
我试图将 excel 中的坐标值转换为 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').rowwhich returns 4then it's just a matter of subtracting 1. But a similar argument ws.cell('D4').columnreturns Dand 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?
例如，如果我的单元格坐标是 D4，我想找到相应的行号和列号以用于将来的操作，在这种情况下，行 = 3，列 = 3。我可以使用ws.cell('D4').rowwhich 返回轻松获取行号 ，4那么这只是一个问题减去 1。但是ws.cell('D4').column返回一个类似的参数D，我不知道如何轻松地将其转换为 int 形式以进行后续操作。所以我求助于stackoverflow的聪明人。你能帮助我吗？
采纳答案by Adam Morris
What you want is openpyxl.utils.coordinate_from_string()and openpyxl.utils.column_index_from_string()
你想要的是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]
回答by Nathan Villaescusa
You could just use pure Python:
你可以只使用纯 Python：
cell = "D4"
col = ord(cell[0]) - 65
row = int(cell[1:]) - 1
This uses the ordfunction which takes a character and returns its ASCII code. In ASCII the letter Ais 65, Bis 66 and so on.
这使用ord接受一个字符并返回其 ASCII 代码的函数。在 ASCII 中，字母A是 65，B是 66，依此类推。
回答by dylnmc
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:
这是建立在 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()
Tl;dr with a bunch of comments...
Tl;博士有一堆评论......
# 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? ;)')
回答by Ohm
openpyxl has a function called get_column_letterthat converts a number to a column letter.
openpyxl 有一个名为get_column_letter的函数，可以将数字转换为列字母。
from openpyxl.utils import get_column_letter
print(get_column_letter(1))
1 --> A
1 --> 一个
50 --> AX
50 --> 斧头
1234-- AUL
第1234章——
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")
回答by Mehdi
Old topic, but the answer is not correct!
老话题，但答案不正确！
dylnmcmethod was a good one, but has some errors. The calculated row for cell coords like "AA1" or "AAB1" is not correct.
dylmc方法是一个很好的方法，但有一些错误。像“AA1”或“AAB1”这样的单元格坐标的计算行不正确。
Below is the corrected version as a function.
以下是作为函数的更正版本。
NOTE:This function returns the real coordinated. If you want to use it for example in ExcelWriter, both ROW and COL should be deducted by one. So replace the last line with return(row-1,col-1)
注意：此函数返回真正的协调。如果要在 ExcelWriter 中使用它，则 ROW 和 COL 都应减一。所以用return(row-1,col-1)替换最后一行
For example 'AA1' is [1,27] and 'AAA1' is [1,703]; but the python must have them as [0,26] and [0,702].
例如'AA1'是[1,27]，'AAA1'是[1,703]；但蟒蛇必须将它们设为 [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]
