[PYTHON/OPENPYXL] 엑셀 컬럼 위치 변환하기

■ 엑셀 컬럼 위치를 변환하는 방법을 보여준다.

▶ main.py


from string import ascii_uppercase

def getIndex(coordinate):
    columnString = "".join(filter(str.isalpha, coordinate.upper()))
    rowString    = "".join(filter(str.isdigit, coordinate))

    row    = int(rowString)
    column = 0

    for i, character in enumerate(reversed(columnString)):
        column += (ascii_uppercase.index(character) + 1) * (26 ** i)

    return (row, column)

def getCoordinate(y, x):
    columnString = ""
    while x > 0:
        x -= 1
        columnString = ascii_uppercase[x % 26] + columnString
        x //= 26
    return f"{columnString}{y}"

print(getIndex("A1"   )) # (1, 1)
print(getIndex("B2"   )) # (2, 2)
print(getIndex("AA1"  )) # (1, 27)
print(getIndex("ZZ100")) # (100, 702)

print(getCoordinate(  1,   1)) # "A1"
print(getCoordinate(  2,   2)) # "B2"
print(getCoordinate(  1,  27)) # "AA1"
print(getCoordinate(100, 702)) # "ZZ100"

from string import ascii_uppercase

def getIndex(coordinate):

columnString = "".join(filter(str.isalpha, coordinate.upper()))

rowString = "".join(filter(str.isdigit, coordinate))

row = int(rowString)

column = 0

for i, character in enumerate(reversed(columnString)):

column += (ascii_uppercase.index(character) + 1) * (26 ** i)

return (row, column)

def getCoordinate(y, x):

columnString = ""

while x > 0:

x -= 1

columnString = ascii_uppercase[x % 26] + columnString

x //= 26

return f"{columnString}{y}"

print(getIndex("A1" )) # (1, 1)

print(getIndex("B2" )) # (2, 2)

print(getIndex("AA1" )) # (1, 27)

print(getIndex("ZZ100")) # (100, 702)

print(getCoordinate( 1, 1)) # "A1"

print(getCoordinate( 2, 2)) # "B2"

print(getCoordinate( 1, 27)) # "AA1"

print(getCoordinate(100, 702)) # "ZZ100"