问从openpyxl中的坐标值获取行号和列号
EN

Stack Overflow用户

提问于 2012-10-15 19:26:23

回答 9查看 108.4K关注 0票数 46

我正在尝试将excel中的坐标值转换为openpyxl中的行号和列号。

例如，如果我的单元格坐标是D4，我想要找到相应的行号和列号，以便在以后的操作中使用，在row = 3，column = 3的情况下。我可以使用返回4的ws.cell('D4').row轻松地获得行号，然后只需减去1即可。但是类似的参数ws.cell('D4').column返回D，我不知道如何轻松地将其转换为int形式，以便进行后续操作。所以我向你们这些聪明的stackoverflow朋友们求助。你能帮帮我吗?

openpyxl

python

回答 9

Stack Overflow用户

回答已采纳

发布于 2012-10-15 19:39:30

您需要的是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]

票数 73

Stack Overflow用户

发布于 2015-11-08 20:30:47

openpyxl有一个名为get_column_letter的函数，可以将数字转换为列字母。

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

1 --> A

50 --> AX

1234-- AUL

我一直在这样使用它：

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")

票数 65

Stack Overflow用户

发布于 2015-10-26 20:46:27

这是基于内森的回答。基本上，当行和/或列的宽度超过一个字符时，他的答案不能正常工作。抱歉，我有点过火了。下面是完整的脚本：

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和一堆评论...

# 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? ;)')